Runtime Character and Varchar Type Conversion
Automatic conversion occurs between Ingres character string values and COBOL character variables (alphabetic, alphanumeric, and alphanumeric edited data items). The string-valued Ingres objects that can interact with character string variables are:
• Ingres names, such as form and column names
• Database columns of type character
• Database columns of type varchar
• Form fields of type character
• Database columns of type long varchar
Several considerations apply when dealing with character string conversions, both to and from Ingres.
The conversion of COBOL character variables used to represent Ingres names is simple: trailing blanks are truncated from the variables because the blanks make no sense in that context. For example, the string constants empform and empform refer to the same form.
The conversion of other Ingres objects is a bit more complicated. First, the storage of character data in Ingres differs according to whether the medium of storage is a database column of type character, a database column of type varchar, or a character form field. Ingres pads columns of type character with blanks to their declared length. Conversely, it does not add blanks to the data in columns of type varchar or long varchar, or in form fields.
Second, the COBOL convention is to blank‑pad fixed‑length character strings. For example, the character string abc may be stored in a COBOL PIC X(5) data item as the string abc followed by two blanks.
When character data is retrieved from a database column or form field into a COBOL character variable and the variable is longer than the value being retrieved, the variable is padded with blanks. If the variable is shorter than the value being retrieved, the value is truncated. You must always ensure that the variable is at least as long as the column or field, in order to avoid truncation of data. You should note that, when a value is transferred into a data item from an Ingres object, it is copied directly into the variable storage area without regard to the COBOL special insertion rules.
When inserting character data into an Ingres database column or form field from a COBOL variable, note the following conventions:
• When data is inserted from a COBOL variable into a database column of type character and the column is longer than the variable, the column is padded with blanks. If the column is shorter than the variable, the data is truncated to the length of the column.
• When data is inserted from a COBOL variable into a database column of type varchar or long varchar and the column is longer than the variable, no padding of the column takes place. Furthermore, by default, all trailing blanks in the data are truncated before the data is inserted into the varchar column. For example, when a string abc stored in a COBOL PIC X(5) data item as abc (see above) is inserted into the varchar column, the two trailing blanks are removed and only the string abc is stored in the database column. To retain such trailing blanks, you can use the Ingres notrim function. It has the following syntax:
notrim(:charvar)
where charvar is a character variable. The following example demonstrates this feature. If the varchar column is shorter than the variable, the data is truncated to the length of the column.
• When data is inserted from a COBOL variable into a character form field and the field is longer than the variable, no padding of the field takes place. In addition, all trailing blanks in the data are truncated before the data is inserted into the field. If the field is shorter than the data (even after all trailing blanks have been truncated), the data is truncated to the length of the field.
When comparing character data in an Ingres database column with character data in a COBOL variable, note the following convention:
• When comparing data in character or varchar database columns with data in a character variable, all trailing blanks are ignored. Initial and embedded blanks are significant.
Caution! As described above, the conversion of character string data between Ingres objects and COBOL variables often involves the trimming or padding of trailing blanks, with resultant change to the data. If trailing blanks have significance in your application, give careful consideration to the effect of any data conversion. For a more complete description of the significance of blanks in string comparisons, see the SQL Reference Guide.
The Ingres date data type is represented as a 25‑byte character string: PIC X(25).
The program fragment in the following example demonstrates the notrim function and the truncation rules explained above.
Example: notrim function usage
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
EXEC SQL DECLARE varychar TABLE
(row integer,
data varchar(10))
END-EXEC.
01 ROW PIC S9(4) USAGE COMP.
01 DATA PIC X(7).
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
BEGIN.
* DATA will hold "abc " followed by 4 blanks.
MOVE "abc " TO DATA.
* The following INSERT adds the string "abc"
* (blanks truncated).
EXEC SQL INSERT INTO varychar (row, data)
VALUES (1, :DATA)
END-EXEC.
* This statement adds the string "abc ", with 4 trailing
* blanks left intact by using the NOTRIM function
EXEC SQL INSERT INTO varychar (row, data)
VALUES (2, NOTRIM(:DATA))
END-EXEC.
* This SELECT will retrieve the second row,
* because the NOTRIM
* function of the previous INSERT statement
* left trailing blanks in the "data" variable.
EXEC SQL SELECT row
INTO :ROW
FROM varychar
WHERE length(data) = 7
END-EXEC.
DISPLAY "Row found = " ROW.