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(?)
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, 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 INSERT INTO EMP (EMP_ID,COURSE) VALUES(:001,:002) END
Parameterized SQL Passthru provides the following advantages:
Note:
How to: |
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 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.
{ENGINE|SQL} [sqlengine] command [;]
[TABLE FILE statement_name]
[options]
END
where:
Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE 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.
Is permitted only with PREPARE and EXECUTE commands that invoke SQL SELECT requests. This 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.
Is the name of a PREPAREd SELECT statement.
Are FOCUS report-formatting options.
Terminates the request. Is optional for adapter SET commands, the SQL commands COMMIT WORK and ROLLBACK WORK, the DB2 CONNECT command, and the 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 adapter.
How to: |
The BEGIN SESSION command begins a sequence of Direct SQL Passthru commands. The END SESSION command terminates the sequence.
{ENGINE|SQL} [sqlengine] {BEGIN|END} SESSION
where:
Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.
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.
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:
Note:
If you omit the BEGIN SESSION/END SESSION pair, the 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.
How to: |
COMMIT WORK terminates a unit of work and makes all data source changes permanent.
{ENGINE|SQL} [sqlengine] COMMIT WORK
where:
Indicates the target RDBMS. Acceptable values are DB2, 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.
How to: |
ROLLBACK WORK terminates a unit of work and restores all data changed by SQL statements to their state at the last commit point.
{ENGINE|SQL} [sqlengine] ROLLBACK WORK
where:
Indicates the target RDBMS. Acceptable values are DB2, 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.
How to: Reference: |
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.
{ENGINE|SQL} [sqlengine] PREPARE statement_name FOR sql_statement [;]
where:
Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.
Is the 1-to 8-character name of an SQL variable that will contain the PREPAREd (compiled) version of an 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:
Is required if sql-statement is a SELECT statement followed by TABLE FILE report options.
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.
If both the PREPARE and EXECUTE commands specify a TABLE FILE request for the same statement, the EXECUTE request takes precedence.
SQL SQLORA PREPARE I FOR INSERV INTO EMP (EMP_ID,COURSE) VALUES(:001,:002) END
How to: Reference: |
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.
{ENGINE|SQL} [sqlengine] EXECUTE statement_name [USING data_list] [;]
where:
Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.
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.
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.
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.
If both the PREPARE and EXECUTE commands specify a TABLE FILE request for the same statement, the EXECUTE request takes precedence.
EXECUTE xyz USING 8,9,NULL,,'abcd'
How to: |
The PURGE command clears results from a previously issued PREPARE or BIND command. It is optional.
{ENGINE|SQL} [sqlengine] PURGE statement_name [;]
where:
Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.
Is the 1-to 8-character name of an SQL variable that contains a PREPAREd (compiled) version of an SQL statement.
SQL DB2 PREPARE D FOR DELETE FROM USER1.EMPLOYEE
WHERE EMP_ID = ?
SQL DB2 PURGE D;
How to: Reference: |
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.
{ENGINE|SQL} [sqlengine] BIND statement_name USING format_list;
where:
Indicates the target RDBMS. Acceptable values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.
Is the 1-to 8-character name of an SQL variable that contains a PREPAREd (compiled) version of an SQL statement.
Is a comma-delimited list of data types used in the request. The following data types are supported by the RDBMS:
|
DB2 |
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 |
BIND is also cleared when you issue PREPARE for an already PREPAREd statement.
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 repeatSQL 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.
| Information Builders |