DBI: interpreting column

ibm1620 has asked for the wisdom of the Perl Monks concerning the following question:

After two days of reading the CPAN doc on DBI and saying to myself "I KNOW it's here somewhere", I have to ask for help.

The following, I believe, retrieves *all* available metadata info for each column, and puts it into an array:

# Get all columns for each table of interest
#
my $sth_column_info
    = $dbh->column_info( $catalog, $schema, $table, undef );

my $aoa_ref = $sth_column_info->fetchall_arrayref;
$tables{$table} = $aoa_ref;

# Debugging only...
#
say $table;
for my $aref (@$aoa_ref) {
    my @list = map $_ // 'undef', @$aref;
    say join '|', @list;
}
[download]

Here is some sample output:

dw_contact_source
undef|dwcust1|dw_contact_source|contact_id|4|BIGINT|20|undef|undef|10|
+0|undef|undef|4|undef|undef|1|NO|undef|undef|undef|undef|undef|undef|
+undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|und
+ef|1|bigint(20)|undef|0
undef|dwcust1|dw_contact_source|company_id|4|SMALLINT|6|undef|undef|10
+|0|undef|undef|4|undef|undef|2|NO|undef|undef|undef|undef|undef|undef
+|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|un
+def|1|smallint(6)|undef|0
undef|dwcust1|dw_contact_source|contact_type_id|4|TINYINT|4|undef|unde
+f|10|0|undef|undef|4|undef|undef|3|NO|undef|undef|undef|undef|undef|u
+ndef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|unde
+f|undef||tinyint(4)|undef|0
undef|dwcust1|dw_contact_source|insert_date|11|DATETIME|19|undef|0|und
+ef|0|undef|undef|9|-79|undef|4|NO|undef|undef|undef|undef|undef|undef
+|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|un
+def||datetime|undef|0
undef|dwcust1|dw_contact_source|update_date|11|DATETIME|19|undef|0|und
+ef|0|undef|undef|9|-79|undef|5|NO|undef|undef|undef|undef|undef|undef
+|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|undef|un
+def||datetime|undef|0
[download]

My questions are:

* Where is each field documented?

* Where is a list of translations for the codes appearing in some fields?

* Are there any helpful constants or other symbols anywhere, for referring to the columns and the codes within?

* If there *are* helpful symbols identifying these various fields, how can I use them in a call to fetchall_arrayref? (I tried fetchall_arrayref(qw/COLUMN_NAME .../), choosing some symbol-like strings from the documentation, but only got back undefs.)

I'd be very grateful if someone could help me move past this point of complete frustration!

Comment on DBI: interpreting column_info() Select or Download Code

