Sybase 12.4.2 manual Column width issues, Using the Ascii conversion option, 208

Converting data on insertion

Column width issues

Adaptive Server IQ assumes the width of the input data is the same as the destination column width and reads the input file accordingly. If they are not the same width, Adaptive Server IQ may read too few or too many bytes of the input file for that column. The result is that the read for that column may be incorrect, and the reads for subsequent columns in the input file will be incorrect, because they will not start at the correct position in the input file.

For example, if input_column1 is 15 bytes wide and destination_column1 is 10 bytes wide, and you do not specify the ASCII conversion option, Adaptive Server IQ assumes the input column is only 10 bytes wide. This is fine for destination_column1, because the input data is truncated to 10 bytes in any case. But it also means that Adaptive Server IQ assumes that the next column in the input file starts at byte 11, which is still in the middle of the first column, instead of at byte 16, which is the correct starting position of the next column.

Conversely, if input_column1 is 10 bytes wide and destination_column1 is 15 bytes wide, and you do not specify the ASCII conversion option, Adaptive Server IQ assumes the input column is 15 bytes wide. This means that Adaptive Server IQ reads all of input_column1 plus 5 bytes into the next column in the input file and inserts this value into destination_column1. So, the value inserts into destination_column1 and all subsequent columns are incorrect.

To prevent such problems, use the ASCII conversion option. With this option, Adaptive Server IQ provides several ways to specify the fixed or variable width of an input column. Your input data can contain fixed width input columns with a specific size in bytes, variable width input columns with column delimiters, and variable width input columns defined by binary prefix bytes.

Using the ASCII conversion option

Use the ASCII conversion option to either:

•Convert ASCII input data to binary and specify the width of the input column so data can be read in correctly for that column, or

•Insert ASCII data into an ASCII data type column when the width of the input column is different from the width of the destination column. This option lets you specify how much of the input data it should read for each column.

You can use this option with any of the Adaptive Server IQ data types, with 1, 2, or 4 prefix bytes, and with a column delimiter.

208