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