CHAPTER 2: General Topics for the Precompiler

This chapter covers on topics and methods pertinent to the use of the SQL Precompiler. These include:

• accessing databases and tables
• multiple record operations
• transactions
• using program variables
• the SQL Communications Area (SQLCA)
• error handling
• restrictions
• invoking the Precompiler

These discussions apply only to the SQL Precompiler and to the use of Empress SQL commands embedded in C programs.

The SQL Precompiler allows you to perform operations on tables in Empress databases from within your C program. Tables to be accessed are assumed to be in the default database. Unless otherwise specified, operations will take place on the default database.

A table in a database other than the default database can be accessed by preceding the table name in your SQL Precompiler command with a database specification. A database specification is a database name followed by a colon.

A database is made the default by issuing the DATABASE IS command. Until this command is issued, no database is considered the default and a database specification must precede each and every table name. A different database can be made the default using the DATABASE IS command at any time.

For every executed command, the Precompiler verifies that all of the required tables have been opened and, if so, that they have been opened in the correct mode (read or update). If the required tables are not open, they will be opened in a mode corresponding to the requirements of your SQL command.

Tables can be opened with the OPEN_TABLE command. Opened tables will remain open until you explicitly close them or execute the EXIT command.

Tables can be closed with the CLOSE_TABLE command. If you don't specify a list of tables in the CLOSE_TABLE command, all opened tables will be closed.

The SQL Precompiler allows you to store, retrieve or delete data from tables in Empress databases from within your C programs. Supported commands include: UPDATE, DELETE and SELECT INTO. The UPDATE and DELETE commands will normally act on all records that satisfy the conditions specified in their WHERE clause. SELECT INTO will only act on the first record that meets the conditions in its WHERE clause.

The SQL Precompiler will also allow you to selectively manipulate records within a given context. Records can be updated or deleted selectively and the data contained in multiple records can be retrieved for processing by your program.

This is accomplished through the use of a cursor. The cursor allows you to control the rows that are processed by defining a context of rows with a SELECT statement and associating it with that cursor. The cursor points to a single record within its context. By "moving" the cursor, rows in the context can be made current and subsequently processed.

Before a cursor can be used, preparations must be made for its use. These preparations include declaring and opening the cursor.

A cursor is declared with the DECLARE CURSOR command. Declaring a cursor involves assigning the cursor a name and defining the context associated with that cursor through a SELECT statement. The cursor declaration must precede any other cursor-related command.

By default, a cursor is declared for read operations only. If you wish to use the cursor for update operations as well, the FOR UPDATE or FOR DEFERRED option must be specified in the declaration command. A given cursor name may be declared only once within a program, although more than one cursor may be declared. As well, the same context can be defined for more than one cursor name.

Once a cursor has been declared, it must be opened before it can be used. A cursor is opened with the OPEN CURSOR command.

Opening a cursor initializes the selection process defined by the SELECT statement in the cursor declaration. A given cursor may be declared only once in a program, but it may be opened and closed many times. After opening the cursor, Empress will be ready to make the first row in the selected context the current row. Merely opening the cursor does not make any row the current row.

To process the next available row of values in the defined context from within your program, the desired attribute values must be read into appropriate program variables. This is accomplished with the FETCH command. FETCH makes the next available row in the context the current row and attempts to read the named attribute values into the corresponding named program variables.

If the next available row in the context is locked (SQLCODE = 1), FETCH will make it the current row but will not read the appropriate values into the named variables. You can attempt to re-select these values using the FETCH_AGAIN command. FETCH_AGAIN operates identically to FETCH except that it attempts to read the current row, rather than the next available row. To read all of the available rows, your program must FETCH the values in a loop until SQLCODE = 100.

The UPDATE and DELETE commands can be used to update or delete the current row in a selected context. In this form of these commands, the WHERE clause is replaced with a WHERE CURRENT OF clause.

This form of the UPDATE and DELETE commands acts on the current record in a selected context. Consequently, the commands will not be executed if a row has not been made current with the FETCH command.

After an UPDATE with a WHERE CURRENT OF clause, the cursor remains pointed to the updated row (the updated row remains the current row). After a DELETE with a WHERE CURRENT OF clause, the cursor will not be pointing to any row. No row will be considered current until after the next FETCH command. After the next FETCH command the next available row will be made the current row.

After a selected context of rows has been processed with the aid of a cursor, the cursor should be closed. A cursor can be closed with the CLOSE CURSOR command. This command will close the named cursor.

Although the cursor has been closed, its declaration is not discarded and can be used to subsequently re-open the named cursor and initialize its associated context. To initialize the selected context, the cursor should be opened using the OPEN CURSOR command as discussed above.

It is possible to declare more than one cursor within a single program. Each cursor can be defined to select different contexts, or the same contexts can be selected but associated with different cursor names.

