Re: Stored Procedure Problem

["Tom Heiber-Cobb" <heibercobb@xxxxxxxxxxx>]

Mike,
        Am I mistaken or is it a fact that you never call "executeQuery" or any
other method to actually invoke the stored procedure? I mean, right after
you set all of the parms you close the callable statement object! You need
to invoke a method to have the SP run and then either get the result set or
some other feedback from having it run.
One possible example might be:

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

=============>> int rowcount = cstmt.executeQuery();

>                       cstmt.close();
>                       con.close();
>               } catch(SQLException ex) {
>                       System.err.println("SQLException: " +
> ex.getMessage());
>               }

Not sure what the proper way to call it is. That is, whether you should
expect a result set or if you actually receive a row count but I do know
you have to call it one way or another.
Good luck,
        Tom H-C

----------
> From: Mike Perez <MPEREZ@tycoint.com>
> To: Java400 (E-mail) <java400-l@midrange.com>
> Subject: Stored Procedure Problem
> Date: Tuesday, February 06, 2001 3:16 PM
> 
> 
> 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
+---