Informix Guide to SQL: Tutorial Programming with SQL

You can write a program in any of several languages and mix SQL statements among the other statements of the program, just as if they were ordinary statements of that programming language. These SQL statements are embedded in the program, and the program contains embedded SQL, which Informix often abbreviates as ESQL.

ESQL products are Informix SQL APIs (application programming interfaces). Informix produces an SQL API for the C programming language.

Figure 7-1 shows how an SQL API product works. You write a source program in which you treat SQL statements as executable code. Your source program is processed by an embedded SQL preprocessor, a program that locates the embedded SQL statements and converts them into a series of procedure calls and special data structures.

Figure 7-1 Overview of Processing a Program with Embedded SQL Statements

The converted source program then passes through the programming language compiler. The compiler output becomes an executable program after it is linked with a static or dynamic library of SQL API procedures. When the program runs, the SQL API library procedures are called; they set up communication with the database server to carry out the SQL operations.

If you link your executable program to a threading library package, you can develop ESQL/C multithreaded applications. A multithreaded application can have many threads of control. It separates a process into multiple execution threads, each of which runs independently. The major advantage of a multithreaded ESQL/C application is that each thread can have many active connections to a database server simultaneously. While a nonthreaded ESQL/C application can establish many connections to one or more databases, it can have only one connection active at a time. A multithreaded ESQL/C application can have one active connection per thread and many threads per application.

For more information on multithreaded applications, see the Informix ESQL/C Programmer's Manual.

Whereas an SQL API product allows you to embed SQL in the host language, some languages include SQL as a natural part of their statement set. Informix Stored Procedure Language (SPL) uses SQL as a natural part of its statement set. You use an SQL API product to write application programs. You use SPL to write routines that are stored with a database and called from an application program.

You can introduce SQL statements into a program through static embedding or dynamic statements. The simpler and more common way is by static embedding, which means that the SQL statements are written as part of the code. The statements are static because they are a fixed part of the source text. For more information on static embedding, see Retrieving Single Rows and Retrieving Multiple Rows.

Some applications require the ability to compose SQL statements dynamically, in response to user input. For example, a program might have to select different columns or apply different criteria to rows, depending on what the user wants.

With dynamic SQL, the program composes an SQL statement as a string of characters in memory and passes it to the database server to be executed. Dynamic statements are not part of the code; they are constructed in memory during execution. For more information, see Dynamic SQL.

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 Informix ESQL/C source program:

EXEC SQL delete FROM items

	WHERE order_num = :onum;

In this program, you see an ordinary DELETE statement, as Chapter 6 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 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 all the power of the host language to them. You can hide the SQL statements under many interfaces, and you can embellish the SQL functions in many ways.