Parameterized SQL Passthru

The Direct SQL Passthru facility supports parameterized SQL statements. These statements incorporate parameter markers to indicate where a value should be substituted, so you can execute the SQL statements multiple times with varying input values. The following is an example of a parameterized SQL statement: INSERT INTO INVENTORY (PARTNO) VALUES(?)

In this section: Parameterized SQL Command Summary Using the SQL Passthru BEGIN/END SESSION Commands Using the SQL Passthru COMMIT WORK Command Using the SQL Passthru ROLLBACK WORK Command Using the SQL Passthru PREPARE Command Using the SQL Passthru EXECUTE Command Using the SQL Passthru PURGE Command Using the SQL Passthru BIND Command Parameterized SQL Passthru Sample Session

The INSERT statement is executed once for each value you provide for the parameter marker (?), and a new row with that value is placed in the PARTNO column.

Note: You must use the proper form of parameter marker for the RDBMS you are accessing. DB2, DB2 for VM, IDMS SQL, and Teradata use the question mark (?) as the parameter marker. Oracle uses :00n, where n is incremented by 1 for each parameter. For example:

SQL SQLORA PREPARE I FOR INSERV INTO EMP (EMP_ID,COURSE)
VALUES(:001,:002)
END

Parameterized SQL Passthru provides the following advantages:

• You can execute an SQL statement using varying values without keying in the entire SQL statement for each value.
• Internally, it utilizes the optimal SQL for the native RDBMS.
• The execution of a FOCUS session becomes equivalent to that of a 3GL program, with a framework consisting of such elements as compiled statements and parameter markers (input values).

Note:

• All errors are posted to Dialogue Manager variables &RETCODE and &FOCERRNUM.
• Direct SQL Passthru and Parameterized SQL Passthru are not available within MODIFY.

Parameterized SQL Command Summary

With parameterized SQL you can compile, bind, and repeatedly execute a series of SQL commands. To avoid invoking END processing between the SQL statements in the series, you place the whole sequence of SQL requests within SQL BEGIN SESSION and SQL END SESSION commands.

To incorporate parameter markers in SQL statements, first compile the statements with the PREPARE command, then bind them with the BIND command, and subsequently execute them with the EXECUTE command. Place this group of actions within a BEGIN SESSION/END SESSION pair. You can also include other FOCUS, SQL, and data adapter environmental commands within the BEGIN SESSION/END SESSION pair.

Subsequent sections explain the individual commands involved in Parameterized Passthru design. Parameterized SQL Passthru Sample Session contains a sample session.

Syntax: How to Issue Parameterized Passthru Commands

SQL [sqlengine] command [;]
[TABLE FILE statement_name]
[options]
END

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

command

Is one of the following: BEGIN SESSSION, END SESSION, PREPARE, BIND, or EXECUTE.

;

For SQL SELECT requests only, the semicolon is required if you intend to specify additional FOCUS report options.

TABLE FILE

Is permitted only with PREPARE and EXECUTE commands that invoke SQL SELECT requests; invokes FOCUS report formatting options or operations. By including a TABLE FILE request, you can produce different customized reports with one SQL query. The answer set is returned at EXECUTE time. If you include a TABLE FILE request in both a PREPARE and EXECUTE command for the same SQL statement, the EXECUTE request takes precedence.

statement_name

Is the name of a PREPAREd SELECT statement.

options

Are FOCUS report-formatting options.

END

Terminates the request. Is optional for data adapter SET commands, the SQL commands COMMIT WORK and ROLLBACK WORK, the DB2 CONNECT command, and the data adapter parameterized Passthru commands BEGIN SESSION, END SESSION, and PURGE (discussed in subsequent sections). Required for all other commands.

Note: Do not confuse the SQL PREPARE and BIND statements with the RDBMS prepare and bind; the RDBMS versions are not available through the data adapter.

Using the SQL Passthru BEGIN/END SESSION Commands

The BEGIN SESSION command begins a sequence of Direct SQL Passthru commands; the END SESSION command terminates the sequence.

Syntax: How to Begin and Terminate a Sequence of Direct SQL Passthru Commands

SQL [sqlengine] {BEGIN|END} SESSION

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

