SQLDescribeParam

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(
SQLHSTMTStatementHandle,
SQLUSMALLINTParameterNumber,
SQLSMALLINT *DataTypePtr,
SQLUINTEGER *ParameterSizePtr,
SQLSMALLINT *DecimalDigitsPtr,
SQLSMALLINT *NullablePtr);

Argument

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 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 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 of the column or expression of the corresponding parameter marker as defined by the data source. For further information concerning column size, see “Column Size, Decimal Digits, Transfer Octet Length, and Display Size,” in Appendix D, “Data Types.”

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 on decimal digits, see “Column Size, Decimal Digits, Transfer Octet Length, and Display Size,” in Appendix D, “Data Types.”

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 if 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 may 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 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.

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” in Chapter 9, “Executing Statements.”

Code Example

The following example prompts the user for an SQL statement and then prepares that statement. Next, it calls SQLNumParams to determine if the statement contains any parameters. If so, 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);

Related Functions