informix
INFORMIX-ESQL/C Programmer's Manual
Using Dynamic SQL

SQL Statements That Are Known at Compile Time

The simplest type of dynamic SQL to execute is one for which you know both of the following items:

The following sections describe how to execute both non-SELECT statements and SELECT statements whose structure and data are known at compile time.

Executing Non-SELECT Statements

In this chapter, the term non-SELECT statement refers to any SQL statement that can be prepared, except SELECT and EXECUTE FUNCTION. This term includes the EXECUTE PROCEDURE statement.

Important: The INSERT statement is an exception to the rules for non-SELECT statements. If the INSERT inserts a single row, use PREPARE and EXECUTE to execute it. However, if the INSERT is associated with an insert cursor, you must declare the insert cursor.

For more information, see Sending More Than One Row.

For a list of SQL statements that cannot be prepared, see the entry for the PREPARE statement in the Informix Guide to SQL: Syntax.

You can execute a non-SELECT statement in the following ways:

Using PREPARE and EXECUTE

The PREPARE and EXECUTE statements allow you to separate the execution of a non-SELECT statement into two steps:

  1. PREPARE sends the statement string to the database server, which parses the statement and assigns it a statement identifier.
  2. EXECUTE executes the prepared statement indicated by a statement identifier.

This two-step process is useful for statements that need to be executed more than once. You reduce the traffic between the client application and the database server when you parse the statement only once.

For example, you can write a general-purpose deletion program that works on any table. This program would take the following steps:

  1. Prompt the user for the name of the table and the text of the WHERE clause and put the information into C variables such as tabname and search_condition. The tabname and search_condition variables do not need to be host variables because they do not appear in the actual SQL statement.
  2. Create a text string by concatenating the following four components: DELETE FROM, tabname, WHERE, and search_condition. In this example, the string is in a host variable called stmt_buf:
  3. Prepare the entire statement. The following PREPARE statement operates on the string in stmt_buf and creates a statement identifier called d_id:
  4. Execute the statement. The following EXECUTE statement executes the DELETE:
  5. If you do not need to execute the statement again, free the resources used by the statement identifier structure. This example would use the following FREE statement:

If the non-SELECT statement contains input parameters, you must use the USING clause of the EXECUTE statement. For more information, see Using an EXECUTE USING Statement.

The EXECUTE statement is generally used to execute non-SELECT statements. You can use EXECUTE with the INTO clause for a SELECT or an EXECUTE FUNCTION statement as long as these statements return only one group of values (one row). For more information, see Using PREPARE and EXECUTE INTO. However, do not use the EXECUTE statement for:

Using EXECUTE IMMEDIATE

Rather than prepare the statement and then execute it, you can prepare and execute the statement in the same step with the EXECUTE IMMEDIATE statement. The EXECUTE IMMEDIATE statement also frees statement-identifier resources upon completion.

For example, for the DELETE statement used in the previous section, you can replace the PREPARE-EXECUTE statement sequence with the following statement:

You cannot use EXECUTE IMMEDIATE if the statement string contains input parameters. (For information, see Using an EXECUTE USING Statement.) The SQL statements also have restrictions that you can execute with EXECUTE IMMEDIATE. For a complete list of SQL statement restrictions, see the entry for EXECUTE IMMEDIATE in the Informix Guide to SQL: Syntax.

Executing SELECT Statements

You can execute a SELECT statement in the following two ways:

Using PREPARE and EXECUTE INTO

The only prepared SELECT statement that you can execute with the EXECUTE statement is a singleton SELECT. Your ESQL/C program must take the following actions:

  1. Declare host variables to receive the values that the database server returns.
  2. For a prepared SELECT statement, these values are the select-list columns.

  3. Assemble and prepare the statement.
  4. A prepared SELECT statement can contain input parameters in the WHERE clause. For more information, see Assembling and Preparing the SQL Statement.

  5. Execute the prepared selection with the EXECUTE...INTO statement, with the host variables after the INTO keyword.
  6. If the SELECT statement contains input parameters, include the USING clause of EXECUTE.

    Tip: To execute a singleton SELECT, using EXECUTE...INTO is usually more efficient than using the DECLARE, OPEN, and FETCH statements.

