|
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.
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:
The PREPARE and EXECUTE statements allow you to separate the execution of a non-SELECT statement into two steps:
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:
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:
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.
You can execute a SELECT statement in the following two ways:
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:
With the INTO clause of the EXECUTE statement, you can still use the following features:
Warning: When you use the EXECUTE INTO statement, make sure that the SELECT statement is a singleton SELECT. If the SELECT returns more than one row, you receive a runtime error. An error is also generated if you attempt to execute a prepared statement that was declared (with DECLARE).
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.
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.
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:
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
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.
For more information on these statements, see the Informix Guide to SQL: Syntax.
Informix Dynamic Server supports several languages for user-defined routines:
Tip: In earlier versions of Informix products, the term "stored procedure" was used for both SPL procedures and SPL functions. That is, a stored procedure can include the RETURN statement to return values. For backward compatibility with earlier products, Informix Dynamic Server continues to support the execution of SPL functions with the EXECUTE PROCEDURE statement. However, for new SPL routines, Informix recommends that you use EXECUTE PROCEDURE only for procedures and EXECUTE FUNCTION only for functions.
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.
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:
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:
Tip: If you do not know the data type of the return value, you must use a dynamic-management structure to hold the value. For more information, see Determining Return Values Dynamically.
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:
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 FunctionTo 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:
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.
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.
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:
Tip: You cannot use an input parameter as the procedure name for a user-defined procedure.
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:
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.
You can execute the following statements with the OPEN...USING statement:
Tip: You cannot use an input parameter as the function name for a user-defined function.
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.
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.
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.
This line declares the demo2cursor cursor for the prepared statement identifier, demo2id. All nonsingleton SELECT statements must have a declared cursor.
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.
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.
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.
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").
After all the rows are fetched, the CLOSE statement closes the demo2cursor cursor.
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.