SQLDescribeParam Function
Conformance
Version Introduced: ODBC 1.0 Standards Compliance: ODBC
Summary
SQLDescribeParam returns the description of a parameter marker associated with a prepared SQL statement. This information is also available in the fields of the IPD.
Syntax
SQLRETURN SQLDescribeParam(
SQLHSTMT StatementHandle,
SQLUSMALLINT ParameterNumber,
SQLSMALLINT * DataTypePtr,
SQLULEN * ParameterSizePtr,
SQLSMALLINT * DecimalDigitsPtr,
SQLSMALLINT * NullablePtr);
Arguments
StatementHandle
[Input] Statement handle.
ParameterNumber
[Input] Parameter marker number ordered sequentially in increasing parameter order, starting at 1.
DataTypePtr
[Output] Pointer to a buffer in which to return the SQL data type of the parameter. This value is read from the SQL_DESC_CONCISE_TYPE record field of the IPD. This will be one of the values in the SQL Data Types section of Appendix D: Data Types, or a driver-specific SQL data type.
In ODBC 3.x, SQL_TYPE_DATE, SQL_TYPE_TIME, or SQL_TYPE_TIMESTAMP will be returned in *DataTypePtr for date, time, or timestamp data, respectively; in ODBC 2.x, SQL_DATE, SQL_TIME, or SQL_TIMESTAMP will be returned. The Driver Manager performs the required mappings when an ODBC 2.x application is working with an ODBC 3.x driver or when an ODBC 3.x application is working with an ODBC 2.x driver.
When ColumnNumber is equal to 0 (for a bookmark column), SQL_BINARY is returned in *DataTypePtr for variable-length bookmarks. (SQL_INTEGER is returned if bookmarks are used by an ODBC 3.x application working with an ODBC 2.x driver or by an ODBC 2.x application working with an ODBC 3.x driver.)
For more information, see SQL Data Types in Appendix D: Data Types. For information about driver-specific SQL data types, see the driver's documentation.
ParameterSizePtr
[Output] Pointer to a buffer in which to return the size, in characters, of the column or expression of the corresponding parameter marker as defined by the data source. For more information about column size, see Column Size, Decimal Digits, Transfer Octet Length, and Display Size.
DecimalDigitsPtr
[Output] Pointer to a buffer in which to return the number of decimal digits of the column or expression of the corresponding parameter as defined by the data source. For more information about decimal digits, see Column Size, Decimal Digits, Transfer Octet Length, and Display Size.
NullablePtr
[Output] Pointer to a buffer in which to return a value that indicates whether the parameter allows NULL values. This value is read from the SQL_DESC_NULLABLE field of the IPD. One of the following:
SQL_NO_NULLS: The parameter does not allow NULL values (this is the default value).
SQL_NULLABLE: The parameter allows NULL values.
SQL_NULLABLE_UNKNOWN: The driver cannot determine whether the parameter allows NULL values.
Returns
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.
Diagnostics
When SQLDescribeParam 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 typically returned by SQLDescribeParam 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.) |
| 07009 | Invalid descriptor index | (DM) The value specified for the argument ParameterNumber is less than 1. The value specified for the argument ParameterNumber was greater than the number of parameters in the associated SQL statement. The parameter marker was part of a non-DML statement. The parameter marker was part of a SELECT list. |
| 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. |
| 21S01 | Insert value list does not match column list | The number of parameters in the INSERT statement did not match the number of columns in the table named in the statement. |
| 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 that is 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 or SQLCancelHandle was called on the StatementHandle. Then the function was called again on the StatementHandle. The function was called, and before it completed execution, SQLCancel or SQLCancelHandle was called on the StatementHandle from a different thread in a multithread application. |
| HY010 | Function sequence error | (DM) The function was called before calling SQLPrepare or SQLExecDirect for the StatementHandle. (DM) An asynchronously executing function was called for the connection handle that is associated with the StatementHandle. This asynchronous function was still executing when the SQLDescribeParam function was called. (DM) An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called. (DM) SQLExecute, SQLExecDirect, SQLBulkOperations, or SQLSetPos was called for the StatementHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. |
| 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. |
| HY117 | Connection is suspended due to unknown transaction state. Only disconnect and read-only functions are allowed. | (DM) For more information about suspended state, see SQLEndTran Function. |
| 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. |
| IM017 | Polling is disabled in asynchronous notification mode | Whenever the notification model is used, polling is disabled. |
| IM018 | SQLCompleteAsync has not been called to complete the previous asynchronous operation on this handle. | If the previous function call on the handle returns SQL_STILL_EXECUTING and if notification mode is enabled, SQLCompleteAsync must be called on the handle to do post-processing and complete the operation. |
Comments
Parameter markers are numbered in increasing parameter order, starting with 1, in the order they appear in the SQL statement.
SQLDescribeParam does not return the type (input, input/output, or output) of a parameter in a SQL statement. Except in calls to procedures, all parameters in SQL statements are input parameters. To determine the type of each parameter in a call to a procedure, an application calls SQLProcedureColumns.
For more information, see Describing Parameters.
Code Example
The following example prompts the user for a SQL statement and then prepares that statement. Next, it calls SQLNumParams to determine whether the statement contains any parameters. If the statement contains parameters, it calls SQLDescribeParam to describe those parameters and SQLBindParameter to bind them. Finally, it prompts the user for the values of any parameters and then executes the statement.
SQLCHAR Statement[100];
SQLSMALLINT NumParams, i, DataType, DecimalDigits, Nullable;
SQLUINTEGER ParamSize;
SQLHSTMT hstmt;
// Prompt the user for a SQL statement and prepare it.
GetSQLStatement(Statement);
SQLPrepare(hstmt, Statement, SQL_NTS);
// Check to see if there are any parameters. If so, process them.
SQLNumParams(hstmt, &NumParams);
if (NumParams) {
// Allocate memory for three arrays. The first holds pointers to buffers in which
// each parameter value will be stored in character form. The second contains the
// length of each buffer. The third contains the length/indicator value for each
// parameter.
SQLPOINTER * PtrArray = (SQLPOINTER *) malloc(NumParams * sizeof(SQLPOINTER));
SQLINTEGER * BufferLenArray = (SQLINTEGER *) malloc(NumParams * sizeof(SQLINTEGER));
SQLINTEGER * LenOrIndArray = (SQLINTEGER *) malloc(NumParams * sizeof(SQLINTEGER));
for (i = 0; i < NumParams; i++) {
// Describe the parameter.
SQLDescribeParam(hstmt, i + 1, &DataType, &ParamSize, &DecimalDigits, &Nullable);
// Call a helper function to allocate a buffer in which to store the parameter
// value in character form. The function determines the size of the buffer from
// the SQL data type and parameter size returned by SQLDescribeParam and returns
// a pointer to the buffer and the length of the buffer.
AllocParamBuffer(DataType, ParamSize, &PtrArray[i], &BufferLenArray[i]);
// Bind the memory to the parameter. Assume that we only have input parameters.
SQLBindParameter(hstmt, i + 1, SQL_PARAM_INPUT, SQL_C_CHAR, DataType, ParamSize,
DecimalDigits, PtrArray[i], BufferLenArray[i],
&LenOrIndArray[i]);
// Prompt the user for the value of the parameter and store it in the memory
// allocated earlier. For simplicity, this function does not check the value
// against the information returned by SQLDescribeParam. Instead, the driver does
// this when the statement is executed.
GetParamValue(PtrArray[i], BufferLenArray[i], &LenOrIndArray[i]);
}
}
// Execute the statement.
SQLExecute(hstmt);
// Process the statement further, such as retrieving results (if any) and closing the
// cursor (if any). Code not shown.
// Free the memory allocated for each parameter and the memory allocated for the arrays
// of pointers, buffer lengths, and length/indicator values.
for (i = 0; i < NumParams; i++) free(PtrArray[i]);
free(PtrArray);
free(BufferLenArray);
free(LenOrIndArray);
Related Functions
| For information about | See |
|---|---|
| Binding a buffer to a parameter | SQLBindParameter Function |
| Canceling statement processing | SQLCancel Function |
| Executing a prepared SQL statement | SQLExecute Function |
| Preparing a statement for execution | SQLPrepare Function |