Composite query execution on SQL Server 2014 does not return result set
We have the following code:
Connection conn = null;
String dbURL = "jdbc:sqlserver://DBDerver details here";
String user = "user name";
String pass = "password@123";
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(dbURL, user, pass);
String sql = "update Table1" + "set DBID = DBID+1 where TABLENAME = '" + "Table2" + "';" + "select DBID from Table 1 where TABLENAME = '" + "Table 2" + "'";
System.out.println("generateId(), SQL = " + sql);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
int id = -1;
System.out.println("Result set :->"+rs);
while(rs.next()) {
id = rs.getInt(1);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
This works fine on SQL Server 2005. We recently upgraded to SQL Server 2014. I also upgraded the jar to SQLJDBC4.jar (since we are using JDK6 as the runtime). But doing this on SQL Server 2014 results in the following exception.
An exception:
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:800)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
at com.hcl.JDBCTest$QueryTogether.executeQueryHere(JDBCTest.java:63)
at com.hcl.JDBCTest.main(JDBCTest.java:34)
It could be because in 2012 the update counter is returned -> result set where 2005 returned the result set -> number of updates, but this is just a guess (any confirmation here will be added as a bonus for me).
I don't want to change executeQuery to execute / executeUpdate. Is there any other way to get around this exception? Also I don't use stored procedures.
Or
Is there any other SQL driver I can use and make a compound query on sql server 2014
source to share
I just changed the driver to JTD as follows and it worked like a charm
Connection conn = null;
String dbURL = "jdbc:jtds:sqlserver://DataBase Name";
String user = "username";
String pass = "password";
try {
Class.forName("net.sourceforge.jtds.jdbc.Driver");
conn = DriverManager.getConnection(dbURL, user, pass);
JDBCTest jt = new JDBCTest();
String sql = "update Table1 " + "set DBID = DBID+1 where TABLENAME = '" + "Table2'" +"\n"+
"select DBID from Table1 where TABLENAME = '" + "Table2" + "'";
System.out.println("generateId(), SQL = " + sql);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
int id = -1;
System.out.println("Result set :->"+rs);
while(rs.next()) {
id = rs.getInt(1);
System.out.println("id is :-> "+ id);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
OUTPUT:
generateId(), SQL = update SEQUENCE_TABLE set DBID = DBID+1 where TABLENAME = 'TBGP_TABLE'
select DBID from SEQUENCE_TABLE where TABLENAME = 'TBGP_TABLE'
Result set :->net.sourceforge.jtds.jdbc.JtdsResultSet@d42d08
id is :-> 86532
source to share