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

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

Replies are listed 'Best First'.
Re: Can't query SQLite in a while loop
by Corion (Patriarch) 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]
      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 (Abbot) on Sep 15, 2011 at 15:59 UTC

    Sounds like you just need to chomp the newline off.

    -derby
[reply]
      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]
      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]

Back to Seekers of Perl Wisdom