Dynamic SQL Techniques | Microsoft SQL Server 2008 Bible

The embedded static SQL techniques discussed in the previous section can be useful in many situations, but sometimes they are not flexible enough to satisfy all your needs. The classic example is a GUI-based application that allows users to build their own ad hoc queries by using dropdown lists to choose column (or even table) names and conditions for the WHERE clause. The application would build the appropriate query dynamically, send it to the RDBMS, receive the results, and display them in some nice, easily readable form. The static SQL would not allow you to build your queries on the fly because of the way it handles the five steps of a SQL statement execution (discussed in the beginning of this chapter). In fact, the first four steps are carried out during the compile time; that is, the statement parsing, validation, optimization, and the binary execution plan generation are all done when you compile your host-language program. That means your SQL statements are hardcoded in your program, allowing you to substitute values for the third group of tokens only (explained earlier in this chapter).

Dynamic SQL overrides these limitations by postponing all the five steps until the actual runtime. The SQL statements could be built dynamically during the program execution based on user-supplied parameters, such as table names, column names, search conditions, and so on.

Two varieties of dynamic SQL

Generally, all dynamic SQL can be divided into two categories. The first one includes any dynamically built DML and DDL statements, and the other one handles dynamic queries.

The difference between these two categories is rooted in SQL processing specifics. As we mentioned in the beginning of this chapter, there is an additional step in SELECT statement processing that involves returning query results in organized form. Because a DML (or DDL) statement can either be successful or unsuccessful only, all we usually need back from the RDBMS is the return code (SQLCODE or SQLSTATE); an SQL query returns the resulting set that consists of some columns selected from some tables. The exact number of columns may be unknown until the actual program execution, as well as the columns data types. That means the host program has to allocate an appropriate data structure to hold the anticipated resulting set just after the dynamic query has been built and before it is submitted to the RDBMS for execution.

For all these reasons, dynamic DML/DDL processing is much simpler than handling dynamic queries. Fortunately, most modern programming languages, including Visual Basic, Visual C++, PowerBuilder, Delphi, Java, and many others hide the complexity from the programmers by delegating the sophisticated part to the internal mechanisms of the programming language. All a programmer needs to do is to dynamically assemble a character string that represents the dynamic query and assign its value to the appropriate (often predefined within a class) variable. Building and handling dynamic queries manually is generally obsolete, so we are not going to go into great details when discussing dynamic query techniques. You should refer to vendor-specific documentation in case you want to know more.

Dynamic SQL and SQL99 standards

By now, you probably are already used to the fact that ANSI/ISO standards are often quite different from what each vendor has actually implemented. This is also the case for the dynamic SQL that existed long before any ANSI/ISO standards were accepted. IBM implemented its own version of dynamic SQL in the early 1980s; all major RDBMS vendors followed these standards to a certain extent, but with their own twist. This is especially true about dynamic query processing, where each RDBMS has its own mechanism for handling the resulting set returned by a query. For example, all three major databases covered in this book use special dynamic SQL data structure, known as the SQL Data Area (SQLDA), to handle the dynamic query output, but all three implemented their own version of this structure incompatible with the others. SQL99 standards replace SQLDA with a similar construct called Dynamic SQL Descriptor that plays exactly the same role but is structured quite differently.

Dynamic SQL basic elements

Dynamic SQL shares most elements with static embedded SQL. Including SQLCA, connecting to and disconnecting from database, declaring host variables, handling errors, and performing COMMIT and ROLLBACK are exactly the same for both. The embedded statements start with EXEC SQL keywords and end with a language-specific delimiter. However, since the SQL statement processing has to be done in runtime rather than in compile-time, the dynamic SQL introduces some additional elements to enable users to build their statements on the fly. As we mentioned before, DML/DDL statements are easier to handle than dynamic queries, which require additional preparation. In the next sections of this chapter, we introduce the techniques of working with both.

Dynamic DML and DDL

Dynamic SQL provides two methods of executing almost any DML or DDL statement dynamically in your host program. The first method is called EXECUTE IMMEDIATE and allows you to submit a programmatically assembled string that represents a DML/DDL statement to the RDBMS in one step. The alternative, also known as two-step dynamic execution, consists of two statements, PREPARE and EXECUTE.

