Both, SQL/CLI and ODBC define a function 'SQLColumns' for obtaining
column metadata:
SQLRETURN SQLColumns
(
SQLHSTMT StatementHandle
, SQLCHAR* CatalogName , SQLSMALLINT NameLength1
, SQLCHAR* SchemaName , SQLSMALLINT NameLength2
, SQLCHAR* TableName , SQLSMALLINT NameLength3
, SQLCHAR* ColumnName , SQLSMALLINT NameLength4
);
A perlish interface - similar to table_info etc. - may look like:
$sth = $dbh->column_info( $catalog, $schema, $table, $column );
- Except for $catalog, these parameters can contain string search
patterns.
- The result set is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME
and ORDINAL_POSITION.
- ODBC defines less columns in the result set than SQL/CLI:
# SQL/CLI ODBC
-- ----------------- -------------
1 TABLE_CAT TABLE_CAT
2 TABLE_SCHEM TABLE_SCHEM
3 TABLE_NAME TABLE_NAME
4 COLUMN_NAME COLUMN_NAME
5 DATA_TYPE DATA_TYPE
6 TYPE_NAME TYPE_NAME
7 COLUMN_SIZE COLUMN_SIZE
8 BUFFER_LENGTH BUFFER_LENGTH
9 DECIMAL_DIGITS DECIMAL_DIGITS
10 NUM_PREC_RADIX NUM_PREC_RADIX
11 NULLABLE NULLABLE
12 REMARKS REMARKS
13 COLUMN_DEF COLUMN_DEF
14 SQL_DATA_TYPE SQL_DATA_TYPE
15 SQL_DATETIME_SUB SQL_DATETIME_SUB
16 CHAR_OCTET_LENGTH CHAR_OCTET_LENGTH
17 ORDINAL_POSITION ORDINAL_POSITION
18 IS_NULLABLE IS_NULLABLE
19 CHAR_SET_CAT
20 CHAR_SET_SCHEM
21 CHAR_SET_NAME
22 COLLATION_CAT
23 COLLATION_SCHEM
24 COLLATION_NAME
25 UDT_CAT
26 UDT_SCHEM
27 UDT_NAME
28 DOMAIN_CAT
29 DOMAIN_SCHEM
30 DOMAIN_NAME
31 SCOPE_CAT
32 SCOPE_SCHEM
33 SCOPE_NAME
34 MAX_CARDINALITY
35 DTD_IDENTIFIER
36 IS_SELF_REF
Luckily, the only difference is, that SQL/CLI defines more columns,
columns 1-18 are the same (and have the same meaning).
- A driver may provide column metadata not only for base tables, but
also for derived objects like SYNONYMS etc.
- There is some overlap with statement attributes (in perl) and
SQLDescribeCol (in ODBC).
However, SQLColumns provides more metadata.
If nobody raises objections, I'll prepare a patch for DBI.
Steffen
