Perl Guru Forums: Perl Programming Help: DBI: SELECT returning empty rows

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: DBI:
SELECT returning empty rows

seanliam
Novice

Oct 16, 2008, 12:34 AM

Post #1 of 2 (4758 views)

SELECT returning empty rows	Can't Post

Hi,

I am having problem with a select statement that works fine if I run it in MySQL but not from within the perl script I have written.

The script collects data from a file into a hash and when it comes across a blank line passes the hash to a subroutine that updates the database.

I have checked that all the data in the hash is being passed properly to the subroutine and all looks good but the SELECT statement doesn't return any rows. I am fairly new to perl so any help would be gratefully received.

Code

sub updateDB { 
    my %tokens = @_; 
 
    $mailbox = $tokens{'rfc822Mailbox'}; 
 
    print "Mailbox: $mailbox\n"; 
 
    my $sth = $dbh->prepare("SELECT rfc822Mailbox FROM Info WHERE rfc822Mailbox = '$mailbox'"); 
    $sth->execute; 
    $result = $sth->rows; 
    print "updateDB returned: $result\n";

What I am trying to do is check if the entry is already in the database and if so update it, if not insert it.

Thanks

1arryb
User

Feb 26, 2009, 10:58 AM

Post #2 of 2 (4055 views)

Re: [seanliam] SELECT returning empty rows [In reply to]	Can't Post

Hi Sean,

If you are expecting $sth->rows to return the data, you will wait a long time Laugh

. The rows method just returns the number of rows affected by the statement. I'm not sure if it actually returns anything useful for selects. To get the data, you need something like:

Code

... 
$sth->execute; 
while ( my $row = $sth->fetchrow_hashref() ) { 
     # Do something with the row. 
} 
...

UPDATE:

Looking at the DBI perldoc, if you want $sth->rows to return the number of rows returned by your query, you have to get them all at the same time with $sth->fetchall_hashref() or $sth->fetchall_arrayref().

UPDATE 2:

Of course, once you do that, you could just count the entries in the result structure directly.

Cheers,

Larry

(This post was edited by 1arryb on Feb 26, 2009, 11:13 AM)

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives