Syntax

Element	Purpose	Restrictions	Syntax
statement id	Identifier for an SQL statement	You must have defined the statement identifier in a previous PREPARE statement. After you release the database server resources (using a FREE statement), you cannot use the statement identifier with a DECLARE cursor or with the EXECUTE statement until you prepare the statement again.	PREPARE, p. 1-541
statement id variable	Host variable that identifies an SQL statement	You must have defined the host variable in a previous PREPARE statement. The host variable must be a character data type.	PREPARE, p. 1-541

EXEC SQL prepare  del_1 from

	'delete  from  customer  

		where  customer_num  =  119'; 

EXEC SQL execute  del_1; 

If the statement contained question mark (?) placeholders, you use the USING clause to provide specific values for them before execution.For more information, see the "USING Clause".

You can execute any prepared statement except the following:

• A prepared SELECT statement that returns more than one row

When you use a prepared SELECT statement to return multiple rows of data, you can use the DECLARE, OPEN, and FETCH cursor statements to retrieve the data rows. In addition, you can use EXECUTE on a prepared SELECT INTO TEMP statement to achieve the same result.

• A prepared EXECUTE FUNCTION statement for an SPL function that returns more than one row

When you prepare an EXECUTE FUNCTION statement for a SPL function that returns multiple rows, you need to use the DECLARE, OPEN and FETCH cursor statements just as you would with a SELECT statement that returns multiple rows.

For more information on how to execute a SELECT or an EXECUTE FUNCTION, see "PREPARE".

If you create or drop a trigger after you prepared a triggering INSERT, DELETE, or UPDATE statement, the prepared statement returns an error when you execute it.

Scope of Statement Identifiers

In a multiple-file program, if you want to limit the scope of a statement identifier to the file in which it is executed, you can preprocess all the files with the -local command-line option. See your SQL API product manual for more information, restrictions, and performance issues when you preprocess files with the -local option.

INTO Clause

(1 of 2)

Element	Purpose	Restrictions	Syntax
output descriptor	Quoted string that identifies a system-descriptor area	System-descriptor area must already be allocated.	Quoted String, p. 1-1014
output descriptor variable	Host variable name that identifies the system-descriptor area	System-descriptor area must already be allocated.	Quoted String, p. 1-1014
output indicator variable	Host variable that receives a return code if null data is placed in the corresponding output variable	Variable cannot be DATETIME or INTERVAL data type.	Variable name must conform to language-specific rules for variable names.
output sqlda pointer	Points to an sqlda structure that defines the data type and memory location of values that correspond to the question-mark (?) placeholder in a prepared statement.	You cannot begin an output sqlda pointer with a dollar sign ($) or a colon (:). You must use an sqlda structure if you are using dynamic SQL statements.	DESCRIBE, p. 1-338
output variable name	Host variable whose contents replace a question-mark (?) placeholder in a prepared statement	Variable must be a character data type.	Variable name must conform to language-specific rules for variable names.

• A prepared singleton SELECT statement that returns only one row of column values for the columns in the select list
• A prepared EXECUTE FUNCTION statement for an SPL function that returns only one group of values

Important: If you execute a prepared SELECT statement that returns more than one row of data or a prepared EXECUTE FUNCTION for an SPL function that returns more than one group of return values, you receive an error message. In addition, if you prepare and declare a statement, and then attempt to execute that statement, you receive an error message.

You cannot select a null value from a table column and place that value into an output variable. If you know in advance that a table column contains a null value, make sure after you select the data that you check the indicator variable that is associated with the column to determine if the value is null.

The following list describes how to use the INTO clause with the EXECUTE statement:

1. Declare the output variables that the EXECUTE statement uses in its INTO clause.

2. Use the PREPARE statement to prepare your SELECT or EXECUTE FUNCTION statement.

3. Use the EXECUTE statement, with the INTO clause, to execute your SELECT or EXECUTE FUNCTION statement.

