Dynamic Programming

Dynamic programming allows your applications to specify program elements (including queries, SQL statements, and form names) at runtime. In applications where table or column names are not known until runtime, or where queries must be based on the runtime environment of the application, hard-coded SQL statements are not sufficient.

To support applications such as these, use dynamic SQL. Using dynamic SQL, you can:

• Execute a statement that is stored in a buffer (using the execute immediate statement).
• Encode a statement stored in a buffer and execute it multiple times (using the prepare and execute statements).
• Obtain information about a table at runtime (using the prepare and describe statements).

Note: Dynamic FRS allows an application to transfer data between the form and the database using information specified at runtime.

SQLDA

The descriptor area, called the SQLDA (SQL Descriptor Area), is a host language structure used by dynamic SQL. Dynamic SQL uses the SQLDA to store information about each result column of the select statement and to store descriptive information about program variables. Use the SQLDA when executing a describe statement, a prepare statement, an execute statement, or execute immediate statement.

Structure of the SQLDA

Typically, storage for the SQLDA structure is allocated at runtime. If a program allows several dynamically defined cursors to be opened at one time, the program can allocate several SQLDA structures, one for each select statement, and assign each structure a different name.

Each host language has different considerations for the SQLDA structure. Before writing a program that uses the SQLDA, see the Embedded SQL Companion Guide.

The layout of the SQLDA is shown in the following table:

Including the SQLDA in a Program

To define the SQLDA, your application must issue the following include statement:

exec sql include sqlda;

Do not place this statement in a declaration section.

In most host languages, this statement incorporates a set of type definitions that can be used to define the SQLDA structure; in some host languages it actually declares the structure. If the structure is declared directly (instead of using the include statement), any name can be specified for the structure. For information about how your language handles this statement, see the Embedded SQL Companion Guide.

More than one SQLDA-type structure is allowed in a program. A dynamic FRS describe statement and a dynamic SQL statement can use the same SQLDA structure if the described form fields or table field columns have the same names, lengths, and data types as the columns of the database table specified in the dynamic SQL statement.

Describe Statement and SQLDA

The describe statement loads descriptive information about a prepared SQL statement, a form, or the table field of a form into the SQLDA structure. There are three versions of this statement, one for each type of object (statement, form, table field) that can be described.

Dynamic SQL uses the describe statement to return information about the result columns of a select statement. Describing a select tells the program the data types, lengths, and names of the columns retrieved by the select. If statements that are not select statements are described, a 0 is returned in the sqld field, indicating that the statement was not a select statement. For a complete discussion of how to use describe in a dynamic SQL application, see Preparing and Describing Select Statement.

Data Type Codes

The describe statement returns a code indicating the data type of a field or column. This code is returned in sqltype, one of the fields in an sqlvar element.

The following table lists the data type codes. If a type code is negative, the column, variable, or field described by the sqlvar element is nullable.

Note: Logical keys are returned as char values.

For details about dynamic programming with long varchar and long byte columns, see Using Large Objects in Dynamic SQL.

Using Clause

The using clause directs the DBMS Server to use the variables pointed to by the sqlvar elements of the SQLDA (or other host language variables) when executing the statement. The syntax of the using clause follows. The keyword descriptor is optional in some statements that accept the using clause.

using descriptor descriptor_name

The statements that accept the using clause are:

Dynamic SQL Statements

Dynamic SQL has the following statements that are used exclusively in a dynamic program:

• Execute immediate
• Prepare and Execute
• Describe

In addition, all statements that support cursors (declare, open, fetch, update, delete) have dynamic versions to support dynamically executed select statements.

This section is an overview of the four statements used in dynamic programs. Detailed discussions on using these statements to execute dynamic statements can be found in Execute a Dynamic Non-select Statement and Execute a Dynamic Select Statement. Information about the dynamic versions of the cursor statements is found in Data Manipulation with Cursors. In addition, information about the dynamic version of the execute procedure statement is found in Execute Procedure of the chapter "Using SQL Statements."

Execute Immediate Statement

The execute immediate statement executes an SQL statement specified as a string literal or using a host language variable. This statement is most useful when the program intends to execute a statement only once, or when using a select loop with a dynamic select statement.

Use the execute immediate statement to execute all SQL statements except for the following statements:

