Clojurians Log v2

Hi, Been awhile since I've used JDBC. I'm having some difficulty getting a connection to a SingleStore (MemSQL)

(def maria-db                                                                                                                                                                                                                                                                              
  {:classname "org.mariadb.jdbc.Driver"                                                                                                                                                                                                                                                    
   :dbname            "db_name"                                                                                                                                                                                                                                                            
   :maximum-pool-size  5                                                                                                                                                                                                                                                                   
   :pool-name          "db-pool"                                                                                                                                                                                                                                                           
   :username           "xxxxx"                                                                                                                                                                                                                                                             
   :password           "yyyyy"                                                                                                                                                                                                                                                             
   :connection-uri           "jdbc:<mariadb://host:3306/db_name?verifyServerCertificate=false&amp;useSSL=true&amp;disableSslHostnameVerification=true%22})|mariadb://host:3306/db_name?verifyServerCertificate=false&amp;useSSL=true&amp;disableSslHostnameVerification=true"})>                                                                                                                                            
  
(j/query maria-db "SELECT * FROM db_name.some_table_name;")                                                                                                                                                                                    

Getting this error message: `(conn=41911492) Access denied for user 'williamswaney'@'z.z.z.z' (using password: NO)`

So it's not getting the username from the config map, nor the password. I tried them in the URI and this time it seems to accept the user and password, but: `Current charset is UTF-8. If password has been set using other charset, consider using option 'passwordCharacterEncoding'`

Anyone tried this, and know the cryptic format? 🙂

@the2bears The intent is that you either provide :connection-uri or you provide other keys and let the Clojure library build the URI for you. You can't provide both.

Also, it's :user, not :username (only HikariCP requires :username -- no idea why it is the odd one out).

in the connection string, the mysql/maria driver has a bug that they aren't handling things correctly ever since the changes in auth for version 8

I'm a bit puzzled what :maximum-pool-size and :pool-name are meant to be -- those aren't meaningful to clojure.java.jdbc (or next.jdbc) and they won't be valid JDBC properties either with those names.

@dpsutton His connection string does not have credentials in it.

Yeah, those are left in from trying both the j/query and a hikari pool 🙂

But the :user works

Remove :connection-uri and let the library build it for you.

> I tried them in the URI and this time it seems to accept the user and password, but: that's what i was responding to

(def maria-db
  {:dbtype "mariadb" :classname "org.mariadb.jdbc.Driver"
   :dbname "db_name"
   :user "xxxxx"
   :password "yyyyy"})

<https://stackoverflow.com/questions/54188309/mariadb-connection-client-access-denied-for-user-using-password-no-on-mysql> is a SO post about the unimplemented part for the maria jdbc driver

You only need :classname for clojure.java.jdbc because next.jdbc already knows the mariadb data type.

If you need verifyServerCertificate=false&useSSL=true&disableSslHostnameVerification=true added to the URI that is constructed, pass those in the hash map as well:

:verifyServerCertificate false
   :useSSL true
   :disableSslHostnameVerification true

So instead of :connection-uri just use :host, :port, :dbtype (not using next.jdbc yet, but might as well as this is just testing for now)

Very cool about the extra options to the URI!

You only need :host and :port if they are non-local/non-standard

(although with c.j.j it may need to be told :port 3306 for mariadb)

Hello, I am trying to use jdbc to do some batch insert using jdbc/db-do-commands and a begin end block, but it doesn't work, I am not sure what would be the best approach

So, yeah: dbtype, classname, dbname, host, port, user, password, other options you need.

Cool, thanks for the help @seancorfield and @dpsutton. Working with :user and I'll play around with next.jdbc and the other suggestions.

db-do-commands is intended for DDL, not SQL.

@archibald.pontier_clo If you're just getting started with JDBC in Clojure, take note of what the clojure.java.jdbc README says on GitHub: "A low-level Clojure wrapper for JDBC-based access to databases. This project is "Stable" (no longer "Active"). It has effectively been superseded by seancorfield/next.jdbc."

I completely missed that, thanks!

Both c.j.j and next.jdbc have specific ways to do batch inserts but they are different. next.jdbc has much better documentation 🙂

If you want to insert multiple rows with c.j.j see http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql.html#inserting-multiple-rows

If you want to insert multiple rows with next.jdbc see https://cljdoc.org/d/seancorfield/next.jdbc/1.1.613/doc/getting-started/friendly-sql-functions#insert-multi

But there are some caveats about batch insertion -- as noted in that doc, and it links you to execute-batch! for large batches.

I saw the doc for c.j.j about multiple inserts, but does it update if the row already exists ?

They are inserts so, they just insert.

what I am trying to reproduce is a batch INSERT INTO mytable VALUES (...) ON DUPLICATE KEY UPDATE ...

If you want more control, you need to supply the SQL yourself with execute-batch! in next.jdbc.

ok, will do that, thank you

What DB are you using?

mariadb

Ah, I figured it was MySQL maybe since ON DUPLICATE KEY is not standard SQL.

Which reminds me, I need to add support for that in HoneySQL 2.0 under the :mysql dialect (it already supports all the PostgreSQL upserting stuff).

is the difference between execute-batch! and execute! mainly that execute-batch wraps the query into a begin ... end ?

@archibald.pontier_clo No. Did the documentation not make sense to you?

Did you read https://cljdoc.org/d/seancorfield/next.jdbc/1.1.613/doc/getting-started/prepared-statements ?

yes I got it to works in the meantime, thanks 😛

Unfortunately, there's quite a lot of JDBC machinery that you need to be aware of to work effectively with databases -- and every database has its own strange quirks.