SQLite3::SQLException: SQL logic error or missing database

[Une Bévue]

I've read a lot of people reporting probs with SQLite3 and this error
message :

SQLite3::SQLException: SQL logic error or missing database

my setup :

sqlite3-ruby latest
ruby 1.8.6 (2007-03-13 patchlevel 0) [powerpc-darwin8.9.0]
Mac OS X 10.4.10

here is my script :

----------------------------------------------------------------------
#! /usr/bin/env ruby

begin; require 'rubygems'; rescue LoadError; end

require 'sqlite3'

DB = SQLite3:atabase.new( "devicephonebook_create_5_bis.db" )

DB.create_function( "get_zy_string", 1 ) do | func, value |
if value.nil?
func.result = "***ZY***NULL"
else
func.result = "***ZY***#{value.to_s}"
end
end

DB.create_function( "get_py_string", 1 ) do | func, value |
if value.nil?
func.result = "***PY***NULL"
else
func.result = "***PY***#{value.to_s}"
end
end

DB.create_function( "get_first_char", 1 ) do | func, value |
if value.nil?
func.result = 20
else
func.result = value[0]
end
end

sql = "INSERT INTO devicephonebook ( lastName, firstName ) VALUES(
'Smith','John' );"
DB.execute( sql )
----------------------------------------------------------------------
which returns :

# = > SQLite3::SQLException: SQL logic error or missing database

Obviously i'm sure the file database does exist.


the sql content of the database :
----------------------------------------------------------------------
BEGIN TRANSACTION;
CREATE TABLE devicephonebook( UID INTEGER PRIMARY KEY AUTOINCREMENT
,lastName TEXT NOT NULL DEFAULT(''),firstName TEXT NOT NULL
DEFAULT(''), lastChar INTEGER , fullLastName TEXT , ZYFLName TEXT ,
PYFLName TEXT , hasPhoto INTEGER DEFAULT(0) , timeStamp TEXT);
CREATE INDEX nameIndex ON devicephonebook (PYFLName, lastName,
firstName, lastChar, hasPhoto);
CREATE TRIGGER DELETE_EXT_TRIGGER AFTER DELETE ON devicephonebook
BEGIN DELETE FROM extraphonebook WHERE extraphonebook.UID = OLD.UID;
SELECT delete_from_cache(OLD.UID); END;
CREATE TRIGGER INSERT_PBK_TRIGGER AFTER INSERT ON devicephonebook BEGIN
UPDATE devicephonebook SET fullLastName=NEW.lastName||NEW.firstName
,ZYFLName=get_zy_string(NEW.lastName||NEW.firstName)
,PYFLName=get_py_string(NEW.lastName|| NEW.firstName)
,lastChar=get_first_char(NEW.lastName|| NEW.firstName) WHERE
UID=NEW.UID; END;
COMMIT;
----------------------------------------------------------------------

this database structure is coming from a mobile phone (e2831) and works
well on my mobile ))

clearly i don't understand why this doesn't work.

also i've try to change the INSERT to insert all the values in a row,
this doesn't change anything...

 

[Clifford Heath]

I've read a lot of people reporting probs with SQLite3 and this error
message :

SQLite3::SQLException: SQL logic error or missing database

SQLLite doesn't handle multi-threading. I don't see any in your example,
but it bit me, for example using it with Webrick. Switched to mongrel,
problem vanished - mongrel only handles one request at a time per process.

Clifford Heath.

 

[Une Bévue]

SQLLite doesn't handle multi-threading. I don't see any in your example,
but it bit me, for example using it with Webrick. Switched to mongrel,
problem vanished - mongrel only handles one request at a time per process.

I've tried also surrounding the DB.execute by a DB.transaction without
any effect...

What i suspect right now os i don't use the same version of SQLite 3 on
my mobile phone than on my Mac...

I hope the SQLite version on mobile phone isn't a custom one because it
is running on linux with Texas Instrument chip...

I've asked too on the specific SQLite 3 mailing list because, i think,
this as nothing to do with ruby (?)...

BUT a google search about "SQL logic error or missing database" found
tons of posts )))

 

[Clifford Heath]

