RE: Java Stored Procedures on ISeries

Gary,

Sure...  I created a .sqlj file called JavaStoredProc with the package name
gov.co.state.cac.util.  I then ran SQLJ on the file followed by javac, which
created four files: Defined_iter.class, JavaStoredProc.class,
JavaStoredProc_SJProfile0.ser, and JavaStoredProc_SJProfileKeys.class.  I
then created a jar filed called storedprocs.jar and ftp'd it to our system
under /home/JavaStoredProcs.  I logged onto our system (AS/400 running V5R2)
and went to an interactive SQL session.  I installed the jar file by
executing CALL
QSYS2/INSTALL_JAR('file:/home/JavaStoredProcs/storedprocs.jar',
CAC.STOREDPROCS.JAR', 0).  I checked the SYSJAROBJECTS and SYSJARCONTENTS
tables and saw that the jar was installed.  I then created the procedure
with the following syntax:  The external name might be the problem

CREATE PROCEDURE TESTPROC                      
(                                              
 OUT sqlCode INTEGER,                          
 OUT sqlState CHAR(40),                        
 OUT sqlMessage CHAR(50)                       
)                                              
EXTERNAL NAME 'JAVASTOREDPROCS.JAVASTOREDPROCS'
RESULT SETS 1                                  
LANGUAGE JAVA                                  
PARAMETER STYLE JAVA NOT FENCED                

Just in case, here is the test code for JavaStoredProc.sqlj.  I do not see
the external name under JAVASTOREDPROCS.JAVASTOREDPROCS, however, I do see
it under table SYSROUTINES.

package gov.co.state.cac.util;

////////////////////////////////////////////////////
// SQLJ Stored Procedure Test
////////////////////////////////////////////////////
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.math.*;

#sql iterator Defined_iter (String, int, int, double);

public class JavaStoredProc {

public static void JavaStoredProc(int sqlCode, String sqlState, String
sqlMessage, ResultSet[] out_res) throws SQLException, Exception {
    try {
      Defined_iter iter;
      #sql iter = {
          SELECT
           PARTYNAME, IDAPPT, EIDNBR, AMTREIMB
          FROM CACREMB01P
      };
      out_res[0] = iter.getResultSet();
    } catch (SQLException sqlE) {
      System.out.println("JavaStoredProc - " + sqlE);
      sqlCode = sqlE.getErrorCode();
      sqlState = sqlE.getSQLState();
      sqlMessage = sqlE.getMessage();
    } catch (Exception e) {
      System.out.println("JavaStoredProc - " + e);
    }
  }
}

Thanks for the help,
Chad


-----Original Message-----
From: Gary L Peskin [mailto:garyp@xxxxxxxxxxxx] 
Sent: Tuesday, September 14, 2004 5:17 PM
To: 'Java Programming on and around the iSeries / AS400'
Subject: RE: Java Stored Procedures on ISeries

What is the name of your stored procedure class?  Please show us the syntax
by which you registered the stored procedure.  What release of OS/400 are
you on?  Using WRKLNK or Operations Navigator, do you see
/QIBM/UserData/OS400/SQLLib/Function/JAVASTOREDPROCS.JAVASTOREDPRO?  If so,
what are the authorities?

Gary 

> -----Original Message-----
> From: java400-l-bounces@xxxxxxxxxxxx 
> [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of cornelius, chad
> Sent: Tuesday, September 14, 2004 12:55 PM
> To: 'JAVA400-L@xxxxxxxxxxxx'
> Subject: Java Stored Procedures on ISeries
> 
> I have created a test stored procedure that should return a 
> result set using SQLJ.  I have compiled, built a jar file, 
> registered the stored procedure in the order that IBM's red 
> books suggest.  When I try to execute the procedure, I 
> continually get a "Not authorized to object 
> JAVASTOREDPROCS.JAVASTOREDPRO in 
> /QIBM/UserData/OS400/SQLLib/Function type *N."  Does anyone 
> know what I am doing wrong or what is causing this error?
> The system admin. has given me full authority to library 
> /QIBM/UserData/OS400/SQLLIB/Functions.
> 
>  
> 
> Thanks,
> 
> Chad
> 
>  
> 
> --
> This is the Java Programming on and around the iSeries / 
> AS400 (JAVA400-L) mailing list To post a message email: 
> JAVA400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change 
> list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx Before posting, 
> please take a moment to review the archives at 
> http://archive.midrange.com/java400-l.
> 

--
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.