SQLBindFileToCol() - Associate a column with a file reference

SQLBindFileToCol() associates a LOB column in a result set to a file reference or an array of file references. This association enables data in that column to be transferred directly into a file when each row is fetched for the statement handle.

ODBC specifications for SQLBindFileToCol()

Table 1. SQLBindFileToCol() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
No No No

Syntax

SQLRETURN   SQLBindFileToCol (SQLHSTMT          StatementHandle,   /* hstmt */
                              SQLUSMALLINT      ColumnNumber,      /* icol */
                              SQLCHAR           *FileName,
                              SQLSMALLINT       *FileNameLength,
                              SQLUINTEGER       *FileOptions,
                              SQLSMALLINT       MaxFileNameLength,
                              SQLINTEGER        *StringLength,
                              SQLINTEGER        *IndicatorValue);

Function arguments

The data type, use, and description for each argument in this function are similar to those of SQLBindCol() arguments.

Table 2. SQLBindFileToCol arguments
Data type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLUSMALLINT icol input Number that identifies the column. Columns are numbered sequentially, from left to right, starting at 1.
SQLCHAR * FileName input (deferred)

Pointer to the location that will contain the file name or an array of file names at the time of the next fetch using StatementHandle. The name is the absolute path name of each file.

This pointer cannot be NULL.
SQLSMALLINT * FileNameLength input (deferred) Pointer to the location that will contain the length of the file name or an array of lengths at the time of the next fetch using StatementHandle. If this pointer is NULL, ODBC treats FileName as a null-terminated string. The result is the same as if a length of SQL_NTS is passed.

The maximum value of the file name length is 255.
SQLUINTEGER * FileOptions input (deferred) Pointer to the location that will contain the file option or an array of file options to be used when data is written to the file at the time of the next fetch using StatementHandle. The following FileOptions values are supported:
SQL_FILE_CREATE
Create a new file. If a file with this name already exists, SQL_ERROR is returned.
SQL_FILE_OVERWRITE
If the file already exists, overwrite it. Otherwise, create a new file.
SQL_FILE_APPEND
If the file already exists, append the data to it. Otherwise, create a new file.

Only one option can be specified for a file. There is no default.
SQLSMALLINT MaxFileNameLength input Specifies the length of the FileName buffer. If the application uses SQLExtendedFetch() to retrieve multiple rows for the LOB column, specifies the length of each element in the FileName array.
SQLINTEGER * StringLength output (deferred) Pointer to the location that contains the length or array of lengths of the LOB data that is returned. If this pointer is NULL, nothing is returned.
SQLINTEGER * IndicatorValue output (deferred) Pointer to the location that contains an indicator value or array of values that indicate if the fetched LOB value is NULL.

Usage

The LOB file reference arguments (file name, file name length, file reference options) refer to a file in the application's environment (on the client). Before fetching each row, the application must ensure that these variables contain the name of a file, the length of the file name, and a file option (create, overwrite, or append). These values can be changed between row fetch operations.

The application calls SQLBindFileToCol() once for each column that should be transferred directly to a file when a row is fetched. LOB data is written directly to the file without any data conversion, and without appending null-terminators.

FileName, FileNameLength, and FileOptions must be set before each fetch. When SQLFetch() or SQLExtendedFetch() is called, the data for any column that has been bound to a LOB file reference is written to the file or files that are pointed to by that file reference. Errors associated with the deferred input argument values of SQLBindFileToCol() are reported at fetch time. The LOB file reference, and the deferred StringLength and IndicatorValue output arguments are updated between fetch operations.

If SQLExtendedFetch() is used to retrieve multiple rows for the LOB column, FileName, FileNameLength, and FileOptions point to arrays of LOB file reference variables. In this case, MaxFileNameLength specifies the length of each element in the FileName array and is used by Db2 ODBC to determine the location of each element in the FileName array. The contents of the array of file references must be valid at the time of the SQLExtendedFetch() call. The StringLength and IndicatorValue pointers each point to an array whose elements are updated when SQLExtendedFetch() is called.

With SQLExtendedFetch(), multiple LOB values can be written to multiple files, or to the same file depending on the file names specified. If writing to the same file, the SQL_FILE_APPEND file option should be specified for each file name entry.

Return codes

After you call SQLBindFileToCol(), it returns one of the following values:
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 3. SQLBindFileToCol SQLSTATEs
SQLSTATE Description Explanation
08S01 Communication link failure. The communication link between the application and data source fails before the function completes.
58004 Unexpected system failure. Unrecoverable system error.
HY002 Invalid column number. The value specified for the argument icol was less than 1.
HY001 Memory allocation failure. Db2 ODBC is unable to allocate memory required to support execution or completion of the function.
HY009 Invalid use of a null pointer. FileName or FileOptions is a null pointer.
HY010 Function sequence error. The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.

The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
HY013 Unexpected memory handling error. Db2 ODBC was unable to access memory required to support execution or completion of the function.
HY090 Invalid string or buffer length. The value specified for the argument MaxFileNameLength was less than 0.
HYC00 Driver not capable. The application is currently connected to a data source that does not support large objects.

Example

/* Bind a file to a BLOB column */
rc = SQLBindFileToCol(hstmt,
                      1,
                      fileName,
                      &fileNameLength,
                      &fileOption,
                      14,
                      NULL,
                      &fileInd);