cannot retrieve value from select statement

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

Hi, I need some help. I have no idea why this statement keeps giving me error message of "Can't call method "fetchrow_array" without a package or object reference." Thanks so much!

my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=\\\\ntsimoddev\\
+module\\AccessDB\\GSIView.mdb';
    my $dbh = DBI->connect("dbi:ODBC:$DSN", '','') or die "$DBI::errst
+r";
my $sth2 = $dbh->do("SELECT id FROM main WHERE machine = '$machinename
+' and weekNumber = '$wwnow'");
my $refarray = $sth2->fetchrow_array();
[download]

Comment on cannot retrieve value from select statement Download Code

Replies are listed 'Best First'.
Re: cannot retrieve value from select statement by jZed (Prior) on Apr 05, 2005 at 05:35 UTC
The steps in using a statement hanlde are prepare, exectue, and fetch, not do and fetch: `my $sth = $dbh->prepare( $sql ); $sth->execute; my @array = $sth->fetchrow_array;` [download] Note also that fetchrow_array returns, as it says, an array so the return should be assigned to an array, not a scalar. Also note that this will only fetch one row. If you want more rows you need to do the fetchrow_array() inside a loop.	[reply] [d/l]
Re: cannot retrieve value from select statement by davido (Cardinal) on Apr 05, 2005 at 05:34 UTC
Probably because your $dbh->do(..) statement is silently failing and you're not checking for success or failure. When it fails, $sth2 gets assigned "undef" instead of a valid statement handle, and that's why you get an error when you call fetchrow_array() as a method of $sth2. Dave	[reply]
Re^2: cannot retrieve value from select statement by jZed (Prior) on Apr 05, 2005 at 05:48 UTC
Actually, I suspect that $dbh->do() succeeded :-). The problem is that do() doesn't ever return an $sth. Good point about error checking though. I'd recommend setting RaiseError in the connect rather than adding it to each statement. update I should add this to the "mistakes I make far too often" thread as I've also mistakenly used do() instead of prepare(). do() is for instructions; prepare() is for requests that return data. do() returns the number of rows impacted; prepare() returns a statement handle that can be used to fetch data and metadata.	[reply]
Re^3: cannot retrieve value from select statement by davido (Cardinal) on Apr 05, 2005 at 05:56 UTC
Good point. And yes, I fell into the same manner of thinking that you identified in your update. :) Ok, either way, he's not getting a valid statement handle (whether its due to silent failure, or because do() never returns a statement handle in the first place). And that's why when he attempts to use $sth2->fetchrow_array() he's getting the error message. $sth2 will never contain a statement handle, and thus, fetchfow_array() will never be a method of $sth2. Dave	[reply]