SQLPutData Function

SQLPutData Function

Switch on low bandwidth view

Collapse All

Language Filter

SQLPutData Function

Conformance: Version Introduced: ODBC 1.0 Standards Compliance: ISO 92

Summary: SQLPutData allows an application to send data for a parameter or column to the driver at statement execution time. This function can be used to send character or binary data values in parts to a column with a character, binary, or data source–specific data type (for example, parameters of the SQL_LONGVARBINARY or SQL_LONGVARCHAR types). SQLPutData supports binding to a Unicode C data type, even if the underlying driver does not support Unicode data.

SQLRETURN SQLPutData(
     SQLHSTMT     StatementHandle,
     SQLPOINTER     DataPtr,
          SQLLEN     StrLen_or_Ind);

Arguments

StatementHandle: [Input] Statement handle.

DataPtr: [Input] Pointer to a buffer containing the actual data for the parameter or column. The data must be in the C data type specified in the ValueType argument of SQLBindParameter (for parameter data) or the TargetType argument of SQLBindCol (for column data).

StrLen_or_Ind

[Input] 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 or column. StrLen_or_Ind is ignored unless it meets one of the following conditions:

StrLen_or_Ind is SQL_NTS, SQL_NULL_DATA, or SQL_DEFAULT_PARAM.
The C data type specified in SQLBindParameter or SQLBindCol is SQL_C_CHAR or SQL_C_BINARY.
The C data type is SQL_C_DEFAULT, and the default C data type for the specified SQL data type is SQL_C_CHAR or SQL_C_BINARY.

For all other types of C data, if StrLen_or_Ind is not SQL_NULL_DATA or SQL_DEFAULT_PARAM, the driver assumes that the size of the *DataPtr buffer is the size of the C data type specified with ValueType or TargetType and sends the entire data value. For more information, see Converting Data from C to SQL Data Types in Appendix D: Data Types.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLPutData returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value can be obtained by calling SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle. The following table lists the SQLSTATE values commonly returned by SQLPutData and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

SQLSTATE	Error	Description
01000	General warning	Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
01004	String data, right truncated	String or binary data returned for an output parameter resulted in the truncation of nonblank character or non-NULL binary data. If it was a string value, it was right-truncated. (Function returns SQL_SUCCESS_WITH_INFO.)
07006	Restricted data type attribute violation	The data value identified by the ValueType argument in SQLBindParameter for the bound parameter could not be converted to the data type identified by the ParameterType argument in SQLBindParameter.
07S01	Invalid use of default parameter	A parameter value, set with SQLBindParameter, was SQL_DEFAULT_PARAM, and the corresponding parameter did not have a default value.
08S01	Communication link failure	The communication link between the driver and the data source to which the driver was connected failed before the function completed processing.
22001	String data, right truncation	The assignment of a character or binary value to a column resulted in the truncation of nonblank (character) or non-null (binary) characters or bytes. The SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo was "Y", and more data was sent for a long parameter (the data type was SQL_LONGVARCHAR, SQL_LONGVARBINARY, or a long data source–specific data type) than was specified with the StrLen_or_IndPtr argument in SQLBindParameter. The SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo was "Y", and more data was sent for a long column (the data type was SQL_LONGVARCHAR, SQL_LONGVARBINARY, or a long data source–specific data type) than was specified in the length buffer corresponding to a column in a row of data that was added or updated with SQLBulkOperations or updated with SQLSetPos.
22003	Numeric value out of range	The data sent for a bound numeric parameter or column caused the whole (as opposed to fractional) part of the number to be truncated when assigned to the associated table column. Returning a numeric value (as numeric or string) for one or more input/output or output parameters would have caused the whole (as opposed to fractional) part of the number to be truncated.
22007	Invalid datetime format	The data sent for a parameter or column that was bound to a date, time, or timestamp structure was, respectively, an invalid date, time, or timestamp. An input/output or output parameter was bound to a date, time, or timestamp C structure, and a value in the returned parameter was, respectively, an invalid date, time, or timestamp. (Function returns SQL_SUCCESS_WITH_INFO.)
22008	Datetime field overflow	A datetime expression computed for an input/output or output parameter resulted in a date, time, or timestamp C structure that was invalid.
22012	Division by zero	An arithmetic expression calculated for an input/output or output parameter resulted in division by zero.
22015	Interval field overflow	The data sent for an exact numeric or interval column or parameter to an interval SQL data type caused a loss of significant digits. Data was sent for an interval column or parameter with more than one field, was converted to a numeric data type, and had no representation in the numeric data type. The data sent for column or parameter data was assigned to an interval SQL type, and there was no representation of the value of the C type in the interval SQL type. The data sent for an exact numeric or interval C column or parameter to an interval C type caused a loss of significant digits. The data sent for column or parameter data was assigned to an interval C structure, and there was no representation of the data in the interval data structure.
22018	Invalid character value for cast specification	The C type was an exact or approximate numeric, a datetime, or an interval data type; the SQL type of the column was a character data type; and the value in the column or parameter was not a valid literal of the bound C type. The SQL type was an exact or approximate numeric, a datetime, or an interval data type; the C type was SQL_C_CHAR; and the value in the column or parameter was not a valid literal of the bound SQL type.
HY000	General error	An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLGetDiagRec in the *MessageText buffer describes the error and its cause.
HY001	Memory allocation error	The driver was 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 use of null pointer	(DM) The argument DataPtr was a null pointer, and the argument StrLen_or_Ind was not 0, SQL_DEFAULT_PARAM, or SQL_NULL_DATA.
HY010	Function sequence error	(DM) The previous function call was not a call to SQLPutData or SQLParamData. (DM) An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
HY013	Memory management error	The function call could not be processed because the underlying memory objects could not be accessed, possibly because of low memory conditions.
HY019	Non-character and non-binary data sent in pieces	SQLPutData was called more than once for a parameter or column, and it was not being used to send character C data to a column with a character, binary, or data source–specific data type or to send binary C data to a column with a character, binary, or data source–specific data type.
HY020	Attempt to concatenate a null value	SQLPutData was called more than once since the call that returned SQL_NEED_DATA, and in one of those calls, the StrLen_or_Ind argument contained SQL_NULL_DATA or SQL_DEFAULT_PARAM.
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.
HYT01	Connection timeout expired	The connection timeout period expired before the data source responded to the request. The connection timeout period is set through SQLSetConnectAttr, SQL_ATTR_CONNECTION_TIMEOUT.
IM001	Driver does not support this function	(DM) The driver associated with the StatementHandle does not support the function.

