3. Embedded SQL for COBOL : Dynamic Programming for COBOL : SQLDA Record Usage
 
Share this page                  
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.
The definition of the SQLDA (as specified in the COPY file) is shown below in Windows, UNIX and VMS:
Windows:
*
* 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.
UNIX:
*
* 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.
VMS:
*
* 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
Structure Definition and Usage Notes:
The sqlvar array (COBOL table) has 1024 elements. If you code your own SQLDA, you can supply a different number of elements.
The sqlvar array begins at subscript 1.
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 (see page 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.
Windows and UNIX
If you code your own SQLDA, you can declare it EXTERNAL and share it with other programs.
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.
VMS
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.