dcsimg
MS SQL error: No resultset was returned
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Greg_Dulski
Posted On:   Thursday, May 16, 2002 12:15 PM

I'm ready to wallow in self pity and loathing and all the rest. My app calls is supposed to get data from several SQL Server 2000 SP2 stored procedures which return data for a single person. My CallableStatement prepares just fine for each of these procedures. When it comes to stmt.executeQuery() though, some will work fine and others will create an exception! To the best of my knowledge, the exception is "No resultset was returned". On the surface you might say that the SP selects no data so no resultset is returned. But that's not the case since data is returned when I run it in SQL Server Enterprise Manager. Yet, other stored procedures, when called by the same code, run and return data just fine! This is really getting to me! Wh   More>>
I'm ready to wallow in self pity and loathing and all the rest.


My app calls is supposed to get data from several SQL Server 2000 SP2 stored procedures which return data for a single person. My CallableStatement prepares just fine for each of these procedures. When it comes to stmt.executeQuery() though, some will work fine and others will create an exception! To the best of my knowledge, the exception is "No resultset was returned". On the surface you might say that the SP selects no data so no resultset is returned. But that's not the case since data is returned when I run it in SQL Server Enterprise Manager. Yet, other stored procedures, when called by the same code, run and return data just fine! This is really getting to me! Why would SQL Server return such an exception for some stored procedures? Can it be something related to the driver? We are using the jdbc SQL server driver from MicroSoft "com.microsoft.jdbc.sqlserver.SQLServerDriver"
I'm new to all this so please take it easy on me and I appreciate any ideas!
greg

   <<Less

Re: MS SQL error: No resultset was returned

Posted By:   Zachary_Hunter  
Posted On:   Thursday, May 16, 2002 01:29 PM

Greg,

I'm not familare with SQLServer per se, but is there a chance that the SP contains several SQL statements? If there's an update, insert or delete statement before the select statement, that might be the issue.


An update, insert or delete will return an integer representing the number of rows effected. You might be getting an update count as the result instead of the result set.


If there is more than one return value generated by a java.sql.*Statment you should us its execute() method.


Try this


CallableStatement stmt;
ResultSet rs;

//... prepare the statement
stmt.execute();

//loop through all results
while(getMoreResults() && rs != null) {
if(stmt.getUpdateCount() == -1) {//it's a result set
rs = stmt.getResultSet();
}
}


Hope this helps,
Zac
About | Sitemap | Contact