Data Type Conversion
A BASIC 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 string variables.
Data type conversion occurs automatically for different numeric types, as follows:
• From floating-point Ingres database column values into integer BASIC variables
• From integer to decimal
• From decimal to integer
• For different length character strings, such as from varying-length Ingres character fields, into static BASIC string variables
Ingres does not automatically convert between numeric and character types. You must use the Ingres type conversion functions, the Ingres ascii function, or a BASIC conversion procedure for this purpose.
Ingres and BASIC Data Type Compatibility
The following table shows the default type compatibility for each Ingres data type. Note that some BASIC types do not match exactly and, consequently, can go through some runtime conversion.
Runtime Numeric Type Conversion
The Ingres runtime system provides automatic data type conversion between numeric-type values in the database and forms system and numeric BASIC variables. The standard type conversion rules (according to 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 when assigning Ingres numeric values into BASIC variables. Overflow caused by assigning BASIC numeric variables into Ingres numeric objects is likely to result in inconsistent data, but does not by default generate a runtime error. Using the -x flag on the Ingres statement changes this behavior by generating errors at runtime.
The BASIC
decimal data type is converted to
real double using BASIC assignment statements generated by the preprocessor. Variables of
decimal data type can be converted twice at runtime, depending on the final Ingres type being set or retrieved from. The standard BASIC arithmetic conversion rules hold for all these generated assignment statements, with a potential loss of precision. For further information, see
Decimal Data Type in this chapter.
The Ingres money type is represented as real double, an 8-byte floating-point value.
Runtime Character and Varchar Type Conversion
Automatic conversion occurs between Ingres character string values and BASIC string variables. There are four string-valued Ingres objects that can interact with string variables. These 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 string conversions, both to and from Ingres.
The conversion of BASIC string 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 literals "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 in form fields.
Second, the BASIC convention is to blank-pad static character strings. For example, the character string "abc" can be stored in a BASIC static string variable of length 5 as the string "abc " followed by two blanks.
When retrieving character data from an Ingres database column or form field into a BASIC variable, take note of the following conventions:
• When character data is retrieved from Ingres into a BASIC static string 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 character data is retrieved into a BASIC dynamic string variable, the variable's new length will exactly match the length of the data retrieved. Ingres manipulates dynamic strings in exactly the same way as BASIC does, creating and modifying storage requirements as necessary. For example, when zero-length varchar data is retrieved into a BASIC dynamic string variable, storage will not be created for the string.
When inserting character data into an Ingres database column or form field from a BASIC variable, note the following conventions:
• When you insert data from a BASIC 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 BASIC 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 BASIC static string variable of length 5 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, use the Ingres notrim function. It has the following syntax:
notrim(:stringvar)
where stringvar 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 BASIC 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.
You cannot use zero-length or uninitialized BASIC dynamic strings in insert or update statements. This is because an uninitialized dynamic string has no storage allocated for it and Ingres treats it as a non-existent variable.
When comparing character data in an Ingres database column with character data in a BASIC 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.
Note: As described above, the conversion of character string data between Ingres objects and BASIC 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 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 string.
The following program fragment demonstrates the notrim function and the truncation rules explained above.
exec sql include sqlca
exec sql begin declare section
exec sql declare textchar table &
(row integer, &
data varchar(10)) ! Note the varchar type
declare word row
common string sdata = 7 ! static string
declare string ddata ! dynamic string
exec sql end declare section
sdata = 'abc ' ! Holds "abc " (with 4 blanks)
ddata = 'abc' ! Holds "abc"
! This insert adds string "abc" (blanks truncated)
exec sql insert into textchar values (1, :sdata)
! This insert adds string "abc" (never had blanks)
exec sql insert into textchar values (2, :ddata)
! This insert adds string "abc ", with tailing blanks
! left intact by using the notrim function.
exec sql insert into textchar values (3, notrim(:sdata))
! This select retrieves rows #1 and #2, because trailing
! blanks were suppressed when those rows were inserted.
exec sql select row into :row from textchar
where length(data) = 3
exec sql begin
print 'Row found =', row
exec sql end
! This select retrieves row #3, because the notrim
! function left trailing blanks in the "sdata"
! variable in the last insert statement.
exec sql select row into :row from textchar
where length(data) = 7
exec sql begin
print 'row found =', row
exec sql end