One-step execution

One-step execution is the simplest way of executing dynamic SQL within your host program. First, the program builds the SQL statement based on user input, command-line arguments, and so on, and stores it in a previously declared character string variable. The variable is then passed to the RDBMS using EXECUTE IMMEDIATE; the statement is executed by the database engine. (All five processing steps are performed at this time.) The RDBMS returns the completion status back to the host program using SCLCA, SQLCODE, SQLSTATE, etc. The generalized syntax is

EXEC SQL EXECUTE IMMEDIATE <language-specific delimiter>

The following examples illustrate how to build and dynamically execute an SQL statement that updates the PROD_PRICE_N column of the PRODUCT table with a value of 25.50 for row(s) matching user-supplied criteria using C and COBOL syntax, respectively:

... EXEC SQL BEGIN   DECLARE SECTION; ... char buffer[101]; ... EXEC SQL END DECLARE SECTION; ...   char searchcond[51]; ... strcpy(buffer, "UPDATE PRODUCT SET PROD_PRICE_N =   25.50 WHERE "); printf("ENTER SEARCH CONDITION:"); gets(searchcond);   strcat(buffer, searchcond); EXEC SQL EXECUTE IMMEDIATE :buffer; ...

COBOL

... EXEC SQL BEGIN   DECLARE SECTION END-EXEC. ... 01 BUFFER PIC X(100). ... EXEC SQL END DECLARE   SECTION END-EXEC. ... 01 UPDCLAUSE PIC X(50). 01 SEARCHCOND PIC X(50). ...   DISPLAY "ENTER SEARCH CONDITION:". MOVE "UPDATE PRODUCT SET PROD_PRICE_N =   25.50 WHERE " TO UPDCLAUSE. DISPLAY UPDCLAUSE. ACCEPT SEARCHCOND. STRING   UPDCLAUSE DELIMITED BY SIZE SEARCHCOND DELIMITED BY SIZE INTO BUFFER. EXEC SQL   EXECUTE IMMEDIATE :BUFFER END-EXEC. ...

The query billet is in this form:

UPDATE PRODUCT SET PROD_PRICE_N = 25.50 WHERE

So, the user can submit any valid search condition to form a legitimate query; for example, PROD_ID_N = 990, PROD_BRAND_S = 'STEEL NAILS', PROD_PRICE_N = 33.28, etc.

Note

The code in this example is just to illustrate the concept of the EXECUTE IMMEDIATE statement; the actual program would probably at least use a host variable for the PROD_PRICE_N rather than a hardcoded constant.

Two-step execution

Two-step execution is more complicated. You build your SQL statement in exactly the same way you would do it for EXECUTE IMMEDIATE; the only difference is, you can use a question mark (?), called the parameter marker or placeholder, instead of any token from group three (discussed earlier in this chapter) to be later substituted with the actual value. The statement is then submitted as an argument for the PREPARE statement that performs the first four SQL statement processing steps (parse, validate, optimize, and generate execution plan). The last step is to use EXECUTE to replace the parameter markers with the actual values and execute the SQL statement.

Note

Oracle uses the host variable notation (not allowed in DB2 or MS SQL Server) for the parameter markers instead of the question marks.

The generalized syntax for the PREPARE and EXECUTE commands is

PREPARE

EXEC SQL PREPARE   <statement_name> FROM {:<host_string > | <string_literal>}   <language-specific delimiter>

EXECUTE

EXEC SQL EXECUTE   <statement_name> [USING <host_variable_list>] <language-specific   delimiter>

Every parameter marker in the prepared dynamic SQL statement (if any) must correspond to a different host variable in the USING clause. When using Oracle notation, the names of the placeholders need not match the names of the host variables; however, the order of the placeholders in the prepared dynamic SQL statement must match the order of corresponding host variables in the USING clause.

Tip

The indicator variables could be used with host variables in the USING clause.

The following examples illustrate how to build and dynamically execute an SQL statement that updates the PROD_DESCRIPTION_S column of the PRODUCT table with a user-supplied value for row(s) matching user-supplied search criteria using C and COBOL syntax, respectively:

