Blog Archive

Call RPGLE from Java using SQL Stored Procedure

A stored procedure is executable code that is associated with, and generally stored in, the database. Stored procedures usually collect and customize common operations, like inserting a tuple into a relation, gathering statistical information about usage patterns, or encapsulating complex business logic and calculations. Frequently they are used as an application programming interface (API) for security or simplicity. Implementations of stored procedures on SQL DBMSs often allow developers to take advantage of procedural extensions (often vendor-specific) to the standard declarative SQL syntax.


Stored procedures are not part of the relational database model, but all commercial implementations include them

Stored procedures must be invoked using the CALL statement.


CALL procedure(...) or EXECUTE procedure(...)

Stored procedures may return result sets, i.e. the results of a SELECT statement. Such result sets can be processed using cursors, by other stored procedures, by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. Stored procedure languages typically include IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared.

Click here for the Sample RPGLE program used for Java Stored Procedure Call


The link above is a must before proceeding further, It has the following:
  • sample RPGLE program
  • SQLRPGLE wrapper program to help create the Stored Procedure ResultSet
  • Create Procedure Statement explaining how to create the Stored Procedure.


Java sample program calling the SQLRPGLE Stored Procedure


The java main () takes 3 arguments:
  • First Name (String)
  • Last Name (String)
  • Age (String)
package com.as400samplecode;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CallRPGProcedure {

 public static void main( String[] args )
 {
  String firstName = args[0].trim();
  String lastName = args[1].trim();
  String age = args[2].trim();
  String message = "";
  Connection conn = null;

  CallableStatement stmt = null;
  ResultSet rs = null;

  try {

   String DRIVER = "com.ibm.as400.access.AS400JDBCDriver"; 
   String URL = "jdbc:as400://{host_name or IP Address};naming=system;errors=full";


   //Connect to iSeries 
   Class.forName(DRIVER); 
   conn = DriverManager.getConnection(URL,{user_id},{password}); 

   //Make sure the Job Description for the {user_id} profile has the library that has the MYNAMEQ //stored procedure
   stmt = conn.prepareCall("{ call MYNAMEQ(?,?,?) }");

   stmt.setString(1, firstName);
   stmt.setString(2, lastName);
   stmt.setInt(3, Integer.parseInt(age));
   rs = stmt.executeQuery();

   if (rs.next()) {
    message = rs.getString(1);
    System.out.println("Message is: " + message);
   } else {
    System.out.println("Stored procedure couldn't generate message !");
   }
  } catch (ClassNotFoundException ex) {
   ex.printStackTrace();
  } catch (SQLException ex) {
   ex.printStackTrace();
  } finally {

   try {

    rs.close();
    stmt.close();
    conn.close();

   } catch (SQLException ex) {
    ex.printStackTrace();
   }

  }

 }

}

Testing Results from the above application

Java Call: java com.as400samplecode.CallRPGProcedure "John" "Doe" "25"
Output: Message is: My name is John Doe age 25 years !