Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: Java and Stored Procedures

    Hello!!

    How can I obtain out param of Procedure in DB2 from Java??
    My code is:

    ConnectDB DB = new ConnectDB();
    CON = DB.getConnection();
    String sRES = "";
    String param = "12345678"

    CallableStatement cs = null;
    String sSQL = " CALL PROCEDURE('"+param+"',?)";
    cs = CON.prepareCall(sSQL);
    cs.registerOutParameter(1,Types.CHAR);
    cs.execute();

    sRES = cs.getString(1);

    But I Have the exception:

    java.lang.NoClassDefFoundError: com/ibm/db2/jcc/SQLJPackage
    at COM.ibm.db2.jdbc.net.DB2Connection.prepareCall(DB2 Connection.java:658)

    Why??? I need help. Thank you.

  2. #2
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37
    Your exception is not thrown at parameter retrieval time. It is thrown at the (attempted) preparation of the CallableStatement.

    First, the CallableStatement declaration needs a touch of rework:
    CallableStatement cs = con.prepareCall("{CALL PROCEDURE(?)}");

    Then, you need an input parameter replacement:
    cs.setString(1, param);

    Register the out parms:
    cs.registerOutParameter(1, java.sql.Types.CHAR);

    Also, you need a ResultSet from which to get the out parms.
    E.g.,
    ResultSet rs = cs.executeQuery();

    Finally:
    sRES = cs.getString(1);

    This should work - unless I mistyped something.

    BTW, I hope PROCEDURE is not the name of the real stored procedure.

    Cheers,

    Julius

  3. #3
    Join Date
    Sep 2003
    Location
    UK
    Posts
    3
    might also want to try handling result set like



    Class.forName(driver);
    try{
    Connection conn = DriverManager.getConnection(url, userid, password);
    PreparedStatement stmt = conn.prepareStatement(HEARTBEAT);
    ResultSet rs = stmt.executeQuery();
    if (rs.next()) {
    do {
    beat = rs.getString("HEART");
    } while (rs.next());
    }
    }catch(Exception ex){
    ex.printStackTrace();
    }

  4. #4
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37
    Yes, once you have a ResultStatement instance, you may use the next() method.

    The thing is though, when the stored procedure is producing output parameters, PreparedStatement cannot be used.

    PreparedStatement can only be used for such stored procedures that do not produce out parms. For those, one needs to use CallableStatement (or a suitable subclass).

    BTW, CallableStatement is a subclass of PreparedStatement. Based on the JDBC specs, the two behave pretty much the same way except when it comes to output paramters.

    Cheers,

    Julius

  5. #5
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37
    Just to further clarify the previous thought.

    I am not advocating the use of PreparedStatement instead of CallableStatement when it comes to stored procedures. IMHO, there are two possible philosophies one can follow:

    1 - go to the most specialized class/interface that is optmized exactly for what you need for stored procedure-related operations -> implement the CallableStatement interface
    2 - use the most generalized class/interface that fits the purpose of you current operation with the least redundancy (and least levels of inheritance resolution) -> implement the PreparedStatement interface.

    In my projects, I am always using the CallableStatement.

    Oh, yes. Almost forgot...there is, of course, no ResultStatement class in JDBC... I meant ResultSet.

    HTH.

    Julius

  6. #6
    Join Date
    Sep 2003
    Posts
    3
    My application's running The problem was I have 'db2driver.jar' in the classpath, but I haven't included `db2jcc.jar' that is necesary for execute stored procedures with Callable Statement. The db2jccc.jar contains the class SQLJPackage that I haven't.

    Thank you very muchhhhhhhhh...

    (I'm sorry my English is very bad)

Posting Permissions

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