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 an 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 an 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 an 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);  
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

See Also

ODBC API Reference
ODBC Header Files