Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

DBD::SQLite select syntax problem

by zentara (Archbishop)
on Feb 12, 2004 at 23:19 UTC ( #328680=perlquestion: print w/replies, xml ) Need Help??

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

Hi, I'm trying to set up an SQLite db. Everything is going fine except for my select statement. What is bothersome, is that it works from the sqlite c binary, but not from Perl. I have some sample data already loaded, and here is my syntax which works from the c binary.
sqlite> .schema CREATE TABLE info ( key, name, location, pic, thumbnail, color, comment, Personal, Test_Scores, Background, Classes, Sports, Problems, Awards, Other ); sqlite>
And my select statement:
sqlite> select * from info where key = 'ScottyZ'; ScottyZ|ScottyZ||pics/ScottyZ.jpg|thumbs/ScottyZ.jpg|lightsteelblue|ni +ce|||||||| sqlite>
It's just what I want.

Now if I do the same thing from Perl, I get a error.

my $cmd = "select * from info where key = $key_sel"; $sth = $dbh->prepare($cmd); $sth->execute; my @row; while (@row = $sth->fetchrow_array() ){ print "@row\n"; my $key = $row[0]; }
I get the error:
DBD::SQLite::st execute failed: no such column: ScottyZ at ./ztkdb1d l +ine 395. Tk::Error: dbih_setup_fbav: invalid number of fields: 0, NUM_OF_FIELDS + attribute probably not set right at ./ztkdb1d line 398. main::browseThis at ./ztkdb1d line 398
now the only clue I have, is that I must put single quotes around 'ScottyZ', when I run the sqlite c program; and if I omit the single quotes, the c version of sqlite throws the same error as the Perl script.

I've tried all sorts of different syntax in $cmd, trying to isolate $key_sel, but to no avail. I've changed = to == and eq , so that isn't the glitch.

I do get my script working with the following hack:

print "$key_sel\n"; my $cmd = "select * from info"; $sth = $dbh->prepare($cmd); $sth->execute; my @row; while (@row = $sth->fetchrow_array() ){ next unless $row[0] eq $key_sel; print "@row\n"; my $key = $row[0]; }
So what am I doing wrong in Perl, should I not use $sth?

Replies are listed 'Best First'.
Re: DBD::SQLite select syntax problem
by gmax (Abbot) on Feb 12, 2004 at 23:31 UTC

    Your $key_sel is not quoted.

    Use $dbh->quote($key_sel) before executing, or better yet, use placeholders.

    my $cmd = "select * from info where key = ?"; my $sth = $dbh->prepare($cmd); $sth->execute($key_sel);

    For an explanation of your error, see "common pitfalls" in this tutorial.

     _  _ _  _  
    (_|| | |(_|><
     _|   
    
Re: DBD::SQLite select syntax problem
by zentara (Archbishop) on Feb 12, 2004 at 23:55 UTC
    Thanks, yeah, I just tried that syntax after reading thru the DBI docs. I got good functionality with:
    my $cmd = "select * from info where key=?"; $sth = $dbh->prepare($cmd); $sth->execute($key_sel); my @row; while (@row = $sth->fetchrow_array ){ print "@row\n"; my $key = $row[0]; }
    I was wondering if I was just lucky. :-) Thanks for the link to your nice tutorial...I should have read it first....but.....:-)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://328680]
Approved by gmax
Front-paged by broquaint
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (7)
As of 2021-01-28 15:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?