... EXEC SQL BEGIN   DECLARE SECTION; ... char buffer[150]; char proddesc[45]; ... EXEC SQL END   DECLARE SECTION; ... char searchcond[51]; ... strcpy(buffer, "UPDATE PRODUCT   SET PROD_NAME_S = ? WHERE "); printf("ENTER PRODUCT DESCRIPTION:");   gets(proddesc); printf("ENTER SEARCH CONDITION:"); gets(searchcond);   strcat(buffer, searchcond); ... EXEC SQL PREPARE S FROM :buffer; ... EXEC SQL   EXECUTE S USING :proddesc; ...

COBOL

... EXEC SQL BEGIN   DECLARE SECTION END-EXEC. ... 01 BUFFER PIC X(100). 01 PRODDESC PIC X(44). ...   EXEC SQL END DECLARE SECTION END-EXEC. ... 01 UPDCLAUSE PIC X(50). 01   SEARCHCOND PIC X(50). ... DISPLAY "ENTER SEARCH CONDITION:". MOVE "UPDATE   PRODUCT SET PROD_PRICE_N = ? WHERE " TO UPDCLAUSE. DISPLAY UPDCLAUSE. ACCEPT   SEARCHCOND. DISPLAY "ENTER PRODUCT DESCRIPTION:". ACCEPT PRODDESC. STRING   UPDCLAUSE DELIMITED BY SIZE SEARCHCOND DELIMITED BY SIZE INTO BUFFER. EXEC SQL   PREPARE S FROM :BUFFER END-EXEC. ... EXEC SQL EXECUTE S USING :PRODDESC.   ...

Note

The S in the PREPARE statement is not a host variable, but rather, an SQL identifier, so it does not appear in the declaration section.

The syntax in these examples works with DB2 and MS SQL Server; you could modify it into Oracle-compliant code by replacing the parameter marker question marks with host variables. Thus, instead of

strcpy(buffer, "UPDATE PRODUCT SET PROD_NAME_S = ? WHERE ");

and

MOVE "UPDATE PRODUCT SET PROD_PRICE_N = ? WHERE " TO UPDCLAUSE.

you use

strcpy(buffer, "UPDATE PRODUCT SET PROD_NAME_S = :n WHERE ");

and

MOVE "UPDATE PRODUCT SET PROD_PRICE_N = :N WHERE " TO UPDCLAUSE.

Two-step execution benefits

The two-step execution yields better performance than EXECUTE IMMEDIATELY, especially when the prepared statement is executed multiple times with different parameter markers. This is so because the PREPARE statement could be executed only once for numerous EXECUTE statements with different values substituted for the placeholders, so RDBMS does not have to execute SQL statement processing Steps 1 through 4 over and over again. A simple loop in the host program will do. However, if the SQL statement is used only once during the program execution, EXECUTE IMMEDIATE is the appropriate choice.

Dynamic queries

As we mentioned earlier in this chapter, dynamic queries are more complicated than dynamic DML and DDL and are characterized by serious discrepancies between vendor implementations. In this section, we introduce dynamic query using ANSI/ISO syntax.

Note

To make the examples more usable, we created them using Oracle's version of SQL99 compliant syntax. In fact, this is just one of many possible ways to work with dynamic queries in Oracle; for example, you could use SQLDA as an alternative to the dynamic SQL descriptor.

Dynamic query syntax

Before you can start working with dynamic queries, you have to learn some more dynamic SQL statements.

The ALLOCATE DESCRIPTOR command allocates descriptor areas for "in" and "out" parameters:

EXEC SQL ALLOCATE DESCRIPTOR <'descriptor_name'> <language-specific delimiter>

The DESCRIBE statement obtains information on a prepared SQL statement. DESCRIBE INPUT describes input host variables for the dynamic statement that has been prepared. DESCRIBE OUTPUT gives the number, type, and length of the output columns:

EXEC SQL DESCRIBE [INPUT | OUTPUT] <sql_statement> USING [SQL] DESCRIPTOR <'descriptor_name'> <language-specific delimiter>

