Using JSM for calling Stored Procedures

Date:	7 September 2012
Product/Release:	LANSA Integrator - All Versions
Abstract:	JSM samples for calling SQL Stored Procedures
Submitted By:	LANSA Technical Support

SQLService can be used to allow calling (CALL Command) of stored procedures, using IN, OUT and INOUT parameters.

You define each parameter by adding it to a working list in the same order as the stored procedure.

• Use the SET PARAMETER(*CALL) to pass the working list to the SQLService.
• Use the GET OBJECT(*PARAMETERCALL) to get the working list from the SQLService.
• The EXECUTE CALL command will update the parameter call working list with returned values.
• You can still use return parameter and results with this command.

Sample Stored Procedure and JSM Function with Call

IBM i stored Procedure

CREATE PROCEDURE JSMJDBC/CALLEXEOUT ( IN CODE CHAR(10), INOUT STS CHAR(10), OUT VAL INT )
LANGUAGE SQL 
MODIFIES SQL DATA 

BEGIN 
DECLARE SQLCODE INTEGER DEFAULT 0; 
DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; 
UPDATE JSMJDBC/TBLNAME SET SALARY=16000.26 WHERE ID = CODE;
SET VAL = 34; 
SET STS = 'DONE'; 
END 

JSM Function

* ********* Beginning of RDML commands ********** 
FUNCTION OPTIONS(*DIRECT) 
* ********* 
DEFINE FIELD(#JSMSTS) TYPE(*CHAR) LENGTH(20) 
DEFINE FIELD(#JSMMSG) TYPE(*CHAR) LENGTH(255) 
DEFINE FIELD(#JSMCMD) TYPE(*CHAR) LENGTH(255) 
* ********* 
DEFINE FIELD(#CDIR) TYPE(*CHAR) LENGTH(6) 
DEFINE FIELD(#CTYP) TYPE(*CHAR) LENGTH(10) 
DEFINE FIELD(#CVAL) TYPE(*CHAR) LENGTH(50) 
DEF_LIST NAME(#CALLLST) FIELDS((#CDIR) (#CTYP) (#CVAL)) TYPE(*WORKING) 
* ********* 
* ********* 'Open service' 
* ********* 
USE BUILTIN(JSM_OPEN) TO_GET(#JSMSTS #JSMMSG) 
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) 
* ********* 
* ********* 'Load service' 
* ********* 
USE BUILTIN(JSM_COMMAND) WITH_ARGS('SERVICE_LOAD SERVICE(SQLSERVICE) TRACE(*YES)') TO_GET(#JSMSTS #JSMMSG) 
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) 
* ********* 
CHANGE FIELD(#JSMCMD) TO('CONNECT DRIVER(DB2) DATABASE(JSMJDBC) USER(ALICK) PASSWORD(xxxx)') 
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG) 
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) 
* ********* 
USE BUILTIN(JSM_COMMAND) WITH_ARGS('SET ONWARNING(*STOP) SQLSTATE(*ERROR)') TO_GET(#JSMSTS #JSMMSG) 
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) 
* ********* 
* ********* Create call parameter 
* ********* 
CLR_LIST NAMED(#CALLLST) 

CHANGE FIELD(#CDIR) TO('''*IN''') 
CHANGE FIELD(#CTYP) TO('''*CHAR''') 
CHANGE FIELD(#CVAL) TO(A1002) 
ADD_ENTRY TO_LIST(#CALLLST) 

CHANGE FIELD(#CDIR) TO('''*INOUT''') 
CHANGE FIELD(#CTYP) TO('''*CHAR''') 
CHANGE FIELD(#CVAL) TO(SOMETHING) 
ADD_ENTRY TO_LIST(#CALLLST) 

CHANGE FIELD(#CDIR) TO('''*OUT''') 
CHANGE FIELD(#CTYP) TO('''*INTEGER''') 
CHANGE FIELD(#CVAL) TO(*BLANK) 
ADD_ENTRY TO_LIST(#CALLLST) 
********** 
CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*CALL) SERVICE_LIST(CDIR,CTYP,CVAL)') 
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #CALLLST) 
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) 
* ********* 
* ********* Call procedure 
* ********* 
CHANGE FIELD(#JSMCMD) TO('EXECUTE CALL("CALLEXEOUT(?,?,?)") CALLTYPE(*EXECUTE)') 
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG) 
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) 
* ********* 
* ********* Get call parameter 
* ********* 
CHANGE FIELD(#JSMCMD) TO('GET OBJECT(*PARAMETERCALL) SERVICE_LIST(CDIR,CTYP,CVAL)') 
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #CALLLST) 
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) 
********** 
SELECTLIST NAMED(#CALLLST) 
DISPLAY FIELDS((#CDIR) (#CTYP) (#CVAL)) 
ENDSELECT 
* ********* 
USE BUILTIN(JSM_COMMAND) WITH_ARGS('DISCONNECT') TO_GET(#JSMSTS #JSMMSG) 
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) 
* ********* 
* ********* 'Close service' 
* ********* 
USE BUILTIN(JSM_CLOSE) TO_GET(#JSMSTS #JSMMSG) 
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) 
* ********* 
* ********* SUB ROUTINES 
* ********* 
SUBROUTINE NAME(CHECK) PARMS((#JSMSTS *RECEIVED) (#JSMMSG *RECEIVED)) 
* ********* 
IF COND('#JSMSTS *NE OK') 
* ********* 
DISPLAY FIELDS((#JSMSTS)(#JSMMSG)) 
USE BUILTIN(JSM_CLOSE) TO_GET(#JSMSTS #JSMMSG)
* ********* 
MENU MSGTXT('Java service error has occured') 
* ********* 
ENDIF 
* ********* 
ENDROUTINE 
* ********* End of RDML commands **********