Replies are listed 'Best First'.
Re: DBI: interpreting column_info() by erix (Prior) on Feb 17, 2013 at 11:47 UTC
It's perhaps useful to mention what database system(s) you are querying, and what exact info you want to get out of it (instead of just 'everything'). It may be more practical to directly query the database, either by the tables in the schema 'information_schema' (as prescribed by the SQL standard) or in any DBMS-specific tables/views.	[reply]
Re: DBI: interpreting column_info() by runrig (Abbot) on Feb 17, 2013 at 22:57 UTC
In my experience, it's more useful to select column info to a hash: `use Data::Dumper qw(Dumper); my $col_names = $sth_column_info->{NAME_uc}; my %row; $sth->bind_columns(\@row{@$col_names}); while ($sth_column_info->fetch) { print Dumper \%row; }` [download] I usually don't care about the DATA_TYPE (the code), the TYPE_NAME (database specific data type name) has been more useful.	[reply] [d/l]
Re: DBI: interpreting column_info() by Anonymous Monk on Feb 17, 2013 at 07:01 UTC
see [href://http://search.cpan.org/~timb/DBI-1.623/DBI.pm#column_info and fetch hashref for the rest, check the database driver documentation, followed by the database documentation	[reply]
Re: DBI: interpreting column_info() by 7stud (Deacon) on Feb 17, 2013 at 17:11 UTC
column_info `$sth = $dbh->column_info( $catalog, $schema, $table, $column );` The statement handle returned has at least the following fields in the order shown below. Other fields, after these, may also be present. TABLE_CAT: The catalog identifier. This field is NULL (undef) if not applicable to the data source, which is often the case. This field is empty if not applicable to the table. TABLE_SCHEM: The schema identifier. This field is NULL (undef) if not applicable to the data source, and empty if not applicable to the table. TABLE_NAME: The table identifier. Note: A driver may provide column metadata not only for base tables, but also for derived objects like SYNONYMS etc. COLUMN_NAME: The column identifier. DATA_TYPE: The concise data type code. TYPE_NAME: A data source dependent data type name. COLUMN_SIZE: The column size. This is the maximum length in characters for character data types, the number of digits or bits for numeric data types or the length in the representation of temporal types. See the relevant specifications for detailed information. BUFFER_LENGTH: The length in bytes of transferred data. DECIMAL_DIGITS: The total number of significant digits to the right of the decimal point. NUM_PREC_RADIX: The radix for numeric precision. The value is 10 or 2 for numeric data types and NULL (undef) if not applicable. NULLABLE: Indicates if a column can accept NULLs. The following values are defined: SQL_NO_NULLS 0 SQL_NULLABLE 1 SQL_NULLABLE_UNKNOWN 2 REMARKS: A description of the column. COLUMN_DEF: The default value of the column, in a format that can be used directly in an SQL statement. Note that this may be an expression and not simply the text used for the default value in the original CREATE TABLE statement. For example, given: col1 char(30) default current_user -- a 'function' col2 char(30) default 'string' -- a string literal where "current_user" is the name of a function, the corresponding COLUMN_DEF values would be: Database col1 col2 -------- ---- ---- Oracle: current_user 'string' Postgres: "current_user"() 'string'::text MS SQL: (user_name()) ('string') SQL_DATA_TYPE: The SQL data type. SQL_DATETIME_SUB: The subtype code for datetime and interval data types. CHAR_OCTET_LENGTH: The maximum length in bytes of a character or binary data type column. ORDINAL_POSITION: The column sequence number (starting with 1). IS_NULLABLE: Indicates if the column can accept NULLs. Possible values are: 'NO', 'YES' and ''. SQL/CLI defines the following additional columns: CHAR_SET_CAT CHAR_SET_SCHEM CHAR_SET_NAME COLLATION_CAT COLLATION_SCHEM COLLATION_NAME UDT_CAT UDT_SCHEM UDT_NAME DOMAIN_CAT DOMAIN_SCHEM DOMAIN_NAME SCOPE_CAT SCOPE_SCHEM SCOPE_NAME MAX_CARDINALITY DTD_IDENTIFIER IS_SELF_REF Drivers capable of supplying any of those values should do so in the corresponding column and supply undef values for the others. Where is a list of translations for the codes appearing in some fields? Based on my searching, they are not readily available. You probably need to go to an SQL forum and ask some experts where those can be found.	[reply] [d/l]
Re^2: DBI: interpreting column_info() by ibm1620 (Hermit) on Feb 17, 2013 at 19:53 UTC
Where is a list of translations for the codes appearing in some fields? Based on my searching, they are not readily available. You probably need to go to an SQL forum and ask some experts where those can be found. This sounds like the best way to proceed. I've posted to http://forums.mysql.com/ unless someone can suggest a better forum.	[reply]
Re^3: DBI: interpreting column_info() by Anonymous Monk on Feb 18, 2013 at 09:08 UTC
The SQL standard Part 3: Call-Level Interface (SQL/CLI) defines the constants in sqlcli.h and ODBC in sql.h	[reply]
Re: DBI: interpreting column_info() by Anonymous Monk on Feb 18, 2013 at 08:53 UTC
Open DBD::mysql.pm and scroll down to `sub column_info`?	[reply] [d/l]


Keep It Simple, Stupid
	PerlMonks

DBI: interpreting column_info()