Programming Interfaces Guide
CHAPTER 2. The Embedded SQL Interface
The SQL Communication Area (SQLCA) is an area of memory that is used on every database request for communicating statistics and errors from the application to the database server and back to the application. The SQLCA is used as a handle for the application-to-database communication link. It is passed in to all database library functions that need to communicate with the database server. It is implicitly passed on all Embedded SQL statements.
A global SQLCA variable is defined in the interface library. The preprocessor generates an external reference for the global SQLCA variable and an external reference for a pointer to it. The external reference is named sqlca and is of type SQLCA. The pointer is named sqlcaptr. The actual global variable is declared in the imports library.
The SQLCA is defined by the sqlca.h header file, included in the h subdirectory of your installation directory.
You reference the SQLCA to test for a particular error code. The sqlcode and sqlstate fields contain error codes when a database request has an error (see below). Some C macros are defined for referencing the sqlcode field, the sqlstate field, and some other fields.
Fields in the SQLCAThe fields in the SQLCA have the following meanings:
sqlcaid An 8-byte character field that contains the string SQLCA as an identification of the SQLCA structure. This field helps in debugging when you are looking at memory contents.
sqlcabc A long integer that contains the length of the SQLCA structure (136 bytes).
sqlcode A long integer that specifies the error code when the database detects an error on a request. Definitions for the error codes can be found in the header file sqlerr.h. The error code is 0 (zero) for a successful operation, positive for a warning and negative for an error.
For a full listing of error codes, see Database Error Messages.
sqlerrml The length of the information in the sqlerrmc field.
sqlerrmc May contain one or more character strings to be inserted into an error message. Some error messages contain a placeholder string (%1) which is replaced with the text in this field.
For example, if a Table Not Found error is generated, sqlerrmc contains the table name, which is inserted into the error message at the appropriate place.
For a full listing of error messages, see Database Error Messages.
sqlerrp Reserved.
sqlerrd A utility array of long integers.
sqlwarn Reserved.
sqlstate The SQLSTATE status value. The ANSI SQL standard (SQL-92) defines a new type of return value from a SQL statement in addition to the SQLCODE value in previous standards. The SQLSTATE value is always a five-character null-terminated string, divided into a two character class (the first two characters) and a three-character subclass. Each character can be a digit from 0 through 9 or an upper case alphabetic character A through Z.
Any class or subclass that begins with 0 through 4 or A through H is defined by the SQL standard; other classes and subclasses are implementation defined. The SQLSTATE value '00000' means that there has been no error or warning.
Other SQLSTATE values are described in Database Error Messages.
The sqlerror field array has the following elements.
sqlerrd[1] (SQLIOCOUNT) The actual number of input/output operations that were required to complete a command.
The database does not start this number at zero for each command. Your program can set this variable to zero before executing a sequence of commands. After the last command, this number is the total number of input/output operations for the entire command sequence.
sqlerrd[2] (SQLCOUNT) The value of this field depends on which statement is being executed.
INSERT, UPDATE and DELETE statements The number of rows that were affected by the statement.
On a cursor OPEN, this field is filled in with either the actual number of rows in the cursor (a value greater than or equal to 0) or an estimate thereof (a negative number whose absolute value is the estimate). It is the actual number of rows if the database server can compute it without counting the rows. The database can also be configured to always return the actual number of rows using the ROW_COUNT option.
FETCH cursor statement The SQLCOUNT field is filled if a SQLE_NOTFOUND warning is returned. It contains the number of rows by which a FETCH RELATIVE or FETCH ABSOLUTE statement goes outside the range of possible cursor positions. (A cursor can be on a row, before the first row or after the last row.)
The value is 0 if the row was not found but the position is valid, for example, executing FETCH RELATIVE 1 when positioned on the last row of a cursor. The value is positive if the attempted fetch was beyond the end of the cursor, and negative if the attempted fetch was before the beginning of the cursor.
GET DATA statement The SQLCOUNT field holds the actual length of the value.
DESCRIBE statement In the WITH VARIABLE RESULT clause used to describe procedures that may have more than one result set, SQLCOUNT is set to one of the following values:
0 The result set may change: the procedure call should be described again following each OPEN statement.
1 The result set is fixed. No redescribing is required.
In the case of a syntax error, SQLE_SYNTAX_ERROR, this field contains the approximate character position within the command string where the error was detected.
sqlerrd[3] (SQLIOESTIMATE) The estimated number of input/output operations that are to complete the command. This field is given a value on an OPEN or EXPLAIN command.
SQLCA management for multi-threaded or reentrant codeYou can use Embedded SQL statements in multi-threaded or reentrant code. However, if you use a single connection, you are restricted to one active request per connection. In a multi-threaded application, you should not use the same connection to the database on each thread unless you use a semaphore to control access.
There are no restrictions on using separate connections on each thread that wishes to use the database. The SQLCA is used by the runtime library to distinguish between the different thread contexts. Thus, each thread wishing to use the database must have it's own SQLCA. Any given database connection will only be accessible from one SQLCA.
Using multiple SQLCAs
To manage multiple SQLCAs in your application:You must use the command line switch on the SQL preprocessor that generates reentrant code (-r). The reentrant code is a little larger and a little slower because statically initialized global variables cannot be used. However, these effects are minimal.
Each SQLCA used in your program must be initialized with a call to db_init and cleaned up at the end with a call to db_fini.
Caution
Failure to call db_fini for each db_init on NetWare can cause the database server to fail, and the NetWare file server to fail.
The Embedded SQL statement SET SQLCA (SET SQLCA statement) is used to tell the SQL preprocessor to use a different SQLCA for database requests. Usually, a statement such as: EXEC SQL SET SQLCA 'task_data->sqlca'; is used at the top of your program or in a header file to set the SQLCA reference to point at task specific data. This statement does not generate any code and thus has no performance impact. It changes the state within the preprocessor so that any reference to the SQLCA will use the given string.
For information about creating SQLCAs, see SET SQLCA statement.
When to use multiple SQLCAsYou can use the multiple SQLCA support in any of the supported Embedded SQL environments, but it is only required in reentrant code.
The following list details the environments where multiple SQLCAs must be used:
Multi-threaded applications If more than one thread uses the same SQLCA, a context switch can cause more than one thread to be using the SQLCA at the same time. Each thread must have its own SQLCA. This can also happen when you have a DLL that uses Embedded SQL and is called by more than one thread in your application.
Dynamic link libraries and shared libraries A DLL has only one data segment. While the database server is processing a request from one application, it may yield to another application that makes a request to the database server. If your DLL uses the global SQLCA, both applications are using it at the same time. Each Windows application must have its own SQLCA.
A DLL with one data segment A DLL can be created with only one data segment or one data segment for each application. If your DLL has only one data segment, you cannot use the global SQLCA for the same reason, that a DLL cannot use the global SQLCA. Each application must have its own SQLCA.
Connection management with multiple SQLCAsYou do not need to use multiple SQLCAs to connect to more than one database or have more than one connection to a single database.
Each SQLCA can have one unnamed connection. Each SQLCA has an active or current connection (see SET CONNECTION statement). All operations on a given database connection must use the same SQLCA that was used when the connection was established.
Record locking |