From: unbewusst.sein@... (=?ISO-8859-1?Q?Une_B=E9v?= =?ISO-8859-1?Q?ue?=)
Date: 2007-10-16T17:00:08+09:00
Subject: Re: SQLite3::SQLException: SQL logic error or missing database

Clifford Heath <no@spam.please.net> wrote:

> 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::Database.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( ...)...



-- 
Une B�vue