| Data Structures | OpenESQL | |
If everything is known about a SQL statement when the application is compiled, it is known as a static SQL statement. In some cases, however, the full text of a SQL statement may not be known when an application is written. For example, you may need to allow the end-user of the application to enter a SQL statement. In this case, the statement needs to be constructed at run-time. This is called a dynamic SQL statement.
There are four types of dynamic SQL statement:
Queries can only be performed using types 3 and 4; data cannot be returned with types 1 and 2, only success or failure.
With type 1, the SQL statement is executed immediately. Each time the statement is executed, it is re-parsed.
The second type of dynamic SQL is a statement that might be executed more than once, or a statement that requires host variables. For the second type, the statement has to be prepared before it can be executed.
The third type of dynamic SQL is a SELECT statement where the number of, and type of host variables is known. The statement is first prepared, a cursor is then declared to hold the results, the cursor is opened, the data fetched from the cursor and finally the cursor is closed.
The fourth type of dynamic SQL, and hardest to code, is where the type of the variables is only resolved at run-time, the number of variables is only resolved at run-time, or a mixture of both. The normal sequence of SQL statements is to prepare the statement, declare a cursor for the statement, describe the variables to be used, open the cursor using the variables just described, describe the variables to be fetched, fetch the variables using their descriptions and finally close the cursor.
If either the input host variables, or the output host variables are known (at compile time), then the OPEN or FETCH can name the host variables and they do not need to be described.
The PREPARE statement takes a character string containing a dynamic SQL statement and associates a name with the statement, for example:
move "INSERT INTO publishers VALUES (?,?,?,?)" to stmtbuf EXEC SQL PREPARE stmt1 FROM :stmtbuf END-EXEC
Dynamic SQL statements can contain parameter markers - question marks (?) that act as a place holder for a value. In the example above, the values to be substituted for the question marks must be supplied when the statement is executed.
Once you have prepared a statement, you can use it in one of two ways:
COBSQL
Oracle does not use question marks as place holders. It uses the host
variable notation. By convention the place holders are named Vn,
where n is a number to make the place holder unique within a
statement. For readability the same place holder can be used more than once,
but when the statement is executed (or opened if you are using a cursor),
there must still be one host variable for each place holder, for example:
string "update ordtab " delimited by size
"set order_no = :v1, "
"line_no = :v2, "
"cust_code = :v3, "
"part_no = :v4, "
"part_name = :v5, "
"order_val = :v6, "
"pay_value = :v7 "
"where order_no = :v1 and "
"line_no = :v2 and "
"cust_code = :v3 " delimited by size
into Updt-Ord-Stmt-Arr
end-string
move 190 to Updt-Ord-Stmt-Len
EXEC SQL PREPARE updt_ord FROM :Updt-Ord-Stmt END-EXEC
EXEC SQL EXECUTE updt_ord USING
:dcl-order-no, :dcl-line-no, :dcl-cust-code,
:dcl-part-no, :dcl-part-name:ind-part-name,
:dcl-order-val,:dcl-pay-value,
:dcl-order-no, :dcl-line-no, :dcl-cust-code
END-EXEC
where Updt-Ord-Stmt has been defined as a host variable type
of VARYING.
COBSQL
When using the Oracle precompiler, the physical location of a PREPARE
statement is important. A PREPARE statement must appear before an EXECUTE or
a DECLARE statement.
The EXECUTE statement runs a specified prepared SQL statement.
Note: Only statements that do not return results can be executed in this way.
If the prepared statement contains parameter markers, the EXECUTE
statement must include either the using :hvar option to supply
parameter values using host variables or the using descriptor
:sqlda_struct option identifying an SQLDA data structure already
populated by the application. The number of parameter markers in the prepared
statement must match the number of SQLDATA entries (using descriptor
:sqlda) or host variables (using :hvar).
move "INSERT INTO publishers VALUES (?,?,?,?)" to stmtbuf EXEC SQL PREPARE stmt1 FROM :stmtbuf END-EXEC ... EXEC SQL EXECUTE stmt1 USING :pubid,:pubname,:city,:state END-EXEC.
In this example, the four parameter markers are replaced by the contents of the host variables supplied via the USING clause in the EXECUTE statement.
If the dynamic SQL statement does not contain any parameter markers, you can use EXECUTE IMMEDIATE instead of PREPARE followed by EXECUTE, for example:
move "DELETE FROM emp WHERE last_name = 'Smith'" to stmtbuf EXEC SQL EXECUTE IMMEDIATE :stmtbuf END-EXEC
COBSQL
When using EXECUTE IMMEDIATE, the statement is re-parsed each time it is
executed. If a statement is likely to be used many times it is better to
PREPARE the statement and then EXECUTE it when required.
If a dynamic SQL statement returns a result, you cannot use the EXECUTE statement. Instead, you must declare and use a cursor.
First, declare the cursor using the DECLARE CURSOR statement:
EXEC SQL DECLARE C1 CURSOR FOR dynamic_sql END-EXEC
In the example above, dynamic_sql is the name of a dynamic
SQL statement. You must use the PREPARE statement to prepare the dynamic SQL
statement before the cursor can be opened, for example:
move "SELECT char_col FROM mfesqltest WHERE int_col = ?" to sql-text EXEC SQL PREPARE dynamic_sql FROM :sql-text END-EXEC
Now, when the OPEN statement is used to open the cursor, the prepared statement is executed:
EXEC SQL OPEN C1 USING :int-col END-EXEC
If the prepared statement uses parameter markers, then the OPEN statement must supply values for those parameters by specifying either host variables or an SQLDA structure.
Once the cursor has been opened, the FETCH statement can be used to retrieve data, for example:
EXEC SQL FETCH C1 INTO :char-col END-EXEC
See the chapter Cursors for a full discussion of the FETCH statement.
Finally, the cursor is closed using the CLOSE statement:
EXEC SQL CLOSE C1 END-EXEC
See the chapter Cursors for a full discussion of the CLOSE statement.
Copyright © 1998 Micro Focus Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| Data Structures | OpenESQL | |