• call
• close
• connect
• declare
• describe
• disconnect
• enddata
• execute procedure
• execute
• fetch
• get data
• get dbevent
• include
• inquire_sql
• open
• prepare to commit
• prepare
• put data
• set_sql
• whenever

The syntax of execute immediate is:

exec sql execute immediate statement_string

[into variable{,variable} | using [descriptor] desrciptor_name

[exec sql begin;

  program_code

exec sql end;]];

The contents of the statement_string must not include the keywords exec sql or a statement terminator. The optional into/using clause and begin/end statement block can only be used when executing a dynamic select statement.

Prepare and Execute Statements

The prepare statement tells the DBMS Server to encode the dynamically built statement and assign it the specified name. After a statement is prepared, the program can execute the statement one or more times within a transaction by issuing the execute statement and specifying the statement name. This method improves performance if your program must execute the same statement many times in a transaction. When a transaction is committed, all statements that were prepared during the transaction are discarded.

The following SQL statements cannot be prepared:

• call
• close
• connect
• declare
• disconnect
• enddata
• execute immediate
• execute fetch
• get data
• get dbevent
• include
• inquire_sql
• open
• prepare to commit
• prepare
• put data
• set
• set_sql
• whenever

The syntax of the prepare statement is:

exec sql prepare statement_name

[into descriptor_name | using descriptor descriptor_name]

from host_string_variable | string_literal;

The statement_name can be a string literal or variable, with a maximum length of 32 characters. The contents of the host string variable or the string literal cannot include exec sql or the statement terminator.

If the into clause is included in the prepare statement, the prepare statement also describes the statement string into the specified descriptor area and it is not necessary to describe the statement string separately.

The syntax of the execute statement is:

exec sql execute statement_name

[using host_variable{,host_variable}

| using descriptor descriptor_name};

A prepared statement can be fully specified, or some portions can be specified by question marks (?); these elements must be filled in (by the using clause) when the statement is executed. For more information see Prepare in the chapter "Using SQL Statements."

Describe Statement

The describe statement describes a prepared SQL statement into a program descriptor (SQLDA), which allows the program to interact with the dynamic statement as though it was hard coded in the program. This statement is used primarily with dynamic select statements.

The syntax for the describe statement is as follows:

exec sql describe statement_name into|using descriptor_name;

For more information about the describe statement, see Describe Statement and SQLDA and Preparing and Describing Select Statement.

Execute a Dynamic Non-select Statement

To execute a dynamic non-select statement, use either the execute immediate statement or the prepare and execute statements. Execute immediate is most useful if the program executes the statement only once within a transaction. If the program executes the statement many times within a transaction, for example, within a program loop, use the prepare and execute combination: prepare the statement once, execute it as many times as necessary.

If the program does not know whether the statement is a select statement, the program can prepare and describe the statement. The results returned by the describe statement indicate whether the statement was a select. For more information, see Executing a Dynamic Select Statement.

Using Execute Immediate to Execute a Non-select Statement

Execute immediate executes an SQL statement specified using a string literal or host language variable. Use this statement to execute all but a few of the SQL statements; the exceptions are listed in Execute Immediate Statement.

For non-select statements, the syntax of execute immediate is as follows:'

exec sql execute immediate statement_string;

For example, the following statement executes a drop statement specified as a string literal:

/*
** Statement specification included
** in string literal. The string literal does
** NOT include 'exec sql' or ';'
*/
exec sql execute immediate 'drop table employee';

The following example reads SQL statements from a file into a host string variable buffer and executes the contents of the variable. If the variable includes a statement that cannot be executed by execute immediate, or if another error occurs, the loop is broken.

exec sql begin declare section;
     character buffer(100);
exec sql end declare section;
open file;
loop while not end of file and not error

     read statement from file into buffer;
          exec sql execute immediate :buffer;

end loop;
close file;

If only the statement parameters (such as an employee name or number) change at runtime, execute immediate is not needed. A value can be replaced with a host language variable. For instance, the following example increases the salaries of employees whose employee numbers are read from a file:

loop while not end of file and not error

read number from file;
     exec sql update employee
          set sal = sal * 1.1
          where eno = :number;

end loop;

Preparing and Executing a Non-select Statement

