CHAPTER 5 Moving Data In and Out of Databases

Truncation of data for VARCHAR and CHAR columns

If the width of the input column is greater than the width of the destination column, Adaptive Server IQ truncates the data upon insertion. If the width of the input data is less than the width of the destination column, for CHAR or VARCHAR data types Adaptive Server IQ pads the data with spaces in the table upon insertion.

Variable width inserts to a VARCHAR column will not have trailing blanks trimmed, while fixed width inserts to a VARCHAR column will be trimmed. For example, assume that you are inserting into column varcolumn in a table called vartable. The following would constitute a fixed-width insert, where the value would not be trimmed because you explicitly say to include the two blanks (indicated by __ here):

INSERT INTO vartable VALUES (’box__’)

If instead you inserted the same value from a flat file using delimited input, it would be a variable-width insert, and the trailing blanks would be trimmed.

The following table illustrates how the ASCII conversion option works with the Adaptive Server IQ data types. The example inserts the data from the flat ASCII file shipinfo.t into the Adaptive Server IQ table lineitem and summarizes the content and format of the input data and the table.

 

Table 5-6: Input file conversion example

 

shipinfo.t

 

 

lineitem

 

 

 

 

 

 

 

 

 

 

 

 

column

format

width

column

datatype

width

 

 

 

 

 

 

l_shipmode

CHAR

15

l_shipmode

VARCHAR

30

 

 

 

 

 

 

l_quantity

ASCII

8

l_quantity

INT

4

 

 

 

 

 

 

For the l_shipmode column, you insert ASCII data into an ASCII column (that has a VARCHAR data type). Notice the width of the two columns is different. In order for the insert on this column and the subsequent l_quantity column to be correct, you specify the width of the l_shipmode column so the correct amount of input data is read at the correct position.

For the l_quantity column, you are inserting ASCII data into a binary column (INT data type). In order for the insert on this column to be correct, you must convert the input data into binary and indicate the width of the input column.

The command for this is shown in the following UNIX example.

LOAD TABLE lineitem( l_shipmode ASCII(15), l_quantity ASCII(8),

FILLER(1))

FROM ’/d1/MILL1/shipinfo.t’

PREVIEW ON

209

Page 229
Image 229
Sybase 12.4.2 manual 209