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;
}
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
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!
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.