--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;
$sth->execute($dbh->selectrow_array('select distinct tableowner from
pg_tables'));
#
# 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]
