Getting result set from a statement block

Question

I have a huge script with a lot of logic and with the select statement in the end which returns final result.

As many people know JDBC is not supposed to run SQL scripts (with multiple statements) out of the box so I wrapped script into begin ... end block making it a single statement block so that I can execute it in one go.

All seems to be good... except I can't get result set from begin ... end block (code below prints "No data"). So I had to split script into two pieces - all the logic (1) + final select statement (2).

val url = "jdbc:sqlserver://" + mssqlServer + ";integratedSecurity=true;"
val connMSSQL = java.sql.DriverManager.getConnection(url)

val qqq0 = """begin
             |
             |if object_id('tempdb..#ttt') is not null drop table #ttt
             |
             |create table #ttt(i int, v varchar(10))
             |
             |insert into #ttt
             |select 0, 'qwerty' union all select 1, 'hello'
             |
             |select 1 id, 0 value
             |
             |end""".stripMargin

val qqq = "select * from #ttt"

val stmt = connMSSQL.createStatement()
stmt.execute(qqq0)
if (stmt.getResultSet==null) println("No data")
val rs = stmt.executeQuery(qqq)

The questions is - whether there is any way to get result set from begin ... end block? If the answer is no then the explanation would be highly appreciated.

PS. If I wrap all the code into procedure my_proc I can easily get result but creating objects in DB is not an option in my specific situation.

Works this way

val stmt1 = connMSSQL.createStatement()
stmt1.execute("my_db..my_proc")
val rs1 = stmt1.getResultSet

And this way as well

val stmt2 = connMSSQL.createStatement()
val rs2 = stmt2.executeQuery("my_db..my_proc")

In both cases record set equals null when code is used with anonymous block instead of procedure.

PPS. No need to mention that if copy and paste begin ... end block into management studio and run it with F5 then it returns result without any problems.

UPDATE

For me behavior is still weird but luckily it works only with set nocount on AND getMoreResults.

However... it works for the firs statement only.

So if i include nocount into the original SQL script then for the code below

val stmt1 = connMSSQL.createStatement()
println(stmt1.execute(qqq0))
println(stmt1.getMoreResults())
if (stmt1.getResultSet==null) println("No data") else println("Yes data")

val stmt2 = connMSSQL.createStatement()
println(stmt2.execute(qqq0))
println(stmt2.getMoreResults())
if (stmt2.getResultSet==null) println("No data") else println("Yes data")

Output is following

false
true
Yes data
false
false
No data

Now the question is how to get it working properly not for the first statement only.

UPDATE 2

None of the links provided by moderator explain unexpected behavior with createStatement.

Try adding `SET NOCOUNT ON;` as the first statement in your script. — Dan Guzman, May 06 '21 at 20:47

score 1 · Accepted Answer · answered May 06 '21 at 23:17

Well, as I wrote in the question, in case of createStatement it works for the first JDBC statement only and after calling getMoreResults.

For prepareStatement it works for all calls. Also it does not matter if I use executeQuery or execute + getResultSet.

val stmt1 = connMSSQL.prepareStatement(qqq0)
if (stmt1.executeQuery()==null) println("No data") else println("Yes data")

val stmt2 = connMSSQL.prepareStatement(qqq0)
println(stmt2.execute)
if (stmt2.getResultSet()==null) println("No data") else println("Yes data")

Output

Yes data
true
Yes data

So the solution is - using prepareStatement and set nocount on.

score 0 · Answer 2 · answered May 06 '21 at 22:52

Try with CallableStatement and registerOutParameter.

val url = "jdbc:sqlserver://" + mssqlServer + ";integratedSecurity=true;"
val connMSSQL = java.sql.DriverManager.getConnection(url)

val qqq0 = """begin
             |
             |if object_id('tempdb..#ttt') is not null drop table #ttt
             |
             |create table #ttt(i int, v varchar(10))
             |
             |insert into #ttt
             |select 0, 'qwerty' union all select 1, 'hello'
             |
             |select 1 id, 0 value
             |
             | ? := 123;
             |
             |end""".stripMargin

// val qqq = "select * from #ttt"

// val stmt = connMSSQL.createStatement()
// stmt.execute(qqq0)
// if (stmt.getResultSet==null) println("No data")
// val rs = stmt.executeQuery(qqq)

try (CallableStatement stmt = connMSSQL.prepareCall(qqq0)) {
    stmt.registerOutParameter(1, Types.INTEGER);
    stmt.execute();
    System.out.println(stmt.getInt(1)); // Display "123";
}

I need a **record set** as a result. Please check an update to original question. I managed to get it working but unfortunately it works for the first execution only. — Dr Y Wit, May 06 '21 at 23:02

Getting result set from a statement block

2 Answers2