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

I'm running the code below and not having much luck getting the bound variables to return any data. I can't see what I'm doing wrong so I need a second (or more) pair of eyes to give me some help please. I know data is being returned, because I get 166 blank lines returned! 
#!/usr/bin/perl

use strict;
use DBI;

my $level=3;
my $district="50";
my $dbh = DBI->connect('dbi:mysql:database=db','un', 'pw') || die $DBI
+::errstr;

my  $distquery = "SELECT tm_club_district, tm_club_number FROM clubs W
+HERE tm_club_district=?";

my %row; # if omitted Global symbol "%row" requires explicit package n
+ame
my $row; # if omitted Global symbol "$row" requires explicit package n
+ame

my $sqlquery = $dbh->prepare($distquery);
$sqlquery->execute($district);

$sqlquery->bind_columns( \( @row{ @{$sqlquery->{NAME_lc} } } ));
print "START\n";
while ($sqlquery->fetch)
{
  print $row->{tm_club_number} . " - " . $row->{tm_club_district} ."\n
+";
}
print "EMD\n";

[download]
I followed the example in perldoc DBI but seem to be getting nowhere. The script checks out and works fine, however I'm getting no data from the backend database into the bound variables. $ perl -cw ww.pl ww.pl syntax OK In the dbi log I can see the bind variables 
>parse_params statement SELECT tm_club_district, tm_club_number FROM c
+lubs WHERE tm_club_district=?
Binding parameters: SELECT tm_club_district, tm_club_number FROM clubs
+ WHERE tm_club_district='50'
 <- dbd_st_execute returning imp_sth->row_num 166
    <- execute= 166 at ww.pl line 16
    -> FETCH for DBD::mysql::st (DBI::st=HASH(0xN)~INNER 'NAME_lc') th
+rN
    -> dbd_st_FETCH_attrib for 02701b10, key NAME_lc
    -> dbd_st_FETCH_attrib for 02701b10, key NAME
    <- FETCH= [ 'tm_club_district' 'tm_club_number' ] at ww.pl line 18

[download]
I'm obviously doing something very stupid and basic and can't see it. Thanks in advance.

Replies are listed 'Best First'.
Re: DBI and bind_columns issue
by Tux (Canon) on Aug 15, 2014 at 06:01 UTC

    As mentioned by anon-monk, you do not want/need $row with binding columns like this

    my %row;
my $sth = $dbh->prepare ("SELECT tm_club_district, tm_club_number FROM
+ clubs WHERE tm_club_district = ?");
$sth->execute ($district);
# Bind fetch to $row{tm_club_district} and $row{tm_club_number}
$sth->bind_columns (\( @row{@{$sth->{NAME_lc}}} ));
# fetch without bind_columns is an alias to fetchrow_arraref (returnin
+g $row)
# but *with* bind_columns you use the return values as boolean
while ($sth->fetch) {
    print $row{tm_club_number} . " - " . $row{tm_club_district} ."\n";
    }
print "END\n";

    [download]

    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
      Oh lordy! I see where I screwed up. I was using -> where I shouldn't have been *facepalm* Now I do feel stupid! I copied a section I was using in an older bit of code which used the fetchrow_hashref before I knew about bind_columns and never got around to fixing it.
[reply]
Re: DBI and bind_columns issue
by Anonymous Monk on Aug 15, 2014 at 01:23 UTC

    See your problem starts here 

    $sqlquery->bind_columns( \( @row{ @{$sqlquery->{NAME_lc} } } ));

    [download]

    Just use fetchrow_hashref

     

    rm -rf goners
[reply]
[d/l]

      Bad monk. Don't ill-advice. The OP uses the fastest way to fetch data. Your change would lose all speed:

      $ cat test.pl
use 5.16.2;
use Benchmark qw(:hireswallclock cmpthese);
use DBI;

my $dbh = DBI->connect ();

sub bindc
{
    my $sth = $dbh->prepare ("select * from url");
    $sth->execute;
    my %url;
    $sth->bind_columns (\@url{@{$sth->{NAME_lc}}});
    my $n = 0;
    while ($sth->fetch) { $n++ }
    return $n;
    } # bindc

sub ftchh
{
    my $sth = $dbh->prepare ("select * from url");
    $sth->execute;
    my %url;
    my $n = 0;
    while (my $ref = $sth->fetchrow_hashref) { $n++ }
    return $n;
    } # bindc

say "url has ", bindc (), " records";
say "url has ", ftchh (), " records";

cmpthese (10, {
    bind_columns        => \&bindc,
    fetch_hashref       => \&ftchh,
    });
$ perl test.pl
url has 491826 records
url has 491826 records
              s/iter fetch_hashref  bind_columns
fetch_hashref   1.72            --          -87%
bind_columns   0.224          670%            --
$

      [download]

      Enjoy, Have FUN! H.Merijn
[reply]
[d/l]

        Bad monk. Don't ill-advice. The OP uses the fastest way to fetch data. Your change would lose all speed:

        The OP doesn't get any data really really fast :) With that recommendation he'd get data, 87% slower, but still plenty fast enough :)

[reply]

      also %row and $row are two different variables, you're binding to %row, but you're printing from $row

       

      rm -rf goners
[reply]

Back to Seekers of Perl Wisdom