It's very strange. I used your example but I still get the same problem. I
think I'll try this on a different machine tomorrow.
P Kishor-3 wrote:
>
> 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
>
>
--
View this message in context:
http://www.nabble.com/newbie-problem-with-select-statement-using-%27AND%27-tp19561141p19564103.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
