shake7 has asked for the wisdom of the Perl Monks concerning the following question:

Hi everyone, I'm very new to Perl, and I was having a bit of an issue with MySQL and Perl. What I want to do is take the output result and assign it to a Perl variable. Here's what I have so far: 

 

use DBI;

my $dsn = 'driver={SQL Server};
Server=SERVERNAMEHERE;
database=DBNAMEHERE;
uid=UIDHERE;
pwd=PWHERE';

my $dbh  = DBI->connect("dbi:OOOO:$dsn",{AutoCommit => 1}) or die "$DB
+I::errstr\n";
my $sql = "select field1, field2 from location where restriction = 'RE
+STRICTION NAME'";
my $sth = $dbh->prepare( $sql );
$sth->execute();

$sth->finish();
$dbh->disconnect();

[download]

As you can see, I only show the actual SQL statements, I've looked at countless forum posts to help solve my problem but I don't think they answer my question accurately. For a little more information, the mySQL statement works fine and returns about 100+ results. I want to assign those results to Perl variables, and then pass those variables into another subroutine which utilizes SOAP::Lite If you'd like to take a look, here's the Perl code which I attempted to write thus far, but I only get one result which contains two fields, and not the additional hundred. Would I need to use an array as well? 

use DBI;

my $dsn = 'driver={SQL Server};
Server=SERVERNAMEHERE;
database=DBNAMEHERE;
uid=UIDHERE;
pwd=PWHERE';

# my %info = (
#     field1 => "field1",
#     field2 => "field2"
#     );

my $dbh  = DBI->connect("dbi:OOOO:$dsn",{AutoCommit => 1}) or die "$DB
+I::errstr\n";
#my $ref = $dbh->selectall_arrayref("select field1, field2 from locati
+on where restriction = 'RESTRICTION NAME'");
my $sql = "select field1, field2 from location where restriction = 'RE
+STRICTION NAME'";
my $sth = $dbh->prepare( $sql );
$sth->execute();
my @record = $sth->fetchrow_array;

#my $href = $dbh->selectall_hashref("select field1, field2 from locati
+on where restriction = 'RESTRICTION NAME'");

#print "$_\n" for (keys %$href); 

# foreach (@record){
#     print $_;
# }

print join(", ", @record);
$sth->finish();
$dbh->disconnect();

[download]

Replies are listed 'Best First'.
Re: MySQL results inserted into Perl variable
by boftx (Deacon) on Jul 09, 2014 at 00:01 UTC

    The following should give you a reference to an array of hashref in $ref: 

    my $ref = $dbh->selectall_arrayref(
    "SELECT field1, field2 FROM location
     WHERE restriction = 'RESTRICTION NAME'",
    { Slice => {} }
);

    [download]
    Each hashref should have 'field1' and 'field2' as keys. You can pass that around as needed and wind up with something like this: 
    for my $row_ref ( @{$ref} ) {
    # do something awesome with $row_ref->{field1} and $row_ref->{fiel
+d2}.
}

    [download]
    BTW, you left out use strict; and use warnings; only for the sake of brevity, right?

    Update: edited SQL for better formatting

    You must always remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.
[reply]
[d/l]
[select]
Re: MySQL results inserted into Perl variable
by trippledubs (Deacon) on Jul 09, 2014 at 00:13 UTC
    You are only fetching one array into @record at a time, so it should be like this: 
    my $sql = "select * from location";
my $sth = $dbh->prepare( $sql );
$sth->execute();
while (my @record = $sth->fetchrow_array) {
  print join(", ", @record);
  print "\n";
}
$dbh->disconnect();

    [download]
    Here is another way to do it 
    #!/usr/bin/env perl
use strict; 
use warnings;
use DBI;
use SQL::Abstract;
use Data::Dump;


my $dbh = DBI->connect("dbi:SQLite:dbfile","","");
my $sql=SQL::Abstract->new;

# See perldoc SQL::Abstract
my($stmt, @bind) = $sql->select('location'); 

my $sth = $dbh->prepare($stmt);
$sth->execute(@bind);
my $data  = $sth->fetchall_arrayref({});

dd @$data[0]; # First row from the database as hashref
dd @$data[1]; # Second row from the database as hashref
dd $data->[0]{field1}; #field1 from first row

    [download]

    Using SQL::Abstract will help avoid SQL injection attacks http://www.stonehenge.com/merlyn/UnixReview/col58.html

    You can also grow into DBI::Class and other Object Relational Mappers later on if you need.
[reply]
[d/l]
[select]

      The only change that I would make to the foregoing is that I would choose to specify the column-name, not as a bareword, but as a literal string, viz:

      ... $data->[0]{'field1'};

      By far, the most common practice in any case is to let the row of data be returned as a hashref, then to retrieve the fields from it by column-name.   If you feel cozy working with Perl variable-names from that point forward (and there’s certainly something to be said for that ...), that’s easily done.   After retrieving the row as a hashref, do this:

      my $field1 = $data->[0]{'field1'};

        ... not as a bareword, but as a literal string, ...

        Provided the bareword is a “single identifier” (i.e., something that would be a valid variable name if preceded by the appropriate sigil), Perl parses it as a string literal when it’s used as a hash subscript. From the Camel Book (4th Edition, p. 70):

        As in some shells, you can put braces around the identifier to distinguish it from following alphanumerics: "How ${verb}able!". An identifier within such braces is forced to be a string, as is any single identifier within a hash subscript. For example:

        $days{"Feb"}

        [download]

        can be written as:

        $days{Feb}

        [download]

        and the quotes will be assumed. Anything more complicated in the subscript is interpreted as an expression, and then you’d have to put in the quotes...

        (Emphasis added.)

        Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]

        The only change that I would make to the foregoing is that I would choose to specify the column-name, not as a bareword, but as a literal string, viz:

        So you have more to type?

        my $field1 = $data->[0]{'field1'};

        That is not stupid at all , you really should have $field99 instead of that pesky $data array

[reply]

Back to Seekers of Perl Wisdom