Howdy:
I am trying to return an array of
all records in the subroutine below.
I want to hold all of that info
in one location so that I can use
it (each row and each column of said
row) later in a program.
In the $sql part, I had the 'selectrow_array';
as was noted to me in another email, it returns
ONE row in the array. But I want to select
ALL of the rows in the query. So, I got rid
of that and just set up the sql/prepare/execute
part.
According to the DBI book, fetchall_arrayref
can be used to fetch all the data to be returned.
So I thought I could just:
*do my query
*prep and check the query
*execute
*create an empty array
*push each row returned from fetchall_array in
to the array
*call the sub routine and do something silly
like get the 2nd records and 3rd column and
use it
[snippet of my broken code]
sub getcols {
my ($table)[EMAIL PROTECTED];
my $sql=q(
select
column_name,
nullable,
column_id,
data_type
from
all_tab_columns
where
lower(table_name)='$table'
and
column_id > 0
);
my $sth=$dbh->prepare($sql);
sth->execute();
my @rows;
while (my ($col_name, $nullable, $col_id,
$data_type)=$sql->fetchrow_array()) {
push (@rows, $sql);
}
[/snippet of my broken code]
On the other hand, there is a program that I am
trying to imitate that DOES this very thing:
[snip of working code]
sub getcols {
my($table)[EMAIL PROTECTED];
return doquery('find rows',"
SELECT a1.attname, a1.attnotnull, a1.attnum, t.typname,
a1.attlen, a1.atttypmod, c1.relkind
FROM pg_class c1,
pg_attribute a1,
pg_type t
WHERE c1.relname='$table'
and a1.attnum > 0
and a1.attrelid = c1.oid
and a1.atttypid = t.oid
ORDER BY upper(attname);
");
}
#--------------- GET THE TARGET TABLE'S VARIABLES
$target=&getcols($opt_t);
if ( $target->ntuples==0 ) {
print STDERR "Target table $opt_t not found\n";
exit 1;
}
print "resultstatus=".$result_error{$target->resultStatus}."\n".
"n= ".$target->ntuples."\n" if $debug;
#--------------- DOES THE SOURCE HAVE THE NECESSARY VARS?
while ( @trow=$target->fetchrow ) {
print "-- $trow[0]\n" if $debug;
$view=1 if $srow[6] eq 'v';
if ($trow[0] eq 'user') {
$cols[$trow[2]-1]=q!'ADMIN'!;
}
elsif ($trow[0] eq 'updated') {
$cols[$trow[2]-1]=q!'now'::datetime!;
$cols[$trow[2]-1]=q!'now'::datetime!;
}
else {
print "whatever\n";
}
[/snip of working code]
I know this is getting tired, but I would appreciate it
if someone could tell me why the 'broken code' will only
pull back one row and not hold anything into an array to
be used later.
TIA!
-X
