Fetch Resultset of created&filled MSSQL temp table over Java

Question

query5

String query5 ="USE DBTwo\n" +
    "DECLARE @temp_table table (column1 VARCHAR(60))\n" +
    "insert into @temp_table (column1)\n" +
    "select column1 from real_table (nolock)";

query3

String query3 = "USE DBTwo\n" +
    "select column1 from @temp_table";

Connections;

ResultSet rs1;
Statement stmt;

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://192.168.131.10;" +"databaseName=DBOne;" +"user=" + "exuser" + ";" + "password="+"userpass" + ";" + "allowMultiQueries=true" + ";"; 
Connection con = DriverManager.getConnection(connectionUrl);
stmt = con.createStatement();

Fetching Resultset;

   try {
       int rowcount = stmt.executeUpdate(query5);
       System.out.println(rowcount);

       rs1 = stmt.executeQuery(query3);
       while (rs1.next()) {
             System.out.println(rs1.getString(1)); 
       } 
        rs1.close();
   }
   catch (SQLException sqlex){
       sqlex.printStackTrace();
   }

Sql Exception throw below error;

com.microsoft.sqlserver.jdbc.SQLServerException: Must declare the table variable "@temp_table".`rowcount` return 7 

so i succesfully filled the @temp_table, I don't close stmt connection and try to fetch Resultset from this @temp_table. But SQL says I have not declared this table yet. How is it possible ?

-- SOLVED --

Creating just one query;

String query5 ="USE DBTwo\n" +
"DECLARE @temp_table table (column1 VARCHAR(60))\n" +
"insert into @temp_table (column1)\n" +
"select column1 from real_table(nolock)\n" +
"select column1 from @temp_table";

Fetch multiple results like below;

try {
  boolean result = stmt.execute(query5);
  while (true)
   if(result){
    rs1 = stmt.getResultSet();
    while (rs1.next()) {
    System.out.println(rs1.getString(1)); 
    }

  } else {
    int updateCount = stmt.getUpdateCount();
    if (updateCount == -1){
    break; 
  }
    result = stmt.getMoreResults();
}
catch (SQLException sqlex){
   sqlex.printStackTrace();
}

Answer 1

Technically following the JDBC spec you are only allowed to execute a single statement at a time. That the SQL Server JDBC driver allows the execution of multiple statements in one execute is a deviation of the standard. Also note that doing things like USE databasename is contrary the recommendation in JDBC to use Connection methods (like setCatalog) for this, as it may bring the driver in an inconsistent state.

As to your specific problem, I have two theories (I haven't verified if these are the actual cause):

1. You have auto commit enabled. After a statement execute, the connection is committed, and the temporary table is disposed.
2. After a statement execute the query context is disposed by the server, so a subsequent execute does not have access to the temporary table.

These are theories, and I believe that 1. is the most likely. So try to disable auto commit (Connection.setAutoCommit(false)).

If theory 2. is the cause, then you need to execute them in one go, and process multiple results (a count and a result set). For this you would use Statement.execute(...) and process for multiple results. See my answer to Java SQL: Statement.hasResultSet()? for an example on how to process multiple results.

This last solution probably also works if you don't want to disable auto commit.