Call Level Interface Guide and Reference

SQLBindFileToCol - Bind LOB File Reference to LOB Column

Purpose

Specification: DB2 CLI 2.1

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

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

Syntax

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

Function Arguments

Table 17. SQLBindFileToCol Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLUSMALLINT icol input Number identifying the column. Columns are numbered sequentially, from left to right, starting at 1.
SQLPOINTER 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 the StatementHandle. This is either the complete path name of the file(s) or a relative file name(s). If relative file name(s) are provided, they are appended to the current path of the running application. 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 the StatementHandle. If this pointer is NULL, then a length of SQL_NTS is assumed.
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 (array of file options) to be used when writing the file at the time of the next fetch using the StatementHandle. The following FileOptions are supported:

SQL_FILE_CREATE
Create a new file. If a file by this name already exists, SQL_ERROR will be 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 chosen per file, there is no default.
SQLSMALLINT MaxFileNameLength input This specifies the length of the FileName buffer or, if the application uses SQLFetchScroll() to retrieve multiple rows for the LOB column, this 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) in bytes 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).

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLUSMALLINT	`icol`	input	Number identifying the column. Columns are numbered sequentially, from left to right, starting at 1.
SQLPOINTER	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 the `StatementHandle`. This is either the complete path name of the file(s) or a relative file name(s). If relative file name(s) are provided, they are appended to the current path of the running application. 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 the `StatementHandle`. If this pointer is NULL, then a length of `SQL_NTS` is assumed. 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 (array of file options) to be used when writing the file at the time of the next fetch using the `StatementHandle`. The following `FileOptions` are supported: SQL_FILE_CREATE Create a new file. If a file by this name already exists, SQL_ERROR will be 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 chosen per file, there is no default.
SQLSMALLINT	MaxFileNameLength	input	This specifies the length of the `FileName` buffer or, if the application uses `SQLFetchScroll()` to retrieve multiple rows for the LOB column, this 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) in bytes 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).

Usage

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 SQLFetchScroll() is called, the data for any column which has been bound to a LOB file reference is written to the file or files 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 SQLFetchScroll() is used to retrieve multiple rows for the LOB column, FileName, FileNameLength, and FileOptions point to an array of LOB file reference variables. In this case, MaxFileNameLength specifies the length of each element in the FileName array and is used by DB2 CLI 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 SQLFetchScroll() call. The StringLength and IndicatorValue pointers each point to an array whose elements are updated upon the SQLFetchScroll() call.

Using SQLFetchScroll(), 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. Only column-wise binding of arrays of file references is supported with SQLFetchScroll().

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 18. SQLBindFileToCol SQLSTATEs

SQLSTATE Description Explanation
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY002 Invalid column number. The value specified for the argument icol was less than 1.
The value specified for the argument icol exceeded the maximum number of columns supported by the data source.
HY009 Invalid argument value. FileName, StringLength 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 CLI 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.

SQLSTATE	Description	Explanation
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
58004	Unexpected system failure.	Unrecoverable system error.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY002	Invalid column number.	The value specified for the argument `icol` was less than 1. The value specified for the argument `icol` exceeded the maximum number of columns supported by the data source.
HY009	Invalid argument value.	`FileName`, `StringLength` 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 CLI 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.

Restrictions

This function is not available when connected to DB2 servers that do not support Large Object data types. Call SQLGetFunctions() with the function type set to SQL_API_SQLBINDFILETOCOL and check the SupportedPtr output argument to determine if the function is supported for the current connection.

CLI Sample dtlob.c

(The complete sample dtlob.c is also available here .)

 
/* From the CLI sample dtlob.c */
/* ... */
    
    /* bind a file to the BLOB column */
    rc = SQLBindFileToCol(hstmt, 1, fileName, &fileNameLength, &fileOption,
                          14, NULL, &fileInd);

References

[ Top of Page | Previous Page | Next Page ]