Dynamic SQL: Two-Step Dynamic Execution

The EXECUTE IMMEDIATE statement provides one-step support for dynamic statement execution. As described previously, the DBMS goes through all five steps of Figure 18-1 for the dynamically executed statement. The overhead of this process can be very significant if your program executes many dynamic statements, and it’s wasteful if the statements to be executed are identical or very similar. In practice, the EXECUTE IMMEDIATE statement should be used only for one-time statements that will be executed once by a program and then never executed again.

To deal with the large overhead of the one-step approach, dynamic SQL offers an alternative, two-step method for executing SQL statements dynamically. In practice, this two-step approach, separating statement preparation and statement execution, is used for all SQL statements in a program that is executed more than once, and especially for those that are executed repeatedly, hundreds or thousands of times, in response to user interaction. Here is an overview of the two-step technique:

  1. The program constructs a SQL statement string in a buffer, just as it does for the EXECUTE IMMEDIATE statement. A question mark (?) can be substituted for a constant anywhere in the statement text to indicate that a value for the constant will be supplied later. The question mark is called a parameter marker.
  2. The PREPARE statement asks the DBMS to parse, validate, and optimize the statement and to generate an application plan for it. This is Step 1 of the DBMS interaction. The DBMS sets the SQLCODE/SQLSTATE values to indicate any errors found in the statement and retains the application plan for later execution. Note that the DBMS does not execute the plan in response to the PREPARE statement.
  1. When the program wants to execute the previously prepared statement, it uses the EXECUTE statement and passes a value for each parameter marker to the DBMS. This is Step 2 of the DBMS interaction. The DBMS substitutes the parameter values, executes the previously generated application plan, and sets the SQLCODE/SQLSTATE values to indicate its completion status.
  2. The program can use the EXECUTE statement repeatedly, supplying different parameter values each time the dynamic statement is executed. The DBMS can simply repeat Step 2 of the interaction, since the work in Step 1 has already been done, and the results of that work (the application plan for execution) will still be valid.

Figure 18-4 shows a C program that uses these steps, which are labeled by the callouts in the figure. The program is a general-purpose table update program. It prompts the user for a table name and two column names, and constructs an UPDATE statement for the table that looks like this:

update table-name

set second-column-name = ?

where first-column-name = ?

The user’s input thus determines the table to be updated, the column to be updated, and the search condition to be used. The search comparison value and the updated data value are specified as parameters, to be supplied later when the UPDATE statement is actually executed.

After building the UPDATE statement text in its buffer, the program asks the DBMS to compile it with the PREPARE statement. The program then enters a loop, prompting the user to enter pairs of parameter values to perform a sequence of table updates. This user dialog shows how you could use the program in Figure 18-4 to update the quotas for selected salespeople:

Enter name of table to be updated:   staff

Enter name of column to be searched: empl_num

Enter name of column to be updated: quota

 

Enter search value for empl_num: 106

Enter new value for quota: 150000.00

Another (y/n)? y

 

Enter search value for empl_num: 102

Enter new value for quota: 225000.00

Another (y/n)? y

 

Enter search value for empl_num: 107

Enter new value for quota: 215000.00

Another (y/n)? n

 

Updates complete.

This program is a good example of a situation where two-step dynamic execution is appropriate. The DBMS compiles the dynamic UPDATE statement only once but executes it three times, once for each set of parameter values entered by the user. If the program had been written using EXECUTE IMMEDIATE instead, the dynamic UPDATE statement would have been compiled three times and executed three times. Thus, the two-step dynamic execution of PREPARE and EXECUTE helps to eliminate some of the performance disadvantage of dynamic SQL. As mentioned earlier, this same two-step approach is used by all of the callable SQL APIs described in Chapter 19.

1. The prepare Statement

The PREPARE statement, shown in Figure 18-5, is unique to dynamic SQL. It accepts a host variable containing a SQL statement string and passes the statement to the DBMS. The DBMS compiles the statement text and prepares it for execution by generating an application plan. The DBMS sets the SQLCODE/SQLSTATE variables to indicate any errors detected in the statement text. As described previously, the statement string can contain a parameter marker, indicated by a question mark, anywhere that a constant can appear. The parameter marker signals the DBMS that a value for the parameter will be supplied later, when the statement is actually executed.

