The SQL communication area (SQLCA)

Part V. The SQL Anywhere Programming Interfaces

Chapter 34. The Embedded SQL Interface
The SQL communication area (SQLCA)

The SQL Communication Area (SQLCA) is an area of memory used on every database request for communicating statistics and errors from the application to the database engine and back to the application. There is a global SQLCA variable defined in the interface library (imports library for the DLLs). An external reference for this variable named sqlca of type SQLCA and an external reference for a pointer to this variable sqlcaptr are automatically generated by the preprocessor. The actual global variable is declared in the database library (imports library for DLLs).

Structure packing option
All Embedded SQL programs must be compiled with the structure packing option of the compiler set to one-byte alignment (usually the default).

     typedef long int        an_sql_code;
     typedef char an_sql_state[6];
     
     struct sqlwarn{ unsigned char sqlwarn0;
         unsigned char    sqlwarn1;
         unsigned char    sqlwarn2;
         unsigned char    sqlwarn3;
         unsigned char    sqlwarn4;
         unsigned char    sqlwarn5;
         unsigned char    sqlwarn6;
         unsigned char    sqlwarn7;
         unsigned char    sqlwarn8;
         unsigned char    sqlwarn9;
     };
     typedef struct sqlca {
         unsigned char sqlcaid[8];
         long sqlcabc;
         an_sql_code sqlcode;
         short sqlerrml;
         unsigned char sqlerrmc[70];
         unsigned char sqlerrp[8];
         long sqlerrd[6];
         struct sqlwarn    sqlwarn;
         an_sql_state    sqlstate;
     } SQLCA;
     
     #define SQLCODE sqlcaptr->sqlcode
     #define SQLSTATE sqlcaptr->sqlstate
     #define SQLIOCOUNT sqlcaptr->sqlerrd[1]
     #define SQLCOUNT sqlcaptr->sqlerrd[2]
     #define SQLIOESTIMATE sqlcaptr->sqlerrd[3]

The user references 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). As shown in there are some C macros defined for referencing the sqlcode field, the sqlstate field and some other fields.

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 engine. It is implicitly passed on all Embedded SQL statements.

The fields in the SQLCA have the following meanings:

Field Description

sqlcaid An 8 byte character field that contains the string "SQLCA " as an identification of the SQLCA structure. This field helps in debugging when looking at memory contents.

sqlcabc A long integer containing the length of the SQLCA structure (136 bytes).

sqlcode A long integer specifying 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. These error codes are fully explained in he chapter "SQL Anywhere Database Error Messages". The error code will be 0 for a successful operation, positive for a warning and negative for an error.

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. See "SQL Anywhere Database Error Messages" for a list of the error codes and the error messages. Some error messages have a %1 in them which is replaced with the text in this field. For example, if a table not found error is generated, this field will contain the table name which is inserted into the error message at the appropriate place.

sqlerrp Reserved.

sqlerrd A utility array of longs.

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 the digits '0' through '9' or the upper case alphabetic characters '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 used by SQL Anywhere are described in "SQL Anywhere Database Error Messages".

Field	Description
sqlcaid	An 8 byte character field that contains the string `"SQLCA "` as an identification of the SQLCA structure. This field helps in debugging when looking at memory contents.
sqlcabc	A long integer containing the length of the SQLCA structure (136 bytes).
sqlcode	A long integer specifying 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. These error codes are fully explained in he chapter "SQL Anywhere Database Error Messages". The error code will be 0 for a successful operation, positive for a warning and negative for an error.
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. See "SQL Anywhere Database Error Messages" for a list of the error codes and the error messages. Some error messages have a `%1` in them which is replaced with the text in this field. For example, if a table not found error is generated, this field will contain the table name which is inserted into the error message at the appropriate place.
sqlerrp	Reserved.
sqlerrd	A utility array of longs.
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 the digits '0' through '9' or the upper case alphabetic characters '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 used by SQL Anywhere are described in "SQL Anywhere Database Error Messages".

The sqlerror field array has the following elements.

Element Description

sqlerrd[1] (SQLIOCOUNT) The actual number of input/output operations 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 will be the total number of input/output operations for the entire command sequence.

sqlerrd[2] (SQLCOUNT) The number of rows affected by the command (for INSERT, UPDATE and DELETE).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 will be the actual number of rows if the database engine can compute it without counting the rows. The database can also be configured to always return the actual number of rows (see "SET OPTION statement").On a FETCH cursor statement, this field is filled if a SQLE_NOTFOUND warning is returned. It will contain the number of rows that the a FETCH RELATIVE or FETCH ABSOLUTE statement has exceeded the allowable 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 will be positive if the attempted fetch was further beyond the end of the cursor, and negative if the attempted fetch was further before the beginning of the cursor.On a GET DATA statement, this field holds the actual length of the value.In the case of a syntax error, SQLE_SYNTAX_ERROR, this field will contain the approximate character position within the command string where the error was detected..

sqlerrd[3] (SQLIOESTIMATE) The estimated number of input/output operations required to complete the command. This field is filled in on an OPEN or EXPLAIN command.

Element	Description
sqlerrd[1] (SQLIOCOUNT)	The actual number of input/output operations 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 will be the total number of input/output operations for the entire command sequence.
sqlerrd[2] (SQLCOUNT)	The number of rows affected by the command (for INSERT, UPDATE and DELETE).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 will be the actual number of rows if the database engine can compute it without counting the rows. The database can also be configured to always return the actual number of rows (see "SET OPTION statement").On a FETCH cursor statement, this field is filled if a SQLE_NOTFOUND warning is returned. It will contain the number of rows that the a FETCH RELATIVE or FETCH ABSOLUTE statement has exceeded the allowable 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 will be positive if the attempted fetch was further beyond the end of the cursor, and negative if the attempted fetch was further before the beginning of the cursor.On a GET DATA statement, this field holds the actual length of the value.In the case of a syntax error, SQLE_SYNTAX_ERROR, this field will contain the approximate character position within the command string where the error was detected..
sqlerrd[3] (SQLIOESTIMATE)	The estimated number of input/output operations required to complete the command. This field is filled in on an OPEN or EXPLAIN command.