The SET DESCRIPTOR statement lets you specify input values for the WHERE clause of your SELECT statement. A separate SET DESCRIPTOR statement must be used for each host variable. You can specify type, length, and data value; also, you have to specify VALUE, which is the host variable relative position in the dynamic SQL statement:

EXEC SQL SET DESCRIPTOR <'descriptor_name'> [VALUE <item_sequence_number>,] [TYPE = <:host_variable1>,] [LENGTH = <:host_variable2>,] DATA = <:host_variable3> <language-specific delimiter>

For example, if your statement is: SELECT :v1, :v2, :v3, ... then the VALUE for :v1 is 1; the value for :v2 is 2, and so on.

TYPE is the ANSI Type Code selected from the values in Table 15-6.

Table 15-6: ANSI/ISO SQL Data type Codes
Data type	Type Code
CHARACTER	1
CHARACTER VARYING	12
DATE	9
DECIMAL	3
DOUBLE PRECISION	8
FLOAT	6
INTEGER	4
NUMERIC	2
REAL	7
SMALLINT	5

DEALLOCATE DESCRIPTOR spares memory allocated for the descriptor when it is no longer needed:

EXEC SQL DEALLOCATE DESCRIPTOR <'descriptor_name'> <language-specific delimiter>

In addition to these statements, you need to know how to use dynamic cursors. A dynamic cursor is not much different from a static cursor; it allows you to perform the same four basic operations: DECLARE, OPEN, FETCH, and CLOSE. The main difference is, when you declare a dynamic cursor, the query is not specified using a hardcoded SELECT statement but rather referred indirectly using the statement name prepared by the PREPARE statement. The syntax for dynamic cursor statements is

EXEC SQL DECLARE <cursor_name> CURSOR FOR <statement_id> <language-specific delimiter>

EXEC SQL OPEN <cursor_name> CURSOR USING DESCRIPTOR <'descriptor_name'> <language-specific delimiter>

EXEC SQL FETCH <cursor_name> INTO DESCRIPTOR <'descriptor_name'> <language-specific delimiter>

EXEC SQL CLOSE <cursor_name> <language-specific delimiter>

Steps to execute a dynamic query

Perform the following steps to execute a dynamic query:

Declare variables, including a string buffer to hold the statement to be executed.
Allocate descriptors for input and output variables.
Prepare the statement with a PREPARE ... USING DESCRIPTOR statement.
Describe input for the input descriptor.
Set the input descriptor.
Declare and open a dynamic cursor.
Set the output descriptors for each output host variable.
Fetch the cursor in a loop; use GET DESCRIPTOR to retrieve the data for each row.
Use the retrieved data in your program.
Close the dynamic cursor.
Deallocate the input and output descriptors.

Dynamic query example

The following examples (in C and COBOL, respectively) show how to use a dynamic query in your host program using the steps described in the previous section:

... EXEC SQL BEGIN   DECLARE SECTION; ... char* buffer= "SELECT CUST_ID_N, CUST_NAME_S FROM CUSTOMER   WHERE CUST_PAYTERMSID_N = :payterm_data"; int payterm_type = 4, payterm_len =   2, payterm_data = 28; int custid_type = 4, custid_len = 4; int custid_data; int   name_type = 12, name_len = 50; char name_data[51] ; ... EXEC SQL END DECLARE   SECTION; ... long SQLCODE = 0 ; ... main () { ... EXEC SQL ALLOCATE DESCRIPTOR   'in' ; EXEC SQL ALLOCATE DESCRIPTOR 'out' ; EXEC SQL PREPARE S FROM :buffer;   EXEC SQL DESCRIBE INPUT S USING DESCRIPTOR 'in' ; EXEC SQL SET DESCRIPTOR 'in'   VALUE 1 TYPE = :payterm_type, LENGTH = :payterm_len, DATA = :payterm_data ;   EXEC SQL DECLARE cur CURSOR FOR S; EXEC SQL   OPEN cur USING DESCRIPTOR 'in' ; EXEC SQL DESCRIBE OUTPUT S USING DESCRIPTOR   'out' ; EXEC SQL SET DESCRIPTOR 'out' VALUE 1 TYPE = :custid_type, LENGTH =   :custid_len, DATA = :custid_data ; EXEC SQL SET DESCRIPTOR 'out' VALUE 2 TYPE =   :name_type, LENGTH = :name_len, DATA = :name_data ; EXEC SQL WHENEVER NOT FOUND   DO BREAK ; while (SQLCODE == 0) { EXEC SQL FETCH cur INTO DESCRIPTOR 'out' ;   EXEC SQL GET DESCRIPTOR 'out' VALUE 1 :custid_data = DATA; EXEC SQL GET   DESCRIPTOR 'out' VALUE 2 :name_data = DATA ; printf("\nCustomer ID = %s   Customer Name = %s", custid_data, name_data) ; } EXEC SQL CLOSE cur; EXEC SQL   DEALLOCATE DESCRIPTOR 'in'; EXEC SQL DEALLOCATE DESCRIPTOR 'out' ; ... }