As a result of the PREPARE statement, the DBMS assigns the specified statement name to the prepared statement. The statement name is a SQL identifier, like a cursor name. You specify the statement name in subsequent EXECUTE statements when you want to execute the statement. DBMS brands differ in how long they retain the prepared statement and the associated statement name. For some brands, the prepared statement can be reexecuted only until the end of the current transaction (that is, until the next COMMIT or ROLLBACK statement). If you want to execute the same dynamic statement later during another transaction, you must prepare it again. Other brands relax this restriction and retain the prepared statement throughout the current session with the DBMS. The ANSI/ISO sQl2 standard acknowledges these differences and explicitly says that the validity of a prepared statement outside of the current transaction is implementation dependent.

The PREPARE statement can be used to prepare almost any executable DML or DDL statement, including the SELECT statement. Embedded SQL statements that are actually precompiler directives (such as the WHENEVER or DECLARE CURSOR statements) cannot be prepared, of course, because they are not executable.

2. The execute Statement

The EXECUTE statement, shown in Figure 18-6, is unique to dynamic SQL. It asks the DBMS to execute a statement previously prepared with the PREPARE statement. You can execute any statement that can be prepared, with one exception. Like the EXECUTE IMMEDIATE statement, the EXECUTE statement cannot be used to execute a SELECT statement, because it lacks a mechanism for handling query results.

If the dynamic statement to be executed contains one or more parameter markers, the EXECUTE statement must provide a value for each of the parameters. The values can be provided in two different ways, described in the next two sections. The ANSI/ ISO SQL2 standard includes both of these methods.

3. Execute with Host Variables

The easiest way to pass parameter values to the EXECUTE statement is by specifying a list of host variables in the USING clause. The EXECUTE statement substitutes the values of the host variables, in sequence, for the parameter markers in the prepared statement text. The host variables thus serve as input host variables for the dynamically executed statement. This technique was used in the program shown in Figure 18-4. It is supported by all of the popular DBMS brands that support dynamic SQL and is included in the ANSI/ISO SQL2 standard for dynamic SQL.

The number of host variables in the USING clause must match the number of parameter markers in the dynamic statement, and the data type of each host variable must be compatible with the data type required for the corresponding parameter. Each host variable in the list may also have a companion host indicator variable. If the indicator variable contains a negative value when the EXECUTE statement is processed, the corresponding parameter marker is assigned the NULL value.

4. EXECUTE with SQLDA

The second way to pass parameters to the EXECUTE statement is with a special dynamic SQL data structure called a SQL Data Area, or SQLDA. You must use a SQLDA to pass parameters when you don’t know the number of parameters to be passed and their data types at the time that you write the program. For example, suppose you wanted to modify the general-purpose update program in Figure 18-4 so that the user could select more than one column to be updated. You could easily modify the program to generate an UPDATE statement with a variable number of assignments, but the list of host variables in the EXECUTE statement poses a problem; it must be replaced with a variable-length list. The SQLDA provides a way to specify such a variable-length parameter list.

Figure 18-7 shows the layout of the SQLDA used by the IBM databases, including DB2, which set the de facto standard for dynamic SQL. Most other DBMS products also use this IBM SQLDA format or one very similar to it. The ANSI/ISO SQL2 standard provides a similar structure, called a SQL Descriptor Area. The types of information contained in the ANSI/ISO SQL Descriptor Area and the DB2-style SQLDA are the same, and both structures play the same role in dynamic SQL processing. However, the details of use—how program locations are associated with SQL statement parameters, how information is placed into the descriptor area and retrieved from it, and so on— are quite different. In practice, the DB2-style SQLDA is the more important, because dynamic SQL support appeared in most major DBMS brands, modeled on the DB2 implementation, long before the SQL2 standard was written.

The SQLDA is a variable-size data structure with two distinct parts:

  • The fixed part is located at the beginning of the SQLDA. Its fields identify the data structure as a SQLDA and specify the size of this particular SQLDA.
  • The variable part is an array of one or more SQLVAR data structures. When you use a SQLDA to pass parameters to an EXECUTE statement, there must be one SQLVAR structure for each parameter.