I've tried also surrounding the DB.execute by a DB.transaction without
any effect...

The transaction won't help, you get the error from starting
a transaction while one is already running.

Put a begin... rescue=>e ... end around your db access code,
and in the rescue clause, print e.backtrace (it's an array).
That'll tell you exactly where the error is being thrown.

Go and read the code of the sqlite adapter, and don't be afraid
to fill it full of print statements. I personally favour:

print "message: "+caller*"\n\t"

If you can't *then* see why your code is violating SQLite's
non-reentrant requirement, come back and ask again, with code
samples. In other words, debug it yourself before asking here
again. Nothing strange or magical is happening. There is an
error in the code, and we can't find it for you.

Clifford Heath.

 

[Une Bévue]

The transaction won't help, you get the error from starting
a transaction while one is already running.

Put a begin... rescue=>e ... end around your db access code,
and in the rescue clause, print e.backtrace (it's an array).
That'll tell you exactly where the error is being thrown.

Go and read the code of the sqlite adapter, and don't be afraid
to fill it full of print statements. I personally favour:

print "message: "+caller*"\n\t"

If you can't *then* see why your code is violating SQLite's
non-reentrant requirement, come back and ask again, with code
samples. In other words, debug it yourself before asking here
again. Nothing strange or magical is happening. There is an
error in the code, and we can't find it for you.

OK, i'm trying that.

 

[Une Bévue]

Nothing strange or magical is happening. There is an
error in the code, and we can't find it for you.

OK i did some print out and I think "the code is violating SQLite's
non-reentrant requirement" because of a trigger:

the database SQL code (symbolic) :

BEGIN TRANSACTION;
CREATE TABLE devicephonebook( ... );
CREATE TRIGGER DELETE_EXT_TRIGGER AFTER DELETE ON devicephonebook BEGIN
.... ; END;
CREATE TRIGGER INSERT_PBK_TRIGGER AFTER INSERT ON devicephonebook BEGIN
UPDATE ... , lastChar=get_first_char(NEW.lastName|| NEW.firstName)
WHERE UID=NEW.UID; END;
COMMIT;

i believe I've understood when i'm wrong, let me know if you agree.

The above SQL is a dump of the database itself coming from a phone
mobile, i've cpied it into my Mac and want only to make an INSERT.

But the INSERT itself triggers (see "CREATE TRIGGER INSERT_PBK_TRIGGER
AFTER INSERT ON devicephonebook" above) an UPDATE which might violate
SQLite's non-reentrant requirement ???

Am I right here ?

When i do an insert from sqlite3-ruby by :

DB = SQLite3:atabase.new( "devicephonebook_create_5_bis.db" )

+ definition of the three custom functions (get_zy_string,
get_py_string, get_first_char)

sql = "INSERT INTO devicephonebook ( lastName, firstName ) VALUES(
'Smith', 'John' );"

DB.execute( sql )

the last line execute the INSERT and, afterwards triggers the UPDATE
internaly because in my print out i see the call to get_first_char
and the error arrose after the return from that function.

would that means, i have to change the SQL sheme avoiding the lines as
"CREATE TRIGGER INSERT_PBK_TRIGGER AFTER INSERT ON" ???

if it's true, i don't understand the usefullness of such a trigger...

probably i'm wrong with the above and this could be truly a "logic
error" where the value returned by "get_first_char" isn't of correct
type or something like that.


In fact, at least i know now the error comes after "CREATE TRIGGER
INSERT_PBK_TRIGGER AFTER INSERT ON devicephonebook ...
lastChar=get_first_char( ...)...

 

[Clifford Heath]

But the INSERT itself triggers (see "CREATE TRIGGER INSERT_PBK_TRIGGER
AFTER INSERT ON devicephonebook" above) an UPDATE which might violate
SQLite's non-reentrant requirement ???
Am I right here ?

I'm not an sqlite3 guru by any means, but I'd be amazed if that was it.
It's not that sqlite is non-reentrant, but that it only supports one
transaction at one time (subtle difference). The UPDATE is (or should
be!) part of the same transaction.

I suggest you find the SQlite developer community and ask there.

Clifford Heath.