Call Level Interface Guide and Reference

SQLPutData - Passing Data Value for A Parameter

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0 ISO CLI

SQLPutData() is called following an SQLParamData() call returning SQL_NEED_DATA to supply parameter data values. This function can be used to send large parameter values in pieces.

The information is returned in an SQL result set, which can be retrieved using the same functions that are used to process a result set generated by a query.

Syntax

SQLRETURN   SQLPutData       (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLPOINTER        DataPtr,           /* rgbValue */
                SQLINTEGER        StrLen_or_Ind);    /* cbValue */

Function Arguments

Table 144. SQLPutData Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle Input Statement handle.
SQLPOINTER DataPtr Input Pointer to the actual data, or portion of data, for a parameter. The data must be in the form specified in the SQLBindParameter() call that the application used when specifying the parameter.
SQLINTEGER StrLen_or_Ind Input The length of DataPtr. Specifies the amount of data sent in a call to SQLPutData() .
The amount of data can vary with each call for a given parameter. The application can also specify SQL_NTS or SQL_NULL_DATA for StrLen_or_Ind.
StrLen_or_Ind is ignored for all fixed length C buffer types, such as date, time, timestamp, and all numeric C buffer types.
For cases where the C buffer type is SQL_C_CHAR or SQL_C_BINARY, or if SQL_C_DEFAULT is specified as the C buffer type and the C buffer type default is SQL_C_CHAR or SQL_C_BINARY, this is the number of bytes of data in the DataPtr buffer.

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLPOINTER	DataPtr	Input	Pointer to the actual data, or portion of data, for a parameter. The data must be in the form specified in the `SQLBindParameter()` call that the application used when specifying the parameter.
SQLINTEGER	StrLen_or_Ind	Input	The length of `DataPtr`. Specifies the amount of data sent in a call to `SQLPutData()` . The amount of data can vary with each call for a given parameter. The application can also specify SQL_NTS or SQL_NULL_DATA for `StrLen_or_Ind`. `StrLen_or_Ind` is ignored for all fixed length C buffer types, such as date, time, timestamp, and all numeric C buffer types. For cases where the C buffer type is SQL_C_CHAR or SQL_C_BINARY, or if SQL_C_DEFAULT is specified as the C buffer type and the C buffer type default is SQL_C_CHAR or SQL_C_BINARY, this is the number of bytes of data in the `DataPtr` buffer.

Usage

For a description on the SQLParamData() and SQLPutData() sequence, refer to Sending/Retrieving Long Data in Pieces.

The application calls SQLPutData() after calling SQLParamData() on a statement in the SQL_NEED_DATA state to supply the data values for an SQL_DATA_AT_EXEC parameter. Long data can be sent in pieces via repeated calls to SQLPutData(). After all the pieces of data for the parameter have been sent, the application calls SQLParamData() again to proceed to the next SQL_DATA_AT_EXEC parameter, or, if all parameters have data values, to execute the statement.

SQLPutData() cannot be called more than once for a fixed length C buffer type, such as SQL_C_LONG.

After an SQLPutData() call, the only legal function calls are SQLParamData(), SQLCancel(), or another SQLPutData() if the input data is character or binary data. As with SQLParamData(), all other function calls using this statement handle will fail. In addition, all function calls referencing the parent hdbc of StatementHandle will fail if they involve changing any attribute or state of that connection; that is, the following function calls on the parent hdbc are also not permitted:

SQLAllocConnect()
SQLAllocStmt()
SQLSetConnectAttr()
SQLNativeSql()
SQLTransact()

Should they be invoked during an SQL_NEED_DATA sequence, these function will return SQL_ERROR with SQLSTATE of HY010 and the processing of the SQL_DATA_AT_EXEC parameters will not be affected.

If one or more calls to SQLPutData() for a single parameter results in SQL_SUCCESS, attempting to call SQLPutData() with StrLen_or_Ind set to SQL_NULL_DATA for the same parameter results in an error with SQLSTATE of 22005. This error does not result in a change of state; the statement handle is still in a Need Data state and the application can continue sending parameter data.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Some of the following diagnostics conditions may also be reported on the final SQLParamData() call rather than at the time the SQLPutData() is called.

Table 145. SQLPutData SQLSTATEs

