Call a stored procedures that perform an update

Got this nifty bit of script from Janne Lahteenmaki for calling a stored procedure that performs an update, and since Janne has been sent his Metamerge pen for community contribution, it's only proper that the community receives the tribute.

In this example there is a JDBC Connector in the AL named 'connDB2':

var command = "{call LLP.FOOBARPROC (?,?,?)}";

var rs = null;

var cstmt = null;

// wrapping it all in a try-catch in case something goes awry

try {

   // Stored procedure call

   // Get open connection from JDBC Connector

   con = connDB2.getConnector().getConnection();

   // Prepare the statement

   cstmt = con.prepareCall(command);

   // Assign IN parameters (use positional placement)

   cstmt.registerOutParameter (2, java.sql.Types.INTEGER);

   cstmt.registerOutParameter (3, java.sql.Types.INTEGER);

   cstmt.setString(1, "1");

   // Make the call

   cstmt.execute();

   // Get results

   var out1 = cstmt.getInt(2);

   var out2 = cstmt.getInt(3);

   // Print them out

   task.logmsg("out1="+out1);

   task.logmsg("out2="+out2);

}

catch(e) { // oh oh - something bad happened

   task.logmsg("EXCEPTION"+e);

   var out1 = cstmt.getInt(2);

   var out2 = cstmt.getInt(3);

   task.logmsg("out1="+out1);

   task.logmsg("out2="+out2);    

}

His stored procedure looks like this:

CREATE PROCEDURE foobarproc (

   IN ikayttajatun    CHAR(8),

   -- output arvot

   OUT out_pk1 INTEGER,

   OUT out_pk2 INTEGER

)

P1: BEGIN

   -- Declare cursor

   --               ################################################################

   -- # Replace the SQL statement with your statement.

   -- #  Note: Be sure to end statements correctly (usually ';')

   -- #

   -- # The example SQL statement SELECT NAME FROM SYSIBM.SYSTABLES

   -- # returns all names from SYSIBM.SYSTABLES.

   -- ######################################################################

   -- paluukoodit

INSERT INTO TFB (ID) VALUES (ikayttajatun);

   SET out_pk1 = 0;

   SET out_pk2 = 0;

END P1

Thanks, Janne, for sharing!