Re: [sqlite] newbie problem with select statement using 'AND'
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 "Hello World $output ";
>
> -- web page --
> Hello World
>
> 1
> -- end --
>
> On 9/18/08, P Kishor <[EMAIL PROTECTED]> wrote:
>> On 9/18/08, hugh11 <[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 "Hello World $output ";
>> >
>> >
>> >
>> > 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, hugh11 <[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
>> > >>
Re: [sqlite] newbie problem with select statement using 'AND'
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.
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 "Hello World $output ";
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, hugh11 <[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 "Hello World $output ";
>>
>>
>
> 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 "Hello World $output ";
> ___
> 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] newbie problem with select statement using 'AND'
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 "Hello World $output ";
--
View this message in context:
http://www.nabble.com/newbie-problem-with-select-statement-using-%27AND%27-tp19561141p19561141.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
