Shaunn Johnson 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]
> #!/usr/bin/perl -w
>
> # script to connect to Postgres do a count
> # get a list of tables info (DDL) and make
> # new DDL files to move to Oracle
> #
> # need table owner, table name, column, type
> # and pass that into a file
> #
> # should be cool
>
> use strict;
> use diagnostics;
> use DBI;
> use POSIX 'strftime';
>
> my $datestr=strftime '%d%B%Y',localtime;
> # connect to postgres via DBI
> my $dbh=DBI->connect('dbi:Pg:dbname=test_db', 'joe_user')
> or die "Can not connect: $!";
>
> our $listo=&getTable();
>
> sub getTable() {
> my $tabsth = $dbh->table_info();
> while (my ($qual, $owner, $name, $type, $rem)=
>
> # rename the name -> table for fetching
> # and remembering what it's called later
>
> $tabsth->fetchrow_array() ) {
> my $table = $name;
> open (FILE, ">$name.dll") or die "Snootch-to-the-nootch\n";
> print FILE "--Owner: $owner\n";
> print FILE "create $type $name (\n";
>
> # statement
>
> my $statement = "select * from $table";
>
> # prep and execute the SQL statemetn
>
> my $sth = $dbh->prepare ($statement);
> $sth->execute();
>
> my $fields = $sth->{NUM_OF_FIELDS};
> print FILE "Number of fields: $fields\n\n";
>
> # iterate through allthe fields and dump
> # the field info
>
> for (my $i = 0; $i <$fields; $i++) {
> my $type = $sth->{TYPE}->[$i];
> my $prec = $sth->{PRECISION}->[$i];
> print FILE "$name\t$type\t$prec\n";
> }
>
> #print FILE "$owner, $name\n";
> #return $qual, $owner, $name, $type, $rem;
> }
> }
>
> print "$listo\n";
>
> close (FILE);
>
> $dbh->disconnect;
>
> __END__
>
> [/snip]
>
>
> And so far, these are the results I am seeing.
>
> [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).
Hi Shaunn.
What you need is the 'type_info' method. Like this
my $type = $sth->{TYPE}->[$i];
my $type_name = $dbh->type_info($type)->{TYPE_NAME};
HTH,
Rob
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
