Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 5: Programming with SQL

Home Contents Index Master Index New Book

Dynamic SQL

Although static SQL is extremely useful, it requires that you know the exact content of every SQL statement at the time you write the program. For example, you must state exactly which columns are tested in any WHERE clause and exactly which columns are named in any select list.

No problem exists when you write a program to perform a well-defined task. But the database tasks of some programs cannot be perfectly defined in advance. In particular, a program that must respond to an interactive user might need the ability to compose SQL statements in response to what the user enters.

Dynamic SQL allows a program to form an SQL statement during execution, so that the contents of the statement can be determined by user input. This action is performed in the following steps:

1. The program assembles the text of an SQL statement as a character string, which is stored in a program variable.

2. It executes a PREPARE statement, which asks the database server to examine the statement text and prepare it for execution.

3. It uses the EXECUTE statement to execute the prepared statement.

In this way, a program can construct and then use any SQL statement, based on user input of any kind. For example, it can read a file of SQL statements and prepare and execute each one.

DB-Access, the utility that you use to explore SQL interactively, is an INFORMIX-ESQL/C program that constructs, prepares, and executes SQL statements dynamically. For example, it lets users specify the columns of a table using simple, interactive menus. When the user is finished, DB-Access builds the necessary CREATE TABLE or ALTER TABLE statement dynamically and prepares and executes it.

Preparing a Statement

In form, a dynamic SQL statement is like any other SQL statement that is written into a program, except that it cannot contain the names of any host variables.

This situation leads to two restrictions. First, if it is a SELECT statement, it cannot include the INTO clause. The INTO clause names host variables into which column data is placed, and host variables are not allowed in a dynamic statement. Second, wherever the name of a host variable normally appears in an expression, a question mark (?) is written as a placeholder.

You can prepare a statement in this form for execution with the PREPARE statement. The following example is written in INFORMIX-ESQL/C:

EXEC SQL prepare query_2 from

	 	'select * from orders

		 	where customer_num = ? and

			 	order_date > ?';

The two question marks in this example indicate that when the statement is executed, the values of host variables are used at those two points.

You can prepare almost any SQL statement dynamically. The only ones that cannot be prepared are the ones directly concerned with dynamic SQL and cursor management, such as the PREPARE and OPEN statements. After you prepare an UPDATE or DELETE statement, it is a good idea to test the fifth field of SQLWARN to see if you used a WHERE clause (see "The SQLWARN Array").

The result of preparing a statement is a data structure that represents the statement. This data structure is not the same as the string of characters that produced it. In the PREPARE statement, you give a name to the data structure; it is query_2 in the preceding example. This name is used to execute the prepared SQL statement.

The PREPARE statement does not limit the character string to one statement. It can contain multiple SQL statements, separated by semicolons. The following example shows a fairly complex example in INFORMIX-ESQL/COBOL:

	MOVE 		'BEGIN WORK;

			UPDATE account

				SET balance = balance + ?

					WHERE acct_number = ?;

			UPDATE teller

				SET balance = balance + ?

					WHERE teller_number = ?;

			UPDATE branch

				SET balance = balance + ?

					WHERE branch_number = ?;

			INSERT INTO history VALUES(timestamp, values);'

			

	TO BIG-QUERY.



EXEC SQL

 	PREPARE BIG-Q FROM :BIG-QUERY

END-EXEC.

When this list of statements is executed, host variables must provide values for six place-holding question marks. Although it is more complicated to set up a multistatement list, the performance is often better because fewer exchanges take place between the program and the database server.

Executing Prepared SQL

Once a statement is prepared, it can be executed multiple times. Statements other than SELECT statements, and SELECT statements that return only a single row, are executed with the EXECUTE statement.

The following INFORMIX-ESQL/C code prepares and executes a multistatement update of a bank account:

EXEC SQL BEGIN DECLARE SECTION;

char bigquery[270] = "begin work;";

EXEC SQL END DECLARE SECTION;

stcat ("update account set balance = balance + ? where ", bigquery);

stcat ("acct_number = ?;', bigquery);

stcat ("update teller set balance = balance + ? where ", bigquery);

stcat ("teller_number = ?;', bigquery);

stcat ("update branch set balance = balance + ? where ", bigquery);

stcat ("branch_number = ?;', bigquery);

stcat ("insert into history values(timestamp, values);", bigquery);



EXEC SQL prepare bigq from :bigquery;



EXEC SQL execute bigq using :delta, :acct_number, :delta,

	:teller_number, :delta, :branch_number;



EXEC SQL commit work;

The USING clause of the EXECUTE statement supplies a list of host variables whose values are to take the place of the question marks in the prepared statement. If a SELECT statement (or EXECUTE PROCEDURE) returns only one row, you can use the INTO clause of EXECUTE to specify the host variables that receive the values.

EXEC SQL execute immediate 'drop index my_temp_index';

This capability makes it easy to write simple SQL operations. However, because no USING clause is allowed, the EXECUTE IMMEDIATE statement cannot be used for SELECT statements.

Informix Guide to SQL: TutorialChapter 5: Programming with SQL Home Contents Index Master Index New Book

Dynamic SQL

Preparing a Statement

Executing Prepared SQL

Dynamic Host Variables

Freeing Prepared Statements

Quick Execution

Informix Guide to SQL: Tutorial
Chapter 5: Programming with SQL

Home Contents Index Master Index New Book