BEGIN

Indicates that a sequence of SQL commands is to be passed to the RDBMS. While the BEGIN option is in effect, the END syntax that terminates each Direct SQL Passthru statement does not automatically release resources.

END

Indicates the end of a sequence of SQL commands. Closes all cursors and releases all resources. Executes actions specified in SET action ON COMMAND (see Controlling Connection Scope). Purges all statements PREPAREd inside the BEGIN SESSION/END SESSION pair.

After the END SESSION command is executed, cursors and statements PREPAREd within the BEGIN SESSION/END SESSION pair are unavailable. The sequence of statements within the BEGIN SESSION/END SESSION pair can include:

• SQL Passthru commands PREPARE, BIND, and EXECUTE.
• FOCUS commands that refer to Direct SQL Passthru-created views.
• Other FOCUS commands such as TABLE and MODIFY against SQL or other files.

Note:

• FIN issues END COMMAND and then purges all statements PREPAREd outside the BEGIN SESSION/END SESSION pair.
• FOCUS commands must use the same DB2 plan.

• SQL commands. The SQL commands COMMIT WORK and ROLLBACK WORK are particularly useful in Parameterized SQL Passthru requests.
• Environmental commands such as SET PLAN, SET SSID, DB2 CONNECT, and DB2 for VM CONNECT statements.

If you omit the BEGIN SESSION/END SESSION pair, the data adapter automatically brackets each individual Direct SQL Passthru command with BEGIN SESSION and END SESSION. The execution of the END SESSION (either implicitly or explicitly) in a Direct SQL Passthru statement invokes actions requested in SET action ON COMMAND (see Controlling Connection Scope). You can use statements PREPAREd without an explicit BEGIN SESSION/ END SESSION pair in TABLE requests, but you cannot use them in the EXECUTE statement.

Using the SQL Passthru COMMIT WORK Command

COMMIT WORK terminates a unit of work and makes all data source changes permanent.

Syntax: How to COMMIT Data Source Changes

SQL [sqlengine] COMMIT WORK

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

After execution of the COMMIT WORK command, the RDBMS drops the PREPAREd status of SQL statements; it also releases locks. If you need a PREPAREd version of the statement, you must issue the SQL PREPARE statement again.

Using the SQL Passthru ROLLBACK WORK Command

ROLLBACK WORK terminates a unit of work and restores all data changed by SQL statements to their state at the last commit point.

Syntax: How to ROLLBACK Data Source Changes

SQL [sqlengine] ROLLBACK WORK

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

After execution of the ROLLBACK WORK command, the RDBMS drops the PREPAREd status of SQL statements. If you need a PREPAREd version of the statement, you must issue the SQL PREPARE statement again.

Using the SQL Passthru PREPARE Command

The PREPARE command PREPAREs (checks syntax, then compiles) an SQL statement and stores the compiled version for later use. The SQL statement can contain parameter markers.

How to: PREPARE an SQL Statement Example: Preparing a Parameterized Passthru Command Reference: Usage Notes for PREPARE

Syntax: How to PREPARE an SQL Statement

SQL [sqlengine] PREPARE statement_name FOR sql_statement [;]

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

statement_name

Is the 1-to 8-character name of an SQL variable that will contain the PREPAREd (compiled) version of an SQL statement.

sql_statement

Is a character-string expression that can include parameter markers; it represents the SQL statement to PREPARE. The statement must be one of the following:

• UPDATE (WHERE CURRENT OF CURSOR is not supported)
• DELETE (WHERE CURRENT OF CURSOR is not supported)
• INSERT
• SELECT (Master File is created to represent the returned answer set)
• CREATE
• DROP
• ALTER
• COMMENT
• LABEL
• GRANT
• REVOKE
• COMMIT
• ROLLBACK
• LOCK
• EXPLAIN

;

Is required if sql-statement is a SELECT statement followed by TABLE FILE report options.

Example: Preparing a Parameterized Passthru Command

Consider the following SQL PREPARE command for DB2:

SQL DB2 PREPARE D FOR DELETE FROM USER1.EMPLOYEE
   WHERE EMP_ID = ?

