Can't query SQLite in a while loop

Hallo Perlmonks,

I hope that someone might be able to point me in the right direction. I want to query a small SQLite-Database into the while loop, but I can't fetch any data. When I set manually the param $name_to_check, it's work fine; sure, I rewrite in this case any input data from the console (to test is ok). I use Linux Fedora 15, Perl version: 5.12.x and SQLite: 3.7.5 The SQLite database have one table (name_tab) with two cols (name and age). For example:

Name          Age
-------------------
Ana           25
George        20
Denis         21
Jim           28
Mily          22
[download]

I get no errors when the script runs and I can't find the origin of the problem.

#!/usr/bin/perl -W

use strict;
use DBI;

my ($sth,$dbh);
my $db_attr  = {RaiseError => 1, PrintError => 1};   
my $db_error = $DBI::errstr;

# Open the connector
my $name_db  = '/opt/name.db';
$dbh = DBI->connect("dbi:SQLite:$name_db","","",$db_attr); 
if (defined($db_error) && $db_error ne " ") {
    print STDERR "Cannot connect to database $name_db: $db_error\n"; 
    exit;
} # end if


# init
$| = 1;    


# Prepare the SQL
my $query = "SELECT name,age FROM name_tab WHERE name = ?";  
$sth = $dbh->prepare($query) or die "Couldn't prepare statement: " . $
+dbh->errstr;

# the main loop. I read the new line from the console, and fetch the r
+ow for this name

while (defined($name_to_check = <>)) {

    # my $name_to_check = 'Denis';     # rewrite the entry, is working
    
    $sth->execute($name_to_check) or die "Couldn't execute statement: 
+" . $sth->errstr; 
    my ($name_from_db,$age) = $sth->fetchrow_array();
    print "Fetched from SQLite: $name_from_db    Age: $age\n";

}

# Disconect from SQLite
#-----------------------------------------------------------------
$dbh->disconnect(); 
exit 0;
[download]

Thanks in advance, Josef

Comment on Can't query SQLite in a while loop Select or Download Code
Re: Can't query SQLite in a while loop by Corion (Pope) on Sep 15, 2011 at 15:57 UTC
You neither `->finish` the results nor do you `->fetchrow` them all. SQLite does not like more than one statement executing in parallel. Read DBI on the appropriate methods to call.	[reply] [d/l] [select]
Re^2: Can't query SQLite in a while loop by josef (Novice) on Sep 18, 2011 at 11:47 UTC
Thanks Corion, I will read with more attention the DBI doc. I wrote this script to learn how Perl work with SQLite.	[reply]
Re: Can't query SQLite in a while loop by derby (Monsignor) on Sep 15, 2011 at 15:59 UTC
Sounds like you just need to chomp the newline off. -derby	[reply]
Re^2: Can't query SQLite in a while loop by josef (Novice) on Sep 18, 2011 at 11:57 UTC
Thanks Derby. After you pointed me to check the length of the input string the solution was to chomp $name_to_check and the problem was solved. I've missed a small "CRLF" char.	[reply]
Re: Can't query SQLite in a while loop by armstd (Friar) on Sep 17, 2011 at 13:57 UTC
There are a couple issues with this code that are unrelated to your specific problem, but important to resolve: `my $db_attr = {RaiseError => 1, PrintError => 1}; my $db_error = $DBI::errstr; $dbh = DBI->connect("dbi:SQLite:$name_db","","",$db_attr); if (defined($db_error) && $db_error ne " ") {` [download] First, it looks like you're not checking for connect errors properly. What would you expect to see in $db_error after connect? Second, you're using RaiseError, and checking $DBI::errstr. That is why it's clear these issues are unrelated to your specific problem. You'd be getting an exceptions anyway. You should pick one error or exception mechanism and go with it, not both. --Dave	[reply] [d/l]
Re^2: Can't query SQLite in a while loop by josef (Novice) on Sep 18, 2011 at 12:19 UTC
Hallo armstd, you are right. I will change to PrintError => 0, RaiseError => 0. I found more example about on the Perl DBI book from Q`Reilly http://oreilly.com/catalog/perldbi/chapter/ch04.html. Many thanks for your comment.	[reply]


"be consistent"
	PerlMonks

Can't query SQLite in a while loop

The best material for plates (tableware) is: