SQLPutData() - Pass a data value for a parameter

SQLPutData() supplies a parameter data value. This function can be used to send large parameter values in pieces. The information is returned in an SQL result set. This result set is retrieved by the same functions that process a result set that is generated by a query.

ODBC specifications for SQLPutData()

Table 1. SQLPutData() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
1.0 Yes Yes

Syntax

For 31-bit applications, use the following syntax:

SQLRETURN   SQLPutData       (SQLHSTMT          hstmt,
                              SQLPOINTER        rgbValue,
                              SQLINTEGER        cbValue);

For 64-bit applications, use the following syntax:

SQLRETURN   SQLPutData       (SQLHSTMT          hstmt,
                              SQLPOINTER        rgbValue,
                              SQLLEN            cbValue);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. SQLPutData() arguments
Data type Argument Use Description
SQLHSTMT hstmt input Statement handle.
SQLPOINTER rgbValue 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 (31-bit) or SQLLEN (64-bit) 1 cbValue input The length, in bytes, of rgbValue. 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 cbValue.

cbValue 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 rgbValue buffer.
Notes:
  1. For 64-bit applications, the data type SQLINTEGER, which was used in previous versions of Db2, is still valid. However, for maximum application portability, using SQLLEN is recommended.

Usage

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 using 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 fail. In addition, all function calls referencing the parent hdbc of hstmt 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:
  • SQLAllocHandle()
  • SQLSetConnectAttr()
  • SQLNativeSql()
  • SQLEndTran()
If they are invoked during an SQL_NEED_DATA sequence, these functions return SQL_ERROR with SQLSTATE of HY010 and the processing of the SQL_DATA_AT_EXEC parameters is not affected.

If one or more calls to SQLPutData() for a single parameter results in SQL_SUCCESS, attempting to call SQLPutData() with cbValue 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

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

Diagnostics

Some of the following diagnostic conditions are also reported on the final SQLParamData() call rather than at the time the SQLPutData() is called. The following table lists each SQLSTATE with a description and explanation for each value.

Table 3. SQLPutData() SQLSTATEs
SQLSTATE Description Explanation
01004 Data truncated. This SQLSTATE is returned for one or more of the following reasons:
  • The data sent for a numeric parameter is truncated without the loss of significant digits.
  • Timestamp data sent for a date or time column is truncated.
(SQLPutData() returns SQL_SUCCESS_WITH_INFO for this SQLSTATE.)
08S01 Communication link failure. The communication link between the application and data source fails before the function completes.
22001 String data right truncation. More data is sent for a binary or char data than the data source can support for that column.
22008 Invalid datetime format or datetime field overflow. The data value sent for a date, time, or timestamp parameters is invalid.
22018 Error in assignment. The data sent for a parameter is incompatible with the data type of the associated table column.
HY001 Memory allocation failure. Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY009 Invalid use of a null pointer. The argument rgbValue is a NULL pointer, and the argument cbValue is neither 0 nor SQL_NULL_DATA.
HY010 Function sequence error. The statement handle hstmt must be in a need data state and must have been positioned on an SQL_DATA_AT_EXEC parameter using a previous SQLParamData() call.
HY019 Numeric value out of range. This SQLSTATE is returned for one or more of the following reasons:
  • The data sent for a numeric parameter causes the whole part of the number to be truncated when it is assigned to the associated column.
  • SQLPutData() is called more than once for a fixed-length parameter.
HY090 Invalid string or buffer length. The argument rgbValue is not a null pointer, and the argument cbValue is less than 0, but not equal to SQL_NTS or SQL_NULL_DATA.

Restrictions

A new value for pcbValue, 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. Because the concept of default values does not apply to Db2 stored procedure arguments, specification of this value for the pcbValue argument results in an error when the CALL statement is executed because the SQL_DEFAULT_PARAM value is considered an invalid length.

ODBC 2.0 also introduced the SQL_LEN_DATA_AT_EXEC(length) macro to be used with the pcbValue argument. The macro is used to specify the sum total length, in bytes, of the entire data that would be sent for character or binary C data using the subsequent SQLPutData() calls. Because the Db2 ODBC driver does not need this information, the macro is not needed. To check if the driver needs this information, call SQLGetInfo() with the InfoType argument set to SQL_NEED_LONG_DATA_LEN. The Db2 ODBC driver returns 'N' to indicate that this information is not needed by SQLPutData().

Example

Refer to the function SQLGetData() for a related example.