Informix Guide to SQL: Syntax SQL Statements

Use the EXECUTE statement to run a previously prepared statement or set of statements.

Use this statement with ESQL/C.

The EXECUTE statement passes a prepared SQL statement to the database server for execution. The following example shows an EXECUTE statement within an ESQL/C program:

EXEC SQL prepare  del_1 from

	'delete  from  customer  

		where  customer_num  =  119'; 

EXEC SQL execute  del_1; 

Once prepared, an SQL statement can be executed as often as needed.

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 those in the following list:

• A prepared SELECT statement that returns more than one row

When you use a prepared SELECT statement to return multiple rows of data, you must use a cursor to retrieve the data rows. As an alternative, you can EXECUTE a prepared SELECT INTO TEMP statement to achieve the same result.

For more information on cursors, see DECLARE.

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

When you prepare an EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement for an SPL function that returns multiple rows, you must use a cursor to retrieve the data rows.

For more information on how to execute a SELECT or an EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement, 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.

A program can consist of one or more source-code files. By default, the scope of a statement identifier is global to the program, so a statement identifier created in one file can be referenced from another file.

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.

Following an EXECUTE statement, the sqlca can reflect two results:

• The sqlca can reflect an error within the EXECUTE statement. For example, when an UPDATE WHERE statement within a prepared statement processes zero rows, the database server sets sqlca to 100.
• The sqlca can reflect the success or failure of the executed statement.

If a prepared statement fails to access any rows, the database server returns zero (0). In a multistatement prepare, if any statement in the following list fails to access rows, the database server returns SQLNOTFOUND (100):

• INSERT INTO table SELECTWHERE
• SELECT INTO TEMPWHERE
• DELETEWHERE
• UPDATEWHERE

In an ANSI-compliant database, if you prepare and execute any of the statements in the preceding list, and no rows are returned, the database server returns SQLNOTFOUND (100).

Use the INTO clause to save the return values of the following SQL statements:

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

  Element	Purpose	Restrictions	Syntax
  descriptor	Quoted string that identifies a system-descriptor area	System-descriptor area must already be allocated.	Quoted String, p. 4-260
  descriptor_var	Host variable that identifies the system-descriptor area	System-descriptor area must already be allocated.	Quoted String, p. 4-260
  indicator_var	Host variable that receives a return code if null data is placed in the corresponding output _var	Variable cannot be DATETIME or INTERVAL data type.	Variable name must conform to language-specific rules for variable names.
  output_var	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.
  sqlda_pointer	Pointer 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 sqlda_pointer with a dollar sign ($) or a colon (:). You must use an sqlda structure if you are using dynamic SQL statements.	DESCRIBE, p. 2-382

The INTO clause provides a concise and efficient alternative to more complicated and lengthy syntax. In addition, by placing values into variables that can be displayed, the INTO clause simplifies and enhances your ability to retrieve and display data values. For example, if you use the INTO clause, you do not have to use a cursor to retrieve values from a table.

You can store the returned values into output variables, output SQL descriptors, or output sqlda pointers.

If you execute a prepared SELECT statement that returns more than one row of data or a prepared EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement 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, after you select the data, check the indicator variable that is associated with the column to determine if the value is null.

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

1. Declare the output variables that the EXECUTE statement uses.
2. Use the PREPARE statement to prepare your SELECT statement or to prepare your EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement.
3. Use the EXECUTE statement, with the INTO clause, to execute your SELECT statement or to execute your EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement.

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 that identifies a system
• A descriptor that is a pointer to an sqlda structure

If you know the number of return values to be supplied at runtime and their data types, you can define the values that the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement returns as host variables in your program. You use these host variables with the INTO keyword, followed by the names of the variables. These variables are matched with the return values in a one-to-one correspondence, from left to right.

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

If you do not know the number of return values to be supplied at runtime or their data types, you can associate output values with a system-descriptor area. A system-descriptor area describes the data type and memory location of one or more values.

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 shows how to use the 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';

The COUNT field corresponds to the number of values that the prepared statement returns. The value of COUNT must be less than or equal to the value of the occurrences that were specified when the system-descriptor area was allocated with the ALLOCATE DESCRIPTOR statement. You can obtain the value of a field with the GET DESCRIPTOR statement and set the value with the SET DESCRIPTOR statement.

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

If you do not know the number of output values to be returned at runtime or their data types, you can associate output values from an sqlda structure. An sqlda structure lists the data type and memory location of one or more return values. To specify an sqlda structure as the location of return values, use the INTO DESCRIPTOR clause of the EXECUTE statement. Each time the EXECUTE statement is run, the database server places the returns values that the sqlda structure describes into the 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;

The sqld value specifies the number of output values that are described in occurrences of sqlvar. This number must correspond to the number of values that the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement returns.

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

The following example shows how to use the INTO clause with an EXECUTE statement in ESQL/C:

EXEC SQL prepare sel1 from 'select fname, lname from customer

        where customer_num =123';
EXEC SQL execute sel1 into :fname, :lname using :cust_num;

The following example shows how to use the INTO clause to return multiple rows of data:

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);
        }

Use the USING clause to specify the values that are to replace question-mark (?) placeholders in the prepared statement. Providing values in the EXECUTE statement that replace the question-mark placeholders in the prepared statement is sometimes called parameterizing the prepared statement.

If you know the number of parameters to be supplied at runtime and their data types, you can define the parameters that are needed by the statement as host variables in your program.

If you do not know the number of parameters to be supplied at runtime or their data types, you can associate input values from a system-descriptor area or an sqlda structure. Both of these descriptor structures describe the data type and memory location of one or more values to replace question-mark (?) placeholders.

You pass parameters to the database server by opening the cursor with the USING keyword, followed by the names of the variables. These variables are matched with prepared statement question-mark (?) placeholders in a one-to-one correspondence, from left to right. You must supply one storage parameter variable 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 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;

You can create a system-descriptor area that describes the data type and memory location of one or more values and then specify the descriptor in 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 COUNT field corresponds to the number of dynamic parameters in the prepared statement. The value of COUNT must be less than or equal to the number of item descriptors that were specified when the system-descriptor area was allocated with the ALLOCATE DESCRIPTOR statement.

The following example shows how to use system descriptors to execute a prepared statement in ESQL/C:

EXEC SQL execute prep_stmt using sql descriptor 'desc1';

You can specify the sqlda pointer in the USING DESCRIPTOR clause of the EXECUTE statement.

Each time the EXECUTE statement is run, the values that the descriptor structure describes are used to replace question-mark (?) placeholders in the PREPARE statement.

The sqld value specifies the number of input values that are described in occurrences of sqlvar. This number must correspond to the number of dynamic parameters in the prepared statement.

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

EXEC SQL execute prep_stmt using descriptor pointer2

Related statements: ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR, DECLARE, EXECUTE IMMEDIATE, FETCH, GET DESCRIPTOR, PREPARE, PUT, and SET DESCRIPTOR

For a task-oriented discussion of the EXECUTE statement, see the Informix Guide to SQL: Tutorial.

For more information about concepts relating to the EXECUTE statement, refer to the Informix ESQL/C Programmer's Manual.