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:
Prepares and executes a statement that DOES NOT use any host variables. Use this statement as an alternative to the PREPARE and EXECUTE statements.
For example consider the following statement in C:
strcpy (qstring,"INSERT INTO WORK_TABLE SELECT * FROM EMP_ACT WHERE ACTNO >= 100"); EXEC SQL EXECUTE IMMEDIATE :qstring;
Turns the character string form of the SQL statement into an executable form of the statement, assigns a statement name, and optionally places information about the statement in an SQLDA structure.
Executes a previously prepared SQL statement. The statement can be executed repeatedly within a connection.
Places information about a prepared statement into an SQLDA.
For example consider the following statement in C:
strcpy(hostVarStmt, "DELETE FROM org WHERE deptnumb = 15"); EXEC SQL PREPARE Stmt FROM :hostVarStmt; EXEC SQL DESCRIBE Stmt INTO :sqlda; EXEC SQL EXECUTE Stmt;
Note: The content of dynamic SQL statements follows the same syntax as static SQL statements, with the following exceptions:
The statement cannot begin with EXEC SQL.
The statement cannot end with the statement terminator. An exception to this is the CREATE TRIGGER statement which can contain a semicolon (;).
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:
SQL TYPE IS XML AS CLOB(n) hostvar_name
where hostvar_name is a CLOB host variable that contains XML data encoded in the mixed codepage of the application.
SQL TYPE IS XML AS DBCLOB(n) hostvar_name
where hostvar_name is a DBCLOB host variable that contains XML data encoded in the application graphic codepage.
SQL TYPE IS XML AS BLOB(n) hostvar_name
where hostvar_name is a BLOB host variable that contains XML data internally encoded.
SQL TYPE IS XML AS CLOB_FILE hostvar_name
where hostvar_name is a CLOB file that contains XML data encoded in the application mixed codepage.
SQL TYPE IS XML AS DBCLOB_FILE hostvar_name
where hostvar_name is a DBCLOB file that contains XML data encoded in the application graphic codepage.
SQL TYPE IS XML AS BLOB_FILE hostvar_name
where hostvar_name is a BLOB file that contains XML data internally encoded.
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).
.-,-------------. 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.
Static SQL in embedded SQL programs
An example of a statically executed statement in C is:
/* select values from table into host variables using STATIC SQL and print them*/ EXEC SQL SELECT id, name, dept, salary INTO :id, :name, :dept, :salary FROM staff WHERE id = 310; printf(" %3d %-8.8s %4d %7.2f\n\n", id, name, dept, salary);
Dynamic SQL in embedded SQL programs
An example of a dynamically executed statement in C is:
/* Update column in table using DYNAMIC SQL*/ strcpy(hostVarStmtDyn, "UPDATE staff SET salary = salary + 1000 WHERE dept = ?"); EXEC SQL PREPARE StmtDyn FROM :hostVarStmtDyn; EXEC SQL EXECUTE StmtDyn USING :dept;