With the INTO clause of the EXECUTE statement, you can still use the following features:

You are not required to prepare a singleton SELECT. If you do not need the benefits of a prepared statement, you can embed a singleton SELECT statement directly in your ESQL/C program, as shown in the following example:

Figure 14-8 shows how to execute the items_pct() SPL function (which Figure 16-3 on page 16-24 shows). Because this function returns a single decimal value, the EXECUTE...INTO statement can execute it.


Figure 14-8
Executing the items_pct SPL Function

You can use host variables for routine arguments but not the routine name. For example, if the manu_code variable holds the value "HSK", the following EXECUTE statement replaces the input parameter in the prepared statement to perform the same task as the EXECUTE in Figure 14-8 on page 14-44.

If you do not know the number or data types of the select-list columns or function return values, you must use a dynamic-management structure instead of host variables with the EXECUTE...INTO statement. The dynamic-management structure defines the select-list columns at runtime. For more information, see Handling an Unknown Select List.

Using a Select Cursor

To execute a SELECT statement that returns more than one row, you must declare a select cursor. The select cursor enables the ESQL/C application to handle multiple rows that a query returns.

Your ESQL/C program must take the following actions to use a select cursor:

  1. Declare host variables to receive the values that the database server returns.
  2. For a prepared SELECT statement, these values are the select-list columns. For a prepared EXECUTE FUNCTION statement, these values are the return values of the user-defined function.

  3. Assemble and prepare the statement.
  4. A prepared SELECT statement can contain input parameters in the WHERE clause. A prepared EXECUTE FUNCTION statement can contain input parameters as function arguments. For more information, see Assembling and Preparing the SQL Statement.

  5. Declare the select cursor.
  6. The DECLARE statement associates the prepared SELECT statement with the select cursor.

  7. Execute the query.
  8. The OPEN statement sends any input parameters that its USING clause specifies to the database server and tells the database server to execute the SELECT statement.

  9. Retrieve the rows of values from the select cursor.
  10. The FETCH statement retrieves one row of data that matches the query criteria.

For more information on cursors and cursor-management statements, see Using a Database Cursor.

Warning: Do not use the INTO clause in both a SELECT statement that is associated with a cursor and in a FETCH statement that retrieves data from the cursor. Neither the ESQL/C preprocessor nor the executable program generate an error for this condition. Using the INTO clause in both statements, however, can generate unexpected results.

The lvarptr.ec Program

The lvarptr.ec example program, which follows, uses lvarchar pointers

static void process_stmt(char *stmt)
{
EXEC SQL BEGIN DECLARE SECTION;
lvarchar *lv1;
lvarchar *lv2;
lvarchar *lv3;
mint seq;
char *stmt1 = stmt;
EXEC SQL END DECLARE SECTION;

printf("SQL: %s\n", stmt);

EXEC SQL WHENEVER ERROR STOP;
EXEC SQL PREPARE p_collect FROM :stmt1;
EXEC SQL DECLARE c_collect CURSOR FOR p_collect;
EXEC SQL OPEN c_collect;

ifx_var_flag(&lv1, 1);
ifx_var_flag(&lv2, 1);
ifx_var_flag(&lv3, 1);

while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH c_collect INTO :seq, :lv1, :lv2, :lv3;
if (sqlca.sqlcode == 0)
{
printf("Sequence: %d\n", seq);
print_lvarchar_ptr("LVARCHAR 1", &lv1);
print_lvarchar_ptr("LVARCHAR 2", &lv2);
print_lvarchar_ptr("LVARCHAR 3", &lv3);
ifx_var_dealloc(&lv1);
ifx_var_dealloc(&lv2);
ifx_var_dealloc(&lv3);
}
}

