TechNet
Export (0) Print
Expand All

SQLColAttribute

 

System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

You can use SQLColAttribute to retrieve an attribute of a result set column for either prepared or executed ODBC statements. Calling SQLColAttribute on prepared statements causes a roundtrip to SQL Server. The SQL Server Native Client ODBC driver receives result set column data as part of statement execution, so calling SQLColAttribute after the completion of SQLExecute or SQLExecDirect does not involve a server roundtrip.

System_CAPS_ICON_note.jpg Note


ODBC column identifier attributes are not available on all SQL Server result sets.

Field identifierDescription
SQL_COLUMN_TABLE_NAMEAvailable on result sets retrieved from statements that generate server cursors or on executed SELECT statements containing a FOR BROWSE clause.
SQL_DESC_BASE_COLUMN_NAMEAvailable on result sets retrieved from statements that generate server cursors or on executed SELECT statements containing a FOR BROWSE clause.
SQL_DESC_BASE_TABLE_NAMEAvailable on result sets retrieved from statements that generate server cursors or on executed SELECT statements containing a FOR BROWSE clause.
SQL_DESC_CATALOG_NAMEDatabase name. Available on result sets retrieved from statements that generate server cursors or on executed SELECT statements containing a FOR BROWSE clause.
SQL_DESC_LABELAvailable on all result sets. The value is identical to the value of the SQL_DESC_NAME field.

The field is zero length only if a column is the result of an expression and the expression does not contain a label assignment.
SQL_DESC_NAMEAvailable on all result sets. The value is identical to the value of the SQL_DESC_LABEL field.

The field is zero length only if a column is the result of an expression and the expression does not contain a label assignment.
SQL_DESC_SCHEMA_NAMEOwner name. Available on result sets retrieved from statements that generate server cursors or on executed SELECT statements containing a FOR BROWSE clause.

Available only if the owner name is specified for the column in the SELECT statement.
SQL_DESC_TABLE_NAMEAvailable on result sets retrieved from statements that generate server cursors or on executed SELECT statements containing a FOR BROWSE clause.
SQL_DESC_UNNAMEDSQL_NAMED for all columns in a result set unless a column is the result of an expression that does not contain a label assignment as part of the expression. When SQL_DESC_UNNAMED returns SQL_UNNAMED, all ODBC column identifier attributes contain zero length strings for the column.

SQL Server Native Client ODBC driver uses the SET FMTONLY statement to reduce server overhead when SQLColAttribute is called for prepared but unexecuted statements.

For large value types, SQLColAttribute will return the following values:

Field identifierDescription of change
SQL_DESC_DISPLAY_SIZEThis is the maximum number of characters required to display data from the column. For large value type columns, the value returned is SQL_SS_LENGTH_UNLIMITED.
SQL_DESC_LENGTHReturns the actual length of the column in the result set. For large value type columns, the value returned is SQL_SS_LENGTH_UNLIMITED.
SQL_DESC_OCTET_LENGTHReturns the maximum length of a large value type column. SQL_SS_LENGTH_UNLIMITED is used to indicate unlimited size.
SQL_DESC_PRECISIONReturns the value SQL_SS_LENGTH_UNLIMITED for large value type columns.
SQL_DESC_TYPEReturns SQL_VARCHAR, SQL_WVARCHAR, and SQL_VARBINARY for large value types.
SQL_DESC_TYPE_NAMEReturns "varchar", "varbinary", "nvarchar" for the large value types.

For all versions, column attributes are reported for only the first result set when multiple result sets are generated by a prepared batch of SQL statements.

The following column attributes are extensions exposed by the SQL Server Native Client ODBC driver. The SQL Server Native Client ODBC driver returns all values in the NumericAttrPtr parameter. The values are returned as SDWORD (signed long) except SQL_CA_SS_COMPUTE_BYLIST, which is a pointer to a WORD array.

