SQLSetColAttributes() - Set column attributes

SQLSetColAttributes() sets the data source result descriptor (column name, type, precision, scale, and nullability) for one column in the result set. If you set the data source result descriptor, Db2 ODBC does not need to obtain the descriptor information from the database management system server.

ODBC specifications for SQLSetColAttributes()

Table 1. SQLSetColAttributes() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
No No No

Syntax

SQLRETURN  SQLSetColAttributes (SQLHSTMT        hstmt,
                              SQLUSMALLINT      icol,
                              SQLCHAR     FAR   *pszColName,
                              SQLSMALLINT       cbColName,
                              SQLSMALLINT       fSqlType,
                              SQLUINTEGER       cbColDef,
                              SQLSMALLINT       ibScale,
                              SQLSMALLINT       fNullable);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. SQLSetColAttributes() arguments
Data type Argument Use Description
SQLHSTMT hstmt input Statement handle.
SQLUSMALLINT icol input Column number of result data, ordered sequentially left to right, starting at 1.
SQLCHAR * szColName input Pointer to the column name. If the column is unnamed or is an expression, this pointer can be set to NULL, or an empty string can be used.
SQLSMALLINT cbColName input The length, in bytes, of szColName buffer.
SQLSMALLINT fSqlType input The SQL data type of the column. The following values are recognized:
  • SQL_BIGINT
  • SQL_BINARY
  • SQL_BLOB
  • SQL_CHAR
  • SQL_CLOB
  • SQL_DBCLOB
  • SQL_DECFLOAT
  • SQL_DECIMAL
  • SQL_DOUBLE
  • SQL_FLOAT
  • SQL_GRAPHIC
  • SQL_INTEGER
  • SQL_LONGVARBINARY
  • SQL_LONGVARCHAR
  • SQL_LONGVARGRAPHIC
  • SQL_NUMERIC
  • SQL_REAL
  • SQL_ROWID
  • SQL_SMALLINT
  • SQL_TYPE_DATE
  • SQL_TYPE_TIME
  • SQL_TYPE_TIMESTAMP
  • Start of changeSQL_TYPE_TIMESTAMP_WITH_TIMEZONEEnd of change
  • SQL_VARBINARY
  • SQL_VARCHAR
  • SQL_VARGRAPHIC
  • SQL_XML
SQLUINTEGER cbColDef input The precision of the column on the data source. If fSqlType is SQL_XML, ODBC ignores cbColDef.
SQLSMALLINT ibScale input The scale of the column on the data source. This is ignored for all data types except SQL_DECIMAL, SQL_NUMERIC, SQL_TYPE_TIMESTAMP.
SQLSMALLINT fNullable input Indicates whether the column allows null values. This must of one of the following values:
  • SQL_NO_NULLS - the column does not allow null values.
  • SQL_NULLABLE - the column allows null values.

Usage

This function is designed to help reduce the amount of network traffic that can result when an application is fetching result sets that contain an extremely large number of columns. If the application has advanced knowledge of the characteristics of the descriptor information of a result set (that is, the exact number of columns, column name, data type, nullability, precision, or scale), then it can inform Db2 ODBC rather than having Db2 ODBC obtain this information from the database, thus reducing the quantity of network traffic.

An application typically calls SQLSetColAttributes() after a call to SQLPrepare() and before the associated call to SQLExecute(). An application can also call SQLSetColAttributes() before a call to SQLExecDirect(). This function is valid only after the statement attribute SQL_NODESCRIBE has been set to SQL_NODESCRIBE_ON for this statement handle.

SQLSetColAttributes() informs Db2 ODBC of the column name, type, and length that would be generated by the subsequent execution of the query. This information allows Db2 ODBC to determine whether any data conversion is necessary when the result is returned to the application.

Recommendation: Use this function only if you know the exact nature of the result set.

The application must provide the result descriptor information for every column in the result set or an error occurs on the subsequent fetch (SQLSTATE 07002). Using this function only benefits those applications that handle an extremely large number (hundreds) of columns in a result set. Otherwise the effect is minimal.

Return codes

After you call SQLSetColAttributes(), it returns one of the following values:
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 3. SQLSetColAttributes() SQLSTATEs
SQLSTATE Description Explanation
01004 Data truncated. The szColName argument contains a column name that is too long. To obtain the maximum length of the column name, call SQLGetInfo with the InfoType SQL_MAX_COLUMN_NAME_LEN.
08S01 Communication link failure. The communication link between the application and data source fails before the function completes.
24000 Invalid cursor state. A cursor is open on the statement handle.
HY000 General error. An error occurred for which there is no specific SQLSTATE and for which no implementation defined SQLSTATE is defined. The error message returned by SQLGetDiagRec() in the argument szErrorMsg describes the error and its cause.
HY001 Memory allocation failure. Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY004 Invalid SQL data type. The value specified for the argument fSqlType is not a valid SQL data type.
HY010 Function sequence error. The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the SQLParamData() or SQLPutData() functions.)
HY013 Unexpected memory handling error. Db2 ODBC is not able to access the memory that is required to support execution or completion of the function.
HY090 Invalid string or buffer length. The value specified for the argument cbColName is less than 0 and not equal to SQL_NTS.
HY099 Nullable type out of range. The value specified for fNullable is invalid.
HY104 Invalid precision value. This SQLSTATE is returned for one or more of the following reasons:
  • The value specified for fSqlType is either SQL_DECIMAL or SQL_NUMERIC and the value specified for cbColDef is less than 1, or the value specified for ibScale is less than 0 or greater than the value for the argument cbColDef (precision).
  • The value specified for fSqlType is SQL_TYPE_TIMESTAMP and the value for ibScale is less than 0 or greater than 12.
HY002 Invalid column number. The value specified for the argument icol is less than 1 or greater than the maximum number of columns supported by the server.

Example

The following example shows an application that uses SQLSetColAttributes() to set the data source results descriptor.
Figure 1. An application that sets the data source results descriptor
/* ... */
    SQLCHAR         stmt[] =
    { "Select id, name from staff" };
/* ... */
  /* Tell DB2 ODBC not to get column attribute from the server for this hstmt */
    rc = SQLSetStmtAttr(hstmt,SQL_ATTR,NODESCRIBE,(void *)SQL_NODESCRIBE_ON, 0);
    rc = SQLPrepare(hstmt, stmt, SQL_NTS);
/* Provide the columns attributes to DB2 ODBC for this hstmt */
    rc = SQLSetColAttributes(hstmt, 1, "-ID-", SQL_NTS, SQL_SMALLINT,
                             5, 0, SQL_NO_NULLS);
    rc = SQLSetColAttributes(hstmt, 2, "-NAME-", SQL_NTS, SQL_CHAR,
                             9, 0, SQL_NULLABLE);
    rc = SQLExecute(hstmt);
    print_results(hstmt); /* Call sample function to print column attributes
                             and fetch and print rows.  */
    rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    rc = SQLEndTran( SQL_HANDLE, DBC, hdbc, SQL_COMMIT);
    printf("Disconnecting .....\n");
    rc = SQLDisconnect(hdbc);
    rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    if (rc != SQL_SUCCESS)
        return (terminate(henv, rc));
    rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);
    if (rc != SQL_SUCCESS)
        return (terminate(henv, rc));
    return (SQL_SUCCESS);
}                               /* end main */