RE: Stored Procedure Problem

I would just like to note one other thing that might help you along the
way... your code example gives the impression
that eventually you are going to return a ResultSet object from the
function.  Keep in mind that if you close either the
statement of the connection that a ResultSet was created with, the
ResultSet is closed as well.

If you fix this by taking out the close calls, you will be leaking database
resources each time you call this method.

Perhaps you know all this and this is just a very simple test program, but
I thought I would take a moment to point it
out.

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"Biologists have a special word for stability -- dead"

                Larry Wall
                Open Source Developers Journal
                Issue 1, Jan  2000


Mike Perez <MPEREZ@tycoint.com>@midrange.com on 02/06/2001 04:23:46 PM

Please respond to JAVA400-L@midrange.com

Sent by:  owner-java400-l@midrange.com


To:   "'JAVA400-L@midrange.com'" <JAVA400-L@midrange.com>
cc:
Subject:  RE: Stored Procedure Problem




I believe I found the problem...I forgot to add cstmt.executeQuery(); after
the prepareCall stmt.  Doh!.....Thank you sir, may I have another! Thanks
to
those that replied - I'm sure I'll be back after I change it to return a
resultSet.

MP






-----Original Message-----
From: Mike Perez
Sent: Tuesday, February 06, 2001 3:16 PM
To: Java400 (E-mail)
Subject: Stored Procedure Problem



Need some help trying to figure out what's wrong with my prepareCall
statement.  It's supposed call a stored procedure on the AS/400, but it
doesn't run, and the worst part is, it doesn't produce an sql error. I've
tested and run it on the AS/400 (within an sql session) without any
problems.

Thanks in advance,
Mike Perez


Here's the stored procedure:

 CREATE PROCEDURE PRODLIB/RUNPNLBUILD(IN CO CHAR ( 2), IN DIV CHAR
 ( 3), IN MM CHAR ( 2), IN YR CHAR ( 4), IN CNTRY CHAR ( 3), IN CURR
 CHAR ( 1)) LANGUAGE CL DETERMINISTIC NO SQL EXTERNAL NAME
 PRODLIB/GLG010C PARAMETER STYLE GENERAL

Here's how I called it from the sql session:

 CALL RUNPNLBUILD ('11', '011', '12', '2000', 'USA', 'C')

Here's my java app:

package PNL;

import java.sql.*;
import java.util.*;

public class TestExtractPNLData {


     public static void getResultSet(String system, String
collectionName) {

          Connection con;
          CallableStatement cstmt;

          try{
               // Load JDBC Driver

Class.forName("com.ibm.as400.access.AS400JDBCDriver");
          } catch(java.lang.ClassNotFoundException e) {
               System.err.print("ClassNotFoundException: ");
               System.err.println(e.getMessage());
          }

          try {
               // Connect to database.  Will be prompted
               con = DriverManager.getConnection("jdbc:as400://" +
system + "/" + collectionName);

               cstmt = con.prepareCall("{call
runpnlbuild(?,?,?,?,?,?)}");
               cstmt.setString(1, "11");
               cstmt.setString(2, "011");
               cstmt.setString(3, "12");
               cstmt.setString(4, "2000");
               cstmt.setString(5, "USA");
               cstmt.setString(6, "C");

               cstmt.close();
               con.close();
          } catch(SQLException ex) {
               System.err.println("SQLException: " +
ex.getMessage());
          }
     }
}
+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---
+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---



+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---