• A host variable name (if the number and data type of the return values are known at compile time)
• A system-descriptor area that identifies a dynamically generated descriptor for the value

  E/C

• An sqlda structure that is a pointer to a dynamically generated descriptor for the value.

You must supply one variable name for each value that the SELECT or EXECUTE FUNCTION returns. The data type of each variable must be compatible with the corresponding return value of the prepared statement.

The following example shows how to use the INTO clause of an EXECUTE statement to execute a singleton SELECT and store the column values in host variables:

EXEC SQL prepare sel1 from
'select fname, lname from customer \
where customer_num =123';

EXEC SQL execute sel1 into :fname, :lname;

EXEC SQL BEGIN DECLARE SECTION;

int customer_num =100;

char fname[25];

EXEC SQL END DECLARE SECTION;

EXEC SQL prepare sel1 from 'select fname from customer

        where customer_num=?';

 for ( ;customer_num < 200; customer_num++)


         {


         EXEC SQL execute sel1 into :fname using :customer_num;


         printf("Customer number is %d\n", customer_num);


         printf("Customer first name is %s\n\n", fname);


         }

Saving Values in a System-Descriptor Area

X/O

You can also use an sqlda structure (page 1-391) to supply parameters dynamically. However, a system-descriptor area conforms to the X/Open standards.

To specify a system-descriptor area as the location of output values, use the INTO SQL DESCRIPTOR clause of the EXECUTE statement. Each time that the EXECUTE statement is run, the values that the system-descriptor area describes are stored in the system-descriptor area.

The following example show how to use system-descriptor area to execute prepared statements in INFORMIX-ESQL/C:

EXEC SQL allocate descriptor 'desc1';

...

sprintf(sel_stmt, "%s %s %s", 

	"select fname, lname from customer",

	"where customer_num =",

	cust_num);

EXEC SQL prepare sel1 from :sel_stmt;

EXEC SQL execute sel1 into sql descriptor 'desc1';

For further information, refer to the discussion of the system-descriptor area in the INFORMIX-ESQL/C Programmer's Manual.

Saving Values in an sqlda Structure

The following example shows how to use an sqlda structure to execute a prepared statement in INFORMIX-ESQL/C:

struct sqlda *pointer2;

...

sprintf(sel_stmt, "%s %s %s", 

	"select fname, lname from customer",

	"where customer_num =",

	cust_num);

EXEC SQL prepare sel1 from :sel_stmt;

EXEC SQL describe sel1 into pointer2;

EXEC SQL execute sel1 into descriptor pointer2;

For more information, refer to the sqlda discussion in the INFORMIX-ESQL/C Programmer's Manual.

USING Clause

(1 of 2)

Element	Purpose	Restrictions	Syntax
storage descriptor	Quoted string that identifies a system-descriptor area	System-descriptor area must already be allocated. Make sure surrounding quotes are single.	Quoted String, p. 1-1014
storage descriptor variable	Host variable name that identifies a system-descriptor area	System-descriptor area must already be allocated.	Variable name must conform to language-specific rules for variable names.
storage indicator variable	Host variable that receives a return code if null data is placed in the corresponding data variable. It receives truncation information if truncation occurs.	Variable cannot be DATETIME or INTERVAL data type.	Variable name must conform to language-specific rules for variable names.
storage sqlda pointer	Points to an sqlda structure that defines the data type and memory location of values that correspond to the question-mark (?) placeholder in a prepared statement.	You cannot begin storage sqlda pointer with a dollar sign ($) or a colon (:). You must use an sqlda structure if you are using dynamic SQL statements.	DESCRIBE, p. 1-338
storage variable name	Host variable whose contents replace a question-mark (?) placeholder in a prepared statement	Variable must be a character data type.	Variable name must conform to language-specific rules for variable names.

You can specify any of the following items to replace the question-mark placeholders in a statement before you execute it:

• A host variable name (if the number and data type of the question marks are known at compile time)
• A system-descriptor area that identifies a dynamically-generated descriptor for the value

  E/C

