RE: Java Stored Procedures on ISeries -- JAVA400-L

John,

That seemed to work...  At least it is running anyway.  I am getting some
sort of exception within the procedure itself now.  I will have to try and
debug.  IBM's suggestion of debugging in the red book does not seem to work
though.

Thanks,
Chad


-----Original Message-----
From: John Eberhard [mailto:jeber@xxxxxxxxxx] 
Sent: Wednesday, September 15, 2004 10:22 AM
To: cornelius, chad
Cc: cornelius, chad; java400-l@xxxxxxxxxxxx
Subject: RE: Java Stored Procedures on ISeries





You probably need to create a symbolic link for runtime.zip.  Here's some
documentation I found on the web.

  Document Number:   29531649
  ____________________________________________________________
  Functional Area:                       DB2 UDB for OS/400
  SubFunctional Area:               Native JDBC
  SubSubFunctional Area:       General
  ____________________________________________________________
  Product:  OS/400 BASE (5722SS100)
  Release:  V5R2M0

  Classification:       IBM Internal Use

  Keywords:
____________________________________________________________
 Document Title:runtime.zip Required for Java Stored Procedures and
User-Defined Functions  Document Description:
  Because a Java stored procedure or user-defined function (UDF) may use
SQLJ, the Java Virtual Machine (JVM) started to run the stored procedure or
UDF must initialize the SQLJ environment.  This requires the SQLJ runtime
support contained in runtime.zip.  R510 and R520 OS/400 include runtime.zip
in the user classpath; however, newer versions of the JDK require that the
runtime.zip file be loaded by the extensions classloader.  If runtime.zip
cannot be loaded, the application encounters the following message: SQL4304
- Java stored procedure or user-defined function &1, specific name &2 could
not load Java class &3 for return code of 5 (MSGSQL4304 RC5).

To add runtime.zip to the extensions classpath, create a symbolic link
(symlink) to runtime.zip in the directory /QIBM/UserData/Java400/ext.
To create the symlink use the following command in a QSHell (QSH) command
prompt:

ln -s /QIBM/ProdData/OS400/Java400/ext/runtime.zip
/qibm/userdata/java400/ext/runtime.zip

Alternatively the Add Link (ADDLNK) command can be used.  On the OS/400
command line, type the following:

ADDLNK OBJ('/qibm/ProdData/OS400/Java400/ext/runtime.zip')
NEWLNK('/qibm/userdata/Java400/ext/runtime.zip')

Press the Enter key.

The next version of iSeries Information Center will be updated to include
the setup information listed above.






                                                                           
             "cornelius, chad"                                             
             <chad.cornelius@j                                             
             udicial.state.co.                                          To 
             us>                       John Eberhard/Rochester/IBM@IBMUS,  
                                       java400-l@xxxxxxxxxxxx              
             09/15/2004 11:12                                           cc 
             AM                        "cornelius, chad"                   
                                       <chad.cornelius@xxxxxxxxxxxxxxxxxxx 
                                       s>                                  
                                                                   Subject 
                                       RE: Java Stored Procedures on       
                                       ISeries                             
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           




John,

I have made the changes you suggested.  I am no longer receiving the
Authority error, but I am now receiving the following exception:

java.sql.SQLException: [SQL4304]  Java stored procedure or user-defined
function TESTPROC, specific name  could not load Java class
gov/co/state/cac/util/JavaStoredProc for reason code 5.

I found the error codes and have tried IBM's solutions with the exception
of
the CLASSPATH.  I am assuming /QIBM/UserData/OS400/SQLLIB/Functions/jar is
the classpath on the 400.  Is there any way to check that?

Here is what IBM suggests:
1 -- The class was not found on the CLASSPATH.
2 -- The class did not implement the required interface
("com.ibm.db2.app.StoredProc" or "com.ibm.db2.app.UDF") or lacked the Java
"public" access flag.
3 -- The default constructor failed or was unavailable.

Here is my new version of the class:

package gov.co.state.cac.util;

////////////////////////////////////////////////////
// SQLJ Stored Procedure Test
////////////////////////////////////////////////////
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.math.*;
import com.ibm.db2.app.*;

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

public class JavaStoredProc extends StoredProc {

  public static void TestProc(int[] sqlCode, String[] sqlState, String[]
sqlMessage, ResultSet[] out_res) throws SQLException, Exception {
    try {
      StoredProc store = new StoredProc();
      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[0] = sqlE.getErrorCode();
      sqlState[0] = sqlE.getSQLState();
      sqlMessage[0] = sqlE.getMessage();
    } catch (Exception e) {
      System.out.println("JavaStoredProc - " + e);
    }
  }

  public JavaStoredProc() {
  }
}

Any suggestions??

Thanks,
Chad


-----Original Message-----
From: John Eberhard [mailto:jeber@xxxxxxxxxx]
Sent: Wednesday, September 15, 2004 7:00 AM
To: java400-l@xxxxxxxxxxxx
Cc: chad.cornelius@xxxxxxxxxxxxxxxxxxxx
Subject: RE: Java Stored Procedures on ISeries





I see three problems.

1.  POSSIBLE JARID PROBLEM

>> I installed the jar file by executing CALL
>> QSYS2/INSTALL_JAR('file:/home/JavaStoredProcs/storedprocs.jar',
>> 'CAC.STOREDPROCS.JAR', 0)

The jarid with three periods may be contributing to the problem.  Try using
this.
CALL
QSYS2/INSTALL_JAR('file:/home/JavaStoredProcs/storedprocs.jar',
'CAC.STOREDPROCS', 0)

2. EXTERNAL NAME PROBLEM

>> 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

For the external name, you need to indicate the JARID that contains the
classfile
as well as the entire name of the class and method you wish to call.
So, for the previous JARID and the method, you would use the following
external name

EXTERNAL NAME  'CAC.STOREDPROCS:gov.co.state.cac.util.JavaStoredProc.
JavaStoredProc'

One minor point. You probably don't want have your class and method have
the same name.
If you use the same name, the method could be confused as being a
constructor for that object.


3.  OUTPUT PARAMETER PROBLEM.

>>  public static void JavaStoredProc(int sqlCode, String sqlState, String
>> sqlMessage, ResultSet[] out_res) throws SQLException, Exception {

In the create procedure statement, the variable were declared as output.
Since there are output variables, they should be passed as one element
arrays.
So, your code should look like the following.

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[0] = sqlE.getErrorCode();
      sqlState[0] = sqlE.getSQLState();
      sqlMessage[0] = sqlE.getMessage();
    } catch (Exception e) {
      System.out.println("JavaStoredProc - " + e);
    }
  }
}