Data Type Conversion
A Fortran variable declaration must be compatible with the Ingres value it represents. Numeric Ingres values can be set by and retrieved into numeric variables, and Ingres character values can be set by and retrieved into character variables.
Data type conversion occurs automatically for different numeric types, such as from floating-point Ingres database column values into integer Fortran variables, and for character strings, such as from varying-length Ingres character fields into fixed-length Fortran character string buffers.
Ingres does not automatically convert between numeric and character types. You must use the Ingres type conversion operators, the Ingres ascii function, or a Fortran conversion routine for this purpose.
Ingres and Fortran Data Type Compatibility
The following table shows the default Fortran data type compatibility for each Ingres data type.
Runtime Numeric Type Conversion
The Ingres runtime system provides automatic data type conversion between numeric-type values in the database and the forms system and numeric Fortran variables. The standard type conversion rules in UNIX are followed according to standard Fortran rules. In VMS, the standard VAX rules are followed. For example, if you assign a real variable to an integer-valued field, the digits after the decimal point of the variable's value are truncated. Runtime errors are generated for overflow on conversion.
The default size of integers in Embedded SQL/Fortran is four bytes. You can change the default size to two bytes by means of the -i2 preprocessor flag. If you use this flag, you should also compile the program with the -i2 compiler flag (UNIX), the noi4 qualifier (VMS), or the /integer_size:16 or /4I2 (Windows).
The Ingres money type is represented as real*8, an 8-byte real value.
Runtime Character and Varchar Type Conversion
Automatic conversion occurs between Ingres character string values and Fortran fixed-length character variables. 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 Fortran 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 complex. 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 Fortran convention is to blank-pad fixed-length character strings. For example, the character string "abc" is stored in a Fortran character*5 variable as the string "abc " followed by two blanks.
When character data is retrieved from a database column or form field into a Fortran 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 should always ensure that the variable is at least as long as the column or field, in order to avoid truncation of data.
When inserting character data into an Ingres database column or form field from a Fortran variable, note the following conventions:
• When you insert data from a Fortran 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 you insert data from a Fortran variable into a database column of type long varchar or 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 Fortran character*5 variable 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 string variable. An example demonstrating this feature follows later. If the varchar column is shorter than the variable, the data is truncated to the length of the column.
• When you insert data from a Fortran 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 a database column with character data in a Fortran variable, note the following. When comparing data in character or varchar database columns with data in a character variable, trailing blanks are ignored. Initial and embedded blanks are significant. To retain the significance of the trailing blanks in the comparison, you can use the notrim function, as shown in the following example.
IMPORTANT! Caution: As just described, the conversion of character string data between Ingres objects and Fortran 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.
The Ingres date data type is represented as a 25-byte character string.
The following program fragment demonstrates the notrim function and the truncation rules previously explained:
exec sql include sqlca
exec sql begin declare section
exec sql declare varychar table
1 (row integer,
C Note the vchar type
2 data vchar(10))
integer*2 row
character*7 data
exec sql end declare section
C The variable data holds "abc" followed by 4 blanks
data = 'abc '
C The following INSERT adds the string "abc"
C (blanks truncated)
exec sql insert into varychar (row, data)
1 values (1, :data)
C
C This statement adds the string "abc ", with 4 trailing
C blanks left intact by using the NOTRIM function
C
exec sql insert into varychar (row, data)
1 values (2, notrim(:data))
C
C This SELECT will retrieve the second row, because the
C NOTRIM function leaves trailing blanks in the "data"
C variable for the comparison with Ingres
C vchar data.
exec sql select row
1 into :row
2 from varychar
3 where data = notrim(:data)
print *, 'row found = ', row