It is also possible to have more than one cursor open at the same time. In this case however, only the last opened cursor can be referenced by a FETCH command or an UPDATE or DELETE command with a WHERE CURRENT OF clause. References to previously opened cursors will be met with an error message. To reference a previously opened cursor, all cursors opened after it must first be closed with the CLOSE CURSOR command.

A transaction is a series of commands, starting with a START TRANSACTION command and ending with COMMIT TRANSACTION or ROLLBACK TRANSACTION, during which changes to the database are not permanently recorded until the transaction is committed. The effects of changes made while a transaction is in progress may be seen just like those of normal commands, but the option to reverse them exists, if so desired.

In the event of a disk, operating system or network crash, transactions will be resolved (either committed or rolled back) by the warm restart facility, empwarm. This preserves data integrity on all of the databases involved in the transaction. The warm restart facility is described in the Empress: Database Administrator's Guide. A general introduction to transactions can be found in the Empress SQL: User's Guide.

Program variables can be used in SQL Precompiler commands. Variable names in executable commands must be preceded by a colon (:). These variables can be used to store the values obtained by a FETCH or SELECT INTO statement. Variables can also be used to store the name of a database, table, attribute or constant.

Efforts have been made to make the Precompiler work with variables belonging to practically all types implemented by its host languages. It is hoped that minor variations between commercially available compilers will not affect the users of the Precompiler.

For character variables, Empress data is manipulated in external format (i.e., as strings). Character variables allow you to manipulate all of the Empress data types.

In a C program, character variables are character arrays or pointers to a string and are declared appropriately.

For numeric variables, Empress data is manipulated in internal format (i.e., as it is stored in memory). This allows the direct manipulation of SHORTINTEGER, INTEGER, LONGINTEGER, FLOAT and LONGFLOAT data from within your program. In addition, it is possible to manipulate a numeric variable as a character variable in external format.

The usual correspondence (subject to machine architecture) between Empress data types and C variable types is shown in the following table:

Table 2-1: Data Type Correspondence

Before program variables can be used by the Precompiler, they must be declared in a special section in your program. This section must begin with the BEGIN DECLARE SECTION command, and end with the END DECLARE SECTION command. For example:

   EXEC SQL BEGIN DECLARE SECTION;
       int i;
       short j;
       float f;
       char str [10];
   EXEC SQL END DECLARE SECTION;

The declaration section may be located anywhere allowed by the host language syntax. More than one declaration section may be used in a given source file. If more than one declaration section is used, the variable names across declaration sections should be unique. It is recommended that the declaration section be global to the file containing the SQL Precompiler statements.

The Precompiler will recognize all C types, including typedef types (provided that the relevant type has been defined prior to its use). The only restriction applying to embedded variable declarations is that multi-dimensional arrays are not supported.

As an example, the Precompiler will accept the following program fragment:

   EXEC SQL BEGIN DECLARE SECTION
   ...
        typedef struct
             {
                  long length;
                  float x[20];
             } bulk;
        bulk b;
        long a[] = {10L, 15L, 3L, 123L, 1221L};
   ...
   EXEC SQL END DECLARE SECTION
   ...
        EXEC SQL INSERT INTO t (i, raw) VALUES (:a[k], :b);
   ...

It is assumed that table "t" has two attributes called "i" (long) and "raw" (bulk data).

Pointers and dynamic memory can be used freely, provided of course that dynamic objects are allocated correctly. Consider, for instance, the following program fragment:

   EXEC SQL BEGIN DECLARE SECTION
   ...
        typedef struct
             {
                  long length;
                  float x[20];
             } bulk;
        bulk     *a, *b;
   ...
   EXEC SQL END DECLARE SECTION
   ...
        b = (bulk *) malloc (sizeof(bulk));
        a = (bulk *) malloc (sizeof(bulk));
   ...
   EXEC SQL INSERT INTO t (i, raw) VALUES (:a-> length, :b);

There is, however, a restriction applying to the use of pointers: you cannot use pointers for names of databases, tables, or attributes. Given the statement pattern:

   EXEC SQL SELECT ... FROM :table ...;

You should declare table as a character array (char *table is not accepted).

The "address of" (&) operator cannot be used in variable references (it is not likely that programmers will want to store internal variable addresses in database tables).

Environment variables can be used in all statements accepting constants or variables as parameters. The name of an environment variable should be enclosed within double or single quotes and preceded by "$", for example:

   EXEC SQL SELECT amount FROM "$table"
        INTO :x WHERE id = "$name";

Here table and name are environment variables which should hold, at runtime, valid values for a table name and id attribute, respectively. A "$" sign can be quoted at the beginning of a literal string by preceding it by another "$" sign. For example, "$$alpha" means the string "$alpha", with no relation whatsoever to any environment variable.

It is possible to test if an attribute value, obtained through a FETCH or SELECT INTO command and stored into a variable, is NULL. To do this, a control variable must be associated with each attribute to be tested.

