here is my simple test that works fine for me -- -------------- db --------------
[05:38 PM] ~/Sites/test$sqlite3 foo SQLite version 3.5.9 Enter ".help" for instructions sqlite> .s CREATE TABLE members (userID INTEGER PRIMARY KEY, userName TEXT, userOccupation TEXT); sqlite> SELECT * FROM members; 1|richard|db maker 2|igor|db wizard 3|darren|db guru 4|dan|db master sqlite>.q -------------- script -------------- [05:40 PM] ~/Sites/test$less foo.cgi #!/usr/local/bin/perl use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:SQLite:foo", "", ""); my $sth = $dbh->prepare(qq{ SELECT userID FROM members WHERE userName = ? AND userOccupation = ? }); $sth->execute('richard', 'db maker'); my $output; while (my @row = $sth->fetchrow_array) { $output .= "@row\n"; } $sth->finish; $dbh->disconnect; print "Content-type:text/html\n\n"; print "<html><body><h1>Hello World</h1> $output </body></html>"; -------------- web page -------------- Hello World 1 -------------- end -------------- On 9/18/08, P Kishor <[EMAIL PROTECTED]> wrote: > On 9/18/08, hugh111111 <[EMAIL PROTECTED]> wrote: > > > > > Hi P Kishor-3 > > Thanks for your reply. I have altered my script according to your > > recommendation, unfortunately the problem remains. I have also used the > > eval{ } function to capture the log messages. The log said "no such > column: > > userOccupation(1)" Not sure why it should append (1) to my column name. > > > Post your db schema and a few rows... let's see what is going on > > > > > > > my new perl script is... > > > > > > #!/usr/bin/perl > > > > use DBI; > > > > > > > > > > my $sth = $dbh->prepare(qq{ > > SELECT userID FROM members WHERE userName= ? AND userOccupation= ? > > }); > > > > > > eval{ $sth->execute('John', 'Carpenter') }; warn("My warning is: $@") if $@ > > > > ; > > > > my $output; > > while (my @row = $sth->fetchrow_array ) { > > $output .= join(' ', @row) . "\n"; > > } > > > > $dbh -> disconnect; > > > > print "Content-type:text/html\n\n"; > > print "<html><body><h1>Hello World</h1> $output </body></html>"; > > > > > > > > and a section of the log error message is... > > > > 18-Sep-2008 22:56:37 org.apache.catalina.core.ApplicationContext log > > INFO: cgi: runCGI (stderr):DBD::SQLite::db prepare failed: no such column: > > userOccupation(1) at dbdimp.c line 271 at > > C:\tomcat\webapps\test\WEB-INF\cgi-bin\test.pl line 6. > > 18-Sep-2008 22:56:37 org.apache.catalina.core.ApplicationContext log > > INFO: cgi: runCGI (stderr):My warning is: Can't call method "execute" on > an > > undefined value at C:\tomcat\webapps\test\WEB-INF\cgi-bin\test.pl line 10. > > > > > > > > > > > > P Kishor-3 wrote: > > > > > > On 9/18/08, hugh111111 <[EMAIL PROTECTED]> wrote: > > >> > > >> I'm using sqlite for my perl cgi website but I've got a problem with > the > > >> following select statement... > > >> > > >> SELECT userID FROM members WHERE userName='John' AND > > >> userOccupation='Carpenter' > > >> > > >> Yet if I input this statement from the sqlite3 command prompt it > works! > > >> The > > >> problem seems to be with the 'and', the following statement works fine > > >> in > > >> both cgi script and from the sqlite3 prompt... > > >> > > >> SELECT userID FROM members WHERE userName='John' > > >> > > >> I hope somebody can help with this, I'm new to both perl and sqlite > but > > >> very > > >> keen on using sqlite for future projects. > > >> > > >> > > >> > > >> here is my perl script... > > >> > > >> > > >> #!/usr/bin/perl > > >> > > >> use DBI; > > >> > > >> my $output; > > >> my $sql = "SELECT userID FROM members WHERE userName='John' AND > > >> userOccupation='Carpenter'"; > > >> > > >> my $dbh = DBI->connect("dbi:SQLite:test.db", "", ""); > > >> my $sth = $dbh->prepare($sql); > > >> $sth->execute(); > > >> > > >> while ( @row = $sth->fetchrow_array ) { > > >> $output .= "@row\n"; > > >> } > > >> > > >> $dbh -> disconnect; > > >> > > >> print "Content-type:text/html\n\n"; > > >> print "<html><body><h1>Hello World</h1> $output </body></html>"; > > >> > > >> > > > > > > always bind variables. See untested code below > > > > > > #!/usr/bin/perl > > > > > > use DBI; > > > my $dbh = DBI->connect("dbi:SQLite:test.db", "", ""); > > > > > > my $sth = $dbh->prepare(qq{ > > > SELECT userID > > > FROM members > > > WHERE userName = ? AND userOccupation = ? > > > }); > > > > > > $sth->execute('John', 'Carpenter'); > > > > > > my $output; > > > while (my @row = $sth->fetchrow_array ) { > > > $output .= join(' ', @row) . "\n"; > > > } > > > > > > $dbh -> disconnect; > > > > > > print "Content-type:text/html\n\n"; > > > print "<html><body><h1>Hello World</h1> $output </body></html>"; > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > -- > > View this message in context: > http://www.nabble.com/newbie-problem-with-select-statement-using-%27AND%27-tp19561141p19562855.html > > > > Sent from the SQLite mailing list archive at Nabble.com. > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users