Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

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

C Return

7.7 Static SQL without cursor

Because the use of SQL-described tables and views in native I/O has some restrictions (that

is, they cannot be sorted), we must look for an alternate method.

With static and dynamic SQL you can embed SQL statements into your source code.

In static SQL, the statement is determined at compile time. All SQL scalar functions can be

used in the embedded SQL statements. You can integrate host variables, which are set at

runtime. The syntax is checked by the precompiler and then the SQL statements are replaced

by adequate function calls.

Static SQL is commonly used in these ways:

򐂰 To return one single row from a select statement into host variables

򐂰 To insert, update, or delete several rows using one single SQL statement

򐂰 For other actions such as:

– To declare global temporary tables

– To create and drop temporary aliases

– To grant temporary privileges

– To set path or set schema

7.7.1 Static SQL returning a single row

If the result of an SQL statement will be only one row, it can be directly returned into host

variables in one of the following manners:

򐂰 SELECT ... INTO

򐂰 SET HostVariable = SELECT ...

SELECT ... INTO

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 a single row is returned, SQLCODE is set to 0 or a value between 1 and 99, and the

appropriate values are assigned to the host variables.

򐂰 If the table is empty, the statement assigns +100 to SQLCODE and '02000' to

SQLSTATE. If you do not use indicator variables to detect NULL values, the host

variables are

not updated; otherwise NULL is returned.

򐂰 If the result consists of more than one row, SQLCODE -811 is returned, but the host

variables are updated with the results from the first row.

Example 7-17 shows how the total amount of an order is calculated and returned within a

SELECT ... INTO statement.

Example 7-17 Using SELECT ... INTO to retrieve summary values

D TotalDetail S 11P 2

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

C/EXEC SQL

C+ Select Sum(OrderDtl_Total)

C+ into :TotalDetail

C+ from Order_Detail

Chapter 7. Embedded SQL 95

C+ where Order_Number = '00020'

C/End-Exec

C TotalDetail Dsply

C Return

SET :HostVariable = (SELECT ... )

It is possible to fill host variables directly through a select statement. This can only be used

when the result consists only of one single record.

In contrast to the SELECT ... INTO statement, SQLCODE and SQLSTATE cannot be used to

check if a record is found. If no record is found, NULL values are returned by default. You

either have to use indicator variables to detect NULL values or an SQL scalar function like

COALESCE that converts the NULL value into a default.

If the result consists of more than one row, SQLCODE -811 is returned, but in contrast to the

SELECT ... INTO statement, the host variables are not updated.

The following example shows how the total amount of an order is calculated and returned

within a SET-Statement.

Example 7-18 Using the SET statement to retrieve summary values

D TotalDetail S 11P 2

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

C/EXEC SQL

C+ Set :TotalDetail = (Select Sum(OrderDtl_Total)

C+ from Order_Detail

C+ where Order_Number = '00020')

C/End-Exec

C TotalDetail Dsply

C Return

7.7.2 Processing non-Select statements with static SQL without cursor

A 100 percent normalized database is a utopia. In most databases you will find a certain

degree of denormalization, which leads to some redundancies in the tables. Consequently,

you have to sometimes update several rows with the same value.

There are also other situations where you have to insert and delete a couple of rows. For

example, if you have to reorganize your tables. You write rows to history tables and delete the

original rows after.

In Example 7-20 on page 96, written with native I/O, all Order Header and corresponding

Order Detail rows with the order date of the previous year must be saved in history files and

deleted after.

The logical file over the Order Header file is described in Example 5-1 on page 60. The Order

Header history file ORDHDRH is created via CRTDUPOBJ from the Order Header File

(ORDHDR) described in Example 4-2 on page 36.

Example 7-19 on page 96 shows the DDS definition of the Order Detail file ORDDTL.