[DBD-SQLite] problems using sqlite with mod_perl in apache

[Darren Duncan]

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?