Bug in ZSQLProcessor dtSetTerm

[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

[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

[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

[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.

[EgonHugeist]

josimarz, tarnschaf,

bug fixed Rev. 1915. Can you test it?