The fields in the SQLVAR structure describe the data being passed to the EXECUTE statement as a parameter value:

  • The SQLTYPE field contains an integer data type code that specifies the data type of the parameter being passed. For example, the DB2 data type code is 500 for a 2-byte integer, 496 for a 4-byte integer, and 448 for a variable-length character string.
  • The SQLLEN field specifies the length of the data being passed. It will contain a two for a 2-byte integer and a four for a 4-byte integer. When you pass a character string as a parameter, SQLLEN contains the number of characters in the string.
  • The SQLDATA field is a pointer to the data area within your program that contains the parameter value. The DBMS uses this pointer to find the data value as it executes the dynamic SQL statement. The SQLTYPE and SQLLEN fields tell the DBMS which type of data is being pointed to and its length.
  • The SQLIND field is a pointer to a 2-byte integer that is used as an indicator variable for the parameter. The DBMS checks the indicator variable to determine whether you are passing a NULL value. If you are not using an indicator variable for a particular parameter, the SQLIND field must be set to zero.

The other fields in the SQLVAR and SQLDA structures are not used to pass parameter values to the EXECUTE statement. They are used when you use a SQLDA to retrieve data from the database, as described later in the “Dynamic Queries” section.

Figure 18-8 shows a dynamic SQL program that uses a SQLDA to specify input parameters. The program updates the SALESREPS table, but it allows the user to select the columns that are to be updated at the beginning of the program. Then it enters a loop, prompting the user for an employee number and then prompting for a new value for each column to be updated. If the user types an asterisk (*) in response to the new value prompt, the program assigns the corresponding column a NULL value.

Because the user can select different columns each time the program is run, this program must use a SQLDA to pass the parameter values to the EXECUTE statement. The program illustrates the general technique for using a SQLDA, indicated by callouts in Figure 18-8:

  1. The program allocates a SQLDA large enough to hold a SQLVAR structure for each parameter to be passed. It sets the SQLN field to indicate how many SQLVARs can be accommodated.
  2. For each parameter to be passed, the program fills in one of the SQLVAR structures with information describing the parameter.
  3. The program determines the data type of a parameter and places the correct data type code in the SQLTYPE field.
  4. The program determines the length of the parameter and places it in the SQLLEN field.
  5. The program allocates memory to hold the parameter value and puts the address of the allocated memory in the SQLDATA field.
  6. The program allocates memory to hold an indicator variable for the parameter and puts the address of the indicator variable in the SQLIND field.
  7. The program sets the SQLD field in the SQLDA header to indicate how many parameters are being passed. This tells the DBMS how many SQLVAR structures within the SQLDA contain valid data.
  8. The program prompts the user for data values and places them into the data areas allocated in Steps 5 and 6.
  9. The program uses an EXECUTE statement with the USING DESCRIPTOR clause to pass parameter values via the SQLDA.

Note that this particular program copies the prompt string for each parameter value into the SQLNAME structure. The program does this solely for its own convenience; the DBMS ignores the SQLNAME structure when you use the SQLDA to pass parameters. Here is a sample user dialog with the program in Figure 18-8:

*** Salesperson Update Program ***

Update Name column (y/n)? y

Update Office column (y/n)? y

Update Manager column (y/n)? n

Update Hire Date column (y/n)? n

Update Quota column (y/n)? y

Update Sales column (y/n)? n

Enter Salesperson’s Employee Number: 106
Enter new value for Name: Sue Jackson
Enter new value for Office: 22
Enter new value for Quota: 175000.00
Enter Salesperson’s Employee Number: 104
Enter new value for Name: Joe Smith
Enter new value for Office: *
Enter new value for Quota:
275000.00
Enter Salesperson’s Employee Number: 0

All updates committed.

Based on the user’s response to the initial questions, the program generates this dynamic UPDATE statement and prepares it:

update salesreps

set name = ?, office = ?, quota = ?

where empl_num = ?

The statement specifies four parameters, and the program allocates a SQLDA big enough to handle four SQLVAR structures. When the user supplies the first set of parameter values, the dynamic UPDATE statement becomes:

update salesreps

set name = ‘Sue Jackson’, office = 22, quota = 175000.00

where empl_num = 106

and with the second set of parameter values, it becomes:

update salesreps

set name = ‘Joe Smith’, office = NULL, quota = 275000.00

where empl_num = 104

This program is somewhat complex, but it’s simple compared to a real general- purpose database update utility. It also illustrates all of the dynamic SQL features required to dynamically execute statements with a variable number of parameters.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *