execute

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)

Syntax diagram for the execute statement

#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, 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, 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

Implicit SQL DELETE

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.

Implicit SQL INSERT

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

Implicit SQL UPDATE

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 tasks
Syntax diagram