我正在使用sqljdbc4.jar(sqljdbc_2.0)版本.
我正在执行一个插入选择返回以获得这样的身份:
BEGIN INSERT INTO DateRangeOptions (Description,Code) VALUES ('dateRange.quickPick.option.all','ALL'); SELECT SCOPE_IDENTITY() END
我得到:
com.microsoft.sqlserver.jdbc.sqlServerException: The statement did not return a result set.
这条线是:
st.executeQuery(updateQuery)
有任何想法吗?
解决方法
从sql 2000升级到sql 2005并切换到Microsoft sql Server 2005 JDBC驱动程序版本1.2.我在执行Insert后跟SELECT SCOPE_IDENTITY()时收到错误“语句没有返回结果”.我使用executeUpdate()和getGeneratedKeys而不是executeQuery解决了这个问题.这是前后代码.
注意:此示例中使用的连接是java.sql.connection而不是com.microsoft.sqlserver.jdbc.sqlServerConnection.
String dbURL = "jdbc:sqlserver" + "://" + dbServer + ":" + dbServerPort + ";SelectedMethod=cursor;databaseName=" + dbName + ";user=xxx;password=xxx"; Class.forName("com.microsoft.sqlserver.jdbc.sqlServerDriver"); java.sql.Connection connection = DriverManager.getConnection(dbURL); sql = "insert into Contact (name) values ('ABC'); SELECT SCOPE_IDENTITY()"; PreparedStatement ps = connection.prepareStatement(sql); ResultSet rs = ps.executeQuery(); if (rs.next()) { long id = rs.getLong(1); System.out.println("Id=" + id); }
String dbURL = "jdbc:sqlserver" + "://" + dbServer + ":" + dbServerPort + ";SelectedMethod=cursor;databaseName=" + dbName + ";user=xxx;password=xxx"; Class.forName("com.microsoft.sqlserver.jdbc.sqlServerDriver"); java.sql.Connection connection = DriverManager.getConnection(dbURL); sql = "insert into Contact (name) values ('ABC'); SELECT SCOPE_IDENTITY()"; PreparedStatement ps = connection.prepareStatement(sql); ps.executeUpdate(); // do not use execute() here otherwise you may get the error // The statement must be executed before // any results can be obtained on the next // getGeneratedKeys statement. ResultSet rs = ps.getGeneratedKeys(); if (rs.next()) { long id = rs.getLong(1); System.out.println("Id=" + id); }