Control variables take the following form and will be treated as long internally:

   :variable:control_variable

A single control variable may be used to evaluate multiple variables in separate statements. Within a single statement, however, a unique control variable must be appended to each program variable. If not, the control variable reflects the status of the last manipulated program variable.

When retrieving information with a FETCH or SELECT INTO command, control variables are evaluated as follows:

Control variables can be used to insert a NULL (or to update to a NULL) at runtime. During an INSERT, control variables are evaluated as follows:

Control variables can be used to alter the action caused by a WHERE clause. The control variable will be evaluated at runtime as follows:

Control variables can only be used in a WHERE clause that tests for equality (=) or inequality (!=).

The SQLCA (SQL Communication Area) is a data structure that is common to both the Precompiler and the user. It provides a method by which you can inquire about the exit status of each statement and, if errors have occurred, to receive more detailed information about the error.

All programs must include the following statement before any other SQL statement:

   EXEC SQL INCLUDE SQLCA;

The SQLCA is updated after each Precompiler operation. Its primary purpose is to return the SQLCODE indicator. SQLCODE values have the following meaning:

The SQLCA data structure is arranged as follows:

You can access various error variables by using these variables in your program. The rest of the variables are reserved for SQL compatibility or for future use.

The syntax for declaring the SQLCA in C is:

   struct sqlca{
      char sqlcaid[8];
      long sqlabc;
      long sqlcode;
      struct {
         unsigned short sqlerrml;
         char sqlerrmc[70];
         } sqlerrm;
      char sqlerrp[8];
      long sqlerrd[6];
      struct {
         char sqlwarn0;
         char sqlwarn1;
         char sqlwarn2;
         char sqlwarn3;
         char sqlwarn4;
         char sqlwarn5;
         char sqlwarn6;
         char sqlwarn7;
         } sqlwarn;
      char sqlext[8];
   } sqlca;

The course of action to be taken when an error or exception is detected can be defined with a WHENEVER command.

The WHENEVER command lets you define the action that will be taken when:

• an error is detected.
• a warning is detected.
• a row could not be returned or found.

The WHENEVER command uses the value of SQLCODE, to determine the course of action to follow:

The following is a short list of rules to be followed when programming for the Empress SQL Precompiler:

1. You cannot use C macros in an embedded SQL statement.

2. You must use #include <mscc.h> in all files containing embedded SQL.

3. References to databases and tables within a given program must be consistent. For example, if you open a table using a database specification in the table name then all references to that table in the program should include a database specification.

4. When your program starts, no database is considered the default. You must issue a DATABASE IS command to establish a default database.

5. Variable names across declaration sections should be unique within a given compilation unit (source file).

6. Arrays of type CHAR may not be greater than a single dimension.

7. The statement "EXEC SQL INCLUDE SQLCA" must precede any other SQL statement.

8. The statement "EXEC SQL INIT" must be the first executable SQL statement in any given program. Failure to do so will result in runtime error messages and/or unpredictable results.

9. The statement "EXEC SQL INIT" cannot be called more than once. After each subsequent call, an error will be indicated by a negative value for SQLCODE.

The Empress SQL Precompiler allows you to issue SQL commands from within your C program. This is accomplished through calls to the Empress mx routines. On precompilation, SQL commands embedded in your program are replaced with calls to supporting functions.

The file containing your C program with embedded SQL commands can be precompiled using the command:

   empesql [-ptr] infile.pc [outfile.c]

where:

The following sequence of commands can be used to precompile, compile, and link a C program containing embedded SQL statements ("out" is the name of the executable file):

   empesql infile.pc outfile.c
   empecc -o out outfile.c

Currently you have to invoke the Precompiler separately for each input file. Any set of output files produced by the C Precompiler can be compiled and linked to form a single executable program.

Consider the following embedded SQL code fragment:

   ...
      char *p;
      exec sql fetch 
   ... 
      into :p;
   ...

In previous versions of Empress , the fetch into statement returned a pointer to an Empress internal buffer. The programmer had to copy the information from that buffer before the next embedded SQL statement which returned data, since the next such call would overwrite the information in the buffer.

Starting with Version 6.2, Empress requires the programmer to allocate space for the result of embedded SQL statements. The code fragment therefore becomes:

   char p[50];
   exec sql fetch ... into :p;

This means that the programmer no longer has to copy the result of an embedded SQL operation from the internal Empress buffer. However, the buffer provided by the programmer must be large enough to contain the result of the embedded SQL operation. Furthermore, the programmer must ensure that unallocated pointers (such as *p in the first code fragment) are not used within an embedded SQL statement.

In order to maintain compatibility with earlier versions of Empress, a new option -ptr has been added to empesql which causes the Precompiler to revert to the pre-v6.2 behavior. In other words, a program compiled with the -ptr option should not allocate space explicitly; pointers to Empress internal buffers will be returned.