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