Call Level Interface Guide and Reference

SQLBindFileToParam - Bind LOB File Reference to LOB Parameter

Purpose

Specification: DB2 CLI 2.1

SQLBindFileToParam() is used to associate (bind) a parameter marker in an SQL statement to a file reference or an array of file references. This enables data from the file to be transferred directly into a LOB column when that statement is subsequently executed.

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 calling SQLExecute() or SQLExecDirect(), the application must make sure that this information is available in the deferred input buffers. These values can be changed between SQLExecute() calls.

Syntax

SQLRETURN SQLBindFileToParam (SQLHSTMT          StatementHandle,
                              SQLUSMALLINT      TargetType,
                              SQLSMALLINT       DataType,
                              SQLCHAR      *FAR FileName,
                              SQLSMALLINT  *FAR FileNameLength,
                              SQLUINTEGER  *FAR FileOptions,
                              SQLSMALLINT       MaxFileNameLength,
                              SQLINTEGER   *FAR IndicatorValue);

Function Arguments

Table 19. SQLBindFileToParam Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLUSMALLINT TargetType input Parameter marker number. Parameters are numbered sequentially, from left to right, starting at 1.
SQLSMALLINT DataType input SQL Data Type of the column. The data type must be one of:

SQL_BLOB
SQL_CLOB
SQL_DBCLOB

SQLCHAR * FileName input (deferred) Pointer to the location that will contain the file name or an array of file names when the statement (StatementHandle) is executed. This is either the complete path name of the file or a relative file name. If a relative file name is provided, it is appended to the current path of the client process.
This argument 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 SQLExecute() or SQLExecDirect() 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 an array of file options) to be used when reading the file. The location will be accessed when the statement (StatementHandle) is executed. Only one option is supported (and it must be specified):

SQL_FILE_READ
A regular file that can be opened, read and closed. (The length is computed when the file is opened)

This pointer cannot be NULL.
SQLSMALLINT MaxFileNameLength input This specifies the length of the FileName buffer. If the application calls SQLParamOptions() to specify multiple values for each parameter, this is the length of each element in the FileName array.
SQLINTEGER * IndicatorValue output (deferred) Pointer to the location that contains an indicator value (or array of values), which is set to SQL_NULL_DATA if the data value of the parameter is to be null. It must be set to 0 (or the pointer can be set to null) when the data value is not null.

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLUSMALLINT	TargetType	input	Parameter marker number. Parameters are numbered sequentially, from left to right, starting at 1.
SQLSMALLINT	DataType	input	SQL Data Type of the column. The data type must be one of: SQL_BLOB SQL_CLOB SQL_DBCLOB
SQLCHAR *	FileName	input (deferred)	Pointer to the location that will contain the file name or an array of file names when the statement (`StatementHandle`) is executed. This is either the complete path name of the file or a relative file name. If a relative file name is provided, it is appended to the current path of the client process. This argument 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 `SQLExecute()` or `SQLExecDirect()` 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 an array of file options) to be used when reading the file. The location will be accessed when the statement (`StatementHandle`) is executed. Only one option is supported (and it must be specified): SQL_FILE_READ A regular file that can be opened, read and closed. (The length is computed when the file is opened) This pointer cannot be NULL.
SQLSMALLINT	MaxFileNameLength	input	This specifies the length of the `FileName` buffer. If the application calls `SQLParamOptions()` to specify multiple values for each parameter, this is the length of each element in the `FileName` array.
SQLINTEGER *	IndicatorValue	output (deferred)	Pointer to the location that contains an indicator value (or array of values), which is set to SQL_NULL_DATA if the data value of the parameter is to be null. It must be set to 0 (or the pointer can be set to null) when the data value is not null.

Usage

The application calls SQLBindFileToParam() once for each parameter marker whose value should be obtained directly from a file when a statement is executed. Before the statement is executed, FileName, FileNameLength, and FileOptions values must be set. When the statement is executed, the data for any parameter which has been bound using SQLBindFIleToParam() is read from the referenced file and passed to the server.

If the application uses SQLParamOptions() to specify multiple values for each parameter, then 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.

A LOB parameter marker can be associated with (bound to) an input file using SQLBindFileToParam(), or with a stored buffer using SQLBindParameter(). The most recent bind parameter function call determines the type of binding that is in effect.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 20. SQLBindFileToParam 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.
HY004 SQL data type out of range. The value specified for DataType was not a valid SQL type for this function call.
HY009 Invalid argument value. FileName, FileOptions FileNameLength, 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 input argument MaxFileNameLength was less than 0.
HY093 Invalid parameter number. The value specified for TargetType was either less than 1 or greater than the maximum number of parameters supported.
HYC00 Driver not capable. The server does not support Large Object data types.

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.
HY004	SQL data type out of range.	The value specified for `DataType` was not a valid SQL type for this function call.
HY009	Invalid argument value.	`FileName`, `FileOptions` `FileNameLength`, 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 input argument `MaxFileNameLength` was less than 0.
HY093	Invalid parameter number.	The value specified for `TargetType` was either less than 1 or greater than the maximum number of parameters supported.
HYC00	Driver not capable.	The server does not support Large Object data types.

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_SQLBINDFILETOPARAM 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 the file-parameter */
    rc = SQLBindFileToParam(hstmt, 3, SQL_BLOB, fileName, &fileNameLength,
                            &fileOption, 14, &fileInd);

References

[ Top of Page | Previous Page | Next Page ]