EXEC SQL CLOSE c_collect;
EXEC SQL FREE c_collect;
EXEC SQL FREE p_collect;
}

mint main(int argc, char **argv)
{
EXEC SQL BEGIN DECLARE SECTION;
char *dbase = "stores7";
char *stmt1 =
"INSERT INTO t_collections VALUES(0, "
"'LIST{-1,0,-2,3,0,0,32767,249}', 'SET{-1,0,-2,3}', "
"'MULTISET{-1,0,0,-2,3,0}') ";
char *data;
EXEC SQL END DECLARE SECTION;

if (argc > 1)
dbase = argv[1];
EXEC SQL WHENEVER ERROR STOP;
printf("Connect to %s\n", dbase);
EXEC SQL CONNECT TO :dbase;

EXEC SQL CREATE TEMP TABLE t_collections
(
seq serial not null,
l1 list (integer not null),
s1 set (integer not null),
m1 multiset(integer not null)
);

EXEC SQL EXECUTE IMMEDIATE :stmt1;
EXEC SQL EXECUTE IMMEDIATE :stmt1;
EXEC SQL EXECUTE IMMEDIATE :stmt1;

process_stmt("SELECT seq, l1, s1, m1 FROM t_collections");

puts("OK");
return 0;
}

Executing User-Defined Routines in Informix Dynamic Server

In Informix Dynamic Server with Universal Data Option, a user-defined routine is a collection of statements that performs a user-defined task. A procedure is a routine that can accept arguments but does not return any values. A function is a routine that can accept arguments and returns values. The following table summarizes the SQL statements for user-defined routines.

Task SQL Statement for User Defined Routines
Procedure Function
Create and register a routine CREATE PROCEDURE CREATE FUNCTION
Execute a routine EXECUTE PROCEDURE EXECUTE FUNCTION
Drop a routine DROP PROCEDURE DROP FUNCTION

For more information on these statements, see the Informix Guide to SQL: Syntax.

Informix Dynamic Server supports several languages for user-defined routines:

A user-defined routine can use input parameters for its arguments. However, it cannot use an input parameter for its routine name. For more information, see Executing Statements with Input Parameters.

A User-Defined Procedure

If you know the name of the user-defined procedure (external or SPL) at compile time, execute the user-defined procedure with the EXECUTE PROCEDURE statement. The following EXECUTE PROCEDURE statement executes a user-defined procedure called revise_stats():

For more information on the ../sqls/intro.htmltax of the EXECUTE PROCEDURE statement, see the Informix Guide to SQL: Syntax.

If you do not know the name of the user-defined procedure until runtime, you must dynamically execute the procedure. To dynamically execute a user-defined procedure, you can use:

A User-Defined Function

If you know the name of the user-defined function at compile time, execute the user-defined function (external or SPL) with the EXECUTE FUNCTION statement. In the INTO clause of EXECUTE FUNCTION, you list the host variables that hold the return value(s). The following EXECUTE FUNCTION statement executes a user-defined function called items_pct() (which Figure 16-3 on page 16-24 defines):

For more information on the ../sqls/intro.htmltax of the EXECUTE FUNCTION statement, see the Informix Guide to SQL: Syntax.

If you do not know the name of the user-defined function until runtime, you must dynamically execute the function. Dynamic execution of a user-defined function is a similar dynamic execution of a SELECT statement (page 15-26). Both the SELECT and the user-defined function return values to the ESQL/C program.

Execute a user-defined function with the EXECUTE FUNCTION statement. You can execute an EXECUTE FUNCTION statement in the following two ways:

A Noncursor Function

You can also use the PREPARE and EXECUTE statement to execute a noncursor user-defined function. A noncursor function returns only one row of values.

