RE: Stored Procedure Problem

[Jerrold_Bisera@xxxxxxxxxxxxxxx]

You should be using PARAMETER STYLE SQL.





"Clapham, Paul" <pclapham@core-mark.com>@midrange.com on 02/06/2001
02:07:49 PM

Please respond to JAVA400-L@midrange.com

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


To:   JAVA400-L@midrange.com
cc:

Subject:  RE: Stored Procedure Problem


If you added a line like "cstmt.execute()", between "setString(6)" and
"close", would it make a difference?

PC2

-----Original Message-----
From: Mike Perez [mailto:MPEREZ@tycoint.com]
Sent: February 6, 2001 13:16
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
+---