Saving output of SQL query to an array??

[learingperl01]

Hello everyone I am hoping someone can point me in the right direction as I am a little confused at the moment with what I am trying to do. I am trying to perform a sql query which reads some input from another file. They problem that I am having or probably just not doing right is I want to save all results to an array which I will used to sort certain fields. I am not sure if I am saving or how to save all results to an array for later use. I have tried the following but when I print to view the results I don't get all the rows just the first. Also it appears that when I saved it to an array I get dup results. Below is the script which is currently working when printing the results directly not from an array.

Thanks for the help!!

my @lines = read_file('/tmp/test.txt');
chomp(@lines);

my $sql = q|select * from table_name where srcaddr = ? or dstaddr = ?;
foreach my $line (@lines) {
        $sth->execute($line, $line) or die "Can't execute SQL statement: $DBI::errstr\n";
        my @all_rows;
        while (($line) = $sth->fetchrow_array()) {
        push(@all_rows, $line);
        foreach (@all_rows) {
                print "$_\n";
                }
        }
}

 

[learingperl01]

Sorry pasted the old code

updated code

my @lines = read_file('/tmp/test.txt');
chomp(@lines);

my $sql = q|select * from table_name where srcaddr = ? or dstaddr = ?;
foreach my $line (@lines) {
        $sth->execute($line, $line) or die "Can't execute SQL statement: $DBI::errstr\n";
        my @all_rows;
        while (($line) = $sth->fetchrow_array()) {
                 push(@all_rows, $line);
        }
        foreach (@all_rows) {
                print "$_\n";
       }
}

 

[stevexff]

You could always add an ORDER BY clause on your SQL, then it will already be sorted...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[learingperl01]

Thanks for the reply, but the sorting in the sql itself will only sort the current record and since I am reading from a file which has hundreds of records they wont be sorted

 

[travs69]

Move your your printing array outside of your for loop. Look up selectrow_arrayref to save you the work of putting everything to a variable just to push it back into an array. You use the line variable twice once for the data at out of @lines and again for the output from $sth->fetchrow_array, thats going to come back to bite you eventually.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[learingperl01]

Here is what I got, the code below works fine and I get the correct output with all rows. Now I am trying to sort using the @{$row_ref} array var but for some reason I only get ARRAY34SVX pointers not sure why.

What I am trying to do is merge two scripts into one. At the moment what I've been doing is running a script which does all the sql queries and save the results to a file then running another script which sorts the data.(Sorting using MySQL will not be possible in the script since using sort command in the query will only sort the current query not the results after all the queries have run)

I am hoping to save the output of the sql query to an array then use that array to sort the data. I already have the sort code which is shown below, but not sure how to reference/use the array (@{$row_ref}) when sorting, how would I got about this?

thanks for the HELP!!

my @lines = read_file('/tmp/test.txt');
chomp(@lines);

foreach my $line (@lines) {
        $sth->execute($line, $line) or die "Can't execute SQL statement: $DBI::errstr\n";
        my @all_rows;
        while ( my $ref = $sth->fetchrow_arrayref()) {
                push(@all_rows, $ref);
        }
        foreach my $row_ref (@all_rows) {
                print "@{$row_ref}\n";
        }
}

Sorting code

@lines would be the array which contains the SQL query results

my @sorted = map {$_->[0]} sort {$a->[1] <=> $b->[1] ||
             $a->[2] <=> $b->[2] || $a->[3] <=> $b->[3] ||
             $a->[4] <=> $b->[4]} map {
             [$_, split(/\./, (split /\s+/, $_)[1])]} @lines;
print "$_\n" for @sorted;

 

[travs69]

I don't know why it wouldn't work.. but I'm not that familiar with map. You can always derefence it.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[learingperl01]

What do you mean derefence it? How would I do that?

Thanks,

 

[learingperl01]

This is my current code but I am having two problems depending on how I got about printing the results. If I used Dumper to print the sorted array it prints horizontally with [$VAR1 then one row per line] so sorting does not work.

If I use Dumper \@sorted array I get empty VAR results with never ending loop? Shown below.

$VAR1 = [ 
          'data here' 
          'data here' 
          'data here' 
        ];

Any ideas/pointers.

my @lines = read_file('/tmp/test.txt');
chomp(@lines);

foreach my $line (@lines) {
        $sth->execute($line, $line) or die "Can't execute SQL statement: $DBI::errstr\n";
        my @all_rows;
        while ( my $ref = $sth->fetchrow_arrayref()) {
                push(@all_rows, $ref);
        }
        my @sorted = map {$_->[0]} sort {$a->[1] <=> $b->[1] ||
             $a->[2] <=> $b->[2] || $a->[3] <=> $b->[3] ||
             $a->[4] <=> $b->[4]} map {
             [$_, split(/\./, (split /\s+/, $_)[1])]} @all_rows;
        print Dumper @sorted;
        #print "$_\n" for @sorted;
}

 

[cocl04]

<CODE>
I have an array that works....I put the array in a while loop with $i = 0 and I increment it. I tried to pass the array to my query. The query will only read the first array index. Each time it goes through the loop, it will print the value for the next index. But the Oracle query will only return data for the first index. Instead of passing the array directly, I assigned it to a global value. It also prints the data, but only passes the first value. How can I get the query to reconize the value for each increment.
</CODE>

<CODE>
i.e.

use DBI;
use strict;
use vars qw($value);..........

while ($i<=2)
{
print "$array[$i]\n";

$value = "$array[$i]";

print "$value\n".......


############################
#
# Set up Query for Store
#
############################

my $stmt = "select
week_end_date, SVP, RD,
DM, store, wtd_smrr_gain,QTD_SMRR_GAIN,
wtd_bor_gain,QTD_BOR_GAIN,
wtd_cust_gain,QTD_CUST_GAIN,
WTD_CARD_CLOSED,QTD_AVG_CARD_CL
from
bonus_4Q_store
where
SVP <> 'RD' and RD = '$value'
order by
svp,dm,store"; ......

$i++......
};
</CODE>