DBD::SQLite select syntax problem

[zentara]

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?

[gmax]

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.

[zentara]

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.....:-)