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:
Here is some sample output:# 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; }
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
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!
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: DBI: interpreting column_info()
by runrig (Abbot) on Feb 17, 2013 at 22:57 UTC | |
Re: DBI: interpreting column_info()
by Anonymous Monk on Feb 17, 2013 at 07:01 UTC | |
Re: DBI: interpreting column_info()
by erix (Prior) on Feb 17, 2013 at 11:47 UTC | |
Re: DBI: interpreting column_info()
by 7stud (Deacon) on Feb 17, 2013 at 17:11 UTC | |
by ibm1620 (Hermit) on Feb 17, 2013 at 19:53 UTC | |
by Anonymous Monk on Feb 18, 2013 at 09:08 UTC | |
Re: DBI: interpreting column_info()
by Anonymous Monk on Feb 18, 2013 at 08:53 UTC |