Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2021-02-23

Channels

#sql
<
2021-02-23
>
the2bears02:02:20

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:                                                                                                                                            
  
(j/query maria-db "SELECT * FROM db_name.some_table_name;")

the2bears02:02:52

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

the2bears02:02:20

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'`

the2bears02:02:39

Anyone tried this, and know the cryptic format? 🙂

seancorfield02:02:39

@U0QNQ3P3L 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.

seancorfield02:02:17

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

👍 3
dpsutton02:02:23

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

seancorfield02:02:50

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.

seancorfield02:02:18

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

the2bears02:02:23

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

the2bears02:02:30

But the :user works

seancorfield02:02:46

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

dpsutton02:02:55

> 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

👍 3
seancorfield02:02:59

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

dpsutton02:02:10

is a SO post about the unimplemented part for the maria jdbc driver

👍 3
seancorfield02:02:31

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

seancorfield02:02:24

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

the2bears02:02:43

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)

the2bears02:02:22

Very cool about the extra options to the URI!

seancorfield02:02:34

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

seancorfield02:02:03

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

seancorfield02:02:30

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

the2bears02:02:30

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

Nazral02:02:19

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

seancorfield02:02:52

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

seancorfield02:02:19

@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."

Nazral02:02:09

I completely missed that, thanks!

seancorfield02:02:22

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

seancorfield02:02:35

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

Nazral02:02:10

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

seancorfield02:02:32

They are inserts so, they just insert.

Nazral02:02:01

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

seancorfield02:02:27

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

Nazral02:02:54

ok, will do that, thank you

seancorfield02:02:56

What DB are you using?

seancorfield02:02:14

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

seancorfield02:02:47

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).

Nazral02:02:10

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

seancorfield04:02:57

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

Nazral04:02:10

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

seancorfield04:02:48

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.