Clojurians Log v2
sql
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&useSSL=true&disableSslHostnameVerification=true%22})|mariadb://host:3306/db_name?verifyServerCertificate=false&useSSL=true&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 trueSo 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.