# CHAPTER 2: General Topics for the Precompiler

## 2.1 Introduction

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.

## 2.2 Accessing Databases and Tables

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.

## 2.3 Multiple Record Operations

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.

### 2.3.1 The Cursor

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.

### 2.3.2 Declaring a Cursor

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.

### 2.3.3 Processing a Selected 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`.

### 2.3.4 Updating/Deleting a Selected Row

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.

### 2.3.5 Closing a Cursor

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.

### 2.3.6 Multiple Cursors

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.

## 2.4 Transactions

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.

## 2.5 Using Program Variables

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

Empress | C
`SHORTINTEGER` | char
`INTEGER` | short
`LONGINTEGER` | long
`FLOAT` | float
`LONGFLOAT` | double
`CHAR, TEXT` | char */char []

### 2.5.1 Variable Declarations

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.

### 2.5.2 Declaring C Variables

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).

### 2.5.3 Shell Variables

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.

### 2.5.4 Control Variables

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.

### 2.5.5 Fetching and Control Variables

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

`ctrl = 0` | Indicates that a value is not `NULL` and that it was successfully placed in the destination variable.
`ctrl < 0` | Indicates a `NULL` value.
`ctrl > 0` | Indicates that the destination variable is not big enough to contain the value and consequently, that value was truncated. In this case, the control variable contains the real length of the value and the `SQLWARN0` and `SQLWARN1` indicators contain the value '`W`'.

### 2.5.6 Inserting and Control Variables

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:

`ctrl < 0` | A `NULL` is inserted into the row.
`ctrl = 0` | The value contained in the variable is inserted into the row.

### 2.5.7 `WHERE` Clause and Control Variables

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

`ctrl < 0` | Attribute will be tested against `NULL`.
`ctrl >= 0` | Attribute will be tested against the value contained in the variable.

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

## 2.6 SQL Communication Area (SQLCA)

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.

### 2.6.1 Using the SQLCA

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:

`SQLCODE = 0` | No errors have occurred.
`SQLCODE < 0` | An error has occurred.
`SQLCODE = 100` | No more rows were found.
`SQLCODE > 0` | The operation was successful but an exception condition was detected.

### 2.6.2 SQLCA Structure

The SQLCA data structure is arranged as follows:

`SQLCAID` | Contains the marker string "SQLCA" and is updated the first time that the structure is used.
`SQLABC` | Contains the length of the SQLCA structure and is updated the first time that the structure is used.
`SQLERRML` | Contains the length of the error message in runtime.
`SQLERRMC` | Contains the error message.
`SQLWARN` | An area that contains 8 indicators, each of which may contain the value " " or "`W`". "`W`" indicates that an exceptional condition was detected. " " indicates that the statement was executed successfully.
`SQLWARN0` | If the value is "`W`", one of the other indicators was set during the last operation.
`SQLWARN1` | If the value is "`W`", a value in the last `FETCH` or `SELECT INTO` statement was truncated.

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;

## 2.7 Error Handling

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:

`SQLCODE < 0` | The action specified for `SQLERROR` will be executed.
`SQLCODE = 100` | The action specified for `NOT FOUND` will be executed.
`SQLWARN0 =`'`W`' or (`SQLCODE > 0` and `SQLCODE != 100`) | The action specified for `SQLWARNING` will be executed.

## 2.8 Restrictions

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`.

## 2.9 Invoking the Precompiler

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:

`-ptr` | specifies the use of pointers for backward compatibility.
`infile.pc` | is the file containing your program with embedded SQL statements.
`outfile.c` | is your source code file with the SQL commands replaced by function calls. If `outfile` is not specified the default name `mpout.c` is used.

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.

### 2.9.1 Using the -ptr Option

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.