RE: Java Stored Procedures on ISeries

[John Eberhard <jeber@xxxxxxxxxx>]

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