Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

selectrow_array() confusion

by Cody Pendant (Prior)
on Nov 25, 2008 at 19:39 UTC ( #725924=perlquestion: print w/ replies, xml ) Need Help??
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 ...

Comment on selectrow_array() confusion
Select or Download Code
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).

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://725924]
Approved by Corion
Front-paged by MidLifeXis
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (17)
As of 2015-07-31 13:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (278 votes), past polls