SQLAllocConnect allocates memory for a connection handle within the environment identified by henv.
RETCODE SQLAllocConnect(henv, phdbc)
The SQLAllocConnect function accepts the following arguments.
|
Argument |
Use |
Description |
|
henv |
Input |
Environment handle. |
|
phdbc |
Output |
Pointer to storage for the connection handle. |
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.
If SQLAllocConnect returns SQL_ERROR, it will set the hdbc referenced by phdbc to SQL_NULL_HDBC. To obtain additional information, the application can call SQLError with the specified henv and with hdbc and hstmt set to SQL_NULL_HDBC and SQL_NULL_HSTMT, respectively.
When SQLAllocConnect returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLAllocConnect 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.
A connection handle references information such as the valid statement handles on the connection and whether a transaction is currently open. To request a connection handle, an application passes the address of an hdbc to SQLAllocConnect. The driver allocates memory for the connection information and stores the value of the associated handle in the hdbc. On operating systems that support multiple threads, applications can use the same hdbc on different threads and drivers must therefore support safe, multithreaded access to this information. The application passes the hdbc value in all subsequent calls that require an hdbc.
The Driver Manager processes the SQLAllocConnect function and calls the driver’s SQLAllocConnect function when the application calls SQLConnect, SQLBrowseConnect, or SQLDriverConnect. (For more information, see the description of the SQLConnect function.)
If the application calls SQLAllocConnect with a pointer to a valid hdbc, the driver overwrites the hdbc without regard to its previous contents.
See SQLBrowseConnect and SQLConnect.
|
See |
|
SQLConnect |
|
SQLFreeConnect |
SQLAllocEnv allocates memory for an environment handle and initializes the ODBC call level interface for use by an application. An application must call SQLAllocEnv prior to calling any other ODBC function.
The SQLAllocEnv function accepts the following argument.
|
Argument |
Use |
Description |
|
phenv |
Output |
Pointer to storage for the environment handle. |
If SQLAllocEnv returns SQL_ERROR, it will set the henv referenced by phenv to SQL_NULL_HENV. In this case, the application can assume that the error was a memory allocation error.
A driver cannot return SQLSTATE values directly after the call to SQLAllocEnv, since no valid handle will exist with which to call SQLError.
There are two levels of SQLAllocEnv functions, one within the Driver Manager and one within each driver. The Driver Manager does not call the driver-level function until the application calls SQLConnect, SQLBrowseConnect, or SQLDriverConnect. If an error occurs in the driver-level SQLAllocEnv function, then the Driver Manager – level SQLConnect, SQLBrowseConnect, or SQLDriverConnect function returns SQL_ERROR. A subsequent call to SQLError with henv, SQL_NULL_HDBC, and SQL_NULL_HSTMT returns SQLSTATE IM004 (Driver’s SQLAllocEnv failed), followed by one of the following errors from the driver:
- SQLSTATE S1000 (General error).
- A driver-specific SQLSTATE value, ranging from S1000 to S19ZZ. For example, SQLSTATE S1001 (Memory allocation failure) indicates that the Driver Manager’s call to the driver-level SQLAllocEnv returned SQL_ERROR, and the Driver Manager’s henv was set to SQL_NULL_HENV.
For additional information about the flow of function calls between the Driver Manager and a driver, see the SQLConnect function description.
An environment handle references global information such as valid connection handles and active connection handles. To request an environment handle, an application passes the address of an henv to SQLAllocEnv. The driver allocates memory for the environment information and stores the value of the associated handle in the henv. On operating systems that support multiple threads, applications can use the same henv on different threads and drivers must therefore support safe, multithreaded access to this information. The application passes the henv value in all subsequent calls that require an henv.
There should never be more than one henv allocated at one time and the application should not call SQLAllocEnv when there is a current valid henv. If the application calls SQLAllocEnv with a pointer to a valid henv, the driver overwrites the henv without regard to its previous contents.
When the Driver Manager processes the SQLAllocEnv function, it checks the Trace keyword in the [ODBC] section of the ODBC.INI file or the ODBC subkey in the registry. If it is set to 1, the Driver Manager enables tracing for all applications on Windows 3.1 or for the current application on Windows NT and Windows 95.
See SQLBrowseConnect and SQLConnect.
|
See |
|
SQLAllocConnect |
|
SQLConnect |
|
SQLFreeEnv |
SQLAllocStmt allocates memory for a statement handle and associates the statement handle with the connection specified by hdbc.
An application must call SQLAllocStmt prior to submitting SQL statements.
The SQLAllocStmt function accepts the following arguments.
|
Argument |
Use |
Description |
|
hdbc |
Input |
Connection handle. |
|
phstmt |
Output |
Pointer to storage for the statement handle. |
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_INVALID_HANDLE, or SQL_ERROR.
If SQLAllocStmt returns SQL_ERROR, it will set the hstmt referenced by phstmt to SQL_NULL_HSTMT. The application can then obtain additional information by calling SQLError with the hdbc and SQL_NULL_HSTMT.
When SQLAllocStmt returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLAllocStmt 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.
A statement handle references statement information, such as network information, SQLSTATE values and error messages, cursor name, number of result set columns, and status information for SQL statement processing.
To request a statement handle, an application connects to a data source and then passes the address of an hstmt to SQLAllocStmt. The driver allocates memory for the statement information and stores the value of the associated handle in the hstmt. On operating systems that support multiple threads, applications can use the same hstmt on different threads and drivers must therefore support safe, multithreaded access to this information. The application passes the hstmt value in all subsequent calls that require an hstmt.
If the application calls SQLAllocStmt with a pointer to a valid hstmt, the driver overwrites the hstmt without regard to its previous contents.
See SQLBrowseConnect, SQLConnect, and SQLSetCursorName.
|
See |
|
SQLExecDirect |
|
SQLExecute |
|
SQLFreeStmt |
|
SQLPrepare |
SQLBindCol assigns the storage and data type for a column in a result set, including:
- A storage buffer that will receive the contents of a column of data
- The length of the storage buffer
- A storage location that will receive the actual length of the column of data returned by the fetch operation
- Data type conversion
RETCODE SQLBindCol(hstmt, icol, fCType, rgbValue, cbValueMax, pcbValue)
The SQLBindCol function accepts the following arguments.
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.
When SQLBindCol returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLBindCol 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.
The ODBC interface provides two ways to retrieve a column of data:
- SQLBindCol assigns the storage location for a column of data before the data is retrieved. When SQLFetch or SQLExtendedFetch is called, the driver places the data for all bound columns in the assigned locations.
- SQLGetData (an extended function) assigns a storage location for a column of data after SQLFetch or SQLExtendedFetch has been called. It also places the data for the requested column in the assigned location. Because it can retrieve data from a column in parts, SQLGetData can be used to retrieve long data values.
An application may choose to bind every column with SQLBindCol, to do no binding and retrieve data only with SQLGetData, or to use a combination of the two. However, unless the driver provides extended functionality, SQLGetData can only be used to retrieve data from columns that occur after the last bound column.
An application calls SQLBindCol to pass the pointer to the storage buffer for a column of data to the driver and to specify how or if the data will be converted. It is the application’s responsibility to allocate enough storage for the data. If the buffer will contain variable length data, the application must allocate as much storage as the maximum length of the bound column or the data may be truncated. For a list of valid data conversion types, see "Converting Data from SQL to C Data Types" in Appendix D, "Data Types."
At fetch time, the driver processes the data for each bound column according to the arguments specified in SQLBindCol. First, it converts the data according to the argument fCType. Next, it fills the buffer pointed to by rgbValue. Finally, it stores the available number of bytes in pcbValue; this is the number of bytes available prior to calling SQLFetch or SQLExtendedFetch.
- If SQL_MAX_LENGTH has been specified with SQLSetStmtOption and the available number of bytes is greater than SQL_MAX_LENGTH, the driver stores SQL_MAX_LENGTH in pcbValue.
- If the data is truncated because of SQL_MAX_LENGTH, but the user’s buffer was large enough for SQL_MAX_LENGTH bytes of data, SQL_SUCCESS is returned.
Note The SQL_MAX_LENGTH statement option is intended to reduce network traffic and may not be supported by all drivers. To guarantee that data is truncated, an application should allocate a buffer of the desired size and specify this size in the cbValueMax argument.
- If the user’s buffer causes the truncation, the driver returns SQL_SUCCESS_WITH_INFO and SQLSTATE 01004 (Data truncated) for the fetch function.
- If the data value for a column is NULL, the driver sets pcbValue to SQL_NULL_DATA.
- If the number of bytes available to return cannot be determined in advance, the driver sets pcbValue to SQL_NO_TOTAL.
When an application uses SQLExtendedFetch to retrieve more than one row of data, it only needs to call SQLBindCol once for each column of the result set (just as when it binds a column in order to retrieve a single row of data with SQLFetch). The SQLExtendedFetch function coordinates the placement of each row of data into subsequent locations in the rowset buffers. For additional information about binding rowset buffers, see the "Comments" topic for SQLExtendedFetch.
An application can call SQLBindCol to bind a column to a new storage location, regardless of whether data has already been fetched. The new binding replaces the old binding. Note that the new binding does not apply to data already fetched; the next time data is fetched, the data will be placed in the new storage location.
To unbind a single bound column, an application calls SQLBindCol and specifies a null pointer for rgbValue; if rgbValue is a null pointer and the column is not bound, SQLBindCol returns SQL_SUCCESS. To unbind all bound columns, an application calls SQLFreeStmt with the SQL_UNBIND option.
In the following example, an application executes a SELECT statement to return a result set of the employee names, ages, and birthdays, which is sorted by birthday. It then calls SQLBindCol to bind the columns of data to local storage locations. Finally, the application fetches each row of data with SQLFetch and prints each employee’s name, age, and birthday.
For more code examples, see SQLColumns, SQLExtendedFetch, and SQLSetPos.
#define NAME_LEN 30 #define BDAY_LEN 11 UCHAR szName[NAME_LEN], szBirthday[BDAY_LEN]; SWORD sAge; SDWORD cbName, cbAge, cbBirthday; retcode = SQLExecDirect(hstmt,
"SELECT NAME, AGE, BIRTHDAY FROM EMPLOYEE ORDER BY 3, 2, 1", SQL_NTS); if (retcode == SQL_SUCCESS) { /* Bind columns 1, 2, and 3 */ SQLBindCol(hstmt, 1, SQL_C_CHAR, szName, NAME_LEN, &cbName); SQLBindCol(hstmt, 2, SQL_C_SSHORT, &sAge, 0, &cbAge); SQLBindCol(hstmt, 3, SQL_C_CHAR, szBirthday, BDAY_LEN, &cbBirthday); /* Fetch and print each row of data. On */ /* an error, display a message and exit. */ while (TRUE) { retcode = SQLFetch(hstmt); if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) { show_error(); } if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){ fprintf(out, "%-*s %-2d %*s", NAME_LEN-1, szName,
sAge, BDAY_LEN-1, szBirthday); } else { break; } } }
SQLBindParameter binds a buffer to a parameter marker in an SQL statement.
Note This function replaces the ODBC 1.0 function SQLSetParam. For more information, see "Comments."
RETCODE SQLBindParameter(hstmt, ipar, fParamType, fCType, fSqlType, cbColDef, ibScale, rgbValue, cbValueMax, pcbValue)
The SQLBindParameter function accepts the following arguments.
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.
When SQLBindParameter returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLBindParameter 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.
An application calls SQLBindParameter to bind each parameter marker in an SQL statement. Bindings remain in effect until the application calls SQLBindParameter again or until the application calls SQLFreeStmt with the SQL_DROP or SQL_RESET_PARAMS option.
For more information concerning parameter data types and parameter markers, see "Parameter Data Types" and "Parameter Markers" in Appendix C, "SQL Grammar."
fParamType Argument
The fParamType argument specifies the type of the parameter. All parameters in SQL statements that do not call procedures, such as INSERT statements, are input parameters. Parameters in procedure calls can be input, input/output, or output parameters. (An application calls SQLProcedureColumns to determine the type of a parameter in a procedure call; parameters in procedure calls whose type cannot be determined are assumed to be input parameters.)
- SQL_PARAM_INPUT. The parameter marks a parameter in an SQL statement that does not call a procedure, such as an INSERT statement, or it marks an input parameter in a procedure; these are collectively known as input parameters. For example, the parameters in INSERT INTO Employee VALUES (?, ?, ?) and {call AddEmp(?, ?, ?)} are input parameters.
- When the statement is executed, the driver sends data for the parameter to the data source; the rgbValue buffer must contain a valid input value or the pcbValue buffer must contain SQL_NULL_DATA, SQL_DATA_AT_EXEC, or the result of the SQL_LEN_DATA_AT_EXEC macro.
- If an application cannot determine the type of a parameter in a procedure call, it sets fParamType to SQL_PARAM_INPUT; if the data source returns a value for the parameter, the driver discards it.
- SQL_PARAM_INPUT_OUTPUT. The parameter marks an input/output parameter in a procedure. For example, the parameter in {call GetEmpDept(?)} is an input/output parameter that accepts an employee’s name and returns the name of the employee’s department.
- When the statement is executed, the driver sends data for the parameter to the data source; the rgbValue buffer must contain a valid input value or the pcbValue buffer must contain SQL_NULL_DATA, SQL_DATA_AT_EXEC, or the result of the SQL_LEN_DATA_AT_EXEC macro. After the statement is executed, the driver returns data for the parameter to the application; if the data source does not return a value for an input/output parameter, the driver sets the pcbValue buffer to SQL_NULL_DATA.
Note When an ODBC 1.0 application calls SQLSetParam in an ODBC 2.0 driver, the Driver Manager converts this to a call to SQLBindParameter in which the fParamType argument is set to SQL_PARAM_INPUT_OUTPUT.
- SQL_PARAM_OUTPUT. The parameter marks the return value of a procedure or an output parameter in a procedure; these are collectively known as output parameters. For example, the parameter in {?=call GetNextEmpID} is an output parameter that returns the next employee ID.
- After the statement is executed, the driver returns data for the parameter to the application, unless the rgbValue and pcbValue arguments are both null pointers, in which case the driver discards the output value. If the data source does not return a value for an output parameter, the driver sets the pcbValue buffer to SQL_NULL_DATA.
fCType Argument
The C data type of the parameter. This must be one of the following values:
SQL_C_BINARY
SQL_C_BIT
SQL_C_CHAR
SQL_C_DATE
SQL_C_DEFAULT
SQL_C_DOUBLE
SQL_C_FLOAT
SQL_C_SLONG
SQL_C_SSHORT
SQL_C_STINYINT
SQL_C_TIME
SQL_C_TIMESTAMP
SQL_C_ULONG
SQL_C_USHORT
SQL_C_DEFAULT specifies that the parameter value be transferred from the default C data type for the SQL data type specified with fSqlType.
For more information, see "Default C Data Types" and "Converting Data from C to SQL Data Types" and "Converting Data from SQL to C Data Types" in Appendix D, "Data Types."
Note Drivers must also support the following values of fCType from ODBC 1.0. Applications must use these values, instead of the ODBC 2.0 values, when calling an ODBC 1.0 driver:
SQL_C_LONG
SQL_C_SHORT
SQL_C_TINYINT
For more information, see "ODBC 1.0 C Data Types" in Appendix D, "Data Types."
fSqlType Argument
This must be one of the following values:
SQL_BIGINT
SQL_BINARY
SQL_BIT
SQL_CHAR
SQL_DATE
SQL_DECIMAL
SQL_DOUBLE
SQL_FLOAT
SQL_INTEGER
SQL_LONGVARBINARY
SQL_LONGVARCHAR
SQL_NUMERIC
SQL_REAL
SQL_SMALLINT
SQL_TIME
SQL_TIMESTAMP
SQL_TINYINT
SQL_VARBINARY
or a driver-specific value. Values greater than SQL_TYPE_DRIVER_START are reserved by ODBC; values less than or equal to SQL_TYPE_DRIVER_START are driver-specific.
For information about how data is converted, see "Converting Data from C to SQL Data Types" and "Converting Data from SQL to C Data Types" in Appendix D, "Data Types."
cbColDef Argument
The cbColDef argument specifies the precision of the column or expression corresponding to the parameter marker, unless all of the following are true:
- An ODBC 2.0 application calls SQLBindParameter in an ODBC 1.0 driver or an ODBC 1.0 application calls SQLSetParam in an ODBC 2.0 driver. (Note that the Driver Manager converts these calls.)
- The fSqlType argument is SQL_LONGVARBINARY or SQL_LONGVARCHAR.
- The data for the parameter will be sent with SQLPutData.
In this case, the cbColDef argument contains the total number of bytes that will be sent for the parameter. For more information, see "Passing Parameter Values" and SQL_DATA_AT_EXEC in "pcbValue Argument."
rgbValue Argument
The rgbValue argument points to a buffer that, when SQLExecute or SQLExecDirect is called, contains the actual data for the parameter. The data must be in the form specified by the fCType argument.
If rgbValue points to a character string that contains a literal quote character ( ' ), the driver ensures that each literal quote is translated into the form required by the data source. For example, if the data source required that embedded literal quotes be doubled, the driver would replace each quote character ( ' ) with two quote characters ( ' ' ).
If pcbValue is the result of the SQL_LEN_DATA_AT_EXEC(length) macro or SQL_DATA_AT_EXEC, then rgbValue is an application-defined 32-bit value that is associated with the parameter. It is returned to the application through SQLParamData. For example, rgbValue might be a token such as a parameter number, a pointer to data, or a pointer to a structure that the application used to bind input parameters. Note, however, that if the parameter is an input/output parameter, rgbValue must be a pointer to a buffer where the output value will be stored. If SQLParamOptions was called to specify multiple values for the parameter, the application can use the value of the pirow argument in SQLParamOptions in conjunction with the rgbValue. For example, rgbValue might point to an array of values and the application might use pirow to retrieve the correct value from the array. For more information, see "Passing Parameter Values."
If the fParamType argument is SQL_PARAM_INPUT_OUTPUT or SQL_PARAM_OUTPUT, rgbValue points to a buffer in which the driver returns the output value. If the procedure returns one or more result sets, the rgbValue buffer is not guaranteed to be set until all results have been fetched. (If fParamType is SQL_PARAM_OUTPUT and rgbValue and pcbValue are both null pointers, the driver discards the output value.)
If the application calls SQLParamOptions to specify multiple values for each parameter, rgbValue points to an array. A single SQL statement processes the entire array of input values for an input or input/output parameter and returns an array of output values for an input/output or output parameter.
cbValueMax Argument
For character and binary C data, the cbValueMax argument specifies the length of the rgbValue buffer (if it is a single element) or the length of an element in the rgbValue array (if the application calls SQLParamOptions to specify multiple values for each parameter). If the application specifies multiple values, cbValueMax is used to determine the location of values in the rgbValue array, both on input and on output. For input/output and output parameters, it is used to determine whether to truncate character and binary C data on output:
- For character C data, if the number of bytes available to return is greater than or equal to cbValueMax, the data in rgbValue is truncated to cbValueMax – 1 bytes and is null-terminated by the driver.
- For binary C data, if the number of bytes available to return is greater than cbValueMax, the data in rgbValue is truncated to cbValueMax bytes.
For all other types of C data, the cbValueMax argument is ignored. The length of the rgbValue buffer (if it is a single element) or the length of an element in the rgbValue array (if the application calls SQLParamOptions to specify multiple values for each parameter) is assumed to be the length of the C data type.
Note When an ODBC 1.0 application calls SQLSetParam in an ODBC 2.0 driver, the Driver Manager converts this to a call to SQLBindParameter in which the cbValueMax argument is always SQL_SETPARAM_VALUE_MAX. Because the Driver Manager returns an error if an ODBC 2.0 application sets cbValueMax to SQL_SETPARAM_VALUE_MAX, an ODBC 2.0 driver can use this to determine when it is called by an ODBC 1.0 application.
In SQLSetParam, the way in which an application specifies the length of the rgbValue buffer so that the driver can return character or binary data and the way in which an application sends an array of character or binary parameter values to the driver are driver-defined.
pcbValue Argument
The pcbValue argument points to a buffer that, when SQLExecute or SQLExecDirect is called, contains one of the following:
- The length of the parameter value stored in rgbValue. This is ignored except for character or binary C data.
- SQL_NTS. The parameter value is a null-terminated string.
- SQL_NULL_DATA. The parameter value is NULL.
- SQL_DEFAULT_PARAM. A procedure is to use the default value of a parameter, rather than a value retrieved from the application. This value is valid only in a procedure call, and then only if the fParamType argument is SQL_PARAM_INPUT or SQL_PARAM_INPUT_OUTPUT. When pcbValue is SQL_DEFAULT_PARAM, the fCType, fSqlType, cbColDef, ibScale, cbValueMax and rgbValue arguments are ignored for input parameters and are used only to define the output parameter value for input/output parameters.
Note This value was introduced in ODBC 2.0.
- The result of the SQL_LEN_DATA_AT_EXEC(length) macro. The data for the parameter will be sent with SQLPutData. If the fSqlType argument is SQL_LONGVARBINARY, SQL_LONGVARCHAR, or a long, data source–specific data type and the driver returns "Y" for the SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo, length is the number of bytes of data to be sent for the parameter; otherwise, length must be a nonnegative value and is ignored. For more information, see "Passing Parameter Values."
- For example, to specify that 10,000 bytes of data will be sent with SQLPutData for an SQL_LONGVARCHAR parameter, an application sets pcbValue to SQL_LEN_DATA_AT_EXEC(10000).
Note This macro was introduced in ODBC 2.0.
- SQL_DATA_AT_EXEC. The data for the parameter will be sent with SQLPutData. This value is used by ODBC 2.0 applications when calling ODBC 1.0 drivers and by ODBC 1.0 applications when calling ODBC 2.0 drivers. For more information, see "Passing Parameter Values."
If pcbValue is a null pointer, the driver assumes that all input parameter values are non-NULL and that character and binary data are null-terminated. If fParamType is SQL_PARAM_OUTPUT and rgbValue and pcbValue are both null pointers, the driver discards the output value.
Note Application developers are strongly discouraged from specifying a null pointer for pcbValue when the data type of the parameter is SQL_C_BINARY. For SQL_C_BINARY data, a driver sends only the data preceding an occurrence of the null-termination character, 0x00. To ensure that a driver does not unexpectedly truncate SQL_C_BINARY data, pcbValue should contain a pointer to a valid length value.
If the fParamType argument is SQL_PARAM_INPUT_OUTPUT or SQL_PARAM_OUTPUT, pcbValue points to a buffer in which the driver returns SQL_NULL_DATA, the number of bytes available to return in rgbValue (excluding the null termination byte of character data), or SQL_NO_TOTAL if the number of bytes available to return cannot be determined. If the procedure returns one or more result sets, the pcbValue buffer is not guaranteed to be set until all results have been fetched.
If the application calls SQLParamOptions to specify multiple values for each parameter, pcbValue points to an array of SDWORD values. These can be any of the values listed earlier in this section and are processed with a single SQL statement.
Passing Parameter Values
An application can pass the value for a parameter either in the rgbValue buffer or with one or more calls to SQLPutData. Parameters whose data is passed with SQLPutData are known as data-at-execution parameters. These are commonly used to send data for SQL_LONGVARBINARY and SQL_LONGVARCHAR parameters and can be mixed with other parameters.
- 1. Calls SQLBindParameter for each parameter to bind buffers for the parameter’s value (rgbValue argument) and length (pcbValue argument). For data-at-execution parameters, rgbValue is an application-defined 32-bit value such as a parameter number or a pointer to data. The value will be returned later and can be used to identify the parameter.
- 2. Places values for input and input/output parameters in the rgbValue and pcbValue buffers:
- For normal parameters, the application places the parameter value in the rgbValue buffer and the length of that value in the pcbValue buffer.
- For data-at-execution parameters, the application places the result of the SQL_LEN_DATA_AT_EXEC(length) macro (when calling an ODBC 2.0 driver) or SQL_DATA_AT_EXEC (when calling an ODBC 1.0 driver) in the pcbValue buffer.
- 3. Calls SQLExecute or SQLExecDirect to execute the SQL statement.
- If there are no data-at-execution parameters, the process is complete.
- If there are any data-at-execution parameters, the function returns SQL_NEED_DATA.
- 4. Calls SQLParamData to retrieve the application-defined value specified in the rgbValue argument for the first data-at-execution parameter to be processed.
Note Although data-at-execution parameters are similar to data-at-execution columns, the value returned by SQLParamData is different for each.
Data-at-execution parameters are parameters in an SQL statement for which data will be sent with SQLPutData when the statement is executed with SQLExecDirect or SQLExecute. They are bound with SQLBindParameter. The value returned by SQLParamData is a 32-bit value passed to SQLBindParameter in the rgbValue argument.
Data-at-execution columns are columns in a rowset for which data will be sent with SQLPutData when a row is updated or added with SQLSetPos. They are bound with SQLBindCol. The value returned by SQLParamData is the address of the row in the rgbValue buffer that is being processed.
- 5. Calls SQLPutData one or more times to send data for the parameter. More than one call is needed if the data value is larger than the rgbValue buffer specified in SQLPutData; note that multiple calls to SQLPutData for the same parameter are allowed 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.
- 6. Calls SQLParamData again to signal that all data has been sent for the parameter.
- If there are more data-at-execution parameters, SQLParamData returns SQL_NEED_DATA and the application-defined value for the next data-at-execution parameter to be processed. The application repeats steps 5 and 6.
- If there are no more data-at-execution parameters, the process is complete. If the statement was successfully executed, SQLParamData returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO; if the execution failed, it returns SQL_ERROR. At this point, SQLParamData can return any SQLSTATE that can be returned by the function used to execute the statement (SQLExecDirect or SQLExecute).
- Output values for any input/output or output parameters will be available in the rgbValue and pcbValue buffers after the application retrieves any result sets generated by the statement.
After SQLExecute or SQLExecDirect returns SQL_NEED_DATA, and before data is sent for all data-at-execution parameters, the statement is canceled, or an error occurs in SQLParamData or SQLPutData, the application can only call SQLCancel, SQLGetFunctions, SQLParamData, or SQLPutData with the hstmt or the hdbc associated with the hstmt. If it calls any other function with the hstmt or the hdbc associated with the hstmt, the function returns SQL_ERROR and SQLSTATE S1010 (Function sequence error).
If the application calls SQLCancel while the driver still needs data for data-at-execution parameters, the driver cancels statement execution; the application can then call SQLExecute or SQLExecDirect again. If the application calls SQLParamData or SQLPutData after canceling the statement, the function returns SQL_ERROR and SQLSTATE S1008 (Operation canceled).
Conversion of Calls to and from SQLSetParam
When an ODBC 1.0 application calls SQLSetParam in an ODBC 2.0 driver, the ODBC 2.0 Driver Manager maps the call as follows:
When an ODBC 2.0 application calls SQLBindParameter in an ODBC 1.0 driver, the ODBC 2.0 Driver Manager maps the calls as follows:
In the following example, an application prepares an SQL statement to insert data into the EMPLOYEE table. The SQL statement contains parameters for the NAME, AGE, and BIRTHDAY columns. For each parameter in the statement, the application calls SQLBindParameter to specify the ODBC C data type and the SQL data type of the parameter and to bind a buffer to each parameter. For each row of data, the application assigns data values to each parameter and calls SQLExecute to execute the statement.
For more code examples, see SQLParamOptions, SQLProcedures, SQLPutData, and SQLSetPos.
#define NAME_LEN 30
UCHAR szName[NAME_LEN]; SWORD sAge; SDWORD cbName = SQL_NTS, cbAge = 0, cbBirthday = 0; DATE_STRUCT dsBirthday; retcode = SQLPrepare(hstmt,
"INSERT INTO EMPLOYEE (NAME, AGE, BIRTHDAY) VALUES (?, ?, ?)",
SQL_NTS); if (retcode == SQL_SUCCESS) { /* Specify data types and buffers. */ /* for Name, Age, Birthday parameter data. */ SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, NAME_LEN, 0, szName, 0, &cbName); SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_SSHORT,
SQL_SMALLINT, 0, 0, &sAge, 0, &cbAge); SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_DATE,
SQL_DATE, 0, 0, &dsBirthday, 0, &cbBirthday); strcpy(szName, "Smith, John D."); /* Specify first row of */ sAge = 40; /* parameter data */ dsBirthday.year = 1952; dsBirthday.month = 2; dsBirthday.day = 29; retcode = SQLExecute(hstmt); /* Execute statement with */ /* first row */ strcpy(szName, "Jones, Bob K."); /* Specify second row of */ sAge = 52; /* parameter data */ dsBirthday.year = 1940; dsBirthday.month = 3; dsBirthday.day = 31; SQLExecute(hstmt); /* Execute statement with */ /* second row */ }
Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.