Storing the results of a SQL select query that reutrns more than one row into a perl array

[ajguitarmaniac]

How do I go about storing the results of a SQL select query that reutrns more than one row into a perl array?

Please let me know if this is valid

my @array =("Select query")
OR

my $select_qry = ("SELECT column from table WHERE condition");
my @array = ($select_qry);

OR

my $select_qry = ("SELECT column from table WHERE condition");
my @array;
push (@array,("$select_qry));

Monks, please guide me through this.

[moritz]

You use DBI, and pick an appropriate fetch_* method, like fetchall_arrayref.

[CountZero]

Or why not go for the full monty and explore DBIx::Class? It is more than likely you do not have to store the results in an array at all and you can work with the DBIx::Class' objects to retrieve your data on a "just in time" basis.

[Anonymous Monk]

Tutorials: Database Programming: DBI recipes

[oko1]

None of the examples you've given are valid for storing the results of a SQL select query - because none of them perform an SQL query but simply save a string in a variable (this is akin to writing "REALLY FAST CAR" on a piece of paper and then expecting that paper to win the Gran Prix.) As has been pointed out, you need to use a mechanism - e.g., the DB client directly, or the DBI module - which will perform that query. Here's an example of what that might look like:

# Silly example, but fits the spec
my @languages = qx#echo "select Language from CountryLanguage where Co
+untryCode='RUS'"|mysql -u username database_name#;

Another example, more in line with how it would usually be coded:

#!/usr/bin/perl -w
use strict;
use DBI;
$|++;

my %info = (
    db      =>  "database_name",
    user    =>  "username",
    pass    =>  "pAsSwOrD"
);

my $dbh = DBI->connect("DBI:mysql:$info{db}", $info{user}, $info{pass}
+);
my $ref = $dbh->selectall_arrayref("select Language from CountryLangua
+ge where CountryCode='RUS'")
    or die $DBI::errstr;

my @languages = map { $_->[0] } @$ref;    # Store the first element ('
+Language') in array

Update: Saved the return as an array rather than printing it.

[ajguitarmaniac]

Thanks Folks!! Funny analogy that, paper winning the gran(d) prix!! lol!! Thank you !!