Page 1 of 1
Bug in ZSQLProcessor dtSetTerm
Posted: 07.10.2012, 12:07
by tarnschaf
Hi,
I found a reason why people experience difficulties with scripts containing "SET TERM". The problem occurs when there are comments before the SET TERM statement. ZSQLProcessor includes the comment into the complete statement (variable SQL) and then checks if the statement starts with "SET TERM", which is not true because of the comment.
I think the bug is rather old and caused some people to even write
workarounds.
Sorry for not having a patch yet but it shouldn't be hard for someone that knows the Zeos code better.
Thanks,
Tarnschaf
Posted: 08.10.2012, 14:31
by josimarz
Hello
tarnschaf,
I checked the SQL script to understand the problem. Until a solution is available, I suggest to solve the problem another way: use the same delimiter throughout the length of the script. Example:
Code: Select all
CREATE TABLE L4PARTS
(
ID Integer NOT NULL,
"Nummer" VarChar(22),
"Artikel" VarChar(25),
PRIMARY KEY (ID)
)//
CREATE TABLE L4ADRESSES
(
ID Integer NOT NULL,
"Firma" VarChar(25),
"Name" VarChar(25),
PRIMARY KEY (ID)
)//
CREATE TRIGGER AUTOINC_L4PARTS_ID FOR L4PARTS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.ID = gen_id( L4PARTS_AUTOINC, 1 );
END//
The Zeos code would look like:
Code: Select all
function RunScript(AOwner : TComponent; Connection : TZConnection; Script : TStringList): String;
var
SqlProcessor : TZSqlProcessor;
begin
Result := '';
SqlProcessor := TZSqlProcessor.Create(aOwner);
try
SqlProcessor.Connection := Connection;
SqlProcessor.DelimiterType := dtSetTerm;
SqlProcessor.Delimiter := '//'; // <-- look
SqlProcessor.Script.Assign(Script);
try
SqlProcessor.Execute;
except
on E:Exception do Result := E.Message;
end;
finally
SqlProcessor.Free;
end;
end;
I'm willing to check and fix the code so it's left me a break.
Greetings,
Josimar
Posted: 08.10.2012, 18:05
by josimarz
Hello,
Another way to solve the problem is to enable the property CleanupStatements of TZSQLProcessor.
When this property is enabled the tokenizer removes all comments from the script.
Greetings,
Josimar
Posted: 09.10.2012, 12:14
by EgonHugeist
tarnschaf,
bug confirmed. For the first i prose your follow the instructions of josimarz until i or somebody else will find the time to fix this stupid issue.
Posted: 12.10.2012, 19:28
by EgonHugeist
josimarz, tarnschaf,
bug fixed Rev. 1915. Can you test it?