SQLProcessor: Exception happened when creating a trigger

[starofrainnight]

the SQLProcessor.Script set to :

(the sql scripts create by sqlite administrator)

Code: Select all

CREATE TABLE [tblFirst] (
[F_ID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
);

CREATE TABLE [tblSecond] (
[S_ID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[F_ID] BIGINT  NULL
);

CREATE TRIGGER [on_after_delete_from_tblFirst] 
AFTER DELETE ON [tblSecond] 
FOR EACH ROW 
BEGIN 

-- strange here!! when disable the next line, it's fine! why?
delete from tblFirst where tblFirst.F_ID = OLD.F_ID;

END;

when have "delete from tblFirst where tblFirst.F_ID = OLD.F_ID;" and SQLProcessor.Execute, the Exception appear .... but why it run correct in SQLite Administrator, are there something difference??

thanks your response

[mdaems]

Try to use a different delimiter... here the ';' at the end of the 'delete' command is not a delimiter for the create trigger statement.

The script parser tries to split the script into individual statements and sends them to the server one by one. Using delimiter ';' the create trigger statement would end without the 'end;' line.

when you use delimiter '£' the script should be:

Code: Select all

CREATE TABLE [tblFirst] (
[F_ID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
) £

CREATE TABLE [tblSecond] (
[S_ID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[F_ID] BIGINT  NULL
) £

CREATE TRIGGER [on_after_delete_from_tblFirst]
AFTER DELETE ON [tblSecond]
FOR EACH ROW
BEGIN

-- strange here!! when disable the next line, it's fine! why?
delete from tblFirst where tblFirst.F_ID = OLD.F_ID;

END; 
£

[starofrainnight]

thanks Mdaems!!!

This problem encumber me for a week, getting headaches ...

could it more wise in the future ? or there have some difficulties in technology ?

[mdaems]

I suppose the worst part is learning the parser to make the difference between a one line statement and statements that should wait untill the right 'end' is issued. I don't think rules are identical for all databases when deciding 'Is this statement closed?'

I don't know how SQLite Administrator handles this. Maybe the script is parsed by their own code before it is sent to individual TZQuery object instead of the SQLProcessor.

To be honest : I think the way it works now is not so strange. It's the database that's strange as it uses delimiters in 2 different ways, depending on the statement used.
When you look at how mysql console works with delimiters this is more logical. To be able to issue a statement using a ';' you must set the delimiter to something different.

Mark

[starofrainnight]

Thanks for your explain, thanks for your work! Mark!

ZeosLib is a great works! Hope it goes better and better ....