SQL Communication Area

The GNU.DB.ESQL_SUPPORT package provides a so called SQL communication area type. This area contains informations about the result of the last query.

Syntax:

     <include_sqlca_clause> ::=
          INCLUDE SQLCA 
This statement will insert a SQLCA in the Ada 95 code. If this is done in the declare section of a procedure as shown below, the SQLCA will be declared local to the procedure.

Example 14-2. Local SQLCA in procedures

   procedure Print_Departement(
      departement  : in Integer ) is
      ---
      EXEC SQL BEGIN DECLARE SECTION END-EXEC
      Name                    : CHAR(1..15) := (others=>32);
      .............
      Salary                  : DOUBLE_PRECISION := 0.0;
 
      EXEC SQL END DECLARE SECTION END-EXEC
 
      EXEC SQL INCLUDE SQLCA ;    -- Make a private SQLCA
 
   begin
 
      .....
      EXEC SQL AT DB01
         DECLARE emp_cursor CURSOR FOR
            SELECT EMPNO, FIRSTNAME, NAME, JOB, MANAGER, SALARY
            FROM employees
            WHERE deptno = :Depno  ;
      ....
   end;
The application may access the contents by using the variable name SQLCA in the application code. This method is preferable in a multi thread environment, because it avoids interferences between threads through the global variables SQLCODE and SQLSTATE.

The SQLCA provides several fields containing usefull information about the most recently executed query as shown below:

   type SQLCA_Type is record
         Message       : aliased String(1..255 );
         State         : aliased SQLSTATE_TYPE;
         SqlCode       : aliased SQLCODE_TYPE;
         Affected_Rows : aliased Integer := 0;
      end record;                                     

The parameter Affected_Rows contains the number of rows affected by the last query.

State and SqlCode do contain the result code of the last query. The SqlCode should not be used any more because the State information contains more information.

The field Message contains a string generated by the underlying dbcs containing information bout the most recent error.