• An sqlda structure that is a pointer to a dynamically-generated descriptor for the value

You must supply one storage variable name for each placeholder. The data type of each variable must be compatible with the corresponding value that the prepared statement requires.

The following example executes the prepared UPDATE statement in INFORMIX-ESQL/C:

stcopy ("update  orders  set  order_date  =  ?  where  po_num  =  ?", stm1);

EXEC SQL prepare  statement_1  from  :stm1;

EXEC SQL execute  statement_1  using  :order_date,:po_num;

X/O

You can also use an sqlda structure (page 1-395) to dynamically supply parameters. However, a system-descriptor area conforms to the X/Open standards.

To specify a system-descriptor area as the location of parameters, use the USING SQL DESCRIPTOR clause of the EXECUTE statement. Each time that the EXECUTE statement is run, the values that the system-descriptor area describes are used to replace question-mark (?) placeholders in the PREPARE statement.

The following example show how to use system-descriptor area to execute prepared statements in INFORMIX-ESQL/C:

EXEC SQL allocate descriptor 'desc1';

...

EXEC SQL execute prep_stmt using sql descriptor 'desc1';

For further information, refer to the discussion of the system-descriptor area in the INFORMIX-ESQL/C Programmer's Manual.

Supplying INFORMIX-ESQL/C Parameters Through an sqlda Structure

The following example shows how to use an sqlda structure to execute a prepared statement in INFORMIX-ESQL/C:

struct sqlda *pointer2;

...

EXEC SQL execute prep_stmt using descriptor pointer2;

For more information, refer to the sqlda discussion in the INFORMIX-ESQL/C Programmer's Manual.

Error Conditions with EXECUTE

• The sqlca record can reflect an exception within the EXECUTE statement.
• The sqlca structure can also reflect the success or failure of the prepared statement that EXECUTE runs. For example, when an UPDATE ... WHERE ... statement within a prepared object processes zero rows, the database server sets sqlca.sqlcode to 100.

ANSI

In an ANSI-compliant database, if you prepare and execute any of the following statements, and no rows are returned, the database server returns an SQLCODE value of SQLNOTFOUND (100):

• INSERT INTO table-name SELECT ... WHERE ...
• SELECT INTO TEMP ... WHERE ...
• DELETE ... WHERE
• UPDATE ... WHERE ...

Tip: When you encounter an SQLCODE error, a corresponding SQLSTATE error value might exist. See the GET DIAGNOSTICS statement for information about the SQLSTATE status variable.

References

In the Informix Guide to SQL: Tutorial, see the discussion of the EXECUTE statement in Chapter 5.

EXECUTE FUNCTION

Syntax

Element	Purpose	Restrictions	Syntax
SPL variable	A variable created with the DEFINE statement that contains the name of an SPL routine to be executed	The SPL variable must be CHAR, VARCHAR, NCHAR, or NVARCHAR data type. The name assigned to SPL variable must be non-null and the name of an existing SPL function.	Identifier, p. 1-966

You can use EXECUTE FUNCTION to execute an SPL function or an external function. You cannot use EXECUTE FUNCTION to execute any type of user-defined procedure. Instead, use the EXECUTE PROCEDURE statement to execute procedures.

How EXECUTE FUNCTION Works

• The qualified function name or the function signature (the function name with its parameter list) must be unique within the name space or database.
• The function must exist.
• The function must not have any OUT parameters.

The EXECUTE FUNCTION statement returns an error under the following conditions:

• It specifies more arguments than the called function expects.
• One or more arguments are missing and do not have default values. In this case, the arguments are initialized to the value of UNDEFINED.
• The fully qualified function name or the function signature is not unique.
• No function with the specified name or signature that you specify is found.
• You use it to try to execute a user-defined procedure.

• If you use a fully qualified function name, the database server determines which function to use based only on the routine type (which is FUNCTION) and the function name.
• If you use a function signature, the database server uses the function name and its full parameter list during routine resolution to determine which function to use.

INTO Clause