COBOL

EXEC SQL BEGIN DECLARE   SECTION END-EXEC. ... 01 BUFFER PIC X(100) VALUE "SELECT CUST_ID_N, CUST_NAME_S   FROM CUSTOMER WHERE CUST_PAYTERMSID_N = :payterm_data". 01 PAYTERM-DAT PIC   S9(9) COMP VALUE 28. 01 PAYTERM-TYP PIC S9(9) COMP VALUE 4. 01 PAYTERM-LEN PIC   S9(9) COMP VALUE 2. 01 CUSTID-TYP PIC S9(9) COMP VALUE 4. 01 CUSTID-LEN PIC   S9(9) COMP VALUE 4. 01 CUSTID-DAT PIC S9(9) COMP. 01 NAME-TYP PIC S9(9) COMP   VALUE 12. 01 NAME-LEN PIC S9(9) COMP VALUE 50. 01 NAME-DAT PIC X(50). EXEC SQL   END DECLARE SECTION END-EXEC. ... 01 SQLCODE PIC S9(9) COMP VALUE 0. ... EXEC   SQL ALLOCATE DESCRIPTOR 'in' END-EXEC. EXEC SQL ALLOCATE DESCRIPTOR 'out'   END-EXEC. EXEC SQL   PREPARE S FROM :BUFFER END-EXEC. EXEC SQL DESCRIBE INPUT S USING DESCRIPTOR   'in' END-EXEC. EXEC SQL SET DESCRIPTOR 'in' VALUE 1 TYPE=:PAYTERM-TYP,   LENGTH=:PAYTERM-LEN, DATA=:PAYTERM-DAT END-EXEC. EXEC SQL DECLARE cur CURSOR   FOR S END-EXEC. EXEC SQL OPEN cur USING DESCRIPTOR 'in' END-EXEC. EXEC SQL   DESCRIBE OUTPUT S USING DESCRIPTOR 'out' END-EXEC. EXEC SQL SET DESCRIPTOR   'out' VALUE 1 TYPE=:CUSTID-TYP, LENGTH=:CUSTID-LEN, DATA=:CUSTID-DAT END-EXEC.   EXEC SQL SET DESCRIPTOR 'out' VALUE 2 TYPE=:NAME-TYP, LENGTH=:NAME-LEN,   DATA=:NAME-DAT END-EXEC. LOOP. IF SQLCODE NOT = 0 GOTO BREAK. EXEC SQL FETCH   cur INTO DESCRIPTOR 'out' END-EXEC. EXEC SQL GET DESCRIPTOR 'OUT' VALUE 1   :CUSTID-DAT = DATA END-EXEC. EXEC SQL GET DESCRIPTOR 'OUT' VALUE 2 :NAME-DAT =   DATA END-EXEC. DISPLAY "CUSTOMER ID = " WITH NO ADVANCING DISPLAY CUSTID-DAT   WITH NO ADVANCING DISPLAY "CUSTOMER NAME = " WITH NO ADVANCING DISPLAY   NAME-DAT. GOTO LOOP. BREAK: EXEC SQL CLOSE cur END-EXEC. EXEC SQL DEALLOCATE   DESCRIPTOR 'in' END-EXEC. EXEC SQL DEALLOCATE DESCRIPTOR 'out' END-EXEC.   ...