Re: Problem with boolean return parameter of CallableStatement.exe



Ed wrote:

The stored procedure involved is a 'retrieval only' proc with one output parameter and it does no updating, so the execute method should return true. Here is the text:

Ok, and what does the timeoutSession procedure do?
I highly recommend trying my JDBC code example, and
in both cases (Sybase and SQLServer) print out the
updateCounts to see the differences.


ALTER procedure [dbo].[getDiaryValuationClients] (@ErrorMsg T_emxDesc output, @TokenKey T_srvToken, @TokenChk T_srvToken, @DiaryValID T_emxDiaryValuationID)
as
begin

declare @RetVal T_emxRetVal,
@RetErrorMsg T_emxDesc,
@RowCount integer,
@Error integer
select @RetVal = 0,
@RetErrorMsg = ''
/* Check if the Session has expired */ exec @RetVal = timeoutSession @RetErrorMsg output, @TokenKey, @TokenChk if @RetVal != 0 begin /* Return the error message */ select @ErrorMsg = @RetErrorMsg return @RetVal end
/* Return the specified row */
SELECT DVC.ID ID,
DVC.AgencyNumber AgencyNumber, DVC.ClientReference ClientReference,
DVC.ClientShortName ClientShortName,
DVC.AccountReference AccountReference,
PT.ProductType ProductType,
DVC.RequestDesignation RequestDesignation
FROM DiaryValuationClient DVC LEFT OUTER JOIN ProductType PT ON DVC.ProductTypeID = PT.ProductTypeID
WHERE DiaryValuationID = @DiaryValID
ORDER BY DVC.ID

select @RowCount = @@rowcount,
@Error = @@error

if @Error != 0
begin
/* Return the error message */
-- XXX TODO return error message --select @ErrorMsg = @@errormsg
return @Error
end

return 0

end

"Joe Weinstein" wrote:



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.execute
    ... 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: SQL2005 JDBC ClassNotFoundException
    ... The KB article you referenced is refering to the SQL2000 JDBC driver. ... If I execute it with just ... other JAR files that were not in the package that I downloaded from the ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Some indexes not used in JDBC call
    ... the fact that SQL*plus and JDBC are not using the same version of TTC ... JDBC-Thin is pure Java and has to turn each release ... less than a second to execute in SQL*Plus. ... The query is identical, the ...
    (comp.lang.java.databases)
  • Re: jdbc/mysql syntax error when using script from file
    ... With JDBC, you need to execute the statements one by one, not in batch. ... You will need to read the file, split out individual statements and ...
    (comp.lang.java.programmer)
Loading