Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    73

    Question Unanswered: Trying to connect to a DB2 Stored Procedure from Java

    Hi, I'm trying to connect to a DB2 Stored Procedure which has input and output parameters - and return the result to a ResultSet. I'm getting an SQLException with the message "No ResultSet was produced". Here's the code:

    // Connection to database - not actual method of connection - but the connecting to the database seems to work fine.

    Connection con = DriverManager.getConnection("jdbc:odbc:my_database _name");

    // Preparing the callable statement

    CallableStatement call = con.prepareCall("{call procedure_name(?, ?, ?, ?)}");

    call.setInt(1, 3);
    call.setInt(2, 1567); //Examples of my input values

    call.registerOutParameter(3, Types.INTEGER);
    call.registerOutParameter(4, Types.CHAR); //Examples of my output values

    // Actually execute the call - return the results to a Result Set

    ResultSet rs = call.executeQuery(); // This is the point my exception occurs

    // Afterwards I would retrieve the data returned

    int outParmOne = rs.getInt(3);
    int outParmTwo = rs.getString(4);

    The owner of the DB2 system can see my procedure call and sets his output variables accordingly - but I always get the No ResultSet error. When I do the simple rs.execute() - I get false returned (i.e. no ResultSet available).

    I'm not quite sure how the whole thing should work. If I were to call the Stored Procedure through COBOL for example - I'd declare some host variables and send them as the output parameters (3 and 4) - and they'd get updated by the procedure. How does Java turn this into a ResultSet?

    The stored procedure is setup with the following options:
    dynamic result sets 0
    modifies sql data language C
    external name 'external_name'
    fenced
    parameter style db2dari
    program type sub
    no dbinfo ;
    disconnect <database> ;

    Would these have anything to do with it?

    Hope I've provided enough information - I'm quite stumped!!! Any help would be greatly appreciated.
    Last edited by joeldixon66; 07-22-03 at 03:41.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Trying to connect to a DB2 Stored Procedure from Java

    Originally posted by joeldixon66

    call.registerOutParameter(3, Types.INTEGER);
    call.registerOutParameter(4, Types.CHAR);
    ResultSet rs = call.executeQuery();
    int outParmOne = rs.getInt(3);
    int outParmTwo = rs.getString(4);

    I believe that result set does not equal to the return parameters. To fetch return parameters you need to use CallableStatement.getInt(); if your procedure returns a cursor you then should obtain it by using CallableStatement.getResultSet().

    You may want to have a look at the DB2 Application Development Guide - there's a good explanation and examples of calling stored procedures.

    Hope this helps.

    Nick

  3. #3
    Join Date
    Jul 2003
    Posts
    73
    Nick,

    Thanks a lot for that - it worked!! You're right - the method I was using is required when a Cursor (result set) is being returned from the Stored Procedure.

    Have a great day!

    Joel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •