Extracts individual LONG BINARY and LONG VARCHAR cells to individual operating system files on the server.
BFILE( file-name-expression, large-object-column )
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 path name that is supported by the file system.
large-object-column The name of the LONG BINARY or LONG VARCHAR column.
BFILE returns:
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. Use SUBSTRING64 or BYTE_SUBSTR64 to retrieve LONG BINARY cells greater than 2GB using a SELECT (SELECT, OPEN CURSOR). Use SUBSTRING64 to retrieve LONG VARCHAR cells greater than 2GB using a SELECT (SELECT, OPEN CURSOR). Some connection drivers, for example ODBC, JDBC™, and Open Client™, do not allow more than 2GB to be returned in one SELECT.
You can use BFILE with or without the data extraction facility.
This example shows how to use BFILE to extract data from the LONG BINARY column lobcol, which is created and loaded in the “Load example”. To write the data in files that can be used as secondary files in a load, enter:
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.
