Welcome to WebmasterWorld Guest from 34.204.189.171

Forum Moderators: open

Perl DBI: selectrow array with no results throws error

     
1:15 am on Jun 18, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1193
votes: 119


I'm not sure why this one is happening, so I'm hoping you guys and gals can help me figure it out.

I'm using Perl with module DBI. Here is the code in question:

if ($contents{'username'}) {
($found_password, $found_email) =
$dbh->selectrow_array("SELECT password, email FROM users WHERE username=? LIMIT 1", undef,
$contents{'username'}) or die "Couldn't execute SELECT FROM users: " . $dbh->errstr;
}

In this case, $contents{'username'} is submitted by the user.

It works fine unless username isn't found, and then I just get an error message:

Couldn't execute SELECT FROM users: at example.cgi line 383.

(where line 383 begins the code that I pasted above; note that $dbh->errstr is apparently empty)

I do not set RaiseError, so per the docs I should just return an empty list:

[metacpan.org...]

Any thoughts? TIA!
3:21 pm on June 22, 2019 (gmt 0)

New User from US 

joined:June 22, 2019
posts:13
votes: 2


How is the username field defined?

It works fine unless username isn't found, and then I just get an error message:

If the username isn't found, then the statement is being executed successfully and the die statement won't be called. If it is being called, then the statement is failing.

The first step I'd take would be to verify the contents of $contents{'username'} to make sure it contains EXACTLY what you expect. Then run the sql statement from the mysql cli to see if it succeeds or fails with an error message.
3:40 pm on June 22, 2019 (gmt 0)

New User from US 

joined:June 22, 2019
posts:13
votes: 2


Wait, I might be wrong on one point. I need to run a test but I think an empty list will return false in scalar context. If so, that would be the reason why the die statement is being executed.
6:14 pm on June 22, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1193
votes: 119


The first step I'd take would be to verify the contents of $contents{'username'} to make sure it contains EXACTLY what you expect.

I test for the minimum requirements: that it contains at least 1 letter, and then trim any opening or trailing spaces. But in my testing, I'm getting the error on simple mistakes; eg, the registered username is "csdude55" and the user typed "csdude5" (which doesn't exist).


I need to run a test but I think an empty list will return false in scalar context. If so, that would be the reason why the die statement is being executed.

The docs referred to context sensitivity, too, so you're right on that point:

If called in a list context, it returns the first row of data from the statement. The $statement parameter can be a previously prepared statement handle, in which case the prepare is skipped.

If any method fails, and "RaiseError" is not set, selectrow_array will return an empty list.

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.

I had never really heard of "context sensitivity" in Perl before, and the docs don't clarify it at all. But if I understand correctly, I'm assigning it to a list, right?

($found_password, $found_email) = ...;

If I'm wrong, what's the right way to assign it to a list? Or SHOULD I be assigning it to a scalar?
6:56 pm on June 22, 2019 (gmt 0)

New User from US 

joined:June 22, 2019
posts:13
votes: 2


That is the correct way to assign a list. What I'm not sure about (since my perl is a bit rusty) is what DBI is using when when testing the or conditional portion of the statement.

You could take the or die statement out and instead $dbh->errstr or the $found_password and $found_email vars.

Personally, I normally don't like doing db handle calls like that. I prefer to separate out the prepare and execute statements so that I can have finer granularity on the error handling.
6:59 pm on June 22, 2019 (gmt 0)

New User from US 

joined:June 22, 2019
posts:13
votes: 2


Off topic: Doesn't this forum allow us to edit our posts to fix typos? Or do I need to have some minimum number of posts before that access right is given?

[edited by: fishmonger at 7:36 pm (utc) on Jun 22, 2019]

7:10 pm on June 22, 2019 (gmt 0)

Administrator from US 

WebmasterWorld Administrator not2easy is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 27, 2006
posts:4510
votes: 348


OT response - Editing can be done for a short time after posting. Beyond that initial limit you can report the error and request a correction. To do that, click on the Msg# button.

Note that an automated "Welcome" note should have been sent when you signed up. In case you missed it, it offers similar basic information. See this link: Welcome to WebmasterWorld [webmasterworld.com]

;)
7:37 pm on June 22, 2019 (gmt 0)

New User from US 

joined:June 22, 2019
posts: 13
votes: 2


Great, ty
9:27 pm on June 22, 2019 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11842
votes: 242


if ($contents{'username'}) {
($found_password, $found_email) =
$dbh->selectrow_array("SELECT password, email FROM users WHERE username=? LIMIT 1", undef,
$contents{'username'}) or die "Couldn't execute SELECT FROM users: " . $dbh->errstr;
}

If the username isn't found, then the statement is being executed successfully and the die statement won't be called. If it is being called, then the statement is failing.

That is the correct way to assign a list. What I'm not sure about (since my perl is a bit rusty) is what DBI is using when when testing the or conditional portion of the statement.

the basic code structure here is:
if(something){assignment or die}

DBI has nothing to do with the conditional.
it's only the perl assignment that is involved.
in a perl assignment operation, "the final value of the variable on the left is returned as the value of the assignment as a whole" [oreilly.com] (quoted from the "camel book")
9:58 pm on June 22, 2019 (gmt 0)

New User from US 

joined:June 22, 2019
posts: 13
votes: 2


So, when that DBI call doesn't find the username, it returns an empty list which evaluates to false and triggers the or die clause to execute.

There are a couple ways to correct this problem. One would be to remove the or die clause and use a separate statement to test the success/failure of retrieving the data.
5:34 am on June 23, 2019 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11842
votes: 242


remove the or die clause and use a separate statement to test the success/failure of retrieving the data

that's where i would start.
6:54 am on June 23, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1193
votes: 119


Ha! You guys are right... I removed the or die clause, and now it works just fine.

I would have never guessed that one.