Your ESQL/C program must take the following actions:

  1. Declare host variables to receive the values that the database server returns.
  2. For a prepared EXECUTE FUNCTION statement, these values are the return values of the user-defined function.

  3. Assemble and prepare the statement.
  4. A prepared EXECUTE FUNCTION statement can contain input parameters as function arguments. For more information, see Assembling and Preparing the SQL Statement.

  5. Execute the prepared user-defined function with the EXECUTE...INTO statement, with the host variables after the INTO keyword.
  6. If the EXECUTE FUNCTION contains input parameters, include the USING clause of EXECUTE.

    Important: To execute a noncursor function, EXECUTE...INTO is usually more efficient than the DECLARE, OPEN, and FETCH statements. However, you often do not know the number of returned rows. When you do not use a cursor to execute a cursor function that returns multiple rows, ESQL/C generates a runtime error. Therefore, it is a good practice to always associate a user-defined function with a cursor.

For more information, see A Cursor Function.

Most external functions can return only one row of data and only a single value. For example, the following code fragment executes an external function called stnd_dev():

To return more than one value, the external function must return a complex data type, such as a collection or a row type. (For more information on these data types, see Chapter 9, Working with Complex Data Types of the Universal Data Option.)

An SPL function can return one or more values. If the RETURN statement of the SPL function does not contain the WITH RESUME keywords, then the function returns only one row. To execute the SPL function dynamically, prepare the EXECUTE FUNCTION and execute it with the EXECUTE...INTO statement.

For more information, see Using PREPARE and EXECUTE INTO.

A Cursor Function

To execute an EXECUTE FUNCTION statement whose user-defined function returns more than one row, you must declare a function cursor. The function cursor enables the ESQL/C application to handle the multiple rows that a user-defined function returns. Your ESQL/C program must take the following actions to use a function cursor:

  1. Declare host variables to receive the values that the user-defined function returns.
  2. Assemble and prepare the statement.
  3. A prepared EXECUTE FUNCTION statement can contain input parameters as function arguments. For more information, see Assembling and Preparing the SQL Statement.

  4. Declare the function cursor.
  5. The DECLARE statement associates the prepared EXECUTE FUNCTION statement with the function cursor.

  6. Execute the user-defined function.
  7. The OPEN statement sends any input parameters that its USING clause specifies to the database server and tells the database server to execute the EXECUTE FUNCTION statement.

  8. Retrieve the rows of values from the function cursor.
  9. The FETCH statement retrieves one row of values that the user-defined function returns.

Only an external function that is an iterator function can return more than one row of data. For information on how to write an iterator function, see the DataBlade API Programmer's Manual.

If the RETURN statement of the SPL function contains the WITH RESUME keywords, then the function can return more than one row. You must associate such an SPL function with a function cursor. To execute the SPL function dynamically, associate the EXECUTE FUNCTION statement with a cursor, use the OPEN statement to execute the function, and use the FETCH...INTO statement to retrieve the rows from the cursor into host variables.

For more information on cursors and cursor-management statements, see Using a Database Cursor.

Executing Statements with Input Parameters

An input parameter is a placeholder in an SQL statement that indicates that the actual value is provided at runtime. You cannot list a host-variable name in the text of a dynamic SQL statement because the database server knows nothing about variables declared in the application. Instead, you can indicate an input parameter with a question mark (?), which serves as a placeholder, anywhere within a statement where an expression is valid. You cannot use an input parameter to represent an identifier such as a database name, a table name, or a column name.

An SQL statement that contains input parameters is called a parameterized statement. For a parameterized SQL statement, your program must provide the following information to the database server about its input parameters:

The statement that you use to provide an input parameter with a value at runtime depends on the type of SQL statement that you execute, as follows:

When the statement executes, you can list host variables or literal values to substitute for each input parameter in the USING clause. The values must be compatible in number and data type with the associated input parameters. A host variable must also be large enough to hold the data.

Important: To use host variables with the USING clause, you must know the number of parameters in the SQL statement and their data types. If you do not know the number and data types of the input parameters at runtime, you must use a dynamic-management structure with the USING clause. For more information, see Determining Unknown Input Parameters.

The following sections show how to use host variables with the OPEN...USING and the EXECUTE...USING statements.

Using an EXECUTE USING Statement

