Chapter 3: Using Dynamic SQL in COBOL

Dynamic SQL refers to a special type of embedded SQL statement that is prepared and executed within an application program while the program is running. It differs from static embedded SQL in that part or all of the actual SQL commands may be stored in a host variable, whose value may change numerous times during execution of the program. In the extreme case, the SQL commands are generated in their entirety by the application program at run time.

While dynamic SQL is more flexible than static embedded SQL, it does require additional overhead and more programming sophistication.

Overall Considerations

The varying-list SELECT requires use of the SQLDA and address functions (COBOL pointer variables). The other types of dynamic SQL may require those features under certain conditions.

The SQL Descriptor Area (SQLDA) is a data structure that holds information about the execution of dynamic SQL statements. See the SQL Reference for more information about including an SQLDA in your program. Also see your COBOL documentation for an explanation of the use of address functions.

Some SQL statements cannot be executed dynamically, usually because they are needed to enable dynamic SQL commands themselves. The SQL commands that cannot be executed dynamically are shown below.

Non-Select SQL Statements

When non-SELECT statements are issued dynamically, the coding task is relatively easy because there is no need to dynamically allocate main storage.

To issue a non-SELECT statement dynamically:

  1. (Optional) Test each SQL statement using the SQLWizard to see that proper results are obtained.
  2. Load the SQL statement into a data area.
  3. Issue a PREPARE and then an EXECUTE (or EXECUTE IMMEDIATE) statement.

    Be sure to handle any errors that result.

The following example executes an UPDATE statement that gives every employee a 10 percent raise:

     MOVE 'update employee set payrate=payrate*1.10' 
                                                TO SQLCMD
     EXEC SQL 
         EXECUTE IMMEDIATE :sqlcmd 
     END-EXEC 
     ...

Fixed-List Select Statements

A fixed-list SELECT statement is a statement that returns a predictable number of data items of a known type from an open cursor. For each of these items, a host variable is required to accommodate incoming data.

To issue a fixed-list SELECT statement dynamically:

  1. (Optional) Test each SQL statement using the SQLWizard to see that proper results are obtained.
  2. Load the SQL statement into a data area.
  3. Use PREPARE to validate the statement and translate it into an executable form.
  4. Use DECLARE to declare a cursor for the statement.
  5. Use OPEN to open the cursor.
  6. Use FETCH to retrieve a row into a fixed list of variables.
  7. When the end of data is reached, use CLOSE to close the cursor.

    Be sure to handle any errors.

The following example shows how to execute a fixed-list SELECT statement dynamically.

* include SQLDA copybook 
     EXEC SQL 
         INCLUDE SQLDA 
     END-EXEC

* assign value to variable sqlcmd. 
     MOVE 'select e_no, ' & 
          'lname from employee where dept="1050"' TO sqlcmd

* prepare SELECT command. 
     EXEC SQL 
         PREPARE q1 FROM :sqlcmd 
     END-EXEC

* declare cursor for SELECT command. 
     EXEC SQL 
         DECLARE c1 CURSOR FOR q1 
     END-EXEC

* open cursor. 
     EXEC SQL 
         OPEN c1 
     END-EXEC

* fetch data into program variables EMPNO, LASTNME and 
* FIRSTNME. 
     EXEC SQL 
         FETCH c1 INTO :EMPNO, :LASTNME 
     END-EXEC 

* close cursor. 
     EXEC SQL 
         CLOSE c1 
     END-EXEC

Varying-List Select Statements

A varying-list SELECT statement is one that retrieves data items of varying number and format. Because the number and type of data items are not known, you cannot define host variables in advance.

However, by including the SQLDA in your program, you can use PREPARE to define a SELECT statement and follow it with a DESCRIBE statement. This process inserts the data item information into the SQLDA, which your program can use to execute a SELECT statement. Likewise, record fetches are performed using the SQLDA.

To issue a varying-list SELECT statement:

  1. Include the SQLDA copybook in your program.
  2. Use PREPARE to define a statement (either already in a statement buffer or to be read) so that it may be translated to executable form.
  3. Use DESCRIBE for the prepared statement to determine how many columns will be retrieved and their data types. You may use a PREPARE INTO to PREPARE and DESCRIBE in a single statement.
  4. If the SQLDA is not large enough to contain column descriptions for each column, your program must determine how much space is needed, obtain that storage space, build a new SQLDA, and DESCRIBE again.
  5. The program must now determine how much space is needed for the data in each column (and possibly an indicator variable). Storage must be obtained so that when a value is fetched, your program will know where to put the data. Acquiring this storage is accomplished by analyzing the SQLDA. For more information on the SQLDA, see the SQL Option SQL Reference.
  6. Execute the SELECT statement by declaring and opening a cursor for the prepared statement.
  7. Fetch a row of data by specifying USING DESCRIPTOR SQLDA on the FETCH statement.
  8. When end of data is reached, close the cursor.

The following program excerpt shows how to execute a varying-list SELECT statement dynamically.

* include SQLDA copybook. 
     EXEC SQL 
         INCLUDE SQLDA
     END-EXEC

* assign value to variable sqlcmd. 
     MOVE 'select * from employee where dept="1050"' 
                                                   TO sqlcmd

* prepare SELECT command. 
     EXEC SQL 
         PREPARE q1 FROM :sqlcmd 
     END-EXEC

* allocate space for an SQLDA ... 
     MOVE 20 TO SQLN

* describe SELECT command. 
     EXEC SQL 
         DESCRIBE q1 INTO SQLDA 
     END-EXEC

* make sure SQLDA is big enough. 
     IF (SQLN <= SQLD) 
     	   PERFORM ...
             ... 
             MOVE SQLD TO SQLN. 
             EXEC SQL 
                 DESCRIBE q1 INTO SQLDA 
             END-EXEC 
         END-PERFORM
     END-IF

* analyze results of DESCRIBE. 
...
* allocate storage to hold one row of the result. ...
* declare cursor for SELECT command. 
     EXEC SQL 
         DECLARE c1 CURSOR FOR q1 
     END-EXEC

* open cursor. 
     EXEC SQL 
         OPEN c1 
     END-EXEC

* fetch data into program variables EMPNO, LASTNME and 
* FIRSTNME. 
     EXEC SQL 
         FETCH c1 USING DESCRIPTOR SQLDA 
     END-EXEC

* display results fetching next record as long as there 
* is one. 
     PERFORM UNTIL SQLCODE <> 0 
         DISPLAY ... 
         EXEC SQL 
             FETCH c1 USING DESCRIPTOR SQLDA 
         END-EXEC 
     END-PERFORM 
     DISPLAY 'END OF LIST'.

* close cursor. 
     EXEC SQL 
         CLOSE c1 
     END-EXEC

Copyright © 2007 Micro Focus (IP) Ltd. All rights reserved.