Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Can't query SQLite in a while loop

by josef (Acolyte)
on Sep 15, 2011 at 15:32 UTC ( #926178=perlquestion: print w/replies, xml ) Need Help??
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
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;
Thanks in advance, Josef

Replies are listed 'Best First'.
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.

      Thanks Corion, I will read with more attention the DBI doc. I wrote this script to learn how Perl work with SQLite.
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.

      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.
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 " ") {

    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.


      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 Many thanks for your comment.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://926178]
Approved by herveus
[marinersk]: sub newtest{my $expected_result = &target('foo'); my $actual_result = &target('foo'); if ($actual_result eq $expected_result) { &tdd_success(); } else { &tdd_fail(); } } # Test works after three years!
[choroba]: or nobody bothered...
[choroba]: The problem was bigger, as the test tried to call a method that didn't exist anymore
[marinersk]: :: ducking ::
[choroba]: because, someone renamed the method, but didn't notice it was used in the test, as the test was skipped
[marinersk]: Well, if the method doesn't exist, it would be hard to pass the test.
[choroba]: later, someone removed the new method, as all its usage places were safe, but didn't notice the test still used the old name
[choroba]: fortunately, it wasn't that hard to replace the method and fix a few remaining failures due to the changes we did to the codebase over the years
[marinersk]: choroba Sounds like a process improvement opportunity; tests may not all need to be run, but they should all be compiled with perl -c before check-in/promotion happens.
[choroba]: so, now I have the test, so I can start making changes in the code. Back to the original ticket, yay!

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (15)
As of 2017-05-25 15:12 GMT
Find Nodes?
    Voting Booth?