The IQ data extraction facility includes the BFILE function, which allows you to extract individual LONG BINARY and LONG VARCHAR cells to individual operating system files on the server. The BFILE function can be used with or without the data extraction facility.
Syntax:
BFILE( file-name-expression, large-object-column )
Parameters:
file-name-expression The name of the output file into which the LONG BINARY or LONG VARCHAR data is written. This file name can be up to (32K -1) bytes in length, but must be a valid pathname supported by the file system.
large-object-column The name of the LONG BINARY or LONG VARCHAR column.
BFILE returns the following values:
1, if the file is successfully written
0, if the file is not successfully opened or written
NULL, if the LONG BINARY or LONG VARCHAR cell value is NULL
If the LONG BINARY or LONG VARCHAR cell value is NULL, no file is opened and no data is written.
The file path is relative to where the server was started and the open and write operations execute with the permissions of the server process. Tape devices are not supported for the BFILE output file.
LONG BINARY and LONG VARCHAR cells retrieved other than with the BFILE function (that is, retrieved through the client/server database connection later) are limited in size to a maximum length of 2GB. The SUBSTRING64 or BYTE_SUBSTR64 function must be used to retrieve LONG BINARY cells greater than 2GB using a SELECT (SELECT, OPEN CURSOR). The SUBSTRING64 function must be used to retrieve LONG VARCHAR cells greater than 2GB using a SELECT (SELECT, OPEN CURSOR). Also note that some connection drivers, for example ODBC, JDBC, and Open Client™, do not allow more than 2GB to be returned in one SELECT.
This example shows the use of the BFILE function to extract data from the LONG BINARY column lobcol, which is created and loaded in the “Load example”. The following command writes the data in files which can be used as secondary files in a load.
SELECT c1, filename, ext, ‘../myoutput/’ + TRIM(filename) + ‘.’ + TRIM(ext) fname, BFILE(fname, lobcol) FROM ltab WHERE lobcol IS NOT NULL AND ext IS NOT NULL
This command generates the file name with extension boston.jpg for lobcol in row 1 and the file name with extension map_of_concord.bmp for lobcol in row 2.
