SQLDA Record Usage

You can use the SQL Descriptor Area (SQLDA) to pass type and size information about an SQL statement, an Ingres form, or an Ingres table field, between Ingres and your program.

In order to use the SQLDA, issue the include sqlda statement in the COBOL program units that reference the SQLDA. The include sqlda statement generates a COBOL COPY directive of a file that defines an external reference to an SQLDA‑like COBOL record. The file declares a COBOL record called SQLDA. Additionally in VMS, it marks it as EXTERNAL.

You can also code this record directly, instead of using the include sqlda statement. You can choose any name for the structure and you can declare more than one in a single program.

* SQL Descriptor Area

78 IISQ-MAX-COLS VALUE 1024.

01 SQLDA.
   05 SQLDAID         PIC X(8).
   05 SQLDABC         PIC S9(9) USAGE COMP-5.
   05 SQLN            PIC S9(4) USAGE COMP-5
                      VALUE IISQ-MAX-COLS.

   05 SQLD            PIC S9(4) USAGE COMP-5.
   05 SQLVAR          OCCURS IISQ-MAX-COLS TIMES.
      07 SQLTYPE      PIC S9(4) USAGE COMP-5.
      07 SQLLEN       PIC S9(4) USAGE COMP-5.
      07 SQLDATA      USAGE POINTER.
      07 SQLIND       USAGE POINTER.
      07 SQLNAME.
          49 SQLNAMEL PIC S9(4) USAGE COMP-5.
          49 SQLNAMEC PIC X(34).
*

* SQLDA Type Codes
* Type Name Value Length
* --------- ----- ------
* DATE          3    25
* MONEY         5      8
* DECIMAL      10   SQLLEN = 256*P+S
* CHAR       20   SQLLEN
* VARCHAR     21   SQLLEN
* BYTE         23   SQLLEN
* BYTE VARYING 24   SQLLEN
* LONG BYTE    25   SQLLEN
* INTEGER      30   SQLLEN
* FLOAT        31   SQLLEN
* 4GL OBJECT   45   SQLLEN
* TABLE-FIELD 52   0

*
78 IISQ-DTE-TYPE   VALUE 3.
78 IISQ-DTE-LEN    VALUE 25.
78 IISQ-MNY-TYPE VALUE 5.
78 IISQ-DEC-TYPE VALUE 10.
78 IISQ-CHA-TYPE   VALUE 20.
78 IISQ-VCH-TYPE   VALUE 21.
78 IISQ-BYTE-TYPE VALUE 23.
78 IISQ-VBYTE-TYPE VALUE 24.
78 IISQ-LBYTE-TYPE VALUE 25.
78 IISQ-INT-TYPE   VALUE 30.
78 IISQ-FLT-TYPE   VALUE 31.
78 IISQ-OBJ-TYPE   VALUE 45.
78 IISQ-TBL-TYPE   VALUE 52.
78 IISQ-LVCH-TYPE VALUE 22.

* SQL Descriptor Area

78 IISQ-MAX-COLS VALUE 1024.

01 SQLDA.
   05 SQLDAID         PIC X(8).
   05 SQLDABC         PIC S9(9) USAGE COMP-5.
   05 SQLN            PIC S9(4) USAGE COMP-5
                      VALUE IISQ-MAX-COLS.
   05 SQLD            PIC S9(4) USAGE COMP-5.
   05 SQLVAR          OCCURS IISQ-MAX-COLS TIMES.
      07 SQLTYPE      PIC S9(4) USAGE COMP-5.
      07 SQLLEN       PIC S9(4) USAGE COMP-5.
      07 SQLDATA      USAGE POINTER.
      07 SQLIND       USAGE POINTER.
      07 SQLNAME.
          49 SQLNAMEL PIC S9(4) USAGE COMP-5.
          49 SQLNAMEC PIC X(34).
*

* SQL Descriptor Area

  01 SQLDA EXTERNAL.
     05 SQLDAID          PIC X(8).
     05 SQLDABC          PIC S9(9) USAGE COMP.
     05 SQLN             PIC S9(4) USAGE COMP.
     05 SQLDA            PIC S9(4) USAGE COMP.
     05 SQLVAR           OCCURS 1024 TIMES.
          07 SQLTYPE     PIC S9(4) USAGE COMP.
          07 SQLLEN      PIC S9(4) USAGE COMP.
          07 SQLDATE     USAGE POINTER.
          07 SQLIND      USAGE POINTER.
          07 SQLNAME.
              49 SQLNAMEL PIC S9(4) USAGE COMP.
              49 SQLNAMEC PIC X(34).
  01 IISQLHDR
     05 SQLARG        USAGE POINTER.
     05 SQLHDLR       PIC S9(9) USAGE COMP.
*

* SQLDA Type Codes
*
* Type Name Value Length
* --------- ----- ------
* DATE 3 25
* MONEY 5 8
* DECIMAL 10 SQLLEN = 256*P+S
* CHAR 20 QLLEN
* VARCHAR 21 SQLLEN
* BYTE 23 SQLLEN
* BYTE VARYING 24 SQLLEN
* LONG BYTE 25 SQLLEN
* INTEGER 30 SQLLEN
* FLOAT 31 SQLLEN
* TABLE 52 0
* LONG VARCHAR 22 0
* 4GL OBJECT 45 SQLLEN
* DATAHANDLER 46

• The sqlvar array (COBOL table) has 1024 elements. If you code your own SQLDA, you can supply a different number of elements.

• The sqldata and sqlind fields are declared with USAGE POINTER. These must be set to point at the addresses of other data items using the COBOL SET statement with the ADDRESS OF clause (UNIX) or the REFERENCE clause (VMS).

• If your program declares its own SQLDA record, you must confirm that the record layout is identical to that of the Ingres‑defined SQLDA record, although you can declare a different number of sqlvar elements.

• The nested group sqlname is a varying length character string consisting of a length and data area. The sqlnamec field contains the name of a result field or column after the describe (or prepare into) statement. The length of the name is specified by sqlnamel. The characters in the sqlnamec field are blank padded. The sqlname group may also be set by a program using Dynamic FRS. The program is not required to pad sqlnamec with blanks. see How to Set SQLNAME for Dynamic FRS in this chapter.

• The comment listing the type codes represents the types that are returned by the describe statement and the types used by the program when using an SQLDA to retrieve or set data. The type code 52 indicates a table field and is set by the FRS when describing a form that contains a table field.

• Because the SQLDA is passed directly to Ingres without preprocessor intervention (and generated MOVE statements), all numeric fields of the SQLDA are declared as COMP‑5. If, on your machine, the internal storage format of a USAGE COMP data item is identical to the storage format of USAGE COMP‑5 then you may use either USAGE COMP or USAGE COMP‑5 for the corresponding SQLDA fields when you code your own. If you use USAGE COMP and the internal storage format is not the same then Ingres issues runtime errors about unknown data type codes and invalid data type lengths.

• The SQLDA record definition is an EXTERNAL definition. This allows multiple COBOL program modules and source files to reference and process the same SQLDA. If you code your own SQLDA, you are not required to share it with other program modules by declaring it EXTERNAL.