Field identifierValue returned
SQL_CA_SS_COLUMN_HIDDEN*TRUE if the column referenced is part of a hidden primary key created to support a Transact-SQL SELECT statement containing FOR BROWSE.
SQL_CA_SS_COLUMN_IDOrdinal position of a COMPUTE clause result column within the current Transact-SQL SELECT statement.
SQL_CA_SS_COLUMN_KEY*TRUE if the column referenced is part of a primary key for the row and the Transact-SQL SELECT statement contains FOR BROWSE.
SQL_CA_SS_COLUMN_OPInteger specifying the aggregate operator responsible for the value in a COMPUTE clause column. Definitions of the integer values are in sqlncli.h.
SQL_CA_SS_COLUMN_ORDEROrdinal position of the column within an ODBC or Transact-SQL SELECT statement's ORDER BY clause.
SQL_CA_SS_COLUMN_SIZEMaximum length, in bytes, required to bind a data value retrieved from the column to a SQL_C_BINARY variable.
SQL_CA_SS_COLUMN_SSTYPENative data type of data stored in the SQL Server column. Definitions of the type values are in sqlncli.h.
SQL_CA_SS_COLUMN_UTYPEBase data type of the SQL Server column's user-defined data type. Definitions of the type values are in sqlncli.h.
SQL_CA_SS_COLUMN_VARYLENTRUE if the column's data can vary in length, FALSE otherwise.
SQL_CA_SS_COMPUTE_BYLISTPointer to an array of WORD (unsigned short) specifying the columns used in the BY phrase of a COMPUTE clause. If the COMPUTE clause does not specify a BY phrase, a NULL pointer is returned.

The first element of the array contains the count of BY list columns. Additional elements are the column ordinals.
SQL_CA_SS_COMPUTE_IDcomputeid of a row that is the result of a COMPUTE clause in the current Transact-SQL SELECT statement.
SQL_CA_SS_NUM_COMPUTESNumber of COMPUTE clauses specified in the current Transact-SQL SELECT statement.
SQL_CA_SS_NUM_ORDERSNumber of columns specified in an ODBC or Transact-SQL SELECT statement's ORDER BY clause.

* Available if statement attribute SQL_SOPT_SS_HIDDEN_COLUMNS is set to SQL_HC_ON.

SQL Server 2005 introduced driver-specific descriptor fields to provide additional information to denote the XML schema collection name, the schema name, and the catalog name, respectively. These properties do not require quotation marks or an escape character if they contain non-alphanumeric characters. The following table lists these new descriptor fields:

Column nameTypeDescription
SQL_CA_SS_XML_SCHEMACOLLECTION_CATALOG_NAMECharacterAttributePtrThe name of the catalog where an XML schema collection name is defined. If the catalog name cannot be found, then this variable contains an empty string.

This information is returned from the SQL_DESC_SS_XML_SCHEMACOLLECTION_CATALOG_NAME record field of the IRD, which is a read-write field.
SQL_CA_SS_XML_SCHEMACOLLECTION_SCHEMA_NAM ECharacterAttributePtrThe name of the schema where an XML schema collection name is defined. If the schema name cannot be found, then this variable contains an empty string.

This information is returned from the SQL_DESC_SS_XML_SCHEMACOLLECTION_SCHEMA_NAME record field of the IRD, which is a read-write field.
SQL_CA_SS_XML_SCHEMACOLLECTION_NAMECharacterAttributePtrThe name of an XML schema collection. If the name cannot be found, then this variable contains an empty string.

This information is returned from the SQL_DESC_SS_XML_SCHEMACOLLECTION_NAME record field of the IRD, which is a read-write field.

Also, SQL Server 2005 introduced new driver-specific descriptor fields to provide additional information for either a user-defined type (UDT) column of a result set or a UDT parameter of a stored procedure or parameterized query. These properties do not require quotation marks or an escape character if they contain non-alphanumeric characters. The following table lists these new descriptor fields:

Column NameTypeDescription
SQL_CA_SS_UDT_CATALOG_NAMECharacterAttributePtrThe name of the catalog containing the UDT.
SQL_CA_SS_UDT_SCHEMA_NAMECharacterAttributePtrThe name of the schema containing the UDT.
SQL_CA_SS_UDT_TYPE_NAMECharacterAttributePtrThe name of the UDT.
SQL_CA_SS_UDT_ASSEMBLY_TYPE_NAMECharacterAttributePtrThe assembly qualified name of the UDT.

The existing descriptor field identifier SQL_DESC_TYPE_NAME is used to indicate the name of the UDT. The SQL_DESC_TYPE field for a UDT type column is SQL_SS_UDT.

For the values returned for date/time types, see the "Information Returned in IRD Fields" section in Parameter and Result Metadata.

For more information, see Date and Time Improvements (ODBC).

SQLColAttribute supports large CLR user-defined types (UDTs). For more information, see Large CLR User-Defined Types (ODBC).

SQLColAttribute queries the new implementation row descriptor (IRD) field, SQL_CA_SS_IS_COLUMN_SET, to determine if a column is a column_set column.

For more information, see Sparse Columns Support (ODBC).

SQLColAttribute Function
ODBC API Implementation Details
SQLSetStmtAttr

Community Additions

ADD
Show:
© 2016 Microsoft