7. Embedded SQL for Pascal : Pascal Variables and Data Types : Data Type Conversion : Runtime Character and Varchar Type Conversion
 
Share this page                  
Runtime Character and Varchar Type Conversion
Automatic conversion occurs between Ingres character string values and Pascal character string variables. There are string-valued Ingres objects that can interact with character 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 character string conversions, both to and from Ingres.
The conversion of Pascal character 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 storage of character data in Pascal differs according to whether the character variable is of fixed or of varying length. The Pascal convention is to blank-pad fixed-length character strings, but not to pad varying-length character strings. For example, the character string "abc" coming from an Ingres object will be stored in a Pascal packed array[1..5] of char variable as the string "abc  " followed by two blanks. However, the same string would be stored in a varying[5] of char variable as "abc" without any trailing blanks.
When retrieving character data from an Ingres database column or form field into a Pascal variable, you should always ensure that the variable is at least as long as the column or field, in order to avoid truncation of data. Furthermore, take note of the following conventions:
Data stored in a database column of type character is padded with blanks to the length of the column. The variable receiving such data, be it of fixed or varying length, will contain those blanks. Following Pascal rules, if a fixed-length variable is longer than the database column, the data retrieved into it is further padded with blanks to the length of the variable. In the case of a varying-length variable, no further padding takes place. If the variable is shorter than the database column, truncation of data occurs.
Data stored in a database column of type varchar is not padded with blanks. If a fixed-length variable is longer than the data in the varchar column, when retrieved the data is padded with blanks to the length of the variable. In the case of a varying-length variable, no padding takes place. If the variable is shorter than the database column, truncation of data occurs.
Data stored in a character form field contains no trailing blanks. If a fixed-length variable is longer than the data in the field, when retrieved the data is padded with blanks to the length of the variable. In the case of a varying-length variable, no padding takes place. If the variable is shorter than the field, truncation of data occurs.
When inserting character data into an Ingres database column or form field from a Pascal variable, note the following conventions:
When data is inserted from a Pascal 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 Pascal 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 Pascal packed array[1..5] of char 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 Embedded SQL 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 data is inserted from a Pascal 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 Pascal 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 Pascal 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 character string.
The program fragment in the example below 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 data type}

var
    row:     Integer;
    p_data: packed array[1..7] of Char;
    v_data: varying[7] of Char;
    ...

exec sql end declare section;

begin
    p_data := 'abc  ';             {Holds "abc "}
    v_data := 'abc';               {Holds "abc"}
    {The following insert adds the string "abc" (blanks truncated)}
    exec sql insert into textchar (row, data)
         values (1, :p_data);

    {The following insert adds the string "abc" (never had blanks)}
    exec sql insert into textchar (row, data)
         values (2, :v_data);

    {
    | This statement adds the string "abc ", with 4 trailing
    | blanks left intact by using the NOTRIM function.
    }
    exec sql insert into textchar (row, data)
         values (3, notrim(:p_data));

    {
    | The following FETCH retrieves rows #1 and #2, because trailing
    | blanks were suppressed when those rows were inserted.
    }
    exec sql declare csr cursor for
         select row
         from textchar
         wherE length(data) = 3;

    exec sql open csr;

    while (sqlca.sqlcode = 0) do
    begin
        exec sql fetch csr into :row;
        if (sqlca.sqlcode = 0) then
            writeln( 'Row found = ', row );
    end;

    exec sql close csr;

    {
    | The following FETCH retrieves row #3, because the NOTRIM
    | function left trailing blanks in the "p_data" variable
    | in the last INSERT statement.
    }
    exec sql declare csr2 cursor for
         select row
         from textchar
         where length(data) = 7;

            exec sql open csr2;

    while (sqlca.sqlcode = 0) do
    begin
        exec sql fetch csr2 into :row;
        if (sqlca.sqlcode = 0) then
            writeln( 'Row found = ', row );
    end;

    exec sql close csr2;
end;