whewell
asked on
Running a mixed stored-procedure (sybase one) from Java
I have a stored procedure that can on occation do the following: "return -1", but in most cases just performs a select and returns a ResultSet object. ExecuteQuery method cannot handle the case of the "return -1" and throws exception, since it expects a ResultSet.
Alas, Execute method always return a null ResultSet when I try it instead. (and a -1 UpdateCount). What am I doing wrong there??
My tentative code is something of the following:
CallableStatement currStatement;
ResultSet currResultSet;
String sProcCall = "{ call " + spName + sParams;
currStatement = m_connection.prepareCall(s ProcCall);
if (params != null)
for (int i=0; i < params.length ; i++)
currStatement.setObject(i+ 1, params[i]);
currResultSet = currStatement.getResultSet ();
That's it. Does anyone know why do I always get null inside the currResultSet parameter?
Alas, Execute method always return a null ResultSet when I try it instead. (and a -1 UpdateCount). What am I doing wrong there??
My tentative code is something of the following:
CallableStatement currStatement;
ResultSet currResultSet;
String sProcCall = "{ call " + spName + sParams;
currStatement = m_connection.prepareCall(s
if (params != null)
for (int i=0; i < params.length ; i++)
currStatement.setObject(i+
currResultSet = currStatement.getResultSet
That's it. Does anyone know why do I always get null inside the currResultSet parameter?
Because you never run the query :-)
To get at your output parameter you need to add a placeholder for it and register the out parameter.
Something like:
CallableStatement currStatement;
ResultSet currResultSet;
String sProcCall = "{ ? = call " + spName + sParams;
currStatement = m_connection.prepareCall(s ProcCall);
s.registerOutParameter(1, Types.INTEGER);
if (params != null)
for (int i=0; i < params.length ; i++)
currStatement.setObject(i+ 2, params[i]);
ResultSet rs = s.executeQuery();
int code = currStatement.getInt(1);
Something like:
CallableStatement currStatement;
ResultSet currResultSet;
String sProcCall = "{ ? = call " + spName + sParams;
currStatement = m_connection.prepareCall(s
s.registerOutParameter(1, Types.INTEGER);
if (params != null)
for (int i=0; i < params.length ; i++)
currStatement.setObject(i+
ResultSet rs = s.executeQuery();
int code = currStatement.getInt(1);
ASKER
Alread tried what objects suggests...
The executeQuery will still throw an exception if the stored procedure is doing something like a "return -1" statement(and hence does not reach the select query). Maybe I should create an output parameter inside the stored procedure, and init it to the return value, as an alternative to the "return" statement. That still leaves one problem unsolved. I have discovered that a stored procerure not having a return statement cannot be nested into another stored procedure (just doesn't work), so maybe I'll have to create two sets of stored procedures, one for internal (nested) purposes and the other for external (non-nested) purposes.
What do you think?
The executeQuery will still throw an exception if the stored procedure is doing something like a "return -1" statement(and hence does not reach the select query). Maybe I should create an output parameter inside the stored procedure, and init it to the return value, as an alternative to the "return" statement. That still leaves one problem unsolved. I have discovered that a stored procerure not having a return statement cannot be nested into another stored procedure (just doesn't work), so maybe I'll have to create two sets of stored procedures, one for internal (nested) purposes and the other for external (non-nested) purposes.
What do you think?
Can you post your sp?
> executeQuery will still throw an exception
And whats the exception??
And whats the exception??
ASKER
1) The stored procedure is followed below:
create proc G_GenPort_get
@gClientName varchar(40),
@genPortName varchar(60),
@createDate datetime = null,
as
begin
select @retVal = 0
declare
@gClientId numeric(6,0)
exec @gClientId = G_Client_getIdFromName @gClientName
if @gClientId = 0
return -1
-- Init create date to current date if not specified
if @createDate = null SELECT @createDate = getdate()
select genPortId,
gClientId,
genPortName,
portStatus,
portCreateTime,
portAckTime,
portExecuteTime,
portSuggestedDest,
gMemo
from G_GenPort
where G_GenPort.gClientId = @gClientId
and G_GenPort.genPortName = @genPortName
and datediff(day, G_GenPort.portCreateTime, @createDate) = 0
end
2) The Exception is an SQLException with something of the following message: "No result set for this query".
Thanks.
create proc G_GenPort_get
@gClientName varchar(40),
@genPortName varchar(60),
@createDate datetime = null,
as
begin
select @retVal = 0
declare
@gClientId numeric(6,0)
exec @gClientId = G_Client_getIdFromName @gClientName
if @gClientId = 0
return -1
-- Init create date to current date if not specified
if @createDate = null SELECT @createDate = getdate()
select genPortId,
gClientId,
genPortName,
portStatus,
portCreateTime,
portAckTime,
portExecuteTime,
portSuggestedDest,
gMemo
from G_GenPort
where G_GenPort.gClientId = @gClientId
and G_GenPort.genPortName = @genPortName
and datediff(day, G_GenPort.portCreateTime, @createDate) = 0
end
2) The Exception is an SQLException with something of the following message: "No result set for this query".
Thanks.
And you better post the code that your getting the exception from, so we're both looking at the same thing.
I'm doing a similiar thing here and it works fine.
I'm doing a similiar thing here and it works fine.
ASKER
The code throwing the exception is the following:
currResultSet = currStatement.executeQuery ();
This happens if the stored procedure returns through the "return -1" course.
I can use currStatement.execute() instead, but in that case, currStatement.getResultSet () returns null ALWAYS, even when the stored procedure is returning a ResultSet.
currResultSet = currStatement.executeQuery
This happens if the stored procedure returns through the "return -1" course.
I can use currStatement.execute() instead, but in that case, currStatement.getResultSet
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well...
It doesn't work on my side, but since then we decided to do the following workaround:
Select stored procedures will not return any value, onlt record sets (which of course may be empty if something went wrong)
Update/Insert/Delete stored procedures will just return values and we will use execute() function for getting this value.
This seems to be working. I'll give you the points anyway, for your effort.
Thanks and farewell.
It doesn't work on my side, but since then we decided to do the following workaround:
Select stored procedures will not return any value, onlt record sets (which of course may be empty if something went wrong)
Update/Insert/Delete stored procedures will just return values and we will use execute() function for getting this value.
This seems to be working. I'll give you the points anyway, for your effort.
Thanks and farewell.