Use the EXECUTE CALL command to execute a stored procedure.
This stored procedure will contain your SQL statement. The CALL command must be used in conjunction with the CALLTYPE command to specify whether it is of type *QUERY, *UPDATE, or *EXECUTE.
Syntax:
|
Parameters can be passed to the remote procedure using a working list and the SET PARAMETER(*LIST) command. Refer to the following Lists and Variables for information on how to do this.
Stored procedures are able to return a single value instead of a result set. A result set is a list of one or more records or values returned from a query whereas a return value is a single value that a stored procedure returns upon completion. Typical stored procedure syntax will have a RETURN or similar command (according to the target database syntax) and associated field name as one of the last commands executed by the stored procedure. This value could be anything, but some common examples might be a field containing the number of records selected or updated, or the maximum value, or some flag.
The RETURN keyword allows you to indicate the datatype for the returned value. The datatypes possible will depend upon what your target database and JDBC driver can support. The IBM i, as an example, only currently support a datatype of integer for values returned from a stored procedure. It is ultimately your responsibility to be aware of the datatypes that your target database and JDBC driver can handle.
Some stored procedure calls return a warning to say that they have been executed, so the warning check needs to be turned off using the SET ONWARNING(*CONTINUE) command. Warning error code 466 (result sets are available from procedure) is automatically ignored and is not treated as a warning.
Some examples of creating stored procedures follow.
Notes on Oracle
The Oracle database does not fully support the JDBC syntax when used to return a value from a database function. It will only work with Oracle syntax. This is why you need to specify the CALLSYNTAX as *ORACLE, so that the SQLService knows what to expect and how to handle it.
Oracle has two types of callable programs. One is a stored procedure and the other is a function. A stored procedure cannot return a value, so you must use a function if you want to return a value.
The Oracle JDBC driver and the Oracle database do not support the Java JDBC "ResultSet resultSet = call.executeQuery ()" method. For Oracle to return a result set to a JDBC client, an Oracle function needs to be created that returns an Oracle cursor and the JDBC call.execute () method needs to be used. If your target database is Oracle and you are expecting a result set to be returned from the stored procedure, then you must specify *ORACLECURSOR as the RETURN value for this EXECUTE command. This will then be used by the SQLService to determine which records to return. This explicit requirement to state that a cursor is being returned is only relevant to Oracle. Other databases do not need to use this keyword in association with retrieving the result set. When using this value in your keyword your Oracle function will need to have a 'RETURN cursorname' command so that the cursor is returned to the SQLService.
Oracle Stored Procedure Example
The following is an example of an Oracle database function that is creating a result set. Note that it is returning a cursor.
Detailed instructions on creating stored procedures is beyond the scope of this documentation. You are therefore encouraged to research this topic in more detail via other channels.
CREATE OR REPLACE FUNCTION "SYSTEM"."TEST" ( PARAM1 IN CHAR, PARAM2 IN CHAR ) RETURN SYS_REFCURSOR AS CURSOR_1 SYS_REFCURSOR ;
BEGIN
OPEN CURSOR_1 FOR SELECT * FROM MYTABLE WHERE FLD_1='AB';
RETURN CURSOR_1 ;
END;
When it comes to running this function with the SQLService you can use something like the following EXECUTE command:
SET PARAMETER(*LIST) #WRKLST(PARM1,PARAM2)
EXECUTE CALL("TEST(?,?)") CALLTYPE(*EXECUTE) CALLSYNTAX(*ORACLE) RETURN(*ORACLECURSOR) #WRLST(COL1,COL2)
IBM i Stored Procedure Example
To create a stored procedure for an IBM i database you need to edit a source member and use a member type of TXT.
The following are some examples of three IBM i stored procedures. You will notice that the first line specifies the name of the stored procedure. So, for example, the first one is called CALLSELECT and its location is the JSMJDBC library. The second and third examples demonstrate how a value is returned.
Detailed instructions on creating stored procedures is beyond the scope of this documentation. You are therefore encouraged to research this topic in more detail. A good place to start is the IBM Infocentre and Redbooks, both on the IBM web site as they have a wealth of information on this topic.
*************** Beginning of data *************************************
CREATE PROCEDURE JSMJDBC/CALLSELECT(IN CODE CHAR (10))
LANGUAGE SQL
READS SQL DATA
RESULT SETS 1
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR
SELECT ID,NAME,AGE FROM JSMJDBC/TBLNAME WHERE ID = CODE;
OPEN C1;
END
****************** End of data ****************************************
*************** Beginning of data *************************************
CREATE PROCEDURE JSMJDBC/CALLUPDATE(IN CODE CHAR (10))
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE num_records INTEGER;
UPDATE JSMJDBC/TBLNAME SET SALARY=12000.43 WHERE ID = CODE;
GET DIAGNOSTICS num_records = ROW_COUNT;
RETURN num_records;
END
****************** End of data ****************************************
*************** Beginning of data *************************************
CREATE PROCEDURE JSMJDBC/CALLEXECUT(IN CODE CHAR (10))
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE num_flag INTEGER DEFAULT 0;
UPDATE JSMJDBC/TBLNAME SET SALARY=16000.26 WHERE ID = CODE;
RETURN num_flag;
END
****************** End of data ****************************************
To create the stored procedures, you then need to use the RUNSQLSTM command. For example:
RUNSQLSTM SRCFILE(JSMJDBC/QCLSRC) SRCMBR(CALLSELECT) COMMIT(*NONE)
When it comes to running these stored procedures with the SQLService you can use something like the following EXECUTE command:
SET PARAMETER(*LIST) #WRKLST(PARM1)
EXECUTE CALL("CALLSELECT(?)") CALLTYPE(*QUERY)
SET PARAMETER(*LIST) #WRKLST(PARM1)
EXECUTE CALL("CALLUPDATE(?)") CALLTYPE(*UPDATE) RETURN(*INTEGER)
SET PARAMETER(*LIST) #WRKLST(PARM1)
EXECUTE CALL("CALLEXECUT(?)") CALLTYPE(*EXECUTE) RETURN(*INTEGER)
Lists and Variables
If you are using one of the following - the QUERY keyword, the PREPARED keyword where the SQL statement is a SELECT, or a CALL keyword with a CALLTYPE of *QUERY, then you may supply a working list with this command for the result set to be returned back to.
This working list will contain the fields that you are expecting to be returned from the query. The list will be filled in field sequence order. The select statement may actually return more fields than are indicated in this list, but there cannot be more list fields than columns returned. The column value is received from the result set using the resultSet.getString (column index) method. The list entry field is set with this string value and Java data type to native data type conversion is done.
If you are expecting very large lists to be returned then you may omit this working list and access the result set using the READ command.
The UPDATE keyword will not return a result set so a working list need not be supplied in this situation.
A CALL of CALLTYPE *UPDATE will return a row count (number of records updated) so a one column list may be supplied to capture this value.
If you need to pass parameters to a called procedure (using the CALL command), you will need to supply a working list with the parameters in it. This working list will have as many columns as parameters you need to pass. Only the first row of the working list will be used. The list will need to be prepared using the SET PARAMETER(*LIST) command.
Refer to the following examples and the 5.30.12 SQLService Examples to see how this works.
Examples
The SET, EXECUTE, and READ commands of the SQLService service are very tightly related to each other.
RDML
* Define the JSM command and message fields
DEFINE FIELD(#JSMSTS) TYPE(*CHAR) LENGTH(020)
DEFINE FIELD(#JSMMSG) TYPE(*CHAR) LENGTH(256)
DEFINE FIELD(#JSMCMD) TYPE(*CHAR) LENGTH(256)
* Define the fields and list that will contains the result set returned from the query
DEFINE FIELD(#COL1) TYPE(*CHAR) LENGTH(010)
DEFINE FIELD(#COL2) TYPE(*CHAR) LENGTH(020)
DEFINE FIELD(#COL3) TYPE(*DEC) LENGTH(008) DECIMALS(0)
DEF_LIST NAME(#WRKLST) FIELDS(#COL1 #COL2 #COL3) TYPE(*WORKING)
* Define the field and list that will be passed to the remote procedure to indicate what values to query
DEFINE FIELD(#PARAM1) TYPE(*CHAR) LENGTH(010)
DEF_LIST NAME(#PARAMLST) FIELDS(#PARAM1) TYPE(*WORKING)
CHANGE FIELD(#PARAM1) TO(A1001)
ADD_ENTRY TO_LIST(#PARAMLST)
* Set up the list so that it is passed to the remote procedure when the EXECUTE is run
CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*LIST) SERVICE_LIST(PARAM1)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #PARAMLST)
* Call procedure
CHANGE FIELD(#JSMCMD) TO('EXECUTE CALL("CALLSELECT(?)") CALLTYPE(*QUERY) SERVICE_LIST(COL1,COL2,COL3)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #WRKLST)
RDMLX
* Define the JSM command and message fields
Define Field(#JSMSTS) Type(*CHAR) Length(020)
Define Field(#JSMMSG) Type(*CHAR) Length(256)
Define Field(#JSMCMD) Type(*CHAR) Length(256)
Define Field(#JSMHND) Type(*Char) Length(4)
* Define the fields and list that will contains the result set returned from the query
Define Field(#COL1) Type(*CHAR) Length(010)
Define Field(#COL2) Type(*CHAR) Length(020)
Define Field(#COL3) Type(*DEC) Length(008) Decimals(0)
Def_List Name(#WRKLST) Fields(#COL1 #COL2 #COL3) Type(*WORKING)
* Define the field and list that will be passed to the remote procedure to indicate what values to query
Define Field(#PARAM1) Type(*CHAR) Length(010)
Def_List Name(#PARAMLST) Fields(#PARAM1) Type(*WORKING)
#PARAM1 := A1001
Add_Entry To_List(#PARAMLST)
* Set up the list so that it is passed to the remote procedure when the EXECUTE is run
#JSMCMD := 'Set Parameter(*LIST) Service_List(PARAM1)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #PARAMLST)
* Call procedure
#JSMCMD := 'Execute("CallSelect(?)") CallType(*QUERY)'
Use Builtin (JSM_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #WRKLST)
