Was this helpful?
The SQLVAR Table
The SQL Reference Guide discusses the legal values of the sqlvar table (array). The describe and prepare into statements set the type, length, and name information of the SQLDA. This information refers to the result columns of a prepared select statement, the fields of a form, or the columns of a table field. When the program uses the SQLDA to retrieve or set Ingres data, it must assign the type and length information that now refers to the data items being pointed at by the SQLDA.
COBOL Data Item Type Codes
The type codes listed in the COBOL comment appearing in the SQLDA Record section are the types that describe Ingres result fields or columns. For example, the SQL types date and money do not describe program variables but rather data types that are compatible with COBOL character and numeric types.
Character data and the SQLDA have the same rules as character data in regular embedded SQL statements. They are also described in the COBOL Data Items and Data Types section.
The following Windows, UNIX, and VMS sections describe the Ingres type codes to use with COBOL data items that will be pointed at by the sqldata pointers.
Windows and UNIX:
The left column of the following table shows the COBOL pictures and usages of the COBOL data items pointed at by sqldata, while the middle and the right columns show the equivalent SQL type codes and lengths.
COBOL Data Type
SQL Type Code (sqltype)
Length (sqllen)
PIC S9(4) USAGE COMP-5 SYNC
30 (INTEGER)
2
PIC S9(9) USAGE COMP-5 SYNC
30 (INTEGER)
4
PIC S9(10)V9(8) COMP-3 SYNC
31 (FLOAT)
256*18+8
PIC S9(P-S)V9(S) USAGE COMP-3
10 (DECIMAL)
256*P+S
PIC X(LEN)
20 (CHARACTER)
LEN
First, note that since the preprocessor does not generate any conversions for the data items pointed at by sqldata you must confirm that the storage format of the values being pointed at are completely compatible with the storage formats known by Ingres (C storage formats). Consequently, 4‑byte integers are USAGE COMP‑5 SYNC rather than just USAGE COMP. If, on your machine, you verify that the internal storage format of unscaled COMP and COMP‑5 data items are identical then you can use USAGE COMP.
The preprocessor does not need to generate any conversions for the decimal data type. So, sqldata can be pointed directly at a COMP‑3. Ingres expects precision and scale to be encoded in the sqllen field. The precision is stored in the first byte of a 2‑byte integer while scale is stored in the last byte of a 2‑byte integer. For example, decimal(18,8) length is stored as (256*18)+8.
All other Ingres types are compatible with the above types. For more information, see COBOL Data Items and Data Types in this chapter, which describes runtime data conversion. For example, the SQL date data type can be retrieved into a 25‑byte character string, while the SQL money or float data type can be retrieved using a COMP‑3 data item. Ingres will coerce float or money to packed decimal at runtime.
Nullable data types (those data items associated with a null indicator) are specified by assigning the negative of the type code to the sqltype field. If the type is negative when you use the SQLDA to retrieve or set Ingres data, then a null indicator must be pointed at by the corresponding sqlind field. In this case, the COBOL data type of the null indicator must be PIC S9(4) USAGE COMP‑5 SYNC. Once again, USAGE COMP‑5 may be replaced by USAGE COMP if you verify that COMP is identical to COMP‑5 on your machine.
VMS:
The left column of the following table shows the COBOL pictures and usages of the COBOL data items pointed at by sqldata, while the middle and the right columns show the equivalent SQL type codes and lengths.
COBOL Type Codes
SQL Type Codes (sqltype)
Length (sqllen)
PIC S9(4) USAGE COMP
30 (INTEGER)
2
PIC S9(9) USAGE COMP
30 (INTEGER)
4
USAGE COMP-1
31 (FLOAT)
4
IISQLHDLR
46 (Datahandler)
0
USAGE COMP-2
31 (FLOAT)
8
PIC S9(P-S)V9(S) USAGE COMP-3
10 (DECIMAL)
256*P+S
PIC X(LEN)
20 (CHARACTER)
LEN
All other types are compatible with these types, as described in the COBOL Data Items and Data Types in this chapter, which describes runtime data conversion. For example, the SQL date data type can be retrieved into a COBOL 25‑byte character string, while the SQL money type can be retrieved into a COMP-2 data item.
Nullable data types (those data items that are associated with a null indicator) are specified by assigning the negative of the type code to the sqltype field. If the type is negative, a null indicator (a 2‑byte integer data item) must be pointed at by the sqlind field.
Character data and the SQLDA have the exact same rules as character data in regular embedded SQL statements. For more information, see COBOL Data Items and Data Types in this chapter.
Last modified date: 08/28/2024