Solved

Running a mixed stored-procedure (sybase one) from Java

Posted on 2002-07-08
10
287 Views
Last Modified: 2012-05-04
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(sProcCall);

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?
0
Comment
Question by:whewell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 92

Expert Comment

by:objects
ID: 7139133
Because you never run the query :-)
0
 
LVL 92

Expert Comment

by:objects
ID: 7139141
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(sProcCall);

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);

0
 

Author Comment

by:whewell
ID: 7139663
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?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 92

Expert Comment

by:objects
ID: 7139674
Can you post your sp?
0
 
LVL 92

Expert Comment

by:objects
ID: 7139716
> executeQuery will still throw an exception

And whats the exception??
0
 

Author Comment

by:whewell
ID: 7140066
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.
0
 
LVL 92

Expert Comment

by:objects
ID: 7140239
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.
0
 

Author Comment

by:whewell
ID: 7140367
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.
0
 
LVL 92

Accepted Solution

by:
objects earned 75 total points
ID: 7142007
Sorry can you post your complete code that registers a return parameter.

Here's some code I'm using thats working:

Connection c = DB.getUpdateConnection();
               
s = c.prepareCall("{? = call mysp (?, ?) }");
s.registerOutParameter(1, Types.INTEGER);
for (int i=0; params!=null && i<params.length; i++)
{
  s.setObject(i+2, params[i]);
}
rs = s.executeQuery();

int errorCode = 0;
         
if (create && rs!=null && rs.next())
{
  result = create(rs);
}

// Check the error code
     
try
{
  errorCode = s.getInt(1);
}
catch (Exception e)
{
     Log.debug("(Ignored) exception getting error code: "+e);
}
0
 

Author Comment

by:whewell
ID: 7142492
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
going to wrong jsp page 2 68
Eclipse Help Java EE 5,6,7 Documentation, why not Java EE 8 8 69
ArrayList Adding/settign data 3 36
DTD and JAVA versions 1 55
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question