Dynamic Operations

Dynamic operations offer a way to execute SQL commands that cannot be completely defined until run time. You accept part or all of an SQL command that can be dynamically preprocessed from the user, then use one of the following techniques to preprocess and execute the command:

If the dynamic command is not a query, you can use the PREPARE command to preprocess it, then execute it later during the same transaction using the EXECUTE command. Alternatively, you can use the EXECUTE IMMEDIATE command to preprocess and execute the dynamic command in one step.
If the dynamic command is a query, you use special data structures plus the following SQL commands to handle the dynamic command: PREPARE, DESCRIBE, DECLARE CURSOR, OPEN, FETCH, and CLOSE.

To determine whether a dynamic command is a query, you use the DESCRIBE command after preparing the dynamic command:



        The program accepts an SQL command from the user and stores

        it in a host variable named DynamicCommand.



   EXEC SQL {{PREPARE}} DynamCommand FROM :DynamicCommand;



        After the command is prepared, the DESCRIBE command is used

        to determine whether the prepared command is a SELECT command.



   EXEC SQL {{DESCRIBE}} DynamCommand INTO SQLDA;



        The SQLDA.SQLD field of the SQLDA is examined to determine

        whether the dynamic command is a query.



   if SQLDA.SQLD = 0 then       (*the command is not a query*)

     begin

     EXEC SQL {{EXECUTE}} DynamCommand;

     .

     .

     .

   else if SQLDA.SQLD > 0 then  (*the command is a query*)

     .

     .

     .

         A non-query can be executed with the EXECUTE

         command, but a query requires special handling.

Dynamic queries require special handling because you may not know in advance what a query result will look like. The number and type of columns, the existence of null values, the column names...this and other information may need to be obtained by the program at run time because it is not known at programming time.

To obtain the information needed to parse any query result at run time, you use three special data structures in your program: the SQLDA, a format array, and a data buffer:

The SQLDA (SQL Description Area) and the format array contain information about the query result. These data structures have the format described in the chapter, "Host Variables."
The data buffer holds one or more rows fetched from the query result. Its format is also described in the chapter, "Host Variables."

The following example summarizes how you declare and use these data structures:



   const

        OK            =    0;

        Notfound      =  100;

        NbrFmtRecords =  255; (*maximum number of columns*)

        MaxDataBuff   = 1600; (*maximum number of bytes the program*)

                              (*allows in each fetch; 1600 bytes *)

                              (*accommodates the display of 20*)

                              (*80-character lines*)



   var

        EXEC SQL BEGIN DECLARE SECTION;

        DynamicCommand        : String[1024] (*maximum length of*)

                                             (*a dynamic command*)

        EXEC SQL END DECLARE SECTION;



        EXEC SQL INCLUDE SQLDA;

                     (*declaration of SQLDA*)



        SQLFmts    : array[1..NbrFmtRecords] of SqlFormat_Type;

                     (*declaration of format array*)



        DataBuffer : packed array[1..MaxDataBuff] of char;

                     (*declaration of data buffer*)

   .

   .

   .

   procedure ExecuteDynamCmd;

   begin

     with SQLDA do  (*You must set two SQLDA fields before DESCRIBE*)

       begin

       Sqln      := NbrFmtRecords; (*number of records in format array*)

       SqlFmtArr := waddress(SQLFmts); (*address of format array*)

       end;

     .

     .        The program accepts a dynamic command.

     EXEC SQL PREPARE DYNAMCOMMAND FROM :DYNAMICCOMMAND;

     .

     .        Status checking is done.

     EXEC SQL DESCRIBE DYNAMCOMMAND INTO SQLDA;






     if SQLDA.SQLD = 0             (*dynamic command is not a query*)

     .

     .     The program executes the dynamic command.

     .

     else if SQLDA.SQLD > 0 then   (*dynamic command is a query*)

     begin

       EXEC SQL {{DECLARE}} DYNAMCURSOR {{CURSOR FOR}} DYNAMCOMMAND;

       EXEC SQL OPEN DYNAMCURSOR;

       .

       .   If SQLCODE is 0, rows in the query result can be

           fetched.  First, however, you must set three

           SQLDA fields.

       with SQLDA do

       begin

         SqlBufLen := sizeof(DataBuffer); (*bytes in data buffer*)

         SqlNRow   := SqlBufLen DIV SqlRowLen;

                      (*number of rows to fetch into data buffer*)

         SqlRowBuf := waddress(DataBuffer); (*address of data buffer*)

       end;



       while SQLCA.SQLCODE = OK do

       begin

         EXEC SQL FETCH DYNAMCURSOR USING DESCRIPTOR SQLDA;

   

         if SQLCA.SQLCODE <> OK then

         begin

           if SQLCA.SQLCODE = NotFound then

              writeln('No more rows qualify.')



           else

           SQLStatusCheck;

         end;

         else

         DisplaySelect;

       end;

   .

   .

   .

   procedure DisplaySelect;



           This procedure parses the data buffer and displays

           rows fetched.  For each column in each row of the

           query result, various fields in the format array

           are used to identify where data values are located

           in the data buffer as well as the data type of

           these values and null value information.

Techniques for parsing the data buffer and more examples of dynamic preprocessing are in the chapter, "Using Dynamic Operations."

Dynamic Operations

Technical documentation

» Table of Contents

» Index