I've done pretty much what you describe earlier today. My database is
very small, only a few tables, so I have a sql script that I use to
build and rebuild the database tables with.
I precreate my tables using the command-line sqlite3 or sqlite2
executable reading the sql file.
When I create the tables using sqlite3 and my perl script uses
DBD-SQLite-1.27 I get the prepare error when accessing the database from
the web interface (but works fine when the script is run as 'perl
file.pl'). I then remove the database file and re-create it using
sqlite2 and the same sql scripts. In my perl script I use
DBD-SQLite2-0.33 and when I access the database using the web interface
it all works.
I suspect that somehow my web server/mod_perl is using a different a
different DBD driver somehow. How can I determine what sqlite and/or
perl files the webserver is using? Since this is apache on linux I would
suspect that it uses the perl modules in /usr/lib/perl5 but perhaps not.
If the webserver is using sqlite v2 that would explain the behavior I am
seeing but I am at a loss as to how to determine what apache is using.
On 12/1/2009 4:50 PM, Darren Duncan wrote:
Mike Campbell wrote:
Well I tried with DBD-SQLite-1.27 initially and ran into the problem.
I then removed it and installed DBD-SQLite2-0.33 so I only have 1
installed at a time.
I understand that the sqlite versions are incompatible but I thought
I was using only 1 at a time.
Are you saying that DBD-SQLite-1.27 can do both v2 and/or v3? How do
I tell it which to use?
DBD-SQLite-1.27 can do *only* SQLite v3 databases.
Also note that the problem ONLY occurs when the script is accessed
via mod_perl from the apache webserver. If I run it (using
DBD-SQLite-1.27) from the command line it all works perfectly.
Something is different when run via the webserver.
The reason I mentioned the v2/v3 incompatibility was because you said
you used both DBD::SQLite2 (v2) and DBD::SQLite (v3), and your example
code was SQLite2.
Make sure you are using just DBD-SQLite2-0.33 *or* DBD-SQLite-1.27 in
all places for the same database, and make sure you create the
database file with the same version as all places you are using it.
Make sure that the Perl you are running in mod_perl is the same one as
you're running on the command line or that both have the same
DBD::SQLite.
To help troubleshoot, first put your old database aside, then make
sure your programs are all using the same DBD::SQLite, then create a
*new* database using one of those and make sure both can use it
without problems. After this is all known to be working, *then* try
to use your older/existing database, and if the problem returns then
the database may be incompatible with the DBD::SQLite you have installed.
If you have a choice, use just DBD-SQLite-1.27 and *not*
DBD-SQLite2-0.33; the latter is a legacy project and the former is the
new version that everyone should use given a choice.
If your existing SQLite database has important data, it can be
converted to the SQLite 3 if it is SQLite 2, so you can have your
whole system current.
-- Darren Duncan
On 12/1/2009 4:12 PM, Darren Duncan wrote:
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?
--
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
--
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