DBI returning error on selectrow

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

Fellow Monasterians>/p>

I'm trying to learn and apply the selectrow/col/all methods in the DBI. Most everything has worked, with the exception below. The table in question, 'users' has a row that matches the criteria. What am I missing? Thanks! (Googled and Super Searched)

Works:

my $stmt = "SELECT * FROM subscriber_contact WHERE subscriber_id = ?";
my %customer = % { $dbh->selectrow_hashref($stmtm, undef, $user_name))
+ };
[download]

Doesn't work:

my $stmt = "SELECT * FROM users WHERE username = ?";
my %userdata = % { $dbh->selectrow_hashref($stmt, undef, $user_name) }
+;
[download]

Returns error:

Can't call method "selectrow_hashref" on an undefined value
[download]

From the DBI doc (but not sure if it applies here)

If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that. Also, in a scalar context, an undef is returned if there are no more rows or if an error occurred. That undef can't be distinguished from an undef returned because the first field value was NULL. For these reasons you should exercise some caution if you use selectrow_array in a scalar context.

—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot

Comment on DBI returning error on selectrow_hashref Select or Download Code

Replies are listed 'Best First'.
Re: DBI returning error on selectrow_hashref by perrin (Chancellor) on Nov 28, 2005 at 16:25 UTC
You're getting that error because $dbh is undefined in the code where you run the second example.	[reply]
Re^2: DBI returning error on selectrow_hashref by dsb (Chaplain) on Nov 28, 2005 at 17:04 UTC
Is it possible that this error occurs because the query returns no data? I seem to remember a similar message in a script of mine, but through debugging I found that the query was returning no results. dsb This `@ISA my( $cool ) %SIG`	[reply]
Re^3: DBI returning error on selectrow_hashref by perrin (Chancellor) on Nov 28, 2005 at 17:22 UTC
No, the error is caused by $dbh having nothing in it.	[reply]
Re^2: DBI returning error on selectrow_hashref by bradcathey (Prior) on Nov 28, 2005 at 20:40 UTC
That was it. Funny, 'cuz I always connect to the database first...except this time. Thanks. And now, when I get that error, I can Super Search PMs :-) —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply]
Re: DBI returning error on selectrow_hashref by VSarkiss (Monsignor) on Nov 28, 2005 at 16:28 UTC
Prior to executing your second example, somehow your database handle `$dbh` has become undefined (that's what the error messgae means). Are you disconnecting or something before that? Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l]
Re^2: DBI returning error on selectrow_hashref by wazzuteke (Hermit) on Nov 28, 2005 at 17:11 UTC
I think your first comment is the culperate. You should only get this error if your `$dbh` variable is not defined in-line within the application. If the handle is disconnected, the variable should still be defined. Although will recieve another DBI specific error such as "DBI: Cannot selectrow_hashref from a disconnected database handle" (I really don't know the specific error off hand), you should not expect to see the Perl error. However, for future reference, and a little off-topic, if you think there might be a chance you are running into an area where your database handle might be disconnected, you can always test against the boolean handle attribute 'Active' and reconnect. For example: `unless ( $dbh->{'Active'} ) { # Some re-connection magic here }` [download] Good Luck!! ---hA\|\|ta---- `print map{$_.' '}grep{/\w+/}@{[reverse(qw{Perl Code})]} or die while ( 'trying' );`	[reply] [d/l] [select]
Re^3: DBI returning error on selectrow_hashref by jZed (Prior) on Nov 29, 2005 at 05:51 UTC
Your example is good for some uses, but won't prevent the kind of error the OP shows, in fact it will generate a second error of the same kind. A disconnected $dbh is still a $dbh object, his error showed there was no $dbh so doing $dbh->{Active} would generate the same error.	[reply]
Re: DBI returning error on selectrow_hashref by jZed (Prior) on Nov 29, 2005 at 05:55 UTC
As perrin says, the error comes from not having a database handle object. The best way to prevent this kind of error is to always check that the connect() succeeds with my $dbh = DBI->connect(...) or die DBI::errstr; Or, the way I prefer: my $dbh = DBI->connect($dsn,$usr,$pass,{RaiseError=>1}); Either of those ways will die with an appropriate warning if there is no $dbh object.	[reply]


good chemistry is complicated, and a little bit messy -LW
	PerlMonks

DBI returning error on selectrow_hashref