Prev: Problem with formatting numbers
Next: Subtract Month
From: Shiran on 31 Mar 2010 05:36
Hi,

I just migrated to SQLServer 2008 Express from the 2005 version. I
have a Java application that uses SQL JDBC Driver (sqljdbc4.jar -
latest version) to connect to DB. I had to upgrade to the latest
driver as the previous version sqljdbc.jar did not work with SQL 2008.
Now my application works, but in one instance it fails. Debugging i
found the issue to be when accessing this particular stored procedure
that has both Update and Select queries.

In this case the error says 'statement didnt return a resultset' when
executing this code
'preStmt.executeQuery();'

This issue didn't occur with SQL server 2005 and the older jdbc
driver, its only after i did the upgrade. Is this a new limitation in
SQL 2008 or is there a different way to access such a query or is
there a way to stop this in the query itself ???

The main function of the store procedure is to retrive results so i
had to use executeQuery, but it is also essential to do some updates.
So without breaking the query, is there a way to get round this.


snippet of the query:
UPDATE country SET accessed = 1 WHERE countrysid = 3
SELECT name,age,address FROM person WHERE country = 3

java code:
PreparedStatement preStmt = commonConnection.prepareStatement(
"{call pGetPersonList(?)}");
preStmt.setInt(1, statusid);
ResultSet rset = preStmt.executeQuery();
while(rset.next()){
// statements
}

Thanks for your time,
Dragonfruit.

From: Jeroen Mostert on 31 Mar 2010 16:04
On 2010-03-31 11:36, Shiran wrote:
> I just migrated to SQLServer 2008 Express from the 2005 version. I
> have a Java application that uses SQL JDBC Driver (sqljdbc4.jar -
> latest version) to connect to DB. I had to upgrade to the latest
> driver as the previous version sqljdbc.jar did not work with SQL 2008.
> Now my application works, but in one instance it fails. Debugging i
> found the issue to be when accessing this particular stored procedure
> that has both Update and Select queries.
>
> In this case the error says 'statement didnt return a resultset' when
> executing this code
> 'preStmt.executeQuery();'
>
Try SET NOCOUNT ON at the top of the procedure body or remove it if it's
there (or even SET NOCOUNT OFF). Whether or not this does/does not confuse
the driver depends on the version and what method you're calling to execute
the query, so it may or may not work. It should not confuse any native
clients (ODBC and .NET) calling the procedure, but this is not absolute either.

--
J.
 | 
Pages: 1
Prev: Problem with formatting numbers
Next: Subtract Month