If SQLPutData is called while sending data for a parameter in an SQL statement, it can return any SQLSTATE that can be returned by the function called to execute the statement (SQLExecute or SQLExecDirect). If it is called while sending data for a column being updated or added with SQLBulkOperations or being updated with SQLSetPos, it can return any SQLSTATE that can be returned by SQLBulkOperations or SQLSetPos.

Comments

SQLPutData can be called to supply data-at-execution data for two uses: parameter data to be used in a call to SQLExecute or SQLExecDirect, or column data to be used when a row is updated or added by a call to SQLBulkOperations or is updated by a call to SQLSetPos.

When an application calls SQLParamData to determine which data it should send, the driver returns an indicator that the application can use to determine which parameter data to send or where column data can be found. It also returns SQL_NEED_DATA, which is an indicator to the application that it should call SQLPutData to send the data. In the DataPtr argument to SQLPutData, the application passes a pointer to the buffer containing the actual data for the parameter or column.

When the driver returns SQL_SUCCESS for SQLPutData, the application calls SQLParamData again. SQLParamData returns SQL_NEED_DATA if more data needs to be sent, in which case the application calls SQLPutData again. It returns SQL_SUCCESS if all data-at-execution data has been sent. The application then calls SQLParamData again. If the driver returns SQL_NEED_DATA and another indicator in *ValuePtrPtr, it requires data for another parameter or column and SQLPutData is called again. If the driver returns SQL_SUCCESS, then all data-at-execution data has been sent and the SQL statement can be executed or the SQLBulkOperations or SQLSetPos call can be processed.

For more information on how data-at-execution parameter data is passed at statement execution time, see "Passing Parameter Values" in SQLBindParameter and Sending Long Data. For more information on how data-at-execution column data is updated or added, see the section "Using SQLSetPos" in SQLSetPos, "Performing Bulk Updates Using Bookmarks" in SQLBulkOperations, and Long Data and SQLSetPos and SQLBulkOperations.

Note
An application can use SQLPutData to send data in parts only when sending character C data to a column with a character, binary, or data source–specific data type or when sending binary C data to a column with a character, binary, or data source–specific data type. If SQLPutData is called more than once under any other conditions, it returns SQL_ERROR and SQLSTATE HY019 (Non-character and non-binary data sent in pieces).

Note

An application can use SQLPutData to send data in parts only when sending character C data to a column with a character, binary, or data source–specific data type or when sending binary C data to a column with a character, binary, or data source–specific data type. If SQLPutData is called more than once under any other conditions, it returns SQL_ERROR and SQLSTATE HY019 (Non-character and non-binary data sent in pieces).

Code Example