Variable D will contain the PREPAREd version of the following SQL string:

DELETE FROM USER1.EMPLOYEE WHERE EMP_ID = ?

You supply values for the parameter marker in the statement by issuing an EXECUTE statement for variable D. The parameter marker allows you to execute the same DELETE statement many times with different values of EMP_ID. You can use a parameter marker anywhere a literal value appears in an SQL statement.

Reference: Usage Notes for PREPARE

• Answer sets for FOCUS TABLE requests in PREPAREd SELECT statements are returned at EXECUTE time even if they were specified in the PREPARE statement.

If both the PREPARE and EXECUTE commands specify a TABLE FILE request for the same statement, the EXECUTE request takes precedence.

• PREPARE for an already PREPAREd statement unprepares the statement by means of PURGE. As a side effect, BIND for this statement (if any) is cleared.
• Using the PREPARE command with the Oracle RDBMS to issue Data Definition Language statements such as CREATE or DROP, causes the statements to be executed immediately. A subsequent EXECUTE statement is not necessary.
• You must use the proper form of parameter marker for the RDBMS you are accessing. DB2, DB2 for VM, IDMS SQL, and Teradata use the question mark (?) as the parameter marker. Oracle uses :00n, where n is incremented by 1 for each parameter. For example:

SQL SQLORA PREPARE I FOR INSERV INTO EMP (EMP_ID,COURSE)
VALUES(:001,:002)
END

Using the SQL Passthru EXECUTE Command

This statement executes a previously PREPAREd SQL statement. If the SQL statement includes parameter markers, you must supply their values in the USING clause. If the SQL statement is a SELECT statement, you can use the TABLE FILE extension to produce a formatted report.

How to: Execute a Prepared SQL Command Example: Executing a Prepared Passthru Command Reference: Usage Notes for EXECUTE

Syntax: How to Execute a Prepared SQL Command

SQL [sqlengine] EXECUTE statement_name [USING data_list] [;]

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

statement_name

Is the 1-to 8-character name of an SQL variable that contains the PREPAREd (compiled) version of the SQL statement to execute. Use this name in a TABLE FILE extension if you want a formatted report.

data_list

Is a list of arguments to substitute for the parameter markers in the PREPAREd SQL statement. Separate arguments in the list with commas.

;

Is required if the PREPAREd SQL statement is a SELECT statement followed by TABLE FILE report options.

The SQL commands COMMIT WORK and ROLLBACK WORK destroy all statements PREPAREd in a unit of recovery. Thus, after a COMMIT or ROLLBACK, you must again PREPARE any statement you want to EXECUTE.

Example: Executing a Prepared Passthru Command

To execute the PREPARE statement in Preparing a Parameterized Passthru Command, issue:

SQL DB2 EXECUTE D USING 'AAA';

The DELETE statement from Using the SQL Passthru PREPARE Command is sent to the DB2 RDBMS; the RDBMS deletes all rows with an EMP_ID value of 'AAA' from the data source. The resulting SQLCODE is returned to the program.

You can execute this statement many times within the same unit of recovery by supplying different values for the EMP_ID to delete.

Reference: Usage Notes for EXECUTE

• Use of EXECUTE outside a BEGIN SESSION/END SESSION pair produces fatal error FOC1477, "Invalid use of BIND or EXECUTE".
• Use of EXECUTE for a statement PREPAREd outside a BEGIN SESSION/END SESSION pair produces fatal error FOC1477, "Invalid use of BIND or EXECUTE".
• Answer sets for FOCUS TABLE requests in compiled SELECT statements are returned at EXECUTE time even if they were specified in the PREPARE statement.

If both the PREPARE and EXECUTE commands specify a TABLE FILE request for the same statement, the EXECUTE request takes precedence.

• To specify missing values in an EXECUTE statement, you can use the word NULL or denote the missing column values by a comma. For example

EXECUTE xyz USING 8,9,NULL,,'abcd'

• Using the PREPARE command with the Oracle RDBMS to issue Data Definition Language statements such as CREATE or DROP, causes the statements to be executed immediately. A subsequent EXECUTE statement is not necessary.

Using the SQL Passthru PURGE Command

