I'll skip over most of your mail, and go right to the heart of the matter...
> I am trying to return an array of
> all records in the subroutine below.
#
# ...WARNING: UNTESTED CODE AHEAD...
#
sub getcols {
# I prefer this over the ($table) syntax...
# but it has the same end result
my $table = shift;
# this is the array or rows returned.
my @return_rows;
# never use Perl variables in your queries, it
# opens you up to "SQL Injection" attacks.
# Do a query on Google for more info about
# what this is.
my $sql = 'select column_name, nullable,
column_id, data_type
from all_tab_columns
where lower(table_name) = ?
and column_id > 0';
# prepare with the placeholder, and pass
# $table in the execute() statement.
my $sth = $dbh->prepare($sql);
$sth->execute($table);
# this loops over each record returned,
# and pushes a REFERENCE to the array on
# to the result array. This is essentially
# a multi-dimentional array if you do not
# understand referenced.
while (my @row = $sth->fetchrow_array()) {
push @return_rows, [EMAIL PROTECTED];
}
return @return_rows;
);
# call the method like so...
my @records = getcold('my_table_name);
# print first record, first field
# (the column_name column). the array
# is zero-based, so [0] is the first item.
print $records[0][0];
# print second record, fourth field
print $records[1][3];
...etc...
See Also:
perldoc perllol
perldoc perlreftut
Rob
-----Original Message-----
From: Johnson, Shaunn [mailto:[EMAIL PROTECTED]
Sent: Friday, August 29, 2003 4:14 PM
To: [EMAIL PROTECTED]
Subject: how to return all rows selected in subroutine?
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
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