Element	Purpose	Restrictions	Syntax
data variable	A variable that receives the value returned by a function	If you issue this statement within an ESQL/C program, the data variable must be a host variable. If you issue this statement within an SPL routine, the data variable must be an SPL variable. If you issue this statement within a CREATE TRIGGER statement, the data variable must be column names within the triggering table or another table.	The name of a receiving variable must conform to language-specific rules for variable names. For the syntax of SPL variables, see Identifier, p. 1-966. For the syntax of column names, see Identifier, p. 1-966.
data structure	A structure that has been declared as a host variable	The individual elements of the structure must be matched appropriately to the data type of values being selected.	The name of the data structure must conform to language-specific rules for data structures.
indicator variable	A program variable that receives a return code if null data is placed in the corresponding data variable	This parameter is optional, but you should use an indicator variable if the possibility exists that the value of the corresponding data variable is null.	The name of the indicator variable must conform to language-specific rules for indicator variables.

You must specify an INTO clause with EXECUTE FUNCTION to name the variables that receive the values that the function returns. If the function returns more than one value, the values are returned into the list of variables in the order in which you specify them.

If the EXECUTE FUNCTION statement stands alone (that is, it is not part of a DECLARE statement and does not use the INTO clause), it must execute a noncursor function. A noncursor function returns only one row of values. The following example shows a SELECT statement in INFORMIX-ESQL/C:

EXEC SQL execute function cust_num(fname, lname, company_name)

	into :c_num;

INTO Clause with Cursors

EXT

To return more than one row of values, an external function must be defined as an iterator function. For more information on how to write iterator functions, see the DataBlade API Programmer's Manual.

SPL

To return more than one row of values, an SPL function must include the WITH RESUME keywords in its RETURN statement. For more information on how to write SPL functions, see Chapter 14 in the Informix Guide to SQL: Tutorial.

E/C

In an INFORMIX-ESQL/C program, use the DECLARE statement to declare the function cursor and the FETCH statement to fetch the rows individually from the function cursor. You can put the INTO clause in the FETCH statement rather than in the EXECUTE FUNCTION statement, but you cannot put it in both. The following INFORMIX-ESQL/C code examples show different ways you can use the INTO clause:

Using the INTO clause in the EXECUTE FUNCTION statement

EXEC SQL declare f_curs cursor for

	execute function get_orders(customer_num)

	into :ord_num, :ord_date;

EXEC SQL open f_curs;

while (SQLCODE == 0)

	EXEC SQL fetch f_curs;

EXEC SQL close f_curs;

EXEC SQL declare f_curs cursor for

	execute function get_orders(customer_num);

EXEC SQL open f_curs;

while (SQLCODE == 0)

	EXEC SQL fetch f_curs into :ord_num, :ord_date;

EXEC SQL close f_curs;

SPL

In an SPL routine, if a SELECT returns more than one row, you must use the FOREACH statement to access the rows individually. The INTO clause of the SELECT statement holds the fetched values. For more information, see the FOREACH statement on page 2-27.

Preparing an EXECUTE FUNCTION...INTO Statement

SPL

Dynamic Routine-Name Specification of SPL Functions

For more information about how to execute SPL functions dynamically, see Chapter 14 in the Informix Guide to SQL: Tutorial.

References

In the Informix Guide to SQL: Tutorial, see the discussion of writing SPL routines in Chapter 14.

EXECUTE IMMEDIATE

Syntax

Element	Purpose	Restrictions	Syntax
statement variable name	Host variable whose value is a character string that consists of one or more SQL statements	The host variable must have been defined within the program. The variable must be character data type. For additional restrictions, see "EXECUTE IMMEDIATE and Restricted Statements" and "Restrictions on Allowed Statements".	Variable name must conform to language-specific rules for variable names.

The EXECUTE IMMEDIATE statement makes it easy to execute dynamically a single simple SQL statement, which is constructed during program execution. For example, you could obtain the name of a database from program input, construct the DATABASE statement as a program variable, and then use EXECUTE IMMEDIATE to execute the statement, which opens the database.

