sql

All things SQL and JDBC...
Saikyun 2020-08-18T12:52:27.007100Z

we have a procedure that ends with select @@IDENTITY. however, when using jdbc.next/execute! or jdbc.next.sql/query to run the procedure, I only get back the update-count. is it possible to get the result of select @@IDENTITY?

seancorfield 2020-08-18T17:10:03.008Z

@saikyun Per the next.jdbc *Getting Started* page: "If you pass the :multi-rs true option to execute!, you will get back a vector of results sets, instead of just one result set: a vector of zero or more vectors. The result may well be a mix of vectors containing realized rows and vectors containing update counts, reflecting the results from specific SQL operations in the stored procedure or script."

Saikyun 2020-08-21T05:58:00.010400Z

I think I tried that option, but I don't remember exactly how I did it. I'll give it another shot and come back if I have more questions 🙂

Saikyun 2020-08-21T05:58:06.010600Z

thanks for the reply

seancorfield 2020-08-21T06:01:57.010800Z

It will depend on your DB/driver -- what are you using?

Saikyun 2020-08-31T09:32:16.156700Z

microsoft sql server

seancorfield 2020-08-31T17:31:33.156900Z

OK, I've tested multiple result sets on SQL Server and it works (this is from the test suite):

(jdbc/execute! (ds)
                           [(str "begin"
                                 " select * from fruit;"
                                 " select * from fruit where id < 4;"
                                 " end")

seancorfield 2020-08-31T17:32:28.157100Z

That produces a vector containing two result sets, i.e., two vectors of hash maps, and the first result set contains four rows and the second result set contains three rows.

seancorfield 2020-08-18T17:11:27.008500Z

(it's also described on the All The Options page and in the docstring itself for execute!)