Page 1 of 1
SQLProcessor: Exception happened when creating a trigger
Posted: 24.06.2007, 15:34
by 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
Posted: 25.06.2007, 08:09
by 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;
£
Posted: 25.06.2007, 11:17
by 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 ?
Posted: 25.06.2007, 12:20
by 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
Posted: 25.06.2007, 13:47
by starofrainnight
Thanks for your explain, thanks for your work! Mark!
ZeosLib is a great works! Hope it goes better and better ....