The following example shows the EXECUTE IMMEDIATE statement in INFORMIX-ESQL/C:

sprintf(cdb_text,  "create  database  %s",  usr_db_id);

EXEC SQL execute  immediate :cdb_text;

Use a PREPARE statement and either a cursor or the EXECUTE statement to execute a dynamically constructed SELECT statement.

Restrictions on Allowed Statements

• The statement cannot contain a host-language comment.
• Names of host-language variables are not recognized as such in prepared text. The only identifiers that you can use are names defined in the database, such as table names and columns.
• The statement cannot reference a host variable list or a descriptor; it must not contain any question-mark (?) placeholders, which are allowed with a PREPARE statement.
• The text must not include any embedded SQL statement prefix or terminator, such as the dollar sign ($), colon (:), or the words EXEC SQL.
• A SELECT or INSERT statement cannot contain a Collection Derived Table clause. EXECUTE IMMEDIATE cannot process input host variables, which are required for a collection variable. Use EXECUTE or a cursor to process prepared accesses to collection variables.

In the Informix Guide to SQL: Tutorial, see the discussion of quick execution in Chapter 5.

EXECUTE PROCEDURE

Syntax

Element	Purpose	Restrictions	Syntax
receiving variable	A variable that receives the value returned by an SPL function that you execute with EXECUTE PROCEDURE.	If you issue this statement within an ESQL/C program, the receiving variable must be a host variable. If you issue this statement within an SPL routine, the receiving variable must be an SPL variable. If you issue this statement within a CREATE TRIGGER statement, the receiving variable must be a column name within the triggering table or another table.	The name of a receiving variable must conform to language-specific rules for variable names. For the syntax of SPL variables, see Identifier, p. 1-966. For the syntax of column names, see Identifier, p. 1-966.
SPL variable	A variable created with the DEFINE statement that contains the name of an SPL routine to be executed.	The SPL variable must have the data type CHAR, VARCHAR, NCHAR, or NVARCHAR. The name you assign to SPL variable must be non-null and the name of an existing routine.	Identifier, p. 1-966

Usage

SPL

For backward compatibility with earlier Informix products, INFORMIX-Universal Server continues to support the INTO clause of the EXECUTE PROCEDURE statement to save values that a stored procedure returns. However, Informix recommends that you use EXECUTE PROCEDURE only with procedures and EXECUTE FUNCTION with functions. For more information, see "INTO Clause".

How EXECUTE PROCEDURE Works

• The qualified procedure name or the procedure signature (the procedure name with its parameter list) must be unique within the name space or database.
• The procedure must exist.

The EXECUTE PROCEDURE statement returns an error under the following conditions:

• It has more arguments than the called procedure expects.
• One or more arguments are missing and do not have default values. In this case the arguments are initialized to the value of UNDEFINED.
• The fully qualified procedure name or the procedure signature is not unique.
• No procedure with the specified name or signature is found.

• If you use a fully qualified procedure name, the database server determines which procedure to use based only on the routine type (which is PROCEDURE) and the procedure name.
• If you use a procedure signature, the database server uses the procedure name and its full parameter list during routine resolution to determine which procedure to use.

INTO Clause

If the stored procedure (or SPL function) returns more than one value, the values are returned into the list of variables in the order in which you specify them. If the stored procedure returns more than one row or a collection data type, you must access the rows or collection elements with a cursor.

For more information on stored procedures of earlier Informix products, see the CREATE PROCEDURE statement.

SPL

Dynamic Routine-Name Specification of SPL Procedures

If the SPL variable names a stored procedure that returns a value, include the INTO clause of EXECUTE PROCEDURE to specify a receiving variable (or variables) to hold the value (or values) that the stored procedure returns.

For more information on how to execute SPL procedures dynamically, see Chapter 14 in the Informix Guide to SQL: Tutorial.

References

In the Informix Guide to SQL: Tutorial, see the discussion of writing SPL routines in Chapter 14.