Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

Chapter 7. Embedded SQL 109

C if SQLState = '02000' or SQLCode < *Zeros

C leave

C EndIf

C if %SubDt(OrderDate: *Months) <= 3

C/EXEC SQL

C+ delete from Order_Header

C+ where Current of CsrOrdH

C/END-EXEC

C else

C eval OrderTotal += 10

C/EXEC SQL

C+ update Order_Header

C+ Set Order_Total = :OrderTotal,

C+ Order_Delivery = Current Date - 1 Year

C+ where Current of CsrOrdH

C/END-EXEC

C EndIf

C EndDo

C/EXEC SQL Close CsrOrdH

C/END-EXEC

C Return

7.9 Dynamic SQL

Dynamic SQL allows you to define your SQL statements at runtime. That means you create a

text string that contains the SQL statement. Before being executed the text string must be

converted to an SQL statement.

Because the string is created at runtime, host variables are not necessary and cannot be

used. They can be directly integrated into the string. But there are some situations where you

wish to use variables. In this cases you can use parameter markers (?) that can be set in the

EXECUTE or OPEN statement.

To convert the character string containing the SQL statement to an executable SQL

statement one of the following steps is necessary:

򐂰 EXECUTE IMMEDIATE:

A string is converted to an SQL statement and executed immediately. This statement can

only be used if no cursor is needed.

򐂰 PREPARE and EXECUTE:

A string is converted and later executed. Variables can be embedded as parameter

markers and be replaced in the EXECUTE statement. EXECUTE can only be used if no

cursor is needed.

򐂰 PREPARE and DECLARE CURSOR:

A string is converted and the converted SQL statement is used to DECLARE a cursor.

Like in static SQL, either a serial or a scroll cursor can be used.

If you use a variable SELECT list a SQL Descriptor Area (SQLDA) is required where the

returned variables are described.

110 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone

7.9.1 Defining the character string containing the SQL statement

The character field to hold the SQL command can be defined as a Single Byte Character with

fixed or varying length or as a Double Byte Character with fixed or varying length. The

character string must be an executable SQL statement, which means that not only the correct

syntax, but also the integrated variables must be converted.

򐂰 Character strings must be embedded in single quotation marks (‘’).

To integrate a single quotation mark into a string, you have to double it. Do not use an Hex

value instead, because this can cause problems when using different EBCDIC codes.

򐂰 Date and Time fields must be converted into a character string. The character

representation requires a four-digit year format. Then the SQL scalar functions DATE or

TIME must be used to convert these character strings into valid dates.

7.9.2 The EXECUTE IMMEDIATE statement

With EXECUTE IMMEDIATE, the command string is converted and executed in a single SQL

statement. It is a combination of the PREPARE and EXECUTE statements. It can be used to

prepare and execute SQL statements that contain neither host variables nor parameter

markers.

When an EXECUTE IMMEDIATE statement is executed, the specified statement string is

parsed and checked for errors. If the SQL statement is not valid, it is not executed and the

error condition that prevents its execution is reported in the stand-alone SQLSTATE and

SQLCODE. If the SQL statement is valid, but an error occurs during its execution, that error

condition is reported in the stand-alone SQLSTATE and SQLCODE. Additional information

on the error can be retrieved from the SQL Diagnostics Area (or the SQLCA).

In Example 7-35, the Order Header table with order date from the previous year and the

appropriate Order Detail rows are saved in history tables. The names of the history tables are

dynamically built. The year of the stored order data is part of the table name. In the create

table statement the table is not only built but filled with the appropriate data.

Order Header and Order Detail rows are deleted by using static SQL.

Example 7-35 Using the EXECUTE IMMEDIATE statement

D PrevYear S 4P 0

D MySQLString S 32740A varying

*-----------------------------------------------------------------------------------------

/Free

PrevYear = %SubDt(%Date(): *Years) - 1;

MyFile = 'ORDDTL' + %Char(PrevYear);

MySQLString = 'Create Table ' + %Trim(MyLib) + '/' + %Trim(MyFile) +

' as (Select d.* +

from ORDHDR h join ORDDTL d +

on h.OrHNbr = d.OrHNbr +

and year(ORHDTE)= '+ %Char(PrevYear)+')+

with Data';

/End-Free

C/EXEC SQL Execute Immediate :MySQLString

C/End-Exec

C/EXEC SQL

C+ Delete from ORDDTL d

C+ Where d.OrHNbr in (select h.OrHNbr

C+ from ORDHDR h