SQLSTATE Description Explanation
01004 Data truncated. The data sent for a numeric parameter was truncated without the loss of significant digits.
Timestamp data sent for a date or time column was truncated.
Function returns with SQL_SUCCESS_WITH_INFO.
22001 String data right truncation. More data was sent for a binary or char data than the data source can support for that column.
22003 Numeric value out of range. The data sent for a numeric parameter cause the whole part of the number to be truncated when assigned to the associated column.
SQLPutData() was called more than once for a fixed length parameter.
22005 Error in assignment. The data sent for a parameter was incompatible with the data type of the associated table column.
22007 Invalid datetime format. The data value sent for a date, time, or timestamp parameters was invalid.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008 Operation canceled.
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle.
The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application.
HY009 Invalid argument value. The argument DataPtr was a NULL pointer, and the argument StrLen_or_Ind was neither 0 nor SQL_NULL_DATA.
HY010 Function sequence error. The statement handle StatementHandle must be in a need data state and must have been positioned on an SQL_DATA_AT_EXEC parameter via a previous SQLParamData() call.
HY090 Invalid string or buffer length. The argument DataPtr was not a NULL pointer, and the argument StrLen_or_Ind was less than 0, but not equal to SQL_NTS or SQL_NULL_DATA.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
01004	Data truncated.	The data sent for a numeric parameter was truncated without the loss of significant digits. Timestamp data sent for a date or time column was truncated. Function returns with SQL_SUCCESS_WITH_INFO.
22001	String data right truncation.	More data was sent for a binary or char data than the data source can support for that column.
22003	Numeric value out of range.	The data sent for a numeric parameter cause the whole part of the number to be truncated when assigned to the associated column. `SQLPutData()` was called more than once for a fixed length parameter.
22005	Error in assignment.	The data sent for a parameter was incompatible with the data type of the associated table column.
22007	Invalid datetime format.	The data value sent for a date, time, or timestamp parameters was invalid.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008	Operation canceled.	Asynchronous processing was enabled for the `StatementHandle`. The function was called and before it completed execution, `SQLCancel()` was called on the `StatementHandle`. Then the function was called again on the `StatementHandle`. The function was called and, before it completed execution, `SQLCancel()` was called on the `StatementHandle` from a different thread in a multithread application.
HY009	Invalid argument value.	The argument `DataPtr` was a NULL pointer, and the argument `StrLen_or_Ind` was neither 0 nor SQL_NULL_DATA.
HY010	Function sequence error.	The statement handle `StatementHandle` must be in a need data state and must have been positioned on an SQL_DATA_AT_EXEC parameter via a previous `SQLParamData()` call.
HY090	Invalid string or buffer length.	The argument `DataPtr` was not a NULL pointer, and the argument `StrLen_or_Ind` was less than 0, but not equal to SQL_NTS or SQL_NULL_DATA.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

Restrictions

A new value for StrLen_or_Ind, SQL_DEFAULT_PARAM, was introduced in ODBC 2.0, to indicate that the procedure is to use the default value of a parameter, rather than a value sent from the application. Since DB2 stored procedure arguments do not have the concept of default values, specification of this value for StrLen_or_Ind argument will result in an error when the CALL statement is executed since the SQL_DEFAULT_PARAM value will be considered an invalid length.

ODBC 2.0 also introduced the SQL_LEN_DATA_AT_EXEC(length) macro to be used with the StrLen_or_Ind argument. The macro is used to specify the sum total length of the entire data that would be sent for character or binary C data via the subsequent SQLPutData() calls. Since the DB2 ODBC driver does not need this information, the macro is not needed. An ODBC application calls SQLGetInfo() with the SQL_NEED_LONG_DATA_LEN option to check if the driver needs this information. The DB2 ODBC driver will return 'N' to indicate that this information is not needed by SQLPutData().

CLI Sample dtlob.c

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

 
/* From the CLI sample dtlob.c */
/* ... */
 
    /*
     * This paramter will use SQLPutData
     */
    blobInd = SQL_DATA_AT_EXEC;
    sqlrc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BLOB,
                          BUFSIZ, 0, (SQLPOINTER)inputParam, BUFSIZ, &blobInd);

References

[ Top of Page | Previous Page | Next Page ]