TDIing out loud

Ramblings on the paradigm-shift that is TDI.

Monday, October 1, 2012

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!

No comments: