Load LONG BINARY and LONG VARCHAR data using the extended syntax of the LOAD TABLE statement. You can load large object data of unlimited size, unless restricted by the operating system, from a primary file in ASCII or BCP format. The maximum length of fixed-width data loaded from a primary file into large object columns is 32K - 1.
You can also specify a secondary load file in the primary load file. Each individual secondary data file contains exactly one LONG BINARY or LONG VARCHAR cell value.
LOAD [ INTO ] TABLE [ owner ].table-name
... ( column-name load-column-specification [, ...] )
... FROM 'filename-string' [, ...]
... [ QUOTES { ON | OFF } ]
... ESCAPES OFF
... [ FORMAT { ascii | binary | bcp } ]
... [ DELIMITED BY 'string' ]
...
load-column-specification:
...
| { BINARY | ASCII } FILE( integer )
| { BINARY | ASCII } FILE ( ‘string‘ )
The keywords BINARY FILE (for LONG BINARY) or ASCII FILE (for LONG VARCHAR) specify to the load that the primary input file for the column contains the path of the secondary file (which contains the LONG BINARY or LONG VARCHAR cell value), rather than the LONG BINARY or LONG VARCHAR data itself. The secondary file pathname can be either fully qualified or relative. If the secondary file pathname is not fully qualified, then the path is relative to the directory in which the server was started. Tape devices are not supported for the secondary file.
Sybase IQ supports loading LONG BINARY and LONG
VARCHAR values of unlimited length (subject to operating
system restrictions) in the primary load file. When binary data
of hexadecimal format is loaded into a LONG BINARY column
from a primary file, Sybase IQ requires that the total number of hexadecimal
digits is an even number. The error “Odd
length of binary data value detected on column” is
reported, if the cell value contains an odd number of hexadecimal
digits. Input files for LONG BINARY loads should
always contain an even number of hexadecimal digits.
Sybase IQ does not support loading large object columns from primary files using LOAD TABLE…FORMAT BINARY. You can load large object data in binary format from secondary files. For details on loading data using binary format, see “Using binary load format” in Chapter 7, “Moving Data In and Out of Databases” of the System Administration Guide: Volume 1.
For LOAD TABLE FORMAT BCP, the load specification may contain only column names, NULL, and ENCRYPTED. This means that you cannot use secondary files when loading LONG BINARY and LONG VARCHAR columns using the LOAD TABLE FORMAT BCP option. See LOAD TABLE statement in Chapter 1, “SQL Statements” in Reference: Statements and Options.
This example shows the SQL statements to create and load a table with LONG BINARY data.
CREATE TABLE ltab (c1 INT, filename CHAR(64), ext CHAR(6), lobcol LONG BINARY NULL);
LOAD TABLE ltab ( c1, filename, ext NULL(‘NULL’), lobcol BINARY FILE (‘,’) NULL(‘NULL’) ) FROM ‘abc.inp’ QUOTES OFF ESCAPES OFF;
The primary file abc.inp contains this data:
1,boston,jpg,/s1/loads/lobs/boston.jpg, 2,map_of_concord,bmp,/s1/loads/maprs/concord.bmp, 3,zero length test,NULL,, 4,null test,NULL,NULL,
After the LONG BINARY data is loaded into table tab, the first and second rows for column lobcol contain the contents of files boston.jpg and concord.bmp, respectively. The third and fourth rows contain a zero-length value and NULL, respectively.
The database option SECONDARY_FILE_ERROR allows you to specify the action of the load if an error occurs while opening or reading from a secondary BINARY FILE or ASCII FILE.
If SECONDARY_FILE_ERROR is ON, the load rolls back if an error occurs while opening or reading from a secondary BINARY FILE or ASCII FILE.
If SECONDARY_FILE_ERROR is OFF (the default), the load continues, regardless of any errors that occur while opening or reading from a secondary BINARY FILE or ASCII FILE. The LONG BINARY or LONG VARCHAR cell is left with one of these values:
NULL, if the column allows nulls
Zero-length value, if the column does not allow nulls
Any user can set SECONDARY_FILE_ERROR for the PUBLIC group or temporary; it takes effect immediately.
When logging integrity constraint violations to the load error ROW LOG file, the information logged for a LONG BINARY or LONG VARCHAR column is:
Actual text as read from the primary data file, if the logging occurs within the first pass of the load operation
Zero-length value, if the logging occurs within the second pass of the load operation
The LOAD TABLE...STRIP option has no effect on LONG VARCHAR data. Trailing blanks are not stripped from LONG VARCHAR data, even if the STRIP option is on.
The LOAD TABLE...QUOTES option does not apply to loading LONG BINARY (BLOB) or LONG VARCHAR (CLOB) data from the secondary file, regardless of its setting, A leading or trailing quote is loaded as part of CLOB data. Two consecutive quotes between enclosing quotes are loaded as two consecutive quotes with the QUOTES ON option.
Partial multibyte LONG VARCHAR data is truncated during the load according to the value of the TRIM_PARTIAL_MBC database option:
If TRIM_PARTIAL_MBC is ON, a partial multibyte character is truncated for both primary data and the LOAD with ASCII FILE option.
If TRIM_PARTIAL_MBC is OFF, the LOAD with ASCII FILE option handles the partial multibyte character according to the value of the SECONDARY_FILE_ERROR database option.
Table 6-1 lists how a trailing multibyte character is loaded, depending on the values of TRIM_PARTIAL_MBC and SECONDARY_FILE_ERROR.
TRIM_PARTIAL_MBC |
SECONDARY_FILE_ERROR |
Trailing partial multibyte character found |
|---|---|---|
ON |
ON/OFF |
Trailing partial multibyte character truncated |
OFF |
ON |
Cell — null, if null allowed LOAD error — roll back, if null not allowed |
OFF |
OFF |
Cell — null, if null allowed Cell — zero-length, if null not allowed |
For information on support of large object variables by the LOAD TABLE, INSERT…VALUES, INSERT…SELECT, INSERT…LOCATION, SELECT…INTO, and UPDATE SQL statements, see Chapter 7, “Large Object Data Types.”
