SQLDescribeCol - Describe column attributes

 

SQLDescribeCol() returns the result descriptor information (column name, type, precision) for the indicated column in the result set generated by a SELECT statement.

If the application needs only one attribute of the descriptor information, the SQLColAttributes() function can be used in place of SQLDescribeCol().

Either SQLPrepare() or SQLExecDirect() must be called before calling this function.

This function (or SQLColAttributes()) is typically called before SQLBindCol().

 

Syntax

SQLRETURN SQLDescribeCol (SQLHSTMT       hstmt,
                          SQLSMALLINT    icol,
                          SQLCHAR        *szColName,
                          SQLSMALLINT    cbColNameMax,
                          SQLSMALLINT    *pcbColName,
                          SQLSMALLINT    *pfSqlType,
                          SQLINTEGER     *pcbColDef,
                          SQLSMALLINT    *pibScale,
                          SQLSMALLINT    *pfNullable);

 

Function arguments

Table 1. SQLDescribeCol arguments
Data type Argument Use Description
SQLHSTMT hstmt Input Statement handle.
SQLSMALLINT icol Input Column number to be described.
SQLCHAR * szColName Output Pointer to column name buffer.
SQLSMALLINT cbColNameMax Input Size of szColName buffer.
SQLSMALLINT * pcbColName Output Bytes available to return for szColName argument. Truncation of column name (szColName) to cbColNameMax - 1 bytes occurs if pcbColName is greater than or equal to cbColNameMax.
SQLSMALLINT * pfSqlType Output SQL data type of column.
SQLINTEGER * pcbColDef Output Precision of column as defined in the database.

If fSqlType denotes a graphic SQL data type, then this variable indicates the maximum number of double-byte characters the column can hold.

SQLSMALLINT * pibScale Output Scale of column as defined in the database (only applies to SQL_DECIMAL, SQL_NUMERIC, SQL_TIMESTAMP).
SQLSMALLINT * pfNullable Output This indicates whether NULLS are allowed for this column

  • SQL_NO_NULLS.

  • SQL_NULLABLE.

 

Usage

Columns are identified by a number and are numbered sequentially from left to right starting with 1, and can be described in any order.

A valid pointer and buffer space must be made available for the szColName argument. If a null pointer is specified for any of the remaining pointer arguments, DB2® UDB CLI assumes that the information is not needed by the application and nothing is returned.

 

Return codes

 

Diagnostics

If SQLDescribeCol() returns either SQL_ERROR, or SQL_SUCCESS_WITH_INFO, one of the following SQLSTATEs can be obtained by calling the SQLError() function.

Table 2. SQLDescribeCol SQLSTATEs
SQLSTATE Description Explanation
01004 Data truncated The column name returned in the argument szColName is longer than the value specified in the argument cbColNameMax. The argument pcbColName contains the length of the full column name. (Function returns SQL_SUCCESS_WITH_INFO.)
07005 * Not a SELECT statement The statement associated with the hstmt did not return a result set. There were no columns to describe. (Call SQLNumResultCols() first to determine if there are any rows in the result set.)
07009 Column number that is not valid The value specified for the argument icol is less than 1.

The value specified for the argument icol is greater than the number of columns in the result set.

40003 * Statement completion unknown The communication link between the CLI and the data source fails before the function completes processing.
58004 System error Unrecoverable system error.
HY001 Memory allocation failure The driver is unable to allocate memory required to support the processing or completion of the function.
HY009 Argument value that is not valid The length specified in argument cbColNameMax is less than 1.

The argument szColName or pcbColName is a null pointer.

HY010 Function sequence error The function is called before calling SQLPrepare() or SQLExecDirect() for the hstmt.
HY013 * Memory management problem The driver is unable to access memory required to support the processing or completion of the function.
HYC00 Driver not capable The SQL data type of column icol is not recognized by DB2 UDB CLI.

 

Example

By using the code examples, you agree to the terms of the Code license and disclaimer information. 

/*******************************************************************
** file = typical.c ...
/*******************************************************************
** display_results **
**  - for each column **      - get column name **      - bind column **  - display column headings **  - fetch each row **      - if value truncated, build error message **      - if column null, set value to "NULL"
**      - display row **      - print truncation message **  - free local storage *******************************************************************/
display_results(SQLHSTMT hstmt,
                SQLSMALLINT nresultcols)
{
SQLCHAR         colname[32];
SQLSMALLINT     coltype;
SQLSMALLINT     colnamelen;
SQLSMALLINT     nullable;
SQLINTEGER      collen[MAXCOLS];
SQLSMALLINT     scale;
SQLINTEGER      outlen[MAXCOLS];
SQLCHAR *       data[MAXCOLS];
SQLCHAR         errmsg[256];
SQLRETURN       rc;
SQLINTEGER      i;
SQLINTEGER      displaysize;
 
    for (i = 0; i < nresultcols; i++)
    {
        SQLDescribeCol (hstmt, i+1, colname, sizeof (colname),
        &colnamelen, &coltype, &collen[i], &scale, &nullable);
 
        /* get display length for column */
        SQLColAttributes (hstmt, i+1, SQL_COLUMN_DISPLAY_SIZE, NULL, 0,             NULL, &displaysize);
 
        /* set column length to max of display length, and column name            length. Plus one byte for null terminator       */
        collen[i] = max(displaysize, strlen((char *) colname) ) + 1;
 
 
        /* allocate memory to bind column                             */
        data[i] = (SQLCHAR *) malloc (collen[i]);
 
        /* bind columns to program vars, converting all types to CHAR */
        SQLBindCol (hstmt, i+1, SQL_CHAR, data[i], collen[i],
&outlen[i]);
    }
    printf("\n");
 
    /* display result rows                                            */
    while ((rc = SQLFetch (hstmt)) != SQL_NO_DATA_FOUND)
    {
        errmsg[0] = '\0';
        for (i = 0; i < nresultcols; i++)
        {
            /* Build a truncation message for any columns truncated */
            if (outlen[i] >= collen[i])
            {    sprintf ((char *) errmsg + strlen ((char *) errmsg),
                          "%d chars truncated, col %d\n",
                           outlen[i]-collen[i]+1, i+1);
            }
            if (outlen[i] == SQL_NULL_DATA)
            else         } /* for all columns in this row  */
 
        printf ("\n%s", errmsg);  /* print any truncation messages    */
    } /* while rows to fetch */
 
    /* free data buffers                                              */
    for (i = 0; i < nresultcols; i++)
    {
        free (data[i]);
    }
 
}/* end display_results

 

References

 

Parent topic:

DB2 UDB CLI functions