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.
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:
sprintf(stmt_buf, "DELETE FROM %s WHERE %s",
tabname, search_condition);
3. Prepare the entire statement. The following PREPARE statement operates on the string in stmt_buf and creates a statement identifier called d_id:
EXEC SQL prepare d_id from :stmt_buf;
4. Execute the statement. The following EXECUTE statement executes the DELETE:
EXEC SQL execute d_id;
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:
EXEC SQL free d_id;
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.
For a prepared SELECT statement, these values are the select-list columns.
2. Assemble and prepare the statement.
3. Execute the prepared selection with the EXECUTE...INTO statement, with the host variables after the INTO keyword.
If the SELECT statement contains input parameters, include the USING clause of EXECUTE.
With the INTO clause of the EXECUTE statement, you can still use the following features:
Use the INDICATOR keyword followed by the name of the indicator host variable, as follows:
EXEC SQL prepare sel1 from
'select fname, lname from customer where customer_num = 123';
EXEC SQL execute sel1 into :fname INDICATOR :fname_ind,
:lname INDICATOR :lname_ind;
Include the USING clause of EXECUTE, as follows:
EXEC SQL prepare sel2 from
'select fname, lname from customer where customer_num = ?';
EXEC SQL execute sel2 into :fname, :lname using :cust_num;
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 shows). Because this function returns a single decimal value, the EXECUTE...INTO statement can execute it.
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.
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.
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.
2. Assemble and prepare the statement.
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".
3. Declare the select cursor.
The DECLARE statement associates the prepared SELECT statement with the select cursor.
4. Execute the query.
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.
5. Retrieve the rows of values from the select cursor.
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".
Executing User-Defined Routines in Universal Server
In Universal Server, 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.
For more information on these statements, see the Informix Guide to SQL: Syntax.
Universal Server supports several languages for user-defined routines:
An SPL function can return one or more values while an SPL procedure does not return any values. For information on how to write a stored routine, see the Informix Guide to SQL: Tutorial.
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 syntax 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 defines):
For more information on the syntax 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-24). 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:
This type of user-defined function is often called a cursor function. A cursor function that is written in SPL (an SPL function) has the WITH RESUME clause in its RETURN statement. A cursor function that is written in an external language such as C is an iterator function.
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.
For a prepared EXECUTE FUNCTION statement, these values are the return values of the user-defined function.
2. Assemble and prepare the statement.
3. Execute the prepared user-defined function with the EXECUTE...INTO statement, with the host variables after the INTO keyword.
If the EXECUTE FUNCTION contains input parameters, include the USING clause of EXECUTE.
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 INFORMIX-Universal Server Complex Data Types.") 
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. Declare the function cursor.
The DECLARE statement associates the prepared EXECUTE FUNCTION statement with the function cursor.
4. Execute the user-defined function.
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.
5. Retrieve the rows of values from the function cursor.
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 will be 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 first input parameter is defined for the value of the customer_num column and the second for the value of the order_date column.
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.
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.
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.
1 #include <stdio.h>
2 EXEC SQL define FNAME_LEN 15;
3 EXEC SQL define LNAME_LEN 15;
4 main()
5 {
6 EXEC SQL BEGIN DECLARE SECTION;
7 char demoquery[80];
8 char queryvalue[2];
9 char fname[ FNAME_LEN + 1 ];
10 char lname[ LNAME_LEN + 1 ];
11 EXEC SQL END DECLARE SECTION;
12 printf("DEMO2 Sample ESQL program running.\n\n");
13 EXEC SQL connect to'stores7';
14 /* The next three lines have hard-wired the query. This
15 * information could have been entered from the terminal
16 * and placed into the demoquery string
17 */
18 sprintf(demoquery, "%s %s",
19 "select fname, lname from customer",
20 "where lname > ? ");
21 EXEC SQL prepare demo2id from :demoquery;
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".
22 EXEC SQL declare demo2cursor cursor for demo2id;
23 /* The next line has hard-wired the value for the parameter.
24 * This information could also have been entered from the terminal
25 * and placed into the queryvalue string.
26 */
27 sprintf(queryvalue, "C");
28 EXEC SQL open demo2cursor using :queryvalue;
29 for (;;)
30 {
31 EXEC SQL fetch demo2cursor into :fname, :lname;
32 if (strncmp(SQLSTATE, "00", 2) != 0)
33 break;
34 /* Print out the returned values */
35 printf("Column: fname\tValue: %s\n", fname);
36 printf("Column: lname\tValue: %s\n", lname);
37 printf("\n");
38 }
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.
39 if (strncmp(SQLSTATE, "02", 2) != 0)
40 printf("SQLSTATE after fetch is %s\n", SQLSTATE);
41 EXEC SQL close demo2cursor;
42 EXEC SQL free demo2cursor;
43 EXEC SQL free demo2id;
44 EXEC SQL disconnect current;
45 printf("\nProgram Over.\n");
46 }
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.
|
