EXECUTE

EXECUTE

The EXECUTE command is used to run SQL statement against the remote database.

                                                        Conditional

 EXECUTE --------- QUERY ----------- value ----------------------->

                                     *SQLPARAMETER

               >-- UPDATE ---------- value ----------------------->

                                     *SQLPARAMETER

               >-- PREPARED -------- value ----------------------->

                                     *SQLPARAMETER

               >-- CALL ------------ value ----------------------->

               >-- CALLTYPE -------- *QUERY ---------------------->

                                     *UPDATE

                                     *EXECUTE

               >-- CALLSYNTAX ------ *JDBC ----------------------->

                                     *ORACLE

               >-- RETURN ---------- *NONE -----------------------|

                                     *CHAR

                                     *STRING

                                     *SMALLINT

                                     *INTEGER

                                     *FLOAT

                                     *DOUBLE

                                     *DECIMAL

                                     *NUMERIC

                                     *ORACLECURSOR

Keywords

Comments / Warnings

The EXECUTE command is very powerful. It is important that you understand its capability in order to take full advantage of it.

The following details when you should use each command and how best to use it. It is recommended that you read this thoroughly before you proceed.

QUERY

If you want to run a query on a remote database and you are expecting a result set to be returned (for example, values to be returned from a select), then you would typically use the EXECUTE QUERY(SQL statement) command. Refer to the following List and Variables for information on how to retrieve the result set.

If you want to use a very large SQL statement then you have the option of preparing the statement using the SET PARAMETER(*SQL) command, then using the EXECUTE QUERY(*SQLPARAMETER) command.

Also refer to the CALL and PREPARED keywords.

UPDATE

The EXECUTE UPDATE(SQL statement) command will typically be used when you want to update the database using an insert, update, or delete. A result set will not be returned. With this in mind, you will therefore not use this to query data.

If you want to use a very large SQL statement then you have the option of preparing the statement using the SET PARAMETER(*SQL) command, then using the EXECUTE QUERY(*SQLPARAMETER) command.

Also refer to the CALL and PREPARED keywords.

PREPARED

If you were intending to run the same SQL statement more than once then you would be recommended to use the PREPARED command, as it will normally reduce the execution time.

This is achieved by sending the prepared statement to the database and compiling it once. This means that each time it is executed it can run immediately without having to be compiled first. Without taking this approach, we would have to put our UPDATE command into a loop and the statement would have to re-establish a connection to the database every single time.

The prepared SQL statement can be run with no parameters, but in most cases the advantage is gained for those that take parameters. Parameters are passed to the SQL statement by a working list created in conjunction with the SET PARAMETER(*LIST) command. So, for example, you might want to update the address details for a number of employees in one go. Using a prepared SQL and passing a list of the data to be updated will be the most efficient way of doing it.

If you want to use a very large SQL statement then you have the option of preparing the statement using the SET PARAMETER(*SQL) command, then using the EXECUTE QUERY(*SQLPARAMETER) command.

If a prepared SQL statement starts with INSERT and ends with VALUES(*CALC) or VALUES(*ALL) then special parsing of the SQL statement is done. The *CALC option means that a binding parameter for column is calculated and the statement is modified. The *ALL option means that a binding parameter for each column is calculated and all other columns in the table are included using a default value.

CALL

This keyword allows you to call a stored procedure on the remote server. 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.

Stored procedures can provide a very efficient way of running your SQL statements. For example, they can reduce network traffic if a lot of data is being returned. You are encouraged to research this topic in more detail. The IBM Infocentre and Redbooks, both available from the IBM web site, have a wealth of information on this topic.

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.

Some stored procedure calls return a warning to say that they been executed, so the warning check needs to be turned off using the SET ONWARNING(*CONTINUE) command. The warning error code 466 (result sets are available from proceudre) is ignored and is not treated as a warning.

Some examples of this command and how to create stored procedures are provided in the SQLService Examples. Some examples of stored procedures can be found at the end of this section.

CALLSYNTAX

The Oracle JDBC Driver does not fully support the JDBC syntax when it is used to return a value from a database function. It uses its own Oracle syntax. With this in mind, you need to use this keyword to indicate whether or not you are using standard JDBC (in which case specify *JDBC) or the Oracle JDBC (in which case specify *ORACLE). This will tell the SQLService what style of JDBC driver it should be prepared for.

RETURN

Stored procedures are able to specify a return value. This is something different to returning 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.

This 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.

Please refer to the Notes on Oracle if you are using this SQLService with an Oracle database.

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 one.

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 indeed 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.

Further to this, the JDBC client needs to prepare the call using Oracle syntax and not the industry standard JDBC syntax.

An example of an Oracle function is provided in Examples following.

Handling Parameters Using the SET PARAMETER(*LIST) Command

If you are running an EXECUTE command in conjunction with a SET PARAMETER(*LIST) command then a list of the data is being passed to the remote database along with the SQL statement. When taking this approach your SQL statement will need to include a '?' for each value that needs to be inserted into the command. These are referred to as parameter binding positions. Essentially, the first column in the list will be placed where the first '?' is, the second column will be placed where the second '?' is and so forth. The code examples provide some good examples of how this works.

Notes on SQL Syntax

Please also note that the syntax that you use will be relevant to the your target database, so you will need to know the syntax for the JDBC driver that you are using.

You will need to place quotes around string values inside your SQL statements. For example:

 EXECUTE UPDATE(UPDATE TABLE SET COL1='value')

 EXECUTE UPDATE(DELETE FROM TABLE WHERE COL1='value')

If your SQL statement contains open or closed brackets, you will need to include double quotes around the entire statement. For example:

 EXECUTE PREPARED("INSERT INTO TABLE (COL1,COL2,COL3) VALUES(?,?,?)")

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 SQLService Examples to see how this works. For information on how to supply a working list to service commands from RDML, RDMLX or 3GL applications, refer to Java Service Manager Clients.

Examples

The SET, EXECUTE, and READ commands of the SQLService service are very tightly related to each other. As such, you are recommended to review the more extensive examples in SQLService Examples.

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) Service_List(COL1,COL2,COL3)'

Use Builtin (JSM_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #WRKLST)

IBM i Examples of Stored Procedures:

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

EXECUTE CALL("CALLSELECT(?)") CALLTYPE(*QUERY)

EXECUTE CALL("CALLUPDATE(?)") CALLTYPE(*UPDATE) RETURN(*INTEGER)

EXECUTE CALL("CALLEXECUT(?)") CALLTYPE(*EXECUTE) RETURN(*INTEGER)

Oracle Example of a Database Function:

The following is an example of an Oracle database function that is creating a result set. As such, 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

EXECUTE CALL("TEST(?,?)") CALLTYPE(*EXECUTE) CALLSYNTAX(*ORACLE) RETURN(*ORACLECURSOR) SERVICE_LIST(COL1,COL2)