[DBD-SQLite] problems using sqlite with mod_perl in apache
I've developed a small app that uses a webpage to insert/delete data
into a sqlite database. The script work find when I run them from the
command line but not when run from the webpage.
For example, using the following code:
$db = DBI-connect(dbi:SQLite2:dbname=/home/oracle/bugpush/bugpush.db,
, , { RaiseError = 1, AutoCommit = 1 }) || die( $DBI::errstr . \n );
$sth = $db-prepare(select api_key, bugno, last_update_date, analyst
from tracked_bugs order by bugno);
if(! defined($sth) || ! ($sth)) {
print Failed to prepare SQL statement:\n;
print $DBI::errstr\n;
goto END;
}
In the apache error_log I see the following:
[Tue Dec 01 06:49:21 2009] [error] DBD::SQLite::db prepare failed: not
an error
at /home/oracle/bugpush/tracking.cgi
Oddly enough if I use the DBD::SQLite2 module this works just fine
(although the underlying db that is created is sqlite v2.x).
I have verified that this is not a permission problem as I have copied
the database file to /tmp/bugpush.db and set the permissions as 777 but
still get the same result.
Any ideas as to why this is failing to run with DBD-SQLite-1.27 but
works fine with DBD-SQLite2-0.33?
___
DBD-SQLite mailing list
DBD-SQLite@lists.scsys.co.uk
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
Re: [DBD-SQLite] problems using sqlite with mod_perl in apache
Mike, off-hand it looks like you're trying to use SQLite 2.x and SQLite 3.x on
the same database. You can't do this as these 2 SQLite major versions are
incompatible with each other, with different file formats, and SQLite 3 won't
work with SQLite 2 files. Make sure you are using just DBD::SQLite2 (SQLite 2)
or DBD::SQLite (SQLite 3) with the same database on both the command-line and in
your web app. Note that the reason the DBD::SQLite which does SQLite v2 and v3
have different Perl package names is so that you can use both in the same
program at once, if you wanted to such as to do a conversion, though for
conversion using the SQLite command-line utility for dump and then load works
well, perhaps best, also. -- Darren Duncan
Mike Campbell wrote:
I've developed a small app that uses a webpage to insert/delete data
into a sqlite database. The script work find when I run them from the
command line but not when run from the webpage.
For example, using the following code:
$db = DBI-connect(dbi:SQLite2:dbname=/home/oracle/bugpush/bugpush.db,
, , { RaiseError = 1, AutoCommit = 1 }) || die( $DBI::errstr .
\n );
$sth = $db-prepare(select api_key, bugno, last_update_date, analyst
from tracked_bugs order by bugno);
if(! defined($sth) || ! ($sth)) {
print Failed to prepare SQL statement:\n;
print $DBI::errstr\n;
goto END;
}
In the apache error_log I see the following:
[Tue Dec 01 06:49:21 2009] [error] DBD::SQLite::db prepare failed: not
an error
at /home/oracle/bugpush/tracking.cgi
Oddly enough if I use the DBD::SQLite2 module this works just fine
(although the underlying db that is created is sqlite v2.x).
I have verified that this is not a permission problem as I have copied
the database file to /tmp/bugpush.db and set the permissions as 777 but
still get the same result.
Any ideas as to why this is failing to run with DBD-SQLite-1.27 but
works fine with DBD-SQLite2-0.33?
___
DBD-SQLite mailing list
DBD-SQLite@lists.scsys.co.uk
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
Re: [DBD-SQLite] problems using sqlite with mod_perl in apache
Ok here is what I get.
Test using DBD::SQLite (same output when used command-line and via webpage)
=
the SQLite v3 is 1.27
Test using DBD::SQLite2 (same output when used command-line and via webpage)
the SQLite v2 is 0.33
So definately a difference in sqlite versions. I did a complete example
too for you to perhaps test/review.
The v3 example code:
#!/usr/bin/perl -w
use DBD::SQLite;
print Content-type: text/html\n;
print Pragma: no-cache\n;
print Cache-control: no-cache\n;
print Expires: Mon, 06 May 1970 04:00:00 GMT\n;
print \nbody;
$db = DBI-connect(dbi:SQLite:dbname=/tmp/bugpush.db, , )
|| die( $DBI::errstr . \n );
$db-do(create table xyz (c1 number));
$db-do(insert into xyz values (100));
$sth = $db-prepare(select c1 from xyz);
if(! defined($sth) || ! ($sth)) {
print Failed to prepare SQL statement:\n;
print $DBI::errstr\n;
exit 1;
}
$rc = $sth-execute();
if (! defined( $rc ) ) {
print Unable to execute prepared SQL statement:\n;
print $DBI::errstr\n;
exit 1;
}
while( $href = $sth-fetchrow_hashref ) {
print $$href{c1} . \n;
}
$sth-finish;
$db-disconnect;
print /body;
When run as 'perl file.pl' it successfully outputs the value 100 along
with the html tags. But when I access this via mod_perl in a webage I
get the Failed to prepare SQL statement: not an error error. Note
that the /tmp/bugpush.db file gets created but it is zero bytes in size
and it is owned by the correct user.
If I remove the /tmp/bugpush.db file and then modify the code to use
SQLite2 as so:
#!/usr/bin/perl -w
use DBD::SQLite2;
print Content-type: text/html\n;
print Pragma: no-cache\n;
print Cache-control: no-cache\n;
print Expires: Mon, 06 May 1970 04:00:00 GMT\n;
print \nbody;
$db = DBI-connect(dbi:SQLite2:dbname=/tmp/bugpush.db, , )
|| die( $DBI::errstr . \n );
$db-do(create table xyz (c1 number));
$db-do(insert into xyz values (100));
$sth = $db-prepare(select c1 from xyz);
if(! defined($sth) || ! ($sth)) {
print Failed to prepare SQL statement:\n;
print $DBI::errstr\n;
exit 1;
}
$rc = $sth-execute();
if (! defined( $rc ) ) {
print Unable to execute prepared SQL statement:\n;
print $DBI::errstr\n;
exit 1;
}
while( $href = $sth-fetchrow_hashref ) {
print $$href{c1} . \n;
}
$sth-finish;
$db-disconnect;
print /body;
The command-line test again returns 100 but also when accessed via
mod_perl I see it prints 100 as well.
Can you perhaps test this on your end?
On 12/1/2009 6:42 PM, Darren Duncan wrote:
Darren Duncan wrote:
As for finding out what version of DBD::SQLite (SQLite 3) you are
using, you should be able to put code like this in your code either
on the shell script or mod_perl:
use DBD::SQLite;
my $version3 = 'the SQLite v3 is ' . DBD::SQLite::VERSION;
Then display or log the value of $version3 where you can see it.
To see what version of DBD::SQLite2 (SQLite 2) you are using, same
process but for the added '2':
use DBD::SQLite2;
my $version2 = 'the SQLite v2 is ' . DBD::SQLite2::VERSION;
Correction, you need to add a $ sigil like this:
use DBD::SQLite;
my $version3 = 'the SQLite v3 is ' . $DBD::SQLite::VERSION;
use DBD::SQLite2;
my $version2 = 'the SQLite v2 is ' . $DBD::SQLite2::VERSION;
-- Darren Duncan
--
Mike Campbell | Principal Support Engineer | 407.458.2313
Oracle Support Services
7453 TG Lee Blvd. | Orlando, FL 32822
___
DBD-SQLite mailing list
DBD-SQLite@lists.scsys.co.uk
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
Re: [DBD-SQLite] problems using sqlite with mod_perl in apache
Mike Campbell wrote:
Ok here is what I get.
Test using DBD::SQLite (same output when used command-line and via webpage)
=
the SQLite v3 is 1.27
Test using DBD::SQLite2 (same output when used command-line and via webpage)
the SQLite v2 is 0.33
snip
At first glance you seem to be doing things adequately in your code samples.
However, your code looks very old-fashioned, maybe because you based it on old
code you saw somewhere, and making it into more modern Perl can probably help.
For starters, when you are using DBI, *always* configure it to throw exceptions
on errors rather than just return nothing.
Instead of this:
$db = DBI-connect(dbi:SQLite:dbname=/tmp/bugpush.db, , )
|| die( $DBI::errstr . \n );
... do this:
$db = DBI-connect(dbi:SQLite:dbname=/tmp/bugpush.db, , ,
{ RaiseError = 1 });
That alone should go further towards you getting useful error messages when you
should be.
Also you can remove all your tests like if(! defined($sth) || ! ($sth)) or if
(! defined( $rc ) ).
When DBI is set to throw exceptions, you can just code as if each step will work
and you don't need to test, since the program should die by itself if something
goes wrong.
Instead, you just do tests when you want the program to not die but rather be
more graceful when something goes wrong, and then you do it by setting up an
exception handler (an eval block), but we don't need to get into that now.
So try making those changes and see what effect is has.
I'm not immediately in the position to run your code myself, not having mod_perl
installed.
It would be helpful if you could provide more information on your setup.
What versions of Perl and DBI are you using, to start with.
To find out, you can use code like this:
my $perlvers = $[;
use DBI;
my $dbivers = $DBI::VERSION;
Run that on both command-line and mod-perl as usual.
Also your versions of Apache and mod_perl are useful to know.
Also try running your code under FastCGI, or CGI, rather than mod_perl and see
if that has any effect on the result. Include the tests for what
DBD::SQLite/etc version is running if necessary.
Maybe someone else on this list has ideas?
-- Darren Duncan
___
DBD-SQLite mailing list
DBD-SQLite@lists.scsys.co.uk
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
