EGL Reference Guide for iSeries
The EGL execute statement lets you write one or more SQL
statements; in particular, SQL data-definition statements (of type CREATE
TABLE, for example) and data-manipulation statements (of type INSERT or
UPDATE, for example)

- #sql{ sqlStatement }
- An explicit SQL statement. If you want the SQL statement to update
or delete a row in a result set, code an SQL UPDATE or DELETE statement that
includes the following clause:
WHERE CURRENT OF resultSetID
- resultSetID
- The resultSetID specified in the EGL open statement that made the result
set available.
Leave no space between #sql and the left brace.
- for SQL record name
- Name of an SQL record.
If you specify a statement type (delete, insert, or update), EGL uses the
SQL record to build an implicit SQL statement, as described later. In
any case, you can use the SQL record to test the outcome of the
operation.
- preparedStatementID
- Refers to an EGL prepare statement that has the specified ID. If
you do not reference a prepare statement, you must specify either an explicit
SQL statement or a combination of an SQL record and a statement type (delete,
insert, or update).
- delete, insert, update
- Indicates that EGL is to provide an implicit SQL statement of the
specified type. A declaration-time error occurs if you specify a
statement type but not an SQL record name.
If you do not set a statement type, you must specify either an explicit SQL
statement or a reference to a prepare statement.
For an overview of implicit SQL statements, see SQL
support.
Several example statements are as follows (assuming that employeeRecord is
an SQL record):
execute
#sql{
create table employee (
empnum decimal(6,0) not null,
empname char(40) not null,
empphone char(10) not null)
};
execute update for employeeRecord;
execute
#sql{
call aStoredProcedure( :argumentItem)
};
You can use an execute statement to issue SQL statements of the
following types:
- ALTER
- CALL
- CREATE ALIAS
- CREATE INDEX
- CREATE SYNONYM
- CREATE TABLE
- CREATE VIEW
- DECLARE global temporary table
- DELETE
- DROP INDEX
- DROP SYNONYM
- DROP TABLE
- DROP VIEW
- GRANT
- INSERT
- LOCK
- RENAME
- REVOKE
- SAVEPOINT
- SET
- SIGNAL
- UPDATE
- VALUES
You cannot use an execute statement to issue SQL statements of the
following types:
- CLOSE
- COMMIT
- CONNECT
- CREATE FUNCTION
- CREATE PROCEDURE
- DECLARE CURSOR
- DESCRIBE
- DISCONNECT
- EXECUTE
- EXECUTE IMMEDIATE
- FETCH
- OPEN
- PREPARE
- ROLLBACK WORK
- SELECT
- INCLUDE SQLCA
- INCLUDE SQLDA
- WHENEVER
The effect of requesting an implicit SQL DELETE statement is that an SQL
record property (defaultSelectCondition) determines what table rows
are deleted, so long as the value in each SQL table key column is equal to the
value in the corresponding key item of the SQL record. If you specify
neither a record key nor a default selection condition, all table rows are
deleted.
The implicit SQL DELETE statement for a particular record is similar to the
following statement:
DELETE FROM tableName
WHERE keyColumn01 = :keyItem01
You cannot use a single EGL statement to delete rows from more than one
database table.
The effect of requesting an implicit SQL INSERT statement is as follows by
default:
- The key value in the record determines the logical position of the data in
the table. A record that does not have a key is handled in accordance
with the SQL table definition and the rules of the database.
- As a result of the association of record items and SQL table columns in
the record part, the generated code places the data from each record item into
the related SQL table column.
- If you declared a record item to be read only, the generated SQL INSERT
statement does not include that record item, and the database management
system sets the value of the related SQL table column to the default value
that was specified when the column was defined.
The format of the implicit SQL INSERT statement is like this:
INSERT INTO tableName
(column01, ... columnNN)
values (:recordItem01, ... :recordItemNN)
Some error conditions are as follows:
- You specify an SQL statement of a type other than INSERT
- You specify some but not all clauses of an SQL INSERT statement
- You specify an SQL INSERT statement (or accept an implicit SQL statement)
that has any of these characteristics--
- Is related to more than one SQL table
- Includes only host variables that you declared as read only
- Is associated with a column that either does not exist or is incompatible
with the related host variable
The effect of requesting an implicit SQL UPDATE statement is as follows by
default:
- An SQL record property (defaultSelectCondition) determines what
table rows are selected, so long as the value in each SQL table key column is
equal to the value in the corresponding key item of the SQL record. If
you specify neither a record key nor a default selection condition, all table
rows are updated.
- As a result of the association of record items and SQL table columns in
the SQL record declaration, a given SQL table column receives the content of
the related record item. If an SQL table column is associated with a
record item that is read only, however, that column is not updated.
The format of the implicit SQL UPDATE statement for a particular record is
similar to the following statement:
UPDATE tableName
SET column01 = :recordItem01,
column02 = :recordItem01, ...
columnNN = :recordItemNN
WHERE keyColumn01 = :keyItem01
An error occurs in any of the following cases:
- All the items are identified as read only
- The statement attempts to update more than one SQL table
- An item whose value is being written to the database is associated with a
column that either does not exist at run time or is incompatible with that
item
Related concepts
Record types and properties
SQL support
References to parts
Related tasks
Syntax diagram
Related reference
add
close
delete
EGL statements
Exception handling
get
get next
get previous
I/O error values
open
prepare
replace
SQL item properties
sysVar.terminalID
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.