TUniScript SQLite Delimeter error

[VadimShvarts]

Unidac version 3.00.0.9
Exception1: near "sh_id": syntax error
Exception2: SQL logic error or missing database

Code: Select all

procedure Test;
var
 UniConnection1 : TUniConnection;
 UniScript1 : TUniScript;
begin
 UniConnection1 := TUniConnection.Create(nil);
 with UniConnection1 do
  begin
   ProviderName := 'SQLite';
   Database := 'test.sqlite';
   LoginPrompt := False;
   Connected := True;
  end;
 UniScript1 := TUniScript.Create(nil);
 with UniScript1 do
  begin
   Connection := UniConnection1;
   SQL.Text :=
    'create table SYS_HISTORY('#13+
    'SH_ID        Numeric(12)    NOT NULL,'#13+
    'SH_NAME      VarChar2(255)  NOT NULL,'#13+
    'SH_GROUP     VarChar2(60),'#13+
    'SH_PROGRAM   VarChar2(60),'#13+
    'SH_COMMENT   VarChar2(255),'#13+
    'SH_CREATED   DATETIME DEFAULT CURRENT_TIMESTAMP,'#13+
    'SH_CHANGED   DATETIME'#13+
    ');'#13+
    'create index SH_ID_KEY on Sys_History(sh_ID);'#13+
    'create trigger tr_sh_id_Changed'#13+
    'after update on Sys_History'#13+
    'for each row'#13+
    'begin'#13+
    ' update Sys_History set sh_Changed = current_timestamp where sh_id = new.sh_id;'#13+
    'end;'#13+
    '/';
   Execute;
  end;
end;

[bork]

Hello

he default delimiter is ";". So your script will be divided into the following blocks that will be executed one by one:

Code: Select all

