Johnson, Shaunn wrote:
> --howdy.
>
> --i think i am getting closer to solving this, but
> --i have ran into a problem.
>
> --the below program works to the point where it
> --will return the data, but not ALL of the data.
>
> --for example, i get only about 50 or so lines when
> --if i do the queries by hand, i get over 200 with
> --different $tables and $users. in the outfile,
> --i only get the first person and some of her tables.
> --is it my query? how can i tell where this script
> --is failing since Perl has not generated an error?
>
> --thanks again!
>
> -X
>
>
> [snip script]
>
> #!/usr/bin/perl -w
>
> # created: 16 June 03 -X
>
> # script to get table information and
> # append it to a new file (DDL format)
> # and prep for use by Oracle
> #
>
> use strict;
> use diagnostics;
> use DBI;
> use Cwd;
>
> ###
> ### i create a list of users to
> ### abuse later
> ###
>
> my $list="userlist.txt";
> my $file="newlist.txt";
> my $local_dir=cwd;
>
> open (DIR, $local_dir) or die "can nae open local directory: $!";
>
>
> my $date=`date '+%d %B %Y'`;
> chomp ($date);
>
> # should be the address of sender and users
> my $addr='[EMAIL PROTECTED]';
>
> # when all else fails ... cheat ...
>
> # get a list of users best way you
> # can and keep a text copy.
>
> # connect to database
>
> my $dbh=DBI->connect('dbi:Pg:dbname='test_db', 'joe_user')
> or die "Can not connect: $!";
>
> # get list to use later
>
> my $sql =<<EOQ;
> select tablename, tableowner
> from pg_tables
> where tableowner = ?
> EOQ
>
> # set up error checking
> my $sth=$dbh->prepare($sql) or die "Error =", DBI::errstr;
Suggest you use RaiseError=>1, PrintError=>0 on your connect() call so you
don't have to do the "or die" business each time.
>
> $sth->execute($dbh->selectrow_array('select distinct tableowner from
> pg_tables'));
The selectrow_array() method returns only the first row from the inner
query.
The inner query is unecessary anyway if you simply want the rows for all
users. To get an array of all the pg_table rows, just do this:
my $rows = $dbh->selectall_arrayref(q[select tablename, tableowner from
pg_tables]);
Now $rows is a reference to an array of rows. Each row is an array with two
elements: the tablename and tableowner values. Run this and then dump it out
to see what I mean:
require Data::Dumper;
print Dumper($rows);
Your $sth can be used when you want only the rows for selected users. Given
$sth as prepared above, you can do something like:
for my $user (qw/popeye olive bluto/) {
my $rows = $dbh->selectall_arrayref($sth, undef, $user);
# now $rows is a ref to the array of rows for the current user
print "$user has ", scalar(@$rows), " tables:\n";
print Dumper($rows);
}
>
> #
> # this works; now will test the sql part
> #
> #$sth->execute ('joe_user');
>
> unless ($sth->execute) {
> print"\n\tExecute failed for stmt:\n\t$sql\nError = ",
> DBI::errstr; $sth->finish;
> $dbh->disconnect;
> die "\n\t\tClean up finished\n";
> }
>
> open FILE, ">$file" or die "can nae open text file: $!";
>
> while ( my($table, $user)=$sth->fetchrow_array ) {
> print FILE "$table", "$user", "\n";
> }
>
> close (DIR);
> close (FILE);
>
> $sth->finish();
>
> $dbh->disconnect;
>
> __END__
>
>
> [/snip script]
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
