Chapter 6: Dynamic SQL

If everything is known about a SQL statement when the application is compiled, the statement 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.

Dynamic SQL Statement Types

There are four types of dynamic SQL statement:

Dynamic SQL Statement Type	Perform Queries?	Return Data?
Execute a statement once	No	No, can only return success or failure
Execute a statement more than once	No	No, can only return success or failure
Select a given list of data with a given set of selection criteria	Yes	Yes
Select any amount of data with any selection criteria	Yes	Yes

These types of dynamic SQL statement are described more fully below.

Execute a Statement Once

With this type of dynamic SQL statement, the statement is executed immediately. Each time the statement is executed, it is re-parsed.

Execute a Statement More Than Once

This type of dynamic SQL statement is either a statement that can 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.

Select a Given List of Data

This type of dynamic SQL statement is a SELECT statement where the number and type of host variables is known. The normal sequence of SQL statements is:

Prepare the statement
Declare a cursor to hold the results
Open the cursor
Fetch the variables
Close the cursor.

Select any Amount of Data

This type of dynamic SQL statement is the most difficult type to code. The type and/or number of variables is only resolved at run time. The normal sequence of SQL statements is:

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

Preparing Dynamic SQL Statements

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:

You can execute a prepared statement.
You can open a cursor that references a prepared statement.

COBSQL - Oracle

Place Holders

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.

Physical Location of PREPARE Statement

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.

Executing Dynamic SQL Statements

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.

EXECUTE IMMEDIATE 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

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.

FREE Statement (COBSQL Informix)

The Informix precompiler provides a FREE statement that will release resources that are allocated to a prepared statement or to a cursor.

Once you have finished with a prepared statement, you would then use the FREE statement, for example:

     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.
      ...
     EXEC SQL
         FREE stmt1
     END-EXEC

Dynamic SQL Statements and Cursors

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

For a full discussion of the FETCH statement, see the chapter Cursors.

Finally, the cursor is closed using the CLOSE statement:

EXEC SQL
   CLOSE C1
END-EXEC

For a full discussion of the CLOSE statement, see the chapter Cursors.

CALL Statements

A CALL statement can be prepared and executed as dynamic SQL. This is supported only with the Open ESQL precompiler.

You can use parameter markers (?) in dynamic SQL wherever you use host variables in static SQL
Use of the IN, INPUT, OUT, OUTPUT, INOUT and CURSOR keyword following parameter markers is the same as their use after host variable parameters in static SQL.

The whole call statement must be enclosed in braces to conform to ODBC cannonical stored procedure syntax (the Open ESQL precompiler does this for you in static SQL). For example:

     move '{call myproc(?, ? out)}' to sql-text
     exec sql 
         prepare mycall from :sql-text 
     end-exec
     exec sql 
         execute mycall using :parm1, :param2 
     end-exec

If you use parameter arrays, you can limit the number of elements used with a FOR clause on the EXECUTE, for example:

     move 5 to param-count
     exec sql 
         for :param-count 
          execute mycall using :parm1, :param2 
     end-exec

Example

The following is an example of a program that creates a stored procedure "mfexecsptest" using data source "SQLServer 2000" and then retrieves data from "publishers" table using a cursor "c1" with dynamic SQL.

$SET SQL
 WORKING-STORAGE SECTION.

 EXEC SQL INCLUDE SQLCA  END-EXEC

*> after an sql error this has the full message text
 01 MFSQLMESSAGETEXT  PIC X(250).
 01 IDX               PIC X(04)  COMP-5.

 EXEC SQL BEGIN DECLARE SECTION  END-EXEC
*> Put your host variables here if you need to port
*> to other COBOL compilers

 01  stateParam          pic xx.
 01  pubid               pic x(4).
 01  pubname             pic x(40).
 01  pubcity             pic x(20).

 01  sql-stat            pic x(256).

 EXEC SQL END DECLARE SECTION END-EXEC

 PROCEDURE DIVISION.

     EXEC SQL
         WHENEVER SQLERROR perform OpenESQL-Error
     END-EXEC

     EXEC SQL
         CONNECT TO 'SQLServer 2000' USER 'SA'
     END-EXEC

*> Put your program logic/SQL statements here

     EXEC SQL
         create procedure mfexecsptest
                 (@stateParam char(2) = 'NY' ) as

         select pub_id, pub_name, city from publishers
          where state = @stateParam
     END-EXEC

     exec sql
         declare c1 scroll cursor for dsql2 for read only
     end-exec

     move "{call mfexecsptest(?)}" to sql-stat
     exec sql prepare dsql2 from :sql-stat end-exec

     move "CA" to stateParam
     exec sql
         open c1 using :stateParam
     end-exec

     display "Testing cursor with stored procedure"
     perform until exit
         exec sql
             fetch c1 into :pubid, :pubname, :pubcity
         end-exec

         if sqlcode = 100
             exec sql close c1 end-exec
             exit perform
         else
             display pubid " " pubname " " pubcity
         end-if
     end-perform

      EXEC SQL close c1  END-EXEC

      EXEC SQL DISCONNECT CURRENT END-EXEC
      EXIT PROGRAM.
      STOP RUN.
*> Default sql error routine / modify to stop program if
*> needed
 OpenESQL-Error Section.

     display "SQL Error = " sqlstate " " sqlcode
     display MFSQLMESSAGETEXT
      *> stop run
     exit.