Skill in executing SQL statements

[Note]

Embedded SQL dynamic statements

Dynamic SQL statements accept a character-string host variable and a statement name as arguments. The host variable contains the SQL statement to be processed dynamically in text form. The statement text is not processed when an application is precompiled. In fact, the statement text does not have to exist at the time the application is precompiled. Instead, the SQL statement is treated as a host variable for precompilation purposes and the variable is referenced during application execution.

Dynamic SQL support statements are required to transform the host variable containing SQL text into an executable form. Also, dynamic SQL support statements operate on the host variable by referencing the statement name. These support statements are:

Note: The content of dynamic SQL statements follows the same syntax as static SQL statements, with the following exceptions:

Declaring XML host variables in embedded SQL applications

To exchange XML data between the database server and an embedded SQL application, you need to declare host variables in your application source code.

DB2 V9.1 introduces an XML data type that stores XML data in a structured set of nodes in a tree format. Columns with this XML data type are described as an SQL_TYP_XML column SQLTYPE, and applications can bind various language-specific data types for input to and output from these columns or parameters. XML columns can be accessed directly using SQL, the SQL/XML extensions, or XQuery. The XML data type applies to more than just columns. Functions can have XML value arguments and produce XML values as well. Similarly, stored procedures can take XML values as both input and output parameters. Finally, XQuery expressions produce XML values regardless of whether or not they access XML columns.

XML data is character in nature and has an encoding that specifies the character set used. The encoding of XML data can be determined externally, derived from the base application type containing the serialized string representation of the XML document. It can also be determined internally, which requires interpretation of the data. For Unicode encoded documents, a byte order mark (BOM), consisting of a Unicode character code at the beginning of a data stream is recommended. The BOM is used as a signature that defines the byte order and Unicode encoding form.

Existing character and binary types, which include CHAR, VARCHAR, CLOB, and BLOB may be used in addition to XML host variables for fetching and inserting data. However, they will not be subject to implicit XML parsing, as XML host variables would. Instead, an explicit XMLPARSE function with default whitespace stripping is injected and applied.

To declare XML host variables in embedded SQL applications: In the declaration section of the application, declare the XML host variables as LOB data types:

SELECT INTO statement

The SELECT INTO statement produces a result table consisting of at most one row, and assigns the values in that row to host variables. If the table is empty, the statement assigns +100 to SQLCODE and '02000' to SQLSTATE and does not assign values to the host variables. If more than one row satisfies the search condition, statement processing is terminated, and an error occurs (SQLSTATE 21000).

Syntax:


                        .-,-------------.
                        V               |
>>-select-clause--INTO----host-variable-+--from-clause---------->
 
>--+--------------+--+-----------------+--+---------------+----->
   '-where-clause-'  '-group-by-clause-'  '-having-clause-'
 
>--+-----------------+--+--------------------+------------------>
   '-order-by-clause-'  '-fetch-first-clause-'
 
>--+------------------+----------------------------------------><
   '-isolation-clause-'
					
					

Example 1: This C example puts the maximum salary in the EMP table into the host variable MAXSALARY:

EXEC SQL SELECT MAX(SALARY)
    INTO :MAXSALARY
    FROM EMP;
					

Example 2: This C example puts the row for employee 528671 (from the EMP table) into host variables:

EXEC SQL SELECT * INTO :h1, :h2, :h3, :h4
    FROM EMP
    WHERE EMPNO = '528671';
					

Static and dynamic SQL statement execution in embedded SQL applications

Both static and dynamic SQL statement execution is supported in embedded SQL applications. The decision to execute SQL statements statically or dynamically requires an understanding of packages, how SQL statements are executed at run-time, host variables, parameter markers, and how these things are related to application performance.

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     SCDJWS 5.0 Quiz