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




As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.