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>
[download]

And my select statement:

sqlite> select * from info where key = 'ScottyZ';
ScottyZ|ScottyZ||pics/ScottyZ.jpg|thumbs/ScottyZ.jpg|lightsteelblue|ni
+ce||||||||
sqlite>
[download]

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];
             }
[download]

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
[download]

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];
           }
[download]

So what am I doing wrong in Perl, should I not use $sth?

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.