Front page | perl.beginners |
Postings from July 2003
Re: using table_info()
Thread Previous
From:
Thomas A. Lowery
Date:
July 1, 2003 15:08
Subject:
Re: using table_info()
Message ID:
20030701220341.GA18993@stlowery.net
The sql shell that comes with DBI (DBI::Shell or dbish) includes a
describe command. The results vary based on the version of DBD::Pg
installed, but it attempts to translate the type value to a name.
(See DBI/Shell.pm for complete code)
Here's how it determines the types:
...
my $sth = $dbh->column_info(undef, undef, $tab);
if (ref $sth) {
my @t_data = (); # An array of arrays
while (my $row = $sth->fetchrow_hashref() ) {
push my @out_row, map { $row->{$_} } qw/COLUMN_NAME/;
my $type = $row->{DATA_TYPE};
if (defined $row->{COLUMN_SIZE}) {
$type .= "(" . $row->{COLUMN_SIZE} . ")";
}
push(@out_row
, $type
, $row->{NULLABLE} eq 1 ? q{ N}: q{}
, q()
, $row->{REMARKS} || q{}
);
push @t_data, \@out_row;
}
$sth->finish; # Complete the handler from column_info
...
}
# Or use a select * from x where 1 ...
my $sql = qq{select * from $tab where 1 = 0};
$sth = $dbh->prepare( $sql );
$sth->execute;
my $cnt = $#{$sth->{NAME}}; #
my @names = qw{NAME TYPE NULLABLE};
my @ti;
for ( my $c = 0; $c <= $cnt; $c++ ) {
push( my @j, $sth->{NAME}->[$c] || 0 );
my $m = $dbh->type_info($sth->{TYPE}->[$c]);
my $s;
if (ref $m eq 'HASH') {
$s = $m->{TYPE_NAME}; # . q{ } . $sth->{TYPE}->[$c];
} elsif (not defined $m) {
$s = $sth->{TYPE}->[$c];
} else {
warn "describe: not good. Not good at all!";
}
if (defined $sth->{PRECISION}->[$c]) {
$s .= "(" . $sth->{PRECISION}->[$c] || '';
$s .= "," . $sth->{SCALE}->[$c]
if ( defined $sth->{SCALE}->[$c]
and $sth->{SCALE}->[$c] ne 0);
$s .= ")";
}
push(@j, $s, $sth->{NULLABLE}->[$c] ne 1? qq{N}: qq{Y} );
}
$sth->finish;
On Tue, Jul 01, 2003 at 03:48:47PM -0400, Johnson, Shaunn wrote:
> Howdy:
>
> I would like to use the table_info() method for getting
> database / table metadata from a database. In this case,
> PostgreSQL 7.2.x.
>
> I am reading the "Programming the Perl DBI" book and
> I am using the following for my script to get a list of
> tables and get *some* information.
>
> [snip]
> --Owner: joe
> create TABLE temp_gaps (
> Number of fields: 4
>
> contract 1042
> d_eff_dt 1082
> gapd_eff_dt 1082
> gapd_end_dt 1082
>
> [/snip]
>
> My question is: is it possible to just get
> the $type to reflect what it's named for (as an
> example, I can only assume that '1042' is
> really 'CHAR' and '1082' is DATE. But I don't
> want to go through some 1500 tables to figure
> that out).
Thread Previous