@vale Are you around? (Hopefully, since you were just commenting on GitHub)

I'm curious to know what database/driver you're using and what SQL column type, such that .setObject() with a java.util.Date object is not handled correctly.

https://stackoverflow.com/questions/25536969/spring-jdbc-postgres-sql-java-8-conversion-from-to-localdate shows that error for Java Time types (not java.util.Date) and it sounds like recent PostgreSQL drivers should handle that automatically...?

it's postgresql (official docker image 10.10), dependencies:

[org.postgresql/postgresql "42.2.8"]
                 [seancorfield/next.jdbc "1.0.9"]

with a datasource at app.db.core/datasource, the following happens:

user=> (next.jdbc/execute! app.db.core/datasource ["create temporary table temp_table (id serial primary key, deadline timestamp not null)"])
[#:next.jdbc{:update-count 0}]
user=> (next.jdbc/execute! app.db.core/datasource ["INSERT INTO temp_table (deadline) VALUES (?) RETURNING *" #inst "2019-11-20T00:00:00.000-00:00"])
Execution error at user/eval29002$fn (form-init4154366754578911482.clj:6).
PSQLException Can't infer the SQL type to use for an instance of java.util.Date. Use setObject() with an explicit Types value to specify the type to use.
	org.postgresql.jdbc.PgPreparedStatement.setObject (PgPreparedStatement.java:955)
	com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject (HikariProxyPreparedStatement.java:-1)
	next.jdbc.prepare/eval1872/fn--1873 (prepare.clj:36)
	next.jdbc.prepare/eval1872/fn--1873 (prepare.clj:-1)
	next.jdbc.prepare/eval1847/fn--1848/G--1838--1857 (prepare.clj:23)
	next.jdbc.prepare/set-parameters (prepare.clj:49)
	next.jdbc.prepare/set-parameters (prepare.clj:42)
	next.jdbc.prepare/create (prepare.clj:133)
	next.jdbc.prepare/create (prepare.clj:77)
	next.jdbc.result-set/eval2286/fn--2294 (result_set.clj:575)
	next.jdbc.protocols/eval1626/fn--1657/G--1617--1666 (protocols.clj:33)
	next.jdbc/execute! (jdbc.clj:188)

i'm using [org.clojure/clojure "1.10.1"] on the clojure:lein-alpine docker image

Interesting. Could you try it with a plain datasource instead of the HikariCP datasource? (so jdbc/get-datasource instead of conn/->pool)

same result

PSQLException Can't infer the SQL type to use for an instance of java.util.Date. Use setObject() with an explicit Types value to specify the type to use.
	org.postgresql.jdbc.PgPreparedStatement.setObject (PgPreparedStatement.java:955)
	next.jdbc.prepare/eval1872/fn--1873 (prepare.clj:36)

Weird. That looks so basic and I've never seen that fail on any other database. I swear PostgreSQL is the weirdest thing out there.

Let me see if I can create a repro based on what you've shown above...

On a related note, do you have any opinions about handling enums in postgres? It's a chore that i have to manually cast to the enum type because it attempts to insert as varchar

My opinion on that is that MySQL totally gets that right: you pass a string, it inserts it just fine into an enum 🙂

I have a repro of your failure. That is very weird.

(can I just say, I hate PostgreSQL? This kind of stuff just plain works in every other database...)

i'm sorry i have a preference for it haha

but i agree that's really weird

OK, well, I'm going to commit this (breaking) test and see if I can figure out WTF is "wrong" with PostgreSQL (since this works flawlessly on other DBs), and I need to see whether this works on clojure.java.jdbc...

another issue:

with-transaction, if not given an option map, expands into a form that ends in (or nil {})

and linters yell at me about that

would ~(or opts {}) work instead of (or ~opts {}) or are there some concerns i may be missing?

Hi! Using next-jdbc, I can’t get the results from a MS SQL Server database to have qualified keywords (:table/column) as it does in the examples and also when I try with SQLite. Does anyone have a hint on what to look for to get qualified keywords with MSSQL?

(jdbc driver is com.microsoft.sqlserver/mssql-jdbc {:mvn/version “7.4.1.jre8”})

It's possible ms driver doesn't support getTableName without some additional options being passed.

I thought I mentioned that in the docs (Oracle doesn't support it at all, even with an option to all for more data).

When I get to my desk in a few hours I'll look that up for you, if you haven't already found it

That might be it. I see now that you wrote about that in the Oracle chapter, but I didn’t look there.

If that’s the case, does that mean I’m out of luck, or could there be a workaround somehow?

Hmm. It does look like for example (-> rs .getMetaData (.getTableName 2)) returns an empty string.

@seancorfield, I made a workaround that works but requires some discipline when writing queries and use as for every column. Could perhaps be a workaround worth to add to the docs for databases that returns blank table names. Thanks for pointing me in the right direction.

(ns user
  (:require
    [clojure.string :as str]
    [next.jdbc :as jdbc]
    [next.jdbc.result-set :as rs]
    [camel-snake-kebab.core :as csk]))

(defn ->qualified-keyword 
  "Turns a namespaced, PascalCased label into a string representing a qualified keyword.
  Namespaces are indicated with underscore.
  Example: ResourceType_CreatedDate becomes resource-type/created-date"
  [namespaced-label]
  (->> (str/split namespaced-label #"_") (map csk/->kebab-case) (str/join "/")))

(defn as-qualified-keyword-maps [rs opts]
  (rs/as-modified-maps rs (assoc opts :qualifier-fn ->qualified-keyword :label-fn ->qualified-keyword))) 

(def db "jdbc:sqlserver://...")
(def ds (jdbc/get-datasource db))

;; select * doesn't get qualified keywords
(jdbc/execute! ds ["select * from ResourceType"] {:builder-fn as-qualified-keyword-maps})
;; ... as TableName_ColumnName is turned into :table-name/column-name
(jdbc/execute! ds ["select CreatedDate as ResourceType_CreatedDate from ResourceType"] 
               {:builder-fn as-qualified-keyword-maps})

Thanks. I'll update the docs to indicate sql server might also not give qualified column names.

@seancorfield I'm starting to use next jdbc and have a couple questions that are probably detailed somewhere, but I haven't been able to find them. First, "SQL + parameters" seems to be a vector with a "parameterized" string with following items being the values of parameters. Is this defined anywhere? From examples it looks like a 'parameter' is a positional '?' but if you have more than one are they still just '?' or '?i', i in (range 1 n). Also presumably they are substituted in order with the remaining vector elements, correct? Second, it looks like 'result sets' can be customized or at least given this form: "but the row builder and result set builder machinery is open and alternatives are provided to produce unqualified keywords as column names, and to produce a vector the column names followed by vectors of column values for each row". Where is this described? I need that because I have queries where the same table (and column) is aliased as 'different' tables and so only the last column and value is included in the default result set maps.

I think I have figured out 2 above.

SQL + parameters: see https://cljdoc.org/d/seancorfield/next.jdbc/1.0.9/doc/getting-started/friendly-sql-functions#insert for example

I would recommend you use the default qualified keyword approach -- or are you migrating from clojure.java.jdbc?

The default cannot work with a table 'aliased' multiple times, because the result set maps just return the 'last' value of the table column value, i.e., since the multiple values come from the same table and column, in the return map you just get the last k/v pair.

Here's an example (from what I'm working on): select be.name,sfq1.value,sfq2.value from bioentry as be, seqfeature as sf, seqfeature_qualifier_value as sfq1, seqfeature_qualifier_value as sfq2 ...

with the default (jdbc/execute! ds [qstg]) you get vector of maps like this one:

{:bioentry/name "NC_000067",
  :seqfeature_qualifier_value/value
  "Derived by automated computational analysis using gene prediction method: Gnomon."}

with

(jdbc/execute! ds [qstg] {:builder-fn rs/as-unqualified-arrays})

["NC_000067"
  "Xkr4"
  "Derived by automated computational analysis using gene prediction method: Gnomon."]

The latter is correct, the former is not.

I found the result-set chapter document and maybe the former could be 'fixed' by using :as-modified-maps with a :label-fn and :qualifier-fn also given.

Ah, I misunderstood what you were trying to do. Yeah, that's an interesting case. I would recommend aliasing the column in the SQL: select be.name, sf1.value as sf1_value, sf2.value as sf2_value ...

Good point - that would also work. But I actually like the simple vector of vector approach

clojure.java.jdbc would have produced value and value_1 if I recall correctly (which would be just as confusing in this case since value_1 would have come from sf2!).

Sure. And the vector of vectors format works well with insert-multi! if you need that.

This thing is nice - thanks for the work!!

If you can confirm that (either :scroll-sensitive or :scroll-insensitive should work), I will note that in the docs. I'm adding a MS SQL Server section to the Tips & Tricks part of Friendly SQL Functions...

Hmm, that did something different. I’ll try that on something more real and see if it works.

Syntax error (IllegalArgumentException) compiling at (REPL:1:17).
:concurrency, :cursors, and :result-type may not be specified independently.

But

{:result-type :scroll-sensitive :concurrency :updatable})))

I probably need to read up on :concurrency, :cursors and :result-type to make some good choices here, but it seems to work! Thanks!

Thanks. I'd forgotten you needed to specify additional options with that...

If you happen to have a good resource to read for which combinations to choose in which situations, that would be much appreciated (but only if you have one you know about, I’m not asking you to google for me)

@niclasnilsson I suspect I'd have to go digging through the MS SQL Server JDBC documentation for that...

Don’t, I’ll do that myself.

Thanks for you help, this was really nice. The code I posted above turned out to be buggy anyway since it created a keyword that looked like a qualified keyword, but wasn’t. Probably due to next-jdbc merging “” with “namespace/label” which turns into a non qualified kw that looks quite alike a qualified one upon visual inspection. So this solutions was much, much better

Ouch! Glad you have it working properly now. I updated the docs (on master) to clarify that :result-type requires :concurrency as well.

@jsa-aerial I've updated the docs (on master) to clarify column conflicts can arise even with qualified column names. Thank you!

@seancorfield I have another question vis-a-vis SQL + parameter(s). I'm trying to figure out how to pass a set of things to be substituted. Specific example: instead of hard coding where be.name in (\"NC_000067\", \"NC_000077\") in the sql string or (?,?) and pass two strings, is there a way to have ... in (?) and pass a list / vec so that it works? I've tried a couple permutations, but the server complains about bad syntax

Oh, wait maybe I see what I did wrong.

No, I'm wrong about knowing what I did wrong 🙂. So, is there a canonical way to do this?

Normally you need ? for each parameter so it would be ["select ... where be.name in (?,?)" "NC_000067" "NC_000077"] (for two values)

But PostgreSQL has trick that lets you use an array -- see https://cljdoc.org/d/seancorfield/next.jdbc/1.0.9/doc/getting-started/friendly-sql-functions#postgresql

BTW, this is why the docs also recommend looking at things like honeysql since that knows how to deal with the multiple-`?`/multiple-parameters aspect of in when it generates SQL @jsa-aerial

Thanks - I'm using MySql so that PostgresSQL trick doesn't apply. Oh well.

@jsa-aerial HoneySQL might become your best friend then 🙂