RE: Java Stored Procedures on ISeries

["cornelius, chad" <chad.cornelius@xxxxxxxxxxxxxxxxxxxx>]

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