You can execute a parameterized non-SELECT statement (a non-SELECT that contains input parameters) with the EXECUTE...USING statement. The following statements are parameterized non-SELECT statements:

For example, the following UPDATE statement requires two parameters in its WHERE clause:

The USING clause lists the names of the host variables that hold the parameter data. If the input parameter values are stored in hvar1 and hvar2, your program can execute this UPDATE with the following statement:

The following steps describe how to handle a parameterized UPDATE or DELETE statement when the type and number of parameters are known at compile time:

  1. Declare a host variable for each input parameter that is in the prepared statement.
  2. Assemble the character string for the statement, with a question mark (?) placeholder for each input parameter. Once you have assembled the string, prepare it. For more information on these steps, see Assembling and Preparing the SQL Statement.
  3. Assign a value to the host variable that is associated with each input parameter. (The application might obtain these values interactively.)
  4. Execute the UPDATE or DELETE statement with the EXECUTE...USING statement. You must list the host variables that contain the input parameter values in the USING clause.
  5. Optionally, use the FREE statement to release the resources that were allocated with the prepared statement.
  6. Important: If you do not know the number and data types of the input parameters in the prepared statement at compile time, do not use host variables with the USING clause. Instead, use a dynamic-management structure to specify input parameter values.

For more information on determining the number and types of input parameters, see Determining Unknown Input Parameters.

For more information on the USING clause, see the entry for EXECUTE in the Informix Guide to SQL: Syntax.

Using an OPEN USING Statement

You can execute the following statements with the OPEN...USING statement:

For example, the following SELECT statement is a parameterized SELECT that requires two parameters in its WHERE clause:

If the cust_num and ord_date host variables contain the input parameter values, the following OPEN statement executes the SELECT with these input parameters:

Use the USING host_var clause only when you know, at compile time, the type and number of input parameters in the WHERE clause of the SELECT statement. For more information on the USING clause, see the entry for OPEN in the Informix Guide to SQL: Syntax.

The demo2.ec sample program shows how to handle a dynamic SELECT statement that has input parameters in its WHERE clause. The demo2.ec program uses a host variable to hold the value of the input parameter for a SELECT statement. It also uses host variables to hold the column values that are returned from the database.

Lines 9 and 10

These lines declare a host variable (fname) for the parameter in the WHERE clause of the SELECT statement and declare host variables (fname and lname) for values that the SELECT statement returns.

Lines 14 to 21

These lines assemble the character string for the statement (in demoquery) and prepare it as the demo2id statement identifier. The question mark (?) indicates the input parameter in the WHERE clause. For more information on these steps, see Assembling and Preparing the SQL Statement.

Line 22

This line declares the demo2cursor cursor for the prepared statement identifier, demo2id. All nonsingleton SELECT statements must have a declared cursor.

Lines 23 to 27

The queryvalue host variable is the input parameter for the SELECT statement. It contains the value "C". In an interactive application, this value probably would be obtained from the user.

Line 28

The database server executes the SELECT statement when it opens the demo2cursor cursor. Because the WHERE clause of the SELECT statement contains input parameters (lines 20 and 21), the OPEN statement includes the USING clause to specify the input parameter value in queryvalue.

Lines 29 to 38

This for loop executes for each row fetched from the database. The FETCH statement (line 31) includes the INTO clause to specify the fname and lname host variables for the column values. After this FETCH statement executes, the column values are stored in these host variables.

Lines 39 and 40

Outside the for loop, the program tests the SQLSTATE variable again so it can notify the user in the event of successful execution, a runtime error, or a warning (class code not equal to "02").

Line 41

After all the rows are fetched, the CLOSE statement closes the demo2cursor cursor.

Lines 42 and 43

These FREE statements release the resources allocated for the prepared statement (line 42) and the database cursor (line 43). Once a cursor or prepared statement has been freed, it cannot be used again in the program.


INFORMIX-ESQL/C Programmer's Manual, Version 9.21
Copyright © 1999, Informix Software, Inc. All rights reserved