Implementing Java stored procedure IBM DB2 for i (AS400)

Question

I created the Java stored procedure as follows, able to generate a .class file using AS400 Qshell command interpreter.

    import java.sql.*;

    public class sample 
    {
        public sample(){
            super();
        }

        /**
         * @param args
         */
        public static void Test(int test) throws SQLException, Exception {
            // TODO Auto-generated method stub
            try
            {   Class.forName ("com.ibm.as400.access.AS400JDBCDriver").newInstance ();

                String url  = "jdbc:as400://ilava;naming=system;prompt=false;user=IPGUI;password=IPGUI;libraries=IPSRFILI,IPTSFILI,IPWMFILI,IPPAFILI,IPASFILI,IPSAFILI,IPTSUTL;translate binary=true";          
                Connection con = DriverManager.getConnection(url);          
                PreparedStatement ps = con.prepareStatement("INSERT INTO IPTSFILI.TEMP VALUES ('IP', 'WELCOME TO SQLJ')");
                ps.executeUpdate();
                ps.close();
                con.close();                
            } 
            catch (Exception e)
            {  
                System.out.println (e);
                System.exit(1);             
            }                       
    }       
 }

created the respective store procedure as

CREATE procedure IPTSFILI.SPSAMPLE ( 
    IN TEST INTEGER ) 
    LANGUAGE JAVA 
    SPECIFIC IPTSFILI.SPSAMPLE 
    DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'sample!Test' 
    PARAMETER STYLE JAVA ;

When the procedure is getting called (Using callable Statement in JAVA) it gives below error as

java.sql.SQLException: [SQL4304]  Java stored procedure or user-defined function SPSAMPLE, specific name SPSAMPLE could not load Java class Ä?_ÑÂ_À¦ÀÂÄ/øøàâàÊÑÎÁÊ for reason code 3.
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:687)
    at com.ibm.as400.access.JDError.throwSQLException(JDError.java:653)
    at com.ibm.as400.access.AS400JDBCStatement.commonExecute(AS400JDBCStatement.java:920)
    at com.ibm.as400.access.AS400JDBCPreparedStatement.execute(AS400JDBCPreparedStatement.java:1018)
    at Exec.main(Exec.java:23)

Placed the .class file in the QIBM\UserData\OS400\SQLLib\Function directory.

Any suggestions on how to solve this?

Answer 1

You might not specify the procedure in a correct way. Looking at the documentation of OS400 and Java stored proc (see Chap 7) I think you are missing several steps here. I advise to take the code from the doc and run it, then replace with your own implementation.

Answer 2

You have some problems with your Java code.

1. You should not use System.out in your Java stored procedure. Due to the nature of the system, System.out does not work properly if the Java stored procedure is invoked in a recycled QZDASOINIT or QSQSRVR job.

2. You should not use System.exit(1) in your call. That call will end the JVM and once the JVM is ended, it cannot be restarted.

3. You probably should be using the native JDBC driver if are accessing tables on the same system where the Java stored procedure is defined. To get a JDBC connection that uses the native driver, you should call DriverManager.getConnection("jdbc:default:connection");

4. If you don't catch the exception, most JDBC exceptions will come back correctly if you call the stored procedure using the JDBC client.

I updated the program to look like the following.

import java.sql.*;

public class sample 
{
    public sample(){
        super();
    }

    /**
     * @param args
     */
    public static void Test(int test) throws SQLException, Exception {

           String url  = "jdbc:default:connection";
            Connection con = DriverManager.getConnection(url);          
            PreparedStatement ps = con.prepareStatement("INSERT INTO IPTSFILI.TEMP VALUES ('IP', 'WELCOME TO SQLJ')");
            ps.executeUpdate();
            ps.close();
            con.close();                
}       

}

When I called it, I get the following exception (since I did not create the file on my system).

SQLState: 42704
Message:  [SQL0204] TEMP in IPTSFILI type *FILE not found. 
Vendor:   -204

You will also want to check that the JOB CCSID of the database server job is not 65535. You should have received the following error if your JOB CCSID was 65535, but I suspect releases earlier than 6.1 may not have detected this correctly.

 CALL QSYS.QCMDEXC('Chgjob ccsid(65535)                ',000000020.00000)
 call SPSAMPLE(3)

 *** SQLException caught ***
 Statement was call SPSAMPLE(3)
 SQLState: 57017
 Message:  [SQL0332] Character conversion between CCSID 65535 and CCSID 1200 not valid.
 Vendor:   -332