Re: Problem with boolean return parameter of CallableStatement.execute



Ed wrote:

We are moving from Sybase to SQL Server 2005 (9.0.2047) and we are using the 1.1 JDBC driver with Java 1.5.0 06.

Some of our code uses the execute method of a CallableStatement to get a result set and checks that the boolean return parameteris true before then processing that result set. Using the sybase jdbc driver 'true' is returned when there are any results, whereas with the sqljdbc driver a 'false' is being returned.

We have got around the problem by not checking the boolean value at all, but we will then have to make this change elsewhere in our code. Is this a driver bug?

Here is a code snippet:

Please also show the text of the stored procedure. The return from
a call to execute() means: Is the very first return coming back from
the procedure a result set? If the execute() caused an update first,
and then did a query, then execute() will return false, so you would
process or pass the update count before getting the query data.
It may be that the two DBMSes return different numbers of update
counts.
Here is the best code for fully processing any statement:

boolean getResultSetNow = ps.execute();
int updateCount = -1;

while (true) { // handle all in-line results from any procedure
if (getResultSetNow) {
ResultSet r = ps.getResultSet();
while (r.next()) {
// process result set fully before calling getMoreResults()!
}
r.close();
} else {
updateCount = ps.getUpdateCount();
if (updateCount != -1) {
;// process update count if desired
}
}
if ((!getResultSetNow) && (updateCount == -1)) break; // done with loop
getResultSetNow = ps.getMoreResults(); // go to next return
}
// if a Callable statement, get any output parameters after the loop.

HTH,
Joe Weinstein at BEA Systems

boolean l_bExecutedQuery = l_csCallableStatement.execute();
//get the result set and create a result set table
ResultSetTable l_RstTable = null;
if (l_bExecutedQuery) // If this boolean is ignored, it works OK, otherwise not!!
{
// Advance the CallableStatement's current result until the first and only result set
boolean l_bHasMoreResultSets = l_bExecutedQuery;
while (l_csCallableStatement.getUpdateCount() != -1) {
l_bHasMoreResultSets = l_csCallableStatement.getMoreResults();
}

l_RsClientDetails = l_csCallableStatement.getResultSet();

if (l_RsClientDetails != null)
{
process result set
}
} else {
unexpected error
}



.

Relevant Pages

  • Re: Problem with boolean return parameter of CallableStatement.exe
    ... JDBC driver with Java 1.5.0 06. ... Some of our code uses the execute method of a CallableStatement to get a result set and checks that the boolean return parameteris true before then processing that result set. ... Using the sybase jdbc driver 'true' is returned when there are any results, whereas with the sqljdbc driver a 'false' is being returned. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Problem with boolean return parameter of CallableStatement.exe
    ... parameter and it does no updating, so the execute method should return true. ... JDBC driver with Java 1.5.0 06. ... result set and checks that the boolean return parameteris true before then ... Please also show the text of the stored procedure. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Conditional "If Not ... Then" Statement not working properly H
    ... IF Not Bob = 42 Then Exit Sub ... (Condition will not execute because it is FALSE and the "IF" statement ... If Bob42 Then Exit Sub ... Returning to your IF statement, if the variables x, y and z are Boolean types, the simplest way to test if all 3 are TRUE is: ...
    (microsoft.public.excel.programming)
  • Re: Conditional "If Not ... Then" Statement not working properly H
    ... be true in order for VBA to execute the code in the "IF" statement. ... TRUE that Bob is 42) ... IF Not Bob = 42 Then Exit Sub (Condition will not execute because it is ... Dim one As Boolean, two As Boolean, three As Boolean ...
    (microsoft.public.excel.programming)
  • Re: Found JDBC driver bug? PreparedStatement.setBoolean doesnt work
    ... I am using the latest Informix JDBC drivers 3.10 JC1. ... Java VM: Java HotSpotClient VM ... IBM Informix JDBC Driver for IBM Informix Dynamic Server 3.00.JC3 ... Suppose you have a table where you have a BOOLEAN attribute: ...
    (comp.databases.informix)
Loading