The PURGE command clears results from a previously issued PREPARE or BIND command. It is optional.

How to: Purge an SQL Command Example: Purging an SQL Command

Syntax: How to Purge an SQL Command

SQL [sqlengine] PURGE statement_name [;]

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

statement_name

Is the 1-to 8-character name of an SQL variable that contains a PREPAREd (compiled) version of an SQL statement.

Example: Purging an SQL Command

SQL DB2 PREPARE D FOR DELETE FROM USER1.EMPLOYEE
    WHERE EMP_ID = ?
 
SQL DB2 PURGE D;

Using the SQL Passthru BIND Command

You can use the BIND command to define the format of each parameter specified in a PREPARE command. The list of formats is comma delimited; each element is a data type supported by the RDBMS.

How to: Define Formats of SQL Parameters Reference: Usage Notes for BIND

Syntax: How to Define Formats of SQL Parameters

SQL [sqlengine] BIND statement_name USING format_list;

where:

sqlengine

Indicates the target RDBMS. Acceptable values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

statement_name

Is the 1-to 8-character name of an SQL variable that contains a PREPAREd (compiled) version of an SQL statement.

format_list

Is a comma-delimited list of datatypes used in the request. The following datatypes are supported by the RDBMS:

DB2 and DB2 for VM	Teradata	Oracle	IDMS SQL
SMALLINT	SMALLINT	NUMBER(m,n)	SMALLINT
INTEGER	INTEGER	VARCHAR(n)	INTEGER
DECIMAL(m,n)	DECIMAL(m,n)	LONG	DECIMAL(m,n)
FLOAT	FLOAT	CHARACTER(n)	FLOAT
REAL	BYTEINT	DATE	REAL
DOUBLE	BYTE(n)	RAW	DOUBLE
VARCHAR(n)	VARBYTE(n)		VARCHAR(n)
LONGVARCHAR(n)	VARCHAR(n)		LONGVARCHAR(n)
CHARACTER(n)	LONG VARCHAR(n)		CHARACTER(n)
DATE	CHARACTER(n)
	DATE
	TIME
	TIMESTAMP

Reference: Usage Notes for BIND

• The Oracle data type LONGRAW is currently not supported.
• If a statement is PREPAREd and EXECUTEd without a corresponding BIND, the data adapter uses default formats based on the RDBMS storage formats it detects for the columns referenced by parameter markers in the statement.
• Use of BIND outside a BEGIN SESSION/END SESSION pair produces fatal error FOC1477, "Invalid use of BIND or EXECUTE".
• BIND without parameters clears a previous BIND for the statement.

BIND is also cleared when you issue PREPARE for an already PREPAREd statement.

Parameterized SQL Passthru Sample Session

The following sample session illustrates the design of a Parameterized SQL application:

SQL DB2 BEGIN SESSION
 
SQL DB2 PREPARE ABC FOR UPDATE STARS SET NAME=? WHERE DISTANCE=? ;
END
 
SQL DB2 PREPARE DEF FOR SELECT * FROM STARS WHERE DISTANCE=? AND DENSITY=?;
END
 
SQL DB2 BIND ABC USING CHAR(6),DECIMAL(5,0);
END
 
SQL DB2 BIND DEF USING DECIMAL(5,0),DECIMAL(6,2);
END
 
-* repeat with different input data...
    SQL DB2 EXECUTE ABC USING 'GAMMA',555. ;
    END
 
    SQL DB2 EXECUTE ABC USING 'DELTA',777. ;
    END
 
    SQL DB2 EXECUTE ABC USING 'ALPHA',9640. ;
    END
 
    SQL DB2 EXECUTE DEF USING 555.,324.27; TABLE FILE DEF PRINT *
    END
-* end repeat
 
SQL DB2 COMMIT WORK ;
END
 
SQL DB2 END SESSION

Notice that the BIND commands provide formats and the EXECUTE commands provide values for the parameter markers. Since DEF represents a SELECT statement, the EXECUTE command for DEF can include a TABLE FILE DEF request. Alternatively, the TABLE FILE DEF request could have been included in the PREPARE DEF command instead of in the EXECUTE DEF command.