SQLPutData function (CLI) - Passing data value for a parameter

Sends large parameter values in pieces. SQLPutData() is called following an SQLParamData() call returning SQL_NEED_DATA to supply parameter data values.

Specification:

CLI 2.1
ODBC 1.0
ISO CLI

Syntax

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

Function arguments

Table 1. 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.
SQLLEN	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

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(). CLI generates a temporary file for each SQL_DATA_AT_EXEC parameter to which each piece of data is appended when SQLPutData() is called. The path in which CLI creates its temporary files can be set using the TEMPDIR keyword in the db2cli.ini file. If this keyword is not set, CLI attempts to write to the path specified by the environment variables TEMP or TMP. 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 connection handle of StatementHandle will fail if they involve changing any attribute or state of that connection; that is, the following function calls on the parent connection handle are also not permitted:

SQLSetConnectAttr()
SQLEndTran()

However, calls to the SQLEndTran() function specifying SQL_ROLLBACK as completion type are allowed when the SQL_ATTR_FORCE_ROLLBACK connection attribute is set, the StreamPutData configuration keyword is set to 1, and autocommit mode is enabled.

Should they be invoked during an SQL_NEED_DATA sequence, these functions 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 might also be reported on the final SQLParamData() call rather than at the time the SQLPutData() is called.

Table 2. 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 caused 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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY008	Operation was Canceled.	Asynchronous processing was enabled for StatementHandle. The function was called and before it completed execution, `SQLCancel()` was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle.
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. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetStmtAttr()`. Note: This SQLSTATE applies only to .Net applications.

Restrictions

A additional 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 support 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().

Example

  SQLCHAR buffer[BUFSIZ];
  size_t n = BUFSIZ;

  /* ... */
  
  /* passing data value for a parameter */
  cliRC = SQLPutData(hstmt, buffer, n);