SQL Bible [Electronic resources] نسخه متنی

Dynamic SQL Techniques

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:

C

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

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.

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.

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:

C

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

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.

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.

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:

C

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