In the following example, an application prepares an SQL statement to insert data into the PICTURES table. The statement contains parameters for the PARTID and PICTURE columns. For each parameter, the application calls SQLBindParameter to specify the C and SQL data types of the parameter. It also specifies that the data for the second parameter will be passed at execution time and passes the value 2 for later retrieval by SQLParamData. This value will identify the parameter that is being processed.

The application calls GetNextID to get the next available part ID number. It then calls SQLExecute to execute the statement. SQLExecute returns SQL_NEED_DATA when it needs data for the second parameter. The application calls SQLParamData to retrieve the value it stored with SQLBindParameter; it uses this value to determine which parameter to send data for. For each parameter, the application calls InitUserData to initialize the data routine. It repeatedly calls GetUserData and SQLPutData to get and send the parameter data. Finally, it calls SQLParamData to indicate it has sent all the data for the parameter, at which point it returns SQL_SUCCESS.

For the second parameter, InitUserData calls a routine to prompt the user for the name of a file containing a bitmap photo of the part and opens the file. GetUserData retrieves the next MAX_DATA_LEN bytes of photo data from the file. After it has retrieved all the photo data, it closes the photo file.

Note that some application routines are omitted for clarity.

#define MAX_DATA_LEN 1024
SQLINTEGER     cbPartID = 0, cbPhotoParam, cbData;
SQLUINTEGER    sPartID;
               szPhotoFile;
SQLPOINTER     pToken, InitValue;
SQLCHAR        Data[MAX_DATA_LEN];
SQLRETURN      retcode;
SQLHSTMT       hstmt;

retcode = SQLPrepare(hstmt,
         "INSERT INTO PICTURES (PARTID, PICTURE) VALUES 
         (?, ?)", SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {

   /* Bind the parameters. For parameter 2, pass */
   /* the parameter number in ParameterValuePtr instead of a buffer */    
   /* address. */

   SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, 
                  SQL_INTEGER, 0, 0, &sPartID, 0, &cbPartID);
   SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
                  SQL_C_BINARY, SQL_LONGVARBINARY,
                  0, 0, (SQLPOINTER) 2, 0, &cbPhotoParam);

   /* Set values so data for parameter 2 will be */
   /* passed at execution. Note that the length parameter in */
   /* the macro SQL_LEN_DATA_AT_EXEC is 0. This assumes that */
   /* the driver returns "N" for the SQL_NEED_LONG_DATA_LEN */
   /* information type in SQLGetInfo. */

   cbPhotoParam = SQL_LEN_DATA_AT_EXEC(0);

   sPartID = GetNextID();   /* Get next available employee ID */
                   /* number. */

   retcode = SQLExecute(hstmt);

   /* For data-at-execution parameters, call SQLParamData to */
   /* get the parameter number set by SQLBindParameter. */
   /* Call InitUserData. Call GetUserData and SQLPutData */
   /* repeatedly to get and put all data for the parameter. */
   /* Call SQLParamData to finish processing this parameter */

   while (retcode == SQL_NEED_DATA) {
      retcode = SQLParamData(hstmt, &pToken);
      if (retcode == SQL_NEED_DATA) {
         InitUserData((SQLSMALLINT)pToken, InitValue);
         while (GetUserData(InitValue, (SQLSMALLINT)pToken, Data, 
                            &cbData))
            SQLPutData(hstmt, Data, cbData);
      }
   }
}

VOID InitUserData(sParam, InitValue)
SQLPOINTER InitValue;
{
   SQLCHAR szPhotoFile[MAX_FILE_NAME_LEN];

   /* Prompt user for bitmap file containing employee */
   /* photo. OpenPhotoFile opens the file and returns the */
   /* file handle. */

   PromptPhotoFileName(szPhotoFile);
   OpenPhotoFile(szPhotoFile, (FILE *)InitValue);
   break;
   }

BOOL GetUserData(InitValue, sParam, Data, cbData)
SQLPOINTER     InitValue;
SQLCHAR *      Data;
SQLINTEGER *   cbData;
BOOL           Done;

{

   /* GetNextPhotoData returns the next piece of photo */
   /* data and the number of bytes of data returned */
   /* (up to MAX_DATA_LEN). */

   Done = GetNextPhotoData((FILE *)InitValue, Data,
         MAX_DATA_LEN, &cbData);
   if (Done) {
      ClosePhotoFile((FILE *)InitValue);
      return (TRUE);
   }
   return (FALSE);
   }

Related Functions

For information about	See
Binding a buffer to a parameter	SQLBindParameter Function
Canceling statement processing	SQLCancel Function
Executing an SQL statement	SQLExecDirect Function
Executing a prepared SQL statement	SQLExecute Function
Returning the next parameter to send data for	SQLParamData Function

Concepts

ODBC Header Files

Other Resources

ODBC API Reference