http://www.perlmonks.org?node_id=725924

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

If I do this:
my $d = DBI->connect( 'stuff', 'user', 'pass' ) || die("couldn't connect to database"); my ($min,$max) = $d->selectrow_array( "SELECT min(id),max(id) from images where foo like 'bar%'" )
then $max comes out undef.

But if I run that from the command line mysql client, it looks fine.

mysql> SELECT min(id),max(id) from images where foo like 'bar%'; +---------+---------+ | min(id) | max(id) | +---------+---------+ | 61893 | 62437 | +---------+---------+ 1 row in set (0.59 sec)
So, what am I missing?


Nobody says perl looks like line-noise any more
kids today don't know what line-noise IS ...

Replies are listed 'Best First'.
Re: selectrow_array() confusion
by MidLifeXis (Monsignor) on Nov 25, 2008 at 19:58 UTC

    Works just fine under DBD::Oracle. Are any errors thrown by the DBD driver?

    --MidLifeXis

      I was able to get this to work for me as well using MySQL.

      use DBI; use Data::Dumper; my $dbh = DBI->connect( 'DBI:mysql:database:host', 'user', 'pass', {RaiseError=>1} ); print Dumper [ $dbh->selectrow_array( 'select max(id),min(id) from movie' ) ];

      jeffa

      L-LL-L--L-LL-L--L-LL-L--
      -R--R-RR-R--R-RR-R--R-RR
      B--B--B--B--B--B--B--B--
      H---H---H---H---H---H---
      (the triplet paradiddle with high-hat)
      
Re: selectrow_array() confusion
by lostjimmy (Chaplain) on Nov 25, 2008 at 20:01 UTC
    Just out of curiosity, are you getting the expected value in $min?
Re: selectrow_array() confusion
by Cody Pendant (Prior) on Nov 25, 2008 at 21:35 UTC
    To answer the questions:
    • definitely connecting to the same db in both cases
    • that's my actual code in all salient points
    • it works with the hashref
    • it gets the right thing in $min
    I'm on DBD version 1.48 but I can't figure out what version of the mysql driver it is. MySQL is 5.1.25-rc, and it's on OS X.

    Unfortunately I need to be away from this machine for a while. Thanks for the help so far.



    Nobody says perl looks like line-noise any more
    kids today don't know what line-noise IS ...

      To get DBD::mysql version do:

      perl -MDBD::mysql -le 'print $DBD::mysql::VERSION;'

      To get DBI versions do:

      perl -MDBI -le 'print $DBI::VERSION;'

      selectrow_array is built into each DBD so if you upgrade DBI you need to rebuild DBD::mysql. Recent versions of DBI (1.605 - http://search.cpan.org/~timb/DBI-1.607/Changes) contain some fixes for selectrow_array but I don't think ones that affect what you are doing. However, I'm guessing you have upgraded DBI and not rebuilt DBD::mysql as at present I cannot think of any other reason for this.

        I'm guessing you have upgraded DBI and not rebuilt DBD::mysql
        That is indeed possible. I'll check as soon as I can.


        Nobody says perl looks like line-noise any more
        kids today don't know what line-noise IS ...
        The $DBD::mysql version is 4.007.


        Nobody says perl looks like line-noise any more
        kids today don't know what line-noise IS ...
      You said "it works with the hashref", which I gather means you get the right answer when you do:
      my $href = $d->selectall_hashref( ... );
      If that's true, then maybe you haven't shown all the relevant code. In particular, after you call $d->selectrow_array(...) how many other things happen before you notice that $max is undef? And do any of those things involve $max (in ways that might cause it to become undef)?

      (UPDATE: I should point out that I use DBI and Mysql on OSX, and the "selectrow_array" call works for me when I do the equivalent type of query and list assignment on one of my databases.)

        I really have shown you the right code, honestly.

        This is a minimum test case

        #!/sw/bin/perl use strict; use warnings; use DBI; my $d = DBI->connect( 'DBI:mysql:imagedb:localhost', 'root', '******', { RaiseError => 1 } ) || die("couldn't connect to database"); my ( $min, $max ) = $d->selectrow_array( "SELECT min(id),max(id) from images where gallery_path like '/px6% +'") || die $d->errstr; print "min: $min; max: $max;\n";
        And when I run it, I get:
        Use of uninitialized value in concatenation (.) or string at ab.cgi li +ne 13. min: 61893; max: ;


        Nobody says perl looks like line-noise any more
        kids today don't know what line-noise IS ...
Re: selectrow_array() confusion
by perrin (Chancellor) on Nov 25, 2008 at 20:38 UTC
    At a glance, I'd bet it's something about the context when you get the result. If that isn't your real code, maybe it's getting scalar context somehow?
Re: selectrow_array() confusion
by Anonymous Monk on Nov 25, 2008 at 20:51 UTC
    Works under SQLite.
    Are you sure you are connecting to the same database in both test cases?
    What happens if you change from selectrow_array to selectrow_hashref, and dump the output?
    #!/usr/bin/perl use strict; use warnings; use DBI; use Data::Dumper; $Data::Dumper::Useqq=1; my $db_file = 'pm725924.sqlite'; unlink $db_file; my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file") or die; $dbh->{PrintError} = 1; $dbh->{PrintWarn } = 1; $dbh->do($_) or die for split "\n", <<'END_OF_SQL'; create table images( id smallint, foo varchar(10) ); insert into images values( 61893, 'bar-man' ); insert into images values( 62437, 'baretta' ); insert into images values( 50000, 'a' ); insert into images values( 70000, 'c' ); END_OF_SQL #print Dumper $dbh->selectrow_array( print Dumper $dbh->selectrow_hashref( "SELECT min(id),max(id) from images where foo like 'bar%'" ); $dbh->disconnect or die;
    Produces:
    $VAR1 = { "min(id)" => 61893, "max(id)" => 62437 };
Re: selectrow_array() confusion
by Equidor (Sexton) on Nov 27, 2008 at 17:13 UTC
    Never tried the database handle selectrow_array() method, but
    we know the statement handle fetchrow_array() works:

    $dbh = DBI->connect($dsn, $user, $passwd);
    $sql = "select min(thing) as itsmin, max(thing) as itsmax from images where foo like 'bar%'";
    $sth = $dbh->prepare($sql);
    $sth->execute;
    while ( @row = $sth->fetchrow_array() ) {
      print @row . "\n";
    }

    Check the CPAN DBI doc.

Re: selectrow_array() confusion
by Christoph (Initiate) on Apr 15, 2009 at 01:09 UTC

    "Me too"

    This happens to me, too (with DBD::mysql version 4.005, currently). Example:

    my($name,$type) = $dbh->selectrow_array("select name,type from
         folders where id=$id") || die $dbh->errstr;

    The first value, $name, is fine (correct), the second value ($type) is undefined, even though the select statement works fine from the command line. I've seen it on and off, and I just split it into two queries to solve the problem (but I don't like doing that).