Filter
Top Products
The SET USER PASSWORD statement (user control statement) and SET ROLE statement (access control
statement) cannot be executed as preparable statements.
The syntax of the multiple row SELECT statement is the same as that of the cursor specification.
To execute these statements, the statements must be prepared for execution. These statements then become
prepared statements.
The following are examples of preparable statements:
Example 1:
In this example, a single row SELECT statement is specified. This statement targets rows to be fetched
in which ITMNO is "110" in a table containing columns STOCKQTY and WHCODE derived from the
STOCK table.
SELECT STOCKQTY, WHCODE FROM STOCKS. STOCK WHERE ITMNO = 110
Example 2:
In this example, a single row SELECT statement is specified. This statement fetches values in
ascending order from column STOCKQTY in a table containing columns PRODUCT and STOCKQTY
derived from the STOCK table.
SELECT PRODUCT, STOCKQTY FROM STOCKS. STOCK ORDER BY STOCKQTY ASC
Example 3:
In this example, an UPDATE statement (positioned) is specified. This statement changes PRODUCT to
"BATTERY" and WHCODE to "5" in the row in which cursor CU1 is positioned.
UPDATE STOCKS. STOCK
SET PRODUCT = 'BATTERY,' WHCODE = 5 WHERE CURRENT OF CU1
Dynamic parameter specification
To specify a value specification (input variable) in a preparable statement, enter a question mark "?" in the value
specification section. The question mark is known as the dynamic parameter specification. The dynamic parameter
specification is equivalent to the variable specification in an embedded SQL statement. Dynamic parameter
specifications can be specified in the following locations in preparable statements:
· In the search condition of a dynamic SELECT statement, single row SELECT statement, UPDATE statement
(searched), or DELETE statement (searched)
· In the set clause of an UPDATE statement (searched) or dynamic preferable UPDATE statement (searched)
· In the insert value list of an INSERT statement
The following are examples of dynamic parameter specifications:
Example 4:
In this example, the dynamic parameter specification is specified in the search condition of a DELETE
statement (searched).
DELETE FROM STOCKS. STOCK WHERE PRODUCT = ?
Example 5:
In this example, the dynamic parameter specification is specified in the set clause of an UPDATE
statement (searched).
UPDATE STOCKS. STOCK
133