Processing Programs






Processing Programs

In the previous section, we gave a number of examples of programs with embedded SQL, but how can we run these programs? Programs written in a language such as C, Java, COBOL, or Pascal must be processed by a compiler and a link/editor before they can be executed. The compiler generates an object module that will be converted to a load module by the link/editor. A load module is a program that is ready to be loaded into the internal memory of the computer for processing. Compilers and link/editors are not part of a database server but are separate programs or utilities.

To make things easy and clear, we assume in the rest of this section that we are working with C as the host language. For other host languages, the same comments and rules usually apply.

In the previous section, we gave a few examples of programs with embedded SQL. Perhaps you have already asked yourself, what does the C compiler do with embedded SQL? The answer is clear: It gives error messages because SQL statements are not a part of the C language. We have to do something with the program before the compiler can process it. We need to precompile the program.

The precompiler translates a program written with C and SQL statements into a program that contains only pure C statements but still guarantees that the desired SQL statements are processed in some way. Most vendors of SQL products supply a number of precompilers (also called preprocessors) to precompile programs. A precompiler is a stand-alone program (a utility program) that is supplied with the database server. A separate precompiler is generally available for each host language. Figure illustrates the process of precompiling, compiling, and link/editing.

1. Preparation of programs with embedded SQL statements


What is the job of a precompiler? We give a general outline of a precompiler's tasks by listing the steps executed before a SQL statement, included in a program, can be processed. These are the steps:

1.
Identify the SQL statements in the program.

2.
Translate the SQL statements into C statements.

3.
Check the syntactical correctness of the SQL statements.

4.
Check that tables and columns mentioned in the statements actually exist.

5.
Check that the privileges (granted with GRANT statements) required to execute the SQL statements are available.

6.
Determine the processing strategy.

7.
Execute the SQL statements.

The steps that a precompiler executes depend on the product. Each precompiler executes steps 1 and 2. Identifying SQL statements has been made easier by demanding that each SQL statement be preceded by the words EXEC SQL. The differences between the products begin at step 2. The C code generated by the DB2 precompiler is different from that generated by the Oracle precompiler. Is this important? No. The code that is generated is not intended to be modified by human hand, just as code generated by a compiler should not be modified.

As an illustration, we show you the C code that the Oracle precompiler (Version 1.2.14) generates for the statement: DELETE FROM PENALTIES.

/* SQL stmt #4
   EXEC SQL DELETE FROM PENALTIES;
*/
{     /* beginning of SQL code gen stmt */
sqlsca(&sqlca);
if ( !sqlusi[0] )
  {  /* OPEN SCOPE */
sq001.sq001T[0] = (unsigned short)10;
SQLTM[0] = (int)4;
sqlbs2(&sq001.sq001N, sq001.sq001V,
  sq001.sq001L, sq001.sq001T, sq001.sq001I,
  &SQLTM[0], &sqlusi[0]);
  }  /* CLOSE SCOPE */
sqlsch(&sqlusi[0]);
sqlscc(&sqlcun[0]);
sqltfl(&SQLTM[0], &SQLBT0);
if ( !SQLTM[0] )
  {  /* OPEN SCOPE */
SQLTM[0] = (int)16384;
sqlopn(&SQLTM[0], &SQLBT3, &sqlvsn);
SQLTM[0] = (int)19;
sqlosq(sq002, &SQLTM[0]);
  }  /* CLOSE SCOPE */
SQLTM[0] = (int)1;
sqlexe(&SQLTM[0]);
sqlwnr();
}   /* ending of SQL code gen stmt */

Not all precompilers check the syntactical correctness of SQL statements (step 3). Some just assume that what follows EXEC SQL is correct SQL. This means that you can have an error message during the execution of the program in step 7.

In explaining step 4 onward, we should make a distinction between the products that compile the SQL statements and those that interpret the statements at run time (that is, during the execution of the program). Examples of the first group are DB2 and Ingres. Examples of interpreters are Oracle and Informix.

In an interpreter environment, steps 4, 5, and 6 are not executed by the precompiler. Those steps are executed at runtime during step 7. At runtime, SQL determines whether the tables that are used actually exist. This also means that the precompiler can run without the database server being started.

The SQL statements in a compiler environment are placed in a separate file by the precompiler. In some products, this file is called the Database Request Module (DBRM). In other words, the precompiler has two output files: the adapted C program from which the SQL statements have been removed and the DBRM that contains the SQL statements. The adapted program can be compiled, but the program is not yet ready to be executed. First, the DBRM must be processed by a specific utility program, called the binder.

The binder is a program that is supplied by the vendor of the SQL product and can run only when the database server has been started. In fact, the binder executes steps 4, 5, and 6 for each statement in the DBRM. It checks whether the tables and columns actually exist, checks the privileges, and determines the processing strategy to be used for the SQL statement. (In Chapter 20, "Using Indexes," we discussed how to determine the processing strategy.) The result is a set of compiled SQL statements that can be processed. The binder stores them in a special catalog table. To summarize, the following activities are executed in a compiler environment before a program can run: precompiling, binding, compiling, and link/editing.

Step 7, executing an SQL statement, takes place when the program is run. In a compiler environment, this means that when an SQL statement is to be processed, the compiled SQL statement is retrieved from the catalog so that it can be executed. In an interpreter environment, SQL must first check whether the tables and columns exist, and whether the correct privileges exist. And it must also determine the processing strategy.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows