User's Guide Part VI. SQL Anywhere Reference Chapter 43. Watcom-SQL StatementsTo execute a SQL statement.
Syntax 1
EXECUTE statement-name
... [ USING DESCRIPTOR sqlda-name
| USING host-variable-list ]
... [ INTO DESCRIPTOR into-sqlda-name
| INTO into-host-variable-list ]
... [ ARRAY :nnn ]
statement-name: identifier, or host-variable
sqlda-name: identifier
into-sqlda-name: identifier
Syntax 2
EXECUTE IMMEDIATE statement
statement: string, or host-variable
Embedded SQL.
Permissions are checked on the statement being executed.
None.
Format 1 executes the named dynamic statement which was previously prepared. If the dynamic statement contains host variable place holders which supply information for the request (bind variables), then either the sqlda-name must specify a C variable which is a pointer to an SQLDA containing enough descriptors for all bind variables occurring in the statement, or the bind variables must be supplied in the host-variable-list.
The optional ARRAY clause can be used with prepared INSERT statements, to allow wide inserts, which insert more than one row at a time and which may improve performance. The value nnn is the number of rows to be inserted. The SQLDA must contain nnn * (columns per row) variables. The first row is placed in SQLDA variables 0 to (columns per row)-1, and so on.
OUTPUT from a SELECT statement or a CALL statement is put either into the variables in the variable list or into the program data areas described by the named SQLDA. The correspondence is one to one from the OUTPUT (selection list or parameters) to either the host variable list or the SQLDA descriptor array.
If EXECUTE is used with an INSERT statement, the inserted row is returned in the second descriptor. For example, when using auto-increment primary keys or when using before insert triggers that generate primary key values, the EXECUTE statement provides a mechanism to re-fetch the row immediately and determine the primary key value assigned to the row. The same thing can be achieved by using @@identity with auto-increment keys.
Format 2 is a short form to PREPARE and EXECUTE a statement that does not contain bind variables or output. The SQL statement contained in the string or host-variable is immediately executed.
The EXECUTE statement can be used for any SQL statement that can be prepared. Cursors are used for SELECT statements or CALL statements that return many rows from the database (see "Cursors in Embedded SQL").
After successful execution of an INSERT, UPDATE or DELETE statement, the sqlerrd[2] field of the SQLCA (SQLCOUNT) is filled in with the number of rows affected by the operation.
1. EXEC SQL EXECUTE IMMEDIATE'DELETE FROM employee WHERE emp_id = 105';
2. EXEC SQL PREPARE del_stmt FROM'DELETE FROM employee WHERE emp_id = :a';EXEC SQL EXECUTE del_stmt USING :employee_number;
3. EXEC SQL PREPARE sel1 FROM'SELECT emp_lname FROM employee WHERE emp_id = :a';EXEC SQL EXECUTE sel1 USING :employee_number INTO :emp_lname;