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

[Darren Duncan]

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 at lists.scsys.co.uk
> http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite