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]