Clojurians Log v2

Could it be there's a conflict between the table I'm referencing and one of the internal tables used by SQL Server for database users? I suppose if that's so I could disambiguate by providing the schema...

I think you need :quoted true

oh nice! That was it. I was trying :raw which did get me past the syntax error as well.

:raw should pretty much always be a last resort at this point...

duly noted! 🫡

The short answer is: no, not correct. Can you back up and explain more of the context and whether you've actually run into a problem?

PreparedStatement has nothing to do with HoneySQL, so it seems you're asking about some next.jdbc issue here really?

yeah, the root of the problem for me originated in JDBC but I think I was using the HoneySQL DDL in a manner that was producing a SQL string that JDBC didn't like

DDL != SQL -- you mean DSL?

yes

OK, so you have some vector with SQL + params and how are you processing that?

(show code)

Which database? Which driver? Versions of both? What exactly is the error you get? Is this for next.jdbc or c.j.j?

I had a vector of seqs of data like

[(1,2,3),(4,5,6)...]

{:insert-into :a-table
             :columns [:a-column :another :a-third]
             :values [(1,2,3),(4,5,6)]}

["INSERT INTO a-table (a-column, another, a third) values (?, ?, ?), (?,?,?)"]

the conversion from UNKNOWN to UNKNOWN is unsupported

What I ended up doing is only using the :insert-into and :columns keys and adding the rest by hand

which works

> and it was producing Presumably with 1 2 3 4 5 6 in that vector as well?

You're still not describing any of the JDBC side of this -- HoneySQL does not execute SQL.

this is the non-working code

(let [[sql & param-groups]  (sql/format insert-pop-data :quoted true)]
    (try (jdbc/execute-batch! ds sql [(partition 26 param-groups)] {:batch-size 100
                                                                    :return-keys true})
         (catch SQLServerException e
           (Throwable->map e))))

(with-open [conn (jdbc/get-connection ds)
              stmt (jdbc/prepare conn insert-pop-data)]
    (jdbc/execute-batch! stmt test-data {:batch-size 100
                                         :return-keys true}))

OK, execute-batch! is kind of weird -- it doesn't take a standard [SQL+params]` vector

If you use regular execute!, the generated HoneySQL stuff works, but that won't necessarily get you the fast batch insertion.

yeah I moved to execute-batch! from execute! because I was hitting the TDS parameter limit

What you can do in have a HoneySQL DSL with just one row of (dummy) data -- dummy, since you'll throw it away -- and generate [SQL+params] which will have the dummy params and then use the SQL portion with a partition of your original data. But you can't just pass all that data into HoneySQL if your target is execute-batch!

The issue is that the SQL needed for execute-batch! is as-if you are inserting only one row, but then you pass multiple rows to execute-batch! itself and it does the "magic" behind the scenes.

execute! uses PreparedStatement and .execute() under the hood so this is not a limitation of PreparedStatement. execute-batch! uses .executeBatch() under the hood so it's different at the JDBC level.

somewhat awful but the hack I did was

(-> (sql/format {:insert-into :table
                 :columns [:a, :b, :c]}
   first
   (str " VALUES (?,?,?)")
   vector)

That is certainly up there on the list of things which are "not great", yes 😄

:values [(first data)] where data is all your rows.

Just watch out for nil and true / false getting generated inline...

OK, gotta run.

I spent a long time banging my head against the wall over this, largely due to that stunningly opaque error cited above, so thank you for your help and insight!

Yeah, SQL Server (or its driver) is being spectacularly unhelpful there...

Unfortunately, this is just one of those JDBC oddities for which there is no really good solution re: HoneySQL since it is a generic formatter. As you can see, if you start with a vector of vectors (rows) of data, it's that vector you need to pass to execute-batch! and the parameters in the SQL+params vector produced by HoneySQL really don't matter (after all, why would you pass vector of vectors to HoneySQL only to have it unroll them into that SQL+params data again?).

Anyways, off to the gym now for real.