Re: Problem with boolean return parameter of CallableStatement.exe
- From: Ed <Ed@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 28 Nov 2006 00:31:01 -0800
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:
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
}
- References:
- Re: Problem with boolean return parameter of CallableStatement.execute
- From: Joe Weinstein
- Re: Problem with boolean return parameter of CallableStatement.execute
- Prev by Date: RE: Connection Refused
- Previous by thread: Re: Problem with boolean return parameter of CallableStatement.execute
- Next by thread: RE: Connection Refused
- Index(es):
Relevant Pages
|