create table SYS_HISTORY('#13+
    'SH_ID        Numeric(12)    NOT NULL,'#13+
    'SH_NAME      VarChar2(255)  NOT NULL,'#13+
    'SH_GROUP     VarChar2(60),'#13+
    'SH_PROGRAM   VarChar2(60),'#13+
    'SH_COMMENT   VarChar2(255),'#13+
    'SH_CREATED   DATETIME DEFAULT CURRENT_TIMESTAMP,'#13+
    'SH_CHANGED   DATETIME'#13+
    ')

Code: Select all

create index SH_ID_KEY on Sys_History(sh_ID)

Code: Select all

create trigger tr_sh_id_Changed'#13+
    'after update on Sys_History'#13+
    'for each row'#13+
    'begin'#13+
    ' update Sys_History set sh_Changed = current_timestamp where sh_id = new.sh_id

Code: Select all

end

Code: Select all

/

The cause is ";" in the line "update Sys_History set sh_Changed = current_timestamp where sh_id = new.sh_id" that is detected as the end of the block.

I can offer two ways to solve your issue:

1. Set another delimiter (for example "\"):

Code: Select all

UniScript1.Delimiter := '\;

and modify your script:

Code: Select all

SQL.Text :=
    'create table SYS_HISTORY('#13+
    'SH_ID        Numeric(12)    NOT NULL,'#13+
    'SH_NAME      VarChar2(255)  NOT NULL,'#13+
    'SH_GROUP     VarChar2(60),'#13+
    'SH_PROGRAM   VarChar2(60),'#13+
    'SH_COMMENT   VarChar2(255),'#13+
    'SH_CREATED   DATETIME DEFAULT CURRENT_TIMESTAMP,'#13+
    'SH_CHANGED   DATETIME'#13+
    ')\#13+
    'create index SH_ID_KEY on Sys_History(sh_ID)\#13+
    'create trigger tr_sh_id_Changed'#13+
    'after update on Sys_History'#13+
    'for each row'#13+
    'begin'#13+
    ' update Sys_History set sh_Changed = current_timestamp where sh_id = new.sh_id;'#13+
    'end\#13+
    '/';

2. You can execute your script without any changes in the TUniSQL component. In this case your script will be executed correctly as one whole block.

[VadimShvarts]

Hello bork
Thank you for answer

1. Set another delimiter (for example "\"):

Code: Select all

UniScript1.Delimiter := '\;

I can not use first way.

What do you think about another solution

Function TDAScriptProcessor.ExecuteNext the following code:

Code: Select all

            if FCurrDelimiter = FOwner.FDelimiter{';'} {old mode} then begin
              Ready := ((FSt = FOwner.FDelimiter) and not IsSpecificSQL(StatementType));
              if not Ready and (FSt = '/') and (FParser.PrevCol = 0) then begin

procedure TMyScriptProcessor.CheckLexem has the following code:

Code: Select all

    if FCurrDelimiter = ';' then
      if (FPrevCode = lxCREATE) and
        ((FCurrCode = lxPROCEDURE) or (FCurrCode = lxFUNCTION) or (FCurrCode = lxTRIGGER))
      then
        StatementType := ST_SPECIFIC_SQL;
  end;

7 providers from 11 has analogous code (set ST_SPECIFIC_SQL)
There can be to write the analogous code for the command "CREATE TRIGGER" into TLiteScriptProcessor.CheckLexem

[bork]

Hello

This solution cannot be applied in your case. If the ST_SPECIFIC_SQL statement type will be set after the "create trigger" keywords it means that all statement until "/" will be executed as one block.

For example:

Code: Select all

SQL.Text :=
    'create trigger tr_sh_id_Changed'#13+
    'after update on Sys_History'#13+
    'for each row'#13+
    'begin'#13+
    ' update Sys_History set sh_Changed = current_timestamp where sh_id = new.sh_id;'#13+
    'end;'#13+
    'drop table SYS_HISTORY;'#13+
    '/';

This whole script will be determined as ST_SPECIFIC_SQL and will be executed as one block.

TUniScript doesn't contain any SQL script parser, it divides your script in blocks between dividers and executes it one by one only.

[VadimShvarts]

This solution cannot be applied in your case. If the ST_SPECIFIC_SQL statement type will be set after the "create trigger" keywords it means that all statement until "/" will be executed as one block.

This is correct

This whole script will be determined as ST_SPECIFIC_SQL and will be executed as one block.

and this is correct

TUniScript doesn't contain any SQL script parser, it divides your script in blocks between dividers and executes it one by one only.

and this is correct

Why it is the fact that made for MySQL provider (Oracle, Interbase and others) it cannot be made for SQLite provider?

[bork]

I tried to execute the following script in TUniScript with the Oracle provider:

Code: Select all

create table BTEST23(
SH_ID        Numeric    NOT NULL,
SH_NAME      VarChar2(255)  NOT NULL,
SH_GROUP     VarChar2(60),
SH_PROGRAM   VarChar2(60),
SH_COMMENT   VarChar2(255),
SH_CREATED   DATE,
SH_CHANGED   DATE
);
create index BTEST23_SH_ID_KEY on BTEST23(sh_ID);
create trigger tr_BTEST23_sh_id_Changed
after update on BTEST23
for each row
begin
  update BTEST23 set sh_Changed = current_timestamp where sh_id = :new.sh_id;
end;

drop table BTEST23;
create table BTEST23(
SH_ID        Numeric(12)    NOT NULL,
SH_NAME      VarChar2(255)  NOT NULL,
SH_GROUP     VarChar2(60),
SH_PROGRAM   VarChar2(60),
SH_COMMENT   VarChar2(255),
SH_CREATED   DATE,
SH_CHANGED   DATE
);
drop table BTEST23;
/

TUniScript with the Oracle provider divides this script into three blocks:

Code: Select all

create table BTEST23(
SH_ID        Numeric    NOT NULL,
SH_NAME      VarChar2(255)  NOT NULL,
SH_GROUP     VarChar2(60),
SH_PROGRAM   VarChar2(60),
SH_COMMENT   VarChar2(255),
SH_CREATED   DATE,
SH_CHANGED   DATE
);

Code: Select all

create index BTEST23_SH_ID_KEY on BTEST23(sh_ID);

Code: Select all

create trigger tr_BTEST23_sh_id_Changed
after update on BTEST23
for each row
begin
 update BTEST23 set sh_Changed = current_timestamp where sh_id = :new.sh_id;
end;
drop table BTEST23;
create table BTEST23(
SH_ID        Numeric(12)    NOT NULL,
SH_NAME      VarChar2(255)  NOT NULL,
SH_GROUP     VarChar2(60),
SH_PROGRAM   VarChar2(60),
SH_COMMENT   VarChar2(255),
SH_CREATED   DATE,
SH_CHANGED   DATE
);
drop table BTEST23;
/

Last block was executed with errors. But script is correct and if you try to execute queries one by one it will be executed correctly.

So TUniScript with any data provider doesn't parse script. TUniScript divides script into blocks according to dividers. If you want to have your script divided correctly then you should use a divider different from ";".

But if you want we can make the behavior of TUniScirpt with the SQLite provider equal to the behavior of TUniScirpt with other providers.

[VadimShvarts]

But if you want we can make the behavior of TUniScirpt with the SQLite provider equal to the behavior of TUniScirpt with other providers.

YES! YES! YES! YES!:D
I want
Thank for your answer

[bork]

OK, we will notify you as soon as this behavior will be implemented to TUniScirpt with the SQLite provider .