2

I want to run multiple statements on a single execution against SQL Server. I do it with Node.js but I cannot run the same query using next.jdbc

For example, if I run this:

(def db {:jdbcUrl "jdbc:jtds:sqlserver://localhost:1433/TESTDB;user=sa;password=passwd"})
(def ds (jdbc/get-datasource db))
(jdbc/execute! ds ["select * from EMPLOYEE;select FIRST_NAME from EMPLOYEE;"])

I have also tried to wrap the statement within a transaction with the same result

(jdbc/execute! ds ["BEGIN TRANSACTION select * from EMPLOYEE;select FIRST_NAME from EMPLOYEE; COMMIT"])

I always get the first query.

I have tried Microsoft's JDBC driver also. Sean Corfield says that if the database supports it, then next.jdbc should support it. next-jdbc: execute multiple statements?

But I cannot make it work

Solution

As indicated by Sean Corfield

(jdbc/execute! ds ["BEGIN select * from EMPLOYEE;select FIRST_NAME from EMPLOYEE; END"] {:multi-rs true})
aym
  • 233
  • 1
  • 4
  • 17

2 Answers2

2

Yes, you can run multiple statements and multiple result sets back but you have to tell next.jdbc that's the behavior you want.

Take a look at the tests for MS SQL Server running multiple statements: https://github.com/seancorfield/next-jdbc/blob/develop/test/next/jdbc_test.clj#L560-L572

This is mentioned (briefly) in the Getting Started guide: "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."

Sean Corfield
  • 6,297
  • 22
  • 31
  • Your GitHub code snippet is not Java code. You should add an actual reference showing JDBC being used with a statement or prepared statement in Java code. – Tim Biegeleisen Jan 29 '21 at 03:39
  • 2
    @TimBiegeleisen no part of this question involves the Java language. "next.jdbc" is a Clojure library and the question concerns how to extract multiple result sets from it. – Biped Phill Jan 29 '21 at 11:00
  • @TimBiegeleisen The OP posted Clojure code and specifically referred to the documentation I wrote about the library I maintain that he is trying to use! – Sean Corfield Jan 30 '21 at 05:18
0

As far as I know, SQL Server's JDBC driver does not support multiple statements. But, even if it did, you should probably not be using it, as it opens a potential security hole for injection type attacks. Instead, if you really need to execute multiple SQL statements, either refactor your current SQL into a single statement, or else use multiple statements wrapped in a single transaction.

For reference, some other JDBC drivers, such as MySQL, might support multiple statements.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Yes, MS SQL Server can run multiple statements via JDBC -- you just need BEGIN/END around them. You cannot run multiple statements via JDBC on MySQL as far as I know: you have to write a stored procedure for that. Several other databases allow for multiple statements in a stored procedure. PostgreSQL allows that but, unhelpfully, does not actually return multiple result sets from them. – Sean Corfield Jan 29 '21 at 03:34
  • 1
    @SeanCorfield [It is certainly possible to run multiple statements using JDBC with MySQL](https://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement). – Tim Biegeleisen Jan 29 '21 at 03:37
  • I didn't know about allowMultiQueries so, thank you! – Sean Corfield Jan 30 '21 at 05:20