next:jdbc How to allow multiple statements for SQL Server

Question

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})

question from:https://stackoverflow.com/questions/65947958/nextjdbc-how-to-allow-multiple-statements-for-sql-server

Answer 1

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."