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