The prepare and execute statements can also execute dynamic non-select statements. These statements enable your program to save a statement string and execute it as many times as necessary. A prepared statement is discarded when the transaction in which it was prepared is rolled back or committed. If a statement with the same name as an existing statement is prepared, the new statement supersedes the old statement.

The following example demonstrates how a runtime user can prepare a dynamically specified SQL statement and execute it a specific number of times:

read SQL statement from terminal into buffer;
     exec sql prepare s1 from :buffer;
read number in N
     loop N times
          exec sql execute s1;
     end loop;

The following example creates a table whose name is the same as the user name, and inserts a set of rows with fixed-typed parameters (the user's children) into the table:

get user name from terminal;
buffer = 'create table ' + user_name +
     '(child char(15), age integer)';
exec sql execute immediate :buffer;

buffer = 'insert into ' + user_name +
     '(child, age) values (?, ?)';
exec sql prepare s1 from :buffer;

read child's name and age from terminal;
loop until no more children
     exec sql execute s1 using :child, :age;
          read child's name and age from terminal;
end loop;

A list of statements that cannot be executed using prepare and execute can be found in Prepare and Execute Statements.

Execute a Dynamic Select Statement

To execute a dynamic select statement, use one of the following methods:

• If your program knows the data types of the select statement result columns, use the execute immediate statement with the into clause to execute the select. Execute immediate defines a select loop to process the retrieved rows.
• If your program does not know the data types of the select statement result columns, use the execute immediate statement with the using clause to execute the select.
• If your program does not know the data types of the select statement result columns, declare a cursor for the prepared select statement and use the cursor to retrieve the results.

The execute immediate option allows you to define a select loop to process the results of the select. Select loops do not allow the program to issue any other SQL statements while the loop is open. If the program must access the database while processing rows, use the cursor option.

Details about these options are found in When Result Column Data Types Are Known and When Result Column Data Types Are Unknown in this chapter.

To determine whether a statement is a select, use the prepare and describe statements. A repeated select statement cannot be prepared.

The following code demonstrates the use of the prepare and describe statements to execute random statements and print results. This example uses cursors to retrieve rows if the statement is a select.

statement_buffer = ' ';
loop while reading statement_buffer from terminal
     exec sql prepare s1 from :statement_buffer;
     exec sql describe s1 into :rdescriptor;

     if sqlda.sqld = 0 then

          exec sql execute s1;

else

          /* This is a SELECT */
          exec sql declare c1 cursor for s1;
          exec sql open c1;

          allocate result variables using
               result_descriptor;

          loop while there are more rows in the cursor

               exec sql fetch c1 using descriptor
                    :rdescriptor;
               if (sqlca.sqlcode not equal 100) then
                    print the row using
                    rdescriptor;
               end if;

          end loop;

          free result variables from rdescriptor;

          exec sql close c1;

     end if;

     process sqlca for status;

end loop;

Unknown Result Column Data Types

For some dynamic select statements, the program knows the data types of the resulting columns and, consequently, the data types of the result variables used to store the column values. If the program has this information, the program can use the execute immediate statement with the into clause to execute the select statement.

In the following example, a database contains several password tables, each having one column and one row and containing a password value. An application connected to this database requires a user to successfully enter two passwords before continuing. The first password entered is the name of a password table and the second is the password value in that table.

The following code uses the execute immediate statement to execute the dynamically-defined select built by the application to check these passwords:

...
prompt for table_password and value_password
select_stmt = 'select column1 from ' +
     table_password;
exec sql execute immediate :select_stmt
     into :result_password;
if (sqlstate < 0) or (value_password <>
     result_password) then
     print      'Password authorization failure'
endif
...

Because the application developer knows the data type of the column in the password table (although not which password table is selected), the developer can execute the dynamic select with the execute immediate statement and the into clause.

The syntax of execute immediate in this context is:

exec sql execute immediate select_statement

into variable{,variable};

[exec sql begin;

     host_code

exec sql end;]

This syntax retrieves the results of the select into the specified host language variables. The begin and end statements define a select loop that processes each row returned by the select statement and terminates when there are no more rows to process. If a select loop is used, your program cannot issue any other SQL statements for the duration of the loop.

If the select loop is not included in the statement, the DBMS Server assumes that the select statement is a singleton select returning only one row and, if more than one row is returned, issues an error.

How Unknown Result Column Data Types are Handled

In most instances, when a dynamically defined select statement is executed, the program does not know in advance the number or types of result columns. To provide this information to the program, first prepare and describe the select statement. The describe statement returns to the program the type description of the result columns of a prepared select statement. After the select is described, the program must allocate (or reference) dynamically the correct number of result storage areas of the correct size and type to receive the results of the select.

If the statement is not a select statement, describe returns a zero to the sqld and no sqlvar elements are used.

After the statement has been prepared and described and the result variables allocated, the program has two choices regarding the execution of the select statement:

• The program can associate the statement name with a cursor name, open the cursor, fetch the results into the allocated results storage area (one row at a time), and close the cursor.
• The program can use execute immediate. Execute immediate defines a select loop to process the returned rows. If the select returns only one row, it is not necessary to use a select loop.

Prepare and Describe Select Statements

If the program has no advance knowledge of the resulting columns, the first step in executing a dynamic select statement is to prepare and describe the statement. Preparing the statement encodes and saves the statement and assigns it a name. For information about the syntax and use of prepare, see Prepare and Execute Statements in this chapter.

The describe statement returns descriptive information about a prepared statement into a program descriptor, that is, an SQLDA structure. This statement is primarily used to return information about the result columns of a select statement to the program; however, it is also possible to describe other statements. (When a non-select statement is described, the only information returned to the program is that the statement was not a select statement.) The syntax of the describe statement is:

exec sql describe statement_name into|using descriptor_name;

When a select statement is described, information about each result column is returned to an sqlvar element. (For information about sqlvar elements, see Structure of the SQLDA.) This is a one-to-one correspondence: the information in one sqlvar element corresponds to one result column. Therefore, before issuing the describe statement, the program must allocate sufficient sqlvar elements and set the SQLDA sqln field to the number of allocated sqlvars. The program must set sqln before the describe statement is issued.

After issuing the describe statement, the program must check the value of sqld, which contains the number of sqlvar elements actually used to describe the statement. If sqld is zero, the prepared statement was not a select statement. If sqld is greater than sqln, the SQLDA does not have enough sqlvar elements: more storage must be allocated and the statement must be redescribed.

The following example shows a typical describe statement and the surrounding host program code. The program assumes that 20 sqlvar elements are sufficient:

sqlda.sqln = 20;
exec sql describe s1 into sqlda;
if (sqlda.sqld = 0) then

     statement is not a select statement;

else if (sqlda.sqld > sqlda.sqln) then

     save sqld;
     free current sqlda;
     allocate new sqlda using sqld as the size;
     sqlda.sqln = sqld;
     exec sql describe s1 into sqlda;

end if;

Sqlvar Elements

After describing a statement, the program must analyze the contents of the sqlvar array. Each element of the sqlvar array describes one result column of the select statement. Together, all the sqlvar elements describe one complete row of the result table.

The describe statement sets the data type, length, and name of the result column (sqltype, sqllen and sqlname), and the program must use that information to supply the address of the result variable and result indicator variable (sqldata and sqlind). Your program must also allocate the space for these variables.

For example, if you create the table object as follows:

exec sql create table object
     (o_id            integer not null,
      o_desc          char(100) not null,
      o_price         money not null,
      o_sold          date);

and describe the following dynamic query:

exec sql prepare s1 from 'select * from object';
exec sql describe s1 into sqlda;

The SQLDA descriptor results are as follows:

The value that the describe statement returns in sqllen depends on the data type of the column being described, as listed in the following table:

After the statement is described, your program must analyze the values of sqltype and sqllen in each sqlvar element. If sqltype and sqllen do not correspond exactly with the types of variables used by the program to process the select statement, modify sqltype and sqllen to be consistent with the program variables. After describing a select statement, there is one sqlvar element for each expression in the select target list.

After processing the values of sqltype and sqllen, allocate storage for the variables that contain the values in the result columns of the select statement by pointing sqldata at a host language variable that contain the result data. If the value of sqltype is negative, which indicates a nullable result column data type, allocate an indicator variable for the particular result column and set sqlind to point to the indicator variable. If sqltype is positive, indicating that the result column data type is not nullable, an indicator variable is not required. In this case, set sqlind to zero.

To omit the null indicator for a nullable result column (sqltype is negative if the column is nullable), set sqltype to its positive value and sqlind to zero. Conversely, if sqltype is positive and an indicator variable is allocated, set sqltype to its negative value, and set sqlind to point to the indicator variable.

In the preceding example, the program analyzes the results and modifies some of the types and lengths to correspond with the host language variables used by the program: the money data type is changed to float, and the date type to char. In addition, sqlind and sqldata are set to appropriate values. The values in the resulting sqlvar elements are:

Select Statement with Execute Immediate

A dynamic select statement can be executed if the statement has been prepared and described with an execute immediate statement that includes the using clause. The using clause tells the DBMS Server to place the values returned by the select into the variables pointed to by the elements of the SQLDA sqlvar array. If the select returns more than one row, a select loop can also be defined to process each row before another is returned.

The syntax of execute immediate in this context is:

exec sql execute immediate select_statement
               using [descriptor] descriptor_name;
[exec sql begin;
               host_code
exec sql end;]

Within a select loop, no SQL statements other than an endselect can be issued. For non-looped selects, the DBMS Server expects the select to return a single row, and issues an error if more than one row is returned.

To illustrate this option, the following program example contains a dynamic select whose results are used to generate a report:

allocate an sqlda
read the dynamic select from the terminal into
     a stmt_buffer

exec sql prepare s1 from :stmt_buffer;
exec sql describe s1 into :sqlda;
if (sqlca.sqlcode < 0) or (sqlda.sqld = 0) then
     print an error message
          ('Error or statement is not a select');
     return;
else if (sqlda.sqld > sqlda.sqln) then
     allocate a new sqlda;
     exec sql describe s1 into :sqlda;
endif;

analyze the results and allocate variables

exec sql execute immediate :stmt_buffer
     using descriptor :sqlda;
exec sql begin;
     process results, generating report
     if error occurs, then
          exec sql endselect;
     endif;
...
exec sql end;

Retrieve Results Using Cursors

To give your program the ability to access the database or issue other database statements while processing rows retrieved as the result of the select, a cursor must be used to retrieve those rows.

To use cursors, after the SQLDA has been analyzed and result variables have been allocated and pointed at, the program must declare and open a cursor to fetch the result rows.

The syntax of the cursor declaration for a dynamically defined select statement is:

exec sql declare cursor_name cursor for statement_name;

This statement associates the select statement represented by statement_name with the specified cursor. Statement_name is the name assigned to the statement when the statement was prepared. As with non-dynamic cursor declarations, the select statement is not evaluated until the cursor is actually opened. After opening the cursor, the program retrieves the result rows using the fetch statement with the SQLDA instead of the list of output variables.

The syntax for a cursor fetch statement is:

exec sql fetch cursor_name using descriptor descriptor_name;

Before the fetch statement, the program has filled the result descriptor with the addresses of the result storage areas. When executing the fetch statement, the result columns are copied into the result areas referenced by the descriptor.

The following example elaborates on an earlier example in this section. The program reads a statement from the terminal. If the statement is "quit" the program ends; otherwise, the program prepares the statement. If the statement is not a select, it is executed. If the statement is a select statement, it is described, a cursor is opened, and the result rows are fetched. Error handling is not shown.

exec sql include sqlca;
exec sql include sqlda;

allocate an sqlda with 300 sqlvar elements;
sqlda.sqln = 300;

read statement_buffer from terminal;

loop while (statement_buffer <> 'quit')

     exec sql prepare s1 from :statement_buffer;
     exec sql describe s1 into sqlda;

     if (sqlda.sqld = 0) then
                         /* This is not a select */

          exec sql execute s1;
     else                /* This is a select */

          exec sql declare c1 cursor for s1;
          exec sql open c1;

          print column headers from the sqlname
          fields; analyze the SQLDA, inspecting
          types and lengths; allocate result
          variables for a cursor result row;
          set sqlvar fields sqldata and sqlind;

          loop while (sqlca.sqlcode = 0)
               exec sql fetch c1 using descriptor sqlda;
               if (sqlca.sqlcode = 0) then
                    print the row using the results
                    (sqldata and sqlind)
                    pointed at by the sqlvar array;
               end if;

          end loop;

          free result variables from the sqlvar elements;

          exec sql close c1;

     end if;

     process the sqlca and print the status;

     read statement_buffer from the terminal;

end loop;