Runtime Character Type Conversion
Automatic conversion occurs between Ingres character string values, database columns of type c, char, text or varchar, and form fields of type character. 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 c or character, a database column of type text or varchar, or a character form field. Ingres pads columns of type c or character with blanks to their declared length. Conversely, it does not add blanks to the data in columns of type text or varchar, or in form fields.
Second, the BASIC convention is to blank-pad static character strings. For example, the character string "abc" may 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 data is inserted from a BASIC variable into a database column of type c or 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 BASIC variable into a database column of type text 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 text or varchar column. For example, when a string "abc" stored in a BASIC static string variable of length 5 as "abc " is inserted into the text 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 EQUEL notrim function. It has the following syntax with stringvar as a character string variable.
notrim(stringvar)
When used with repeat queries, the notrim syntax is:
@notrim(stringvar)
If the text or varchar column is shorter than the variable, the data is truncated to the length of the column.
• When data is inserted 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 c, character, or varchar database columns with data in a character variable all trailing blanks are ignored. Trailing blanks are significant in text. Initial and embedded blanks are significant in character, text, and varchar; they are ignored in c.
Note: 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. The QUEL Reference Guide has information on blanks when comparing with the various Ingres character types.
The Ingres date data type is represented as a 25-byte string.
The example below uses the notrim function and the truncation rules explained above.
11 ## sub Notrim_Test
!
! Assume a table called "textchar" has been
! created with the following CREATE statement:
!
! CREATE textchar
! (row = integer,
! data = text(10)) Note the text type
!
## declare word row
## common string sdata = 7 ! Static string
## declare string ddata ! Dynamic string
sdata = 'abc ' ! Holds "abc " with 4 blanks
ddata = 'abc' ! Holds "abc"
! This APPEND adds string "abc" (blanks truncated)
## append to textchar (#row = 1, data = sdata)
! This APPEND adds string "abc" (never had blanks)
## append to textchar (#row = 2, data = ddata)
! This APPEND adds string "abc ", with tailing
! blanks left intact by using the NOTRIM function.
## append to textchar
## (#row = 3, data = NOTRIM(sdata))
! This RETRIEVE retrieves rows #1 and #2, because
! trailing blanks were suppressed when those rows
! were inserted.
## range of t IS textchar
## retrieve (row = t.#row) WHERE LENGTH(t.data) = 3
## {
print 'Row found =', row
## }
print '---------'
! This RETRIEVE retrieves row #3, because the
! NOTRIM function left trailing blanks in the
! "sdata" variable
! in the last APPEND statement.
## retrieve (row = t.#row) WHERE LENGTH(t.data) = 7
## {
print 'Row found =', row
## }
## end sub