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

I am trying to get 3 variables from a table row in MySQL and I am using two books for help and they say to use fetchrow_array or _arrayref to get it. I've tried it several ways as shown in the book but my server keeps responding with: Can't locate object method "fetchrow_array" via package "DBI::db" (perhaps you forgot to load "DBI::db"?)
Are there alternatives to getting the data out from MySQL and assigning it to a variable? Here's two of my last attempts
JayBee says Thanks for your help :)
use warnings; use DBI; use DBD::mysql; use CGI qw( :standard ); use CGI::Carp "fatalsToBrowser"; print header, start_html('SQL Must Work!'); my $dbh = DBI->connect("DBI:mysql:data:server","uname","pass") or die( "Could not make connection to database: $DBI::errstr" ); $dbh->do( qq{SELECT Tab1, Tab2, Tab3 FROM UData } ); while (my @row = $dbh->fetchrow_array ) { my ( $item0, $item1, $item2 ) = @row; if ($item0 >= $somevalue) { print br, "$item0"; } } warn( $DBI::errstr ) if ( $DBI::err ); $dbh->disconnect(); print '<h2>All Done</h2>'; print end_html;
Here's another using STDOUT after SELECT - FROM -, to the disconnect:
my @array; while (my @row = $dbh->fetchrow_array ) { write (); } format STDOUT = @<<<<<<<<<<@<<<<<<<<<<<<<@<<<<<<<< $array[0], $array[1], $array[2] .

Replies are listed 'Best First'.
Re: fetchrow_array DBI
by Mr. Muskrat (Canon) on Feb 01, 2004 at 05:26 UTC

    Did you read the DBI documentation for the do method? It clearly states:

    This method is typically most useful for non-SELECT statements that either cannot be prepared in advance (due to a limitation of the driver) or do not need to be executed repeatedly. It should not be used for SELECT statements because it does not return a statement handle (so you can't fetch any data).

    This is mostly a cut and paste from your code and has not been tested:

    use warnings; use DBI; use DBD::mysql; use CGI qw( :standard ); use CGI::Carp "fatalsToBrowser"; print header, start_html('SQL Must Work!'); my $dbh = DBI->connect("DBI:mysql:data:server","uname","pass") or die( "Could not make connection to database: $DBI::errstr" ); my $sth = $dbh->prepare( qq{SELECT Tab1, Tab2, Tab3 FROM UData } ) or die $dbh->errstr; my $rc = $sth->execute or die $sth->errstr; if ($rc > 0) { while (my @row = $dbh->fetchrow_array ) { my ( $item0, $item1, $item2 ) = @row; if ($item0 >= $somevalue) { print br, "$item0"; } } } else { # check if there were no results or an error } $sth->finish; $dbh->disconnect; print '<h2>All Done</h2>'; print end_html;

Re: fetchrow_array DBI
by dws (Chancellor) on Feb 01, 2004 at 05:26 UTC
    I've tried it several ways as shown in the book ...

    What book are you using?

    If the book shows using do() to execute SELECT statements, then you need to get a new book. Consult the DBI documentation (via perldoc DBI) for an explanation.

    You need to rework the code to look something like:

    my $sth = $dbh->prepare(qq{SELECT ....}) or die "..."; $sth->execute() or die "..."; while ( my @row = $sth->fetchrow_array() ) { ... }
Re: fetchrow_array DBI
by b10m (Vicar) on Feb 01, 2004 at 05:29 UTC

    Check DBI, which should be your #1 reference in this matter :) The POD is fairly extensive and quite readable.

    fetchrow_array is part of the statement handle object, not the database handle object (if I get it right)

    If you change your code to the following, it'll probably works:

    use warnings; use DBI; use DBD::mysql; use CGI qw( :standard ); use CGI::Carp "fatalsToBrowser"; print header, start_html('SQL Must Work!'); # Great title ;) my $dbh = DBI->connect("DBI:mysql:data:server","uname","pass") or die( "Could not make connection to database: $DBI::errstr" ); # Slightly altering this my $sth = $dbh->prepare("SELECT Tab1, Tab2, Tab3 FROM UData"); $sth->execute; # Using $sth, instead of $dbh while (my @row = $sth->fetchrow_array ) { my ( $item0, $item1, $item2 ) = @row; if ($item0 >= $somevalue) { print br, "$item0"; } } warn( $DBI::errstr ) if ( $DBI::err ); $dbh->disconnect(); print '<h2>All Done</h2>'; print end_html;

    HTH

    --
    b10m

    All code is usually tested, but rarely trusted.
Re: fetchrow_array DBI
by Zaxo (Archbishop) on Feb 01, 2004 at 05:23 UTC

    Your connect string in line 8 looks fishy to me. Try (with suitable values of 'data' and 'server'),

    my $dbh = DBI->connect("DBI:mysql:database=data;host=server","uname"," +pass") or die( "Could not make connection to database: $DBI::errstr" );
    and see if that helps.

    Update: Mr. Muskrat++ and dws++ spotted a larger problem. I'll just add that fetchrow_array is a method of statement handles, not database handles. I'm informed that your connection string is an ok shorthand for what I suggested.

    After Compline,
    Zaxo

Re: fetchrow_array DBI
by JayBee (Scribe) on Feb 02, 2004 at 01:28 UTC
    When I used the following code after the #!..perl line
    use CGI::Carp "fatalsToBrowser"; use strict; use CGI; use DBI;

    it seemed to work, but then again i did use a different perl file I was using. Would it make that much of a difference?

    As for a few other questions, I am using "SAMS CGI (2nd ed.)" and "DEITEL How to Program." Yes I've seen a few $dbh->do(); statements in the Deitel book. Is that wrong or dangerous?

    I thank you all for helping me.

      Yes I've seen a few $dbh->do(); statements in the Deitel book. Is that wrong or dangerous?

      do() isn't at all dangerous, but it is reserved for SQL statements that don't return data.

      $dbh->do("DELETE FROM foo WHERE bar = 47");
      is O.K., but
      $dbh->do("SELECT bar FROM foo");
      isn't. This is spelled out in the on-line DBI documentation.