Program Variables and Host Variables

Application programs can use program variables within SQL statements. In SPL, you put the program variable in the SQL statement as syntax allows. For example, a DELETE statement can use a program variable in its WHERE clause.

The following code example shows a program variable in SPL:

CREATE PROCEDURE delete_item (drop_number INT)

·
·
·

DELETE FROM items WHERE order_num = drop_number

·
·
·

In applications that use embedded SQL statements, the SQL statements can refer to the contents of program variables. A program variable that is named in an embedded SQL statement is called a host variable because the SQL statement is thought of as a guest in the program.

The following example shows a DELETE statement as it might appear when it is embedded in an IBM Informix ESQL/C source program:

EXEC SQL delete FROM items
   WHERE order_num = :onum;

In this program, you see an ordinary DELETE statement, as Modifying Data describes. When the ESQL/C program is executed, a row of the items table is deleted; multiple rows can also be deleted.

The statement contains one new feature. It compares the order_num column to an item written as :onum, which is the name of a host variable.

An SQL API product provides a way to delimit the names of host variables when they appear in the context of an SQL statement. In ESQL/C, a host variable can be introduced with either a dollar sign ($) or a colon (:). The colon is the ANSI-compatible format. The example statement asks the database server to delete rows in which the order number equals the current contents of the host variable named :onum. This numeric variable was declared and assigned a value earlier in the program.

In IBM Informix ESQL/C, an SQL statement can be introduced with either a leading dollar sign ($) or the words EXEC SQL.

The differences of syntax as illustrated in the preceding examples are trivial; the essential point is that the SQL API and SPL languages let you perform the following tasks:

• Embed SQL statements in a source program as if they were executable statements of the host language.
• Use program variables in SQL expressions the way literal values are used.

If you have programming experience, you can immediately see the possibilities. In the example, the order number to be deleted is passed in the variable onum. That value comes from any source that a program can use. It can be read from a file, the program can prompt a user to enter it, or it can be read from the database. The DELETE statement itself can be part of a subroutine (in which case onum can be a parameter of the subroutine); the subroutine can be called once or repetitively.

In short, when you embed SQL statements in a program, you can apply to them all the power of the host language. You can hide the SQL statements under many interfaces, and you can embellish the SQL functions in many ways.