SQLServerException: The statement did not return a result set

Published Mar 23 2019 10:59 AM 5,963 Views
Microsoft
First published on MSDN on Aug 04, 2008

Have you ever encountered this exception from executeQuery()? If so, chances are good that your query actually did return a result set (we’ll be addressing the error message text...), but it wasn’t the first result.  Look at your query or stored procedure.  Does it contain multiple SQL statements?  Maybe it includes DDL and DML to create and populate a temp table before the SELECT statement.  If that is the case, then you really need to use execute(), not executeQuery(), to execute it.  The problem is that executeQuery(), per JDBC spec, should only be used with queries whose only result is a single ResultSet.  Quoting from Sun’s documentation of this method:



Throws:


SQLException - if a database access error occurs, this method is called on a closed Statement or the given SQL statement produces anything other than a single ResultSet object



Some JDBC drivers apparently just ignore and discard DDL and DML results in executeQuery() rather than throwing an exception.


For an explanation on how to use execute() and its partner, getMoreResults(), to execute complex queries, look here .



--David Olix [SQL Server]


This post is provided 'as is' and confers no express or implied warranties or rights.

%3CLINGO-SUB%20id%3D%22lingo-sub-383465%22%20slang%3D%22en-US%22%3ESQLServerException%3A%20The%20statement%20did%20not%20return%20a%20result%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383465%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Aug%2004%2C%202008%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EHave%20you%20ever%20encountered%20this%20exception%20from%20executeQuery()%3F%20If%20so%2C%20chances%20are%20good%20that%20your%20query%20actually%20did%20return%20a%20result%20set%20(we%E2%80%99ll%20be%20addressing%20the%20error%20message%20text...)%2C%20but%20it%20wasn%E2%80%99t%20the%20first%20result.%26nbsp%3B%20Look%20at%20your%20query%20or%20stored%20procedure.%26nbsp%3B%20Does%20it%20contain%20multiple%20SQL%20statements%3F%26nbsp%3B%20Maybe%20it%20includes%20DDL%20and%20DML%20to%20create%20and%20populate%20a%20temp%20table%20before%20the%20SELECT%20statement.%26nbsp%3B%20If%20that%20is%20the%20case%2C%20then%20you%20really%20need%20to%20use%20execute()%2C%20not%20executeQuery()%2C%20to%20execute%20it.%26nbsp%3B%20The%20problem%20is%20that%20executeQuery()%2C%20per%20JDBC%20spec%2C%20should%20only%20be%20used%20with%20queries%20whose%20only%20result%20is%20a%20single%20ResultSet.%26nbsp%3B%20Quoting%20from%20Sun%E2%80%99s%20documentation%20of%20this%20method%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CB%3E%20Throws%3A%20%3C%2FB%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CCODE%3E%0A%20%20%20%20%3CA%20href%3D%22http%3A%2F%2Fjava.sun.com%2Fjavase%2F6%2Fdocs%2Fapi%2Fjava%2Fsql%2FSQLException.html%22%20title%3D%22class%20in%20java.sql%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%0A%20%20%20%20%20SQLException%0A%20%20%20%20%3C%2FA%3E%0A%20%20%20%3C%2FCODE%3E%0A%20%20%20-%20if%20a%20database%20access%20error%20occurs%2C%20this%20method%20is%20called%20on%20a%20closed%0A%20%20%20%3CCODE%3E%0A%20%20%20%20Statement%0A%20%20%20%3C%2FCODE%3E%0A%20%20%20or%20the%20given%20SQL%20statement%20produces%20anything%20other%20than%20a%20single%0A%20%20%20%3CCODE%3E%0A%20%20%20%20ResultSet%0A%20%20%20%3C%2FCODE%3E%0A%20%20%20object%0A%20%20%3C%2FP%3E%0A%20%20%3CBR%20%2F%3E%0A%20%20%3CP%3E%0A%20%20%3C%2FP%3E%0A%20%20%3CBR%20%2F%3E%0A%20%20%3CP%3E%0A%20%20%20Some%20JDBC%20drivers%20apparently%20just%20ignore%20and%20discard%20DDL%20and%20DML%20results%20in%20executeQuery()%20rather%20than%20throwing%20an%20exception.%0A%20%20%3C%2FP%3E%0A%20%20%3CBR%20%2F%3E%0A%20%20%3CP%3E%0A%20%20%20For%20an%20explanation%20on%20how%20to%20use%20execute()%20and%20its%20partner%2C%20getMoreResults()%2C%20to%20execute%20complex%20queries%2C%20look%0A%20%20%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fjdbcteam%2Farchive%2F2008%2F08%2F01%2Fuse-execute-and-getmoreresults-methods-for-those-pesky-complex-sql-queries.aspx%22%20mce_href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fjdbcteam%2Farchive%2F2008%2F08%2F01%2Fuse-execute-and-getmoreresults-methods-for-those-pesky-complex-sql-queries.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%0A%20%20%20%20here%0A%20%20%20%3C%2FA%3E%0A%20%20%20.%0A%20%20%3C%2FP%3E%0A%20%20%3CBR%20%2F%3E%0A%20%20%3CP%3E%0A%20%20%3C%2FP%3E%0A%20%20%3CBR%20%2F%3E%0A%20%20%3CP%3E%0A%20%20%20--David%20Olix%20%5BSQL%20Server%5D%0A%20%20%3C%2FP%3E%0A%20%20%3CBR%20%2F%3E%0A%20%20%3CP%3E%0A%20%20%20This%20post%20is%20provided%20'as%20is'%20and%20confers%20no%20express%20or%20implied%20warranties%20or%20rights.%0A%20%20%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383465%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Aug%2004%2C%202008%20Have%20you%20ever%20encountered%20this%20exception%20from%20executeQuery()%3F%20If%20so%2C%20chances%20are%20good%20that%20your%20query%20actually%20did%20return%20a%20result%20set%20(we%E2%80%99ll%20be%20addressing%20the%20error%20message%20text.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383465%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerDrivers%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 11:00 AM
Updated by: