Informix Guide to SQL: Tutorial

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

Home Contents Index Master Index New Book

SQL in Programs

You can write a program in any of several languages and mix SQL statements in 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.

SQL in SQL APIs

ESQL products are Informix SQL APIs. Informix produces SQL APIs for the following programming languages:

C
COBOL

All SQL API products work in a similar way, as Figure 5-1 shows. 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 5-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, such as DCE (Distributed Computing Environment 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.

Static Embedding

You can introduce SQL statements into a program in two ways. 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.

Dynamic Statements

Some applications require the ability to compose SQL statements 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.

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 being a "guest" in the program.

The following example is a DELETE statement as it might appear when embedded in a COBOL source program:

EXEC SQL

	DELETE FROM items

		WHERE order_num = :o-num

END-EXEC.

The first and last lines mark off embedded SQL from the normal COBOL statements. Between them, you see an ordinary DELETE statement, as described in Chapter 4, "Modifying Data." When this part of the COBOL 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 :o-num, which is the name of a host variable.

Each SQL API product provides a means of delimiting the names of host variables when they appear in the context of an SQL statement. In COBOL, host-variable names are designated with an initial colon. The example statement asks the database server to delete rows in which the order number equals the current contents of the host variable named :o-num. This numeric variable has been declared and assigned a value earlier in the program.

The same DELETE statement embedded in an INFORMIX-ESQL/C program looks like the following example:

EXEC SQL DELETE FROM items

	WHERE order_num = :onum;

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

These differences of syntax are trivial; the essential points in all languages (an SQL API or SPL) are described in the following list:

You can embed SQL statements in a source program as if they were executable statements of the host language.
You can 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.