Fork me on GitHub
#sql
<
2020-03-31
>
bartuka11:03:23

hi all! When I use sql/insert! and pass a map to it, I have to "rename" my map field and convert "-" to underscore, what would be the approach to make it be automatically converted?

seancorfield14:03:21

@iagwanderson next.jdbc? Sounds like you want the :column-fn option and something like the camel snake kebab library?

dharrigan12:03:03

I use honeysql with next.jdbc, and the kebab to snakecase happens for me automatically

boyanb13:03:00

anyone implemented Readable column for tsrange in psql?

boyanb13:03:34

Hm, nevermind, that feels wrong to do, tbh. Any implementation would lose the idea of "range".

✔️ 4
bartuka14:03:28

@seancorfield yes I am using next.jdbc, lack that info. didn't know this keyword !! thanks

seancorfield14:03:58

Read "All the Options" in the documentation -- may be other options you haven't seen yet that would help you

🚀 4
Saikyun16:03:51

hello, I use next.jdbc -- is it possible to see the sql generated from e.g. sql/update! ? currently having a hard time debugging

bfabry16:03:08

reimplement this as a wrapper for your Executable/connectable but with logging? https://github.com/seancorfield/next-jdbc/blob/master/src/next/jdbc/protocols.clj#L33

bfabry16:03:06

aside: @seancorfield why's update! and execute-one! call the first parameter "connectable" but then call "Executable" protocol functions on it?

seancorfield17:03:44

@U050MP39D Because "naming is hard" and it's a bit more descriptive of what's really happening: the Executable implementations need to obtain a Connection first before they execute the SQL. So you can pass a db-spec hash map, a JDBC string, a DataSource, or a Connection -- or a Statement, and only the latter is directly "executable".

👍 4
seancorfield17:03:46

(but it's a very valid question -- initially the transact/`with-transaction` functions took a connectable but that was renamed to transactable via a community PR).

Saikyun05:04:42

@U050MP39D thanks for the tip, but I don't quite understand what you mean. either way, will try @seancorfield suggestion first 🙂

seancorfield16:03:28

@saikyun You can call next.jdbc.sql.builder/for-update with the table key-map where-params opts arguments you are passing to update! (without the connectable one).

Saikyun05:04:49

aha, thanks. I'll try it out! 🙂

seancorfield16:03:25

That will return the vector of SQL string and the list of parameter values that will be passed to JDBC (via the execute-one! function).

Andreas Edvardsson18:03:22

@seancorfield Hi Sean, using next-jdbc, I am querying a table containing a BLOB field, and am trying to get a column reader parsing that. I have almost got it working, but am a bit perplexed about the object type in the result set for the corresponding column. I the case of H2 database it seems to indeed implement java.sql.Blob, but when using the MySQL adapter the type seems to be a byte array [B. Have I screwed up somehow or is it just the way the MySQL adapter works?

seancorfield18:03:33

@andreas179 Hard to tell without seeing code. I've never tried to use BLOB with MySQL so it's entirely possible it returns a byte array instead of java.sql.Blob -- have you looked at the MySQL docs for the connector?

seancorfield18:03:41

Yeah, just tested it locally and it looks like the MySQL connector returns BLOB as byte[]

seancorfield18:03:02

JDBC is notoriously non-portable once you get away from the basics... ¯\(ツ)

seancorfield18:03:26

I guess I'll update the CLOB & BLOB section of the Tips & Tricks documentation to mention that...

Andreas Edvardsson18:03:58

Looking in the docs (which I probably should have done before asking) reveals that the return value of GetColumnCalssName should return byte[] as you confirmed. However, the docs also states that the BLOB sql type always can be converted to java.sql.Blob among other. Oh well, now that I know what’s happening I can work around it. Thanks for a great library, docs are extensive and easy to follow as well! :)

seancorfield19:03:10

Thanks. The one thing I will say about JDBC is that after maintaining Clojure wrappers around JDBC for nine years, I am still constantly learning "fascinating" new things about JDBC 🙂

Andreas Edvardsson19:03:21

Awesome! At least we’re getting something battle-tested, once one’ve worked around all quirks:sunglasses:

seancorfield21:03:48

I can tell you that clojure.java.jdbc and next.jdbc are in extremely heavily daily usage where I work, with MySQL, and I get a lot of feedback from folks using PostgreSQL, so those are probably the two most battle-tested. But I test against a broad range of databases as a matter of course, including MS SQL Server, and I know there are folks using it with many other databases (including some I'd never heard of...).

💯 4