Beefy Boxes and Bandwidth Generously Provided by pair Networks vroom
There's more than one way to do things
 
PerlMonks  

DBI selectall_arrayref

by THuG (Beadle)
on Sep 02, 2004 at 15:45 UTC ( #387942=perlquestion: print w/ replies, xml ) Need Help??
THuG has asked for the wisdom of the Perl Monks concerning the following question:

Sorry, I've tried searching, I still can't get it to work.

#!C:\Perl\bin use strict; use DBI; my $fromHost = "someServer"; my $fromDB = "someDB"; my $fromTable = "someTable"; my @fromFields = ("Field 1", "Field 2", "Field 3"); my $fromDSN = "dbi:mysqlPP:database=$fromDB;host=$fromHost"; my $dbh = DBI->connect($fromDSN, "", ""); my $drh = DBI->install_driver("mysqlPP"); my $rs = $dbh->selectall_arrayref("SELECT '" . join("','", @fromFields +) . "' FROM $fromTable"); $dbh->disconnect(); foreach my $myRow (@$rs) { print "@$myRow\n"; foreach my $myElem (@$myRow) { print "$myElem,"; } print "\n"; }

This will print the following:

Field 1 Field 2 Field 3
Field 1,Field 2,Field 3,

It will repeat these two lines for each record in the table, but it will never return any data from table. I do admit, I need to know the field names (the hardcoded part will eventually be removed, and fields can be supplied at runtime), but I need to get to the data, too. What am I doing wrong, and in which document should I've found the answer?

Comment on DBI selectall_arrayref
Download Code
Re: DBI selectall_arrayref
by eric256 (Parson) on Sep 02, 2004 at 15:48 UTC

    You used single quotes for the column names, that makes them strings . Use the backtick instead, ` and you should get what you want.


    ___________
    Eric Hodges

      Use the backtick instead, `...

      Huh? Backticks are for executing commands via the operating system shell, and capturing their output. That's not going to help.


      Dave

        He's talking about an SQL quote interpretation issue, not a qx/q/qq issue:
        select 'string constant with spaces' from table select `column name with spaces` from table

        blokhead

        But they are quoted here from perl - these backticks would go to the SQL engine. Recently I've switched to PostgreSQL so I can't check it but perhaps MySQL uses them for quoting fields with spaces in them.

        Not when inside another string. Backticks (and/or square brackets) are quotes for SQL barewords:
        "SELECT COL1,COL2 FROM TABLE"
        and
        "SELECT `COL1`,`COL2` FROM `TABLE`"
        are equivalent. It allows you to have columns with whitepace in the name:
        "SELECT `COL1 NAME`,`COL2 NAME` FROM TABLE"

        "SELECT 'COL1 NAME','COL2 NAME' FROM TABLE"
        would return something entirely different.

      Ah, thanks, that's it. I tried first with "SELECT *" and got data. I then tried with backticks and brackets. Backticks work, brackets don't. Now however, I'm getting a lot of "in short at C:/Perl/site/lib/Net/MySQL.pm line 591." messages.
Re: DBI selectall_arrayref
by zby (Vicar) on Sep 02, 2004 at 15:52 UTC
    Try print the generated SQL statement. Like:
    my $query = "SELECT '" . join("','", @fromFields +) . "' FROM $fromTable"; print $query;
    You'll see that you are selecting string constants (the names of the fields). By the way I've never heard about column names with spaces in them - so I can't help write this the right way.
Re: DBI selectall_arrayref
by shemp (Deacon) on Sep 02, 2004 at 15:52 UTC
    IF you construct your query into a string and print it out, i think you'll see what the problem is. It will look like:

    SELECT 'Field 1','Field 2','Field 3' FROM FROMTABLE

    Which has your field names quoted, so MySQL sees them as literals and returns them directly. Since you have no WHERE condition, the result set (of actual rows) is not limited, so you get a result for every row in the table. But since you didnt select any data from the table, but just asked for the literals back, you get them for each row in the table.

    If you dont quote the field names, i think you'll get the results you're looking for. I.E.

    my $rs = $dbh->selectall_arrayref("SELECT " . join(",", @fromFields) . + " FROM $fromTable");
Re: DBI selectall_arrayref
by EdwardG (Vicar) on Sep 02, 2004 at 15:53 UTC

    It looks like you are selecting the literal values 'Field 1', 'Field 2' etc when you should be selecting columns.

    I don't have time right now to test, but try this line:

    my $rs = $dbh->selectall_arrayref("SELECT [" . join("],[", @fromFields +) . "] FROM $fromTable");

    Notice I've replaced your apostrophes with square brackets.

     

Re: DBI selectall_arrayref
by davido (Archbishop) on Sep 02, 2004 at 16:12 UTC

    In addition to the comments regarding quoting of field names, I wanted to mention one other thing.

    Just as you wouldn't dream of opening a file without checking the return value of open to see that the open was successful, you shouldn't just assume that your database operations are successful either, without proper error checking. For example, when you connect to the database:

    $dbh = DBI->connect($data_source, $username, $password) or die $DBI::errstr;

    Likewise, you need to error check after executing your selectall_arrayref(). This is explained in the POD:

    If "RaiseError" is not set and any method except fetchall_arrayref fails then selectall_arrayref will return undef; if fetchall_arrayref fails then it will return with whatever data has been fetched thus far. You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the data is complete or was truncated due to an error.

    Also, maybe I'm not getting it, but when you call the connect() method, according to the DBI docs, the install_driver() method is called implicitly if needed. I don't think there's any need to call it yourself, and in fact, if you do call it yourself, calling it after attempting to connect is too late; you would need that driver to be installed before you can connect. I haven't run into any need to use the install_driver() method, so I could be way off, but it seems wrong to be installing the driver after trying to connect to the database.


    Dave

      The error checking is coming. I just want to get it to work. Thanks, though.

      As to the driver, I don't know. It was in one of the documents as an example, after the connect, even. I wasn't sure what it did, but I didn't want to mess with that, yet. I now see that removing that line doesn't have any obvious affect.

      I find it almost ironic that you mention error checking each query when you quote "If 'RaiseError' is not set ..."

      Well, why not just set RaiseError and never need worry about explicitly die'ing yourself? Barring that you are underneath miles of framework and child processes, of course -- but for most everyday uses, this is all you need do:

      my $dbh = DBI->connect($ds, $user, $pass, {RaiseError => 1});
      Look into HandleError as well.

      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)
      

        My point is that some error checking needs to be done, and none was being done. If the method is the RaiseError flag, so be it. But something's got to be in place or you'll never know if the DB is connecting, if the statement handles are being prepared and executed, and so on. The irony is that I discussed the more common Perl idiom (or die ...) instead of the more DBI'ish idiom (RaiseError).


        Dave

Re: DBI selectall_arrayref
by manorhce (Sexton) on Feb 06, 2013 at 09:51 UTC

    Please try with the below it is working for me

    @values = map( { $_->[0] } @{ $dbh->selectall_arrayref($sql) } );

    Please provide

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (8)
As of 2014-04-19 16:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (483 votes), past polls