SQLite Provider / TUniScript and bracket issue

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
swierzbicki
Posts: 451
Joined: Wed 19 Jan 2005 09:59

SQLite Provider / TUniScript and bracket issue

Post by swierzbicki » Wed 12 Aug 2015 15:39

Hello,

TUniScript cannot execute this script. Looks like the issue is related with the trigger script execution.
TUniScript raise more exceptions when AutoCommit is set to true.

Here is a test script (using an TUniconnection that has forcecreatedatabase option to true)

Code: Select all

CREATE TABLE articles (
  Id_Article       integer PRIMARY KEY AUTOINCREMENT,
  Libelle_Article  varchar(250),
  Gencod_Article   varchar(250),
  Code_Article     varchar(250),
  Colisage         integer,
  Stock            float(50)
);

CREATE INDEX articles_Index01
  ON articles
  (Gencod_Article);

CREATE INDEX articles_Index02
  ON articles
  (Code_Article);

CREATE INDEX articles_Index03
  ON articles
  (Stock);

CREATE UNIQUE INDEX articles_Index04
  ON articles
  (Gencod_Article, Code_Article);

CREATE TABLE articles_details (
  Id_Article_detail  integer PRIMARY KEY AUTOINCREMENT,
  Gencod_Article     varchar(250),
  Code_Article       varchar(250),
  Gencod_Piece       varchar(250),
  Couleur            varchar(250),
  Taille             varchar(250),
  Nombre_Pieces      integer
);

CREATE INDEX articles_details_Index01
  ON articles_details
  (Gencod_Article);

CREATE INDEX articles_details_Index02
  ON articles_details
  (Gencod_Piece);

CREATE INDEX articles_details_Index03
  ON articles_details
  (Code_Article);

CREATE INDEX articles_details_Index04
  ON articles_details
  (Code_Article, Gencod_Article);

CREATE UNIQUE INDEX articles_details_Index05
  ON articles_details
  (Gencod_Article, Code_Article, Gencod_Piece);

CREATE TABLE colis_entree_details (
  Id_Colis_Entree_Detail  integer PRIMARY KEY,
  Code_Barre              varchar(250) DEFAULT '',
  Id_Emplacement_Colis    varchar(20) DEFAULT '',
  Utilisateur             varchar(20) DEFAULT '',
  Id_Device               varchar(20) DEFAULT '',
  Date_Entree             datetime,
  Version                 varchar(10) DEFAULT '',
  Date_Validation         datetime,
  Date_Transfert          datetime,
  Id_Transfert            varchar(20)
);

CREATE INDEX Idx_Code_Barre
  ON colis_entree_details
  (Code_Barre);

CREATE INDEX Idx_Date_Entree
  ON colis_entree_details
  (Date_Entree);

CREATE INDEX Idx_Date_Transfert
  ON colis_entree_details
  (Date_Transfert);

CREATE INDEX Idx_Date_Validation
  ON colis_entree_details
  (Date_Validation);

CREATE TABLE colis_inventaires (
  Id_Colis_Inventaire  integer PRIMARY KEY AUTOINCREMENT,
  Id_Inventaire        varchar(50),
  Date_Inventaire      datetime,
  Utilisateur          varchar(20),
  Id_Entrepot          varchar(50),
  Date_Validation      datetime,
  Date_Transfert       datetime,
  Statut               varchar(250)
);

CREATE INDEX colis_inventaires_Index01
  ON colis_inventaires
  (Id_Inventaire);

CREATE INDEX colis_inventaires_Index02
  ON colis_inventaires
  (Id_Inventaire, Date_Validation);

CREATE INDEX colis_inventaires_Index04
  ON colis_inventaires
  (Id_Inventaire, Date_Transfert);

CREATE TRIGGER tgrAferDelete_colis_inventaires
  AFTER DELETE
  ON colis_inventaires
BEGIN
    DELETE FROM
        [colis_inventaires_details]
    WHERE
        [Id_Inventaire] = [OLD].[Id_Inventaire];
    
END;

CREATE TABLE colis_inventaires_details (
  Id_Colis_Inventaire_Detail  integer PRIMARY KEY AUTOINCREMENT,
  Id_Inventaire               varchar(50),
  Type                        varchar(10),
  Travee                      varchar(250),
  Gencod_Article              varchar(250),
  Code_Article                varchar(250),
  Utilisateur                 varchar(50),
  Date_Inventaire             datetime,
  Code_Barre                  varchar(250)
);

CREATE INDEX colis_inventaires_details_Index01
  ON colis_inventaires_details
  (Id_Inventaire, Gencod_Article);

CREATE INDEX colis_inventaires_details_Index02
  ON colis_inventaires_details
  (Code_Barre, Id_Inventaire);

CREATE INDEX colis_inventaires_details_Index03
  ON colis_inventaires_details
  (Id_Inventaire);

CREATE INDEX colis_inventaires_details_Index06
  ON colis_inventaires_details
  (Type);

CREATE INDEX colis_inventaires_details_Index08
  ON colis_inventaires_details
  (Gencod_Article);

CREATE INDEX colis_inventaires_details_Index09
  ON colis_inventaires_details
  (Code_Article);

CREATE INDEX colis_inventaires_details_Index10
  ON colis_inventaires_details
  (Travee);

CREATE INDEX colis_inventaires_details_Index11
  ON colis_inventaires_details
  (Id_Inventaire, Gencod_Article, Code_Article, Type);

CREATE TRIGGER colis_inventaires_details_Trigger01
  AFTER DELETE
  ON colis_inventaires_details
BEGIN
      DELETE FROM
        [colis_inventaires_details_quantites]
    WHERE
        [Id_Inventaire] = [OLD].[Id_Inventaire] and
        [Code_Barre] = [OLD].[Code_Barre];
  
END;

CREATE TABLE colis_inventaires_details_quantites (
  Id_Colis_Inventaire_Detail_Quantite  integer PRIMARY KEY AUTOINCREMENT,
  Id_Inventaire                        varchar(50),
  Gencod_Article                       varchar(250),
  Code_Article                         varchar(250),
  Gencod_Piece                         varchar(250),
  Quantite_Pieces                      integer,
  Code_Barre                           varchar(250)
);

CREATE INDEX colis_inventaires_details_quantites_Index01
  ON colis_inventaires_details_quantites
  (Id_Inventaire);

CREATE INDEX colis_inventaires_details_quantites_Index02
  ON colis_inventaires_details_quantites
  (Gencod_Article, Code_Article, Gencod_Piece);

CREATE INDEX colis_inventaires_details_quantites_Index03
  ON colis_inventaires_details_quantites
  (Id_Inventaire, Code_Barre, Gencod_Article, Code_Article);

CREATE TABLE colis_preparations (
  Id_Colis_Preparation  integer PRIMARY KEY,
  Id_Bon_Preparation    varchar(50),
  Date_Preparation      datetime,
  Utilisateur           varchar(20),
  Id_Entrepot           varchar(50),
  Id_Commande           varchar(50),
  Id_Client             varchar(50),
  Total_Colis           float,
  Total_Poids           float,
  Total_Volume          float,
  Date_Validation       datetime,
  Date_Transfert        datetime,
  Statut                varchar(250)
);

CREATE INDEX colis_preparations_Index01
  ON colis_preparations
  (Id_Bon_Preparation);

CREATE INDEX colis_preparations_Index02
  ON colis_preparations
  (Id_Bon_Preparation, Date_Validation);

CREATE INDEX colis_preparations_Index03
  ON colis_preparations
  (Id_Bon_Preparation, Date_Transfert);

CREATE INDEX colis_preparations_Index04
  ON colis_preparations
  (Id_Bon_Preparation, Date_Preparation);

CREATE TRIGGER tgrAferDelete
  AFTER DELETE
  ON colis_preparations
BEGIN
    DELETE FROM
        [Colis_Preparations_details]
    WHERE
        [Id_Bon_Preparation] = [OLD].[Id_Bon_Preparation];

    DELETE FROM
        [Colis_Preparations_items_scannes]
    WHERE
        [Id_Bon_Preparation] = [OLD].[Id_Bon_Preparation];
        
    DELETE FROM
        [Colis_Preparations_cartons_ouverts]
    WHERE
            [Id_Bon_Preparation] = [OLD].[Id_Bon_Preparation];        
END;

CREATE TABLE colis_preparations_cartons_ouverts (
  Id_Colis_Preparation_Carton_Ouvert  integer PRIMARY KEY AUTOINCREMENT,
  Id_Colis_Preparation_Item_Scanne    integer,
  Id_Bon_Preparation                  varchar(50),
  Gencod_Article                      varchar(250),
  Gencod_Piece                        varchar(250),
  Code_Barre                          varchar(250),
  Colisage                            integer,
  Prepare                             integer,
  Original                            boolean,
  Utilisateur                         varchar(50)
);

CREATE INDEX colis_preparations_cartons_ouverts_Index01
  ON colis_preparations_cartons_ouverts
  (Id_Bon_Preparation, Gencod_Article, Gencod_Piece, Code_Barre);

CREATE INDEX colis_preparations_cartons_ouverts_Index02
  ON colis_preparations_cartons_ouverts
  (Id_Bon_Preparation);

CREATE INDEX colis_preparations_cartons_ouverts_Index03
  ON colis_preparations_cartons_ouverts
  (Gencod_Article);

CREATE INDEX colis_preparations_cartons_ouverts_Index04
  ON colis_preparations_cartons_ouverts
  (Code_Barre);

CREATE INDEX colis_preparations_cartons_ouverts_Index05
  ON colis_preparations_cartons_ouverts
  (Gencod_Piece);

CREATE INDEX colis_preparations_cartons_ouverts_Index06
  ON colis_preparations_cartons_ouverts
  (Original);

CREATE INDEX colis_preparations_cartons_ouverts_Index07
  ON colis_preparations_cartons_ouverts
  (Prepare);

CREATE INDEX colis_preparations_cartons_ouverts_Index08
  ON colis_preparations_cartons_ouverts
  (Id_Bon_Preparation, Original);

CREATE TABLE colis_preparations_details (
  Id_Colis_Preparation_Detail  integer PRIMARY KEY AUTOINCREMENT,
  Id_Bon_Preparation           varchar(50),
  Id_Preparation_Sequence      integer,
  Id_Preparation_Detail_Piece  integer,
  Type                         varchar(10),
  Travee                       varchar(250),
  Gencod_Article               varchar(250),
  Code_Article                 varchar(250),
  Libelle_Article              varchar(250),
  Libelle_Piece                varchar(250),
  Gencod_Piece                 varchar(250),
  Nombre_Cartons               integer,
  Nombre_Pieces                integer,
  Colisage                     integer,
  Total_Pieces                 integer
);

CREATE INDEX colis_preparations_details_Index01
  ON colis_preparations_details
  (Id_Bon_Preparation, Gencod_Article);

CREATE INDEX colis_preparations_details_Index02
  ON colis_preparations_details
  (Id_Bon_Preparation, Gencod_Piece);

CREATE INDEX colis_preparations_details_Index03
  ON colis_preparations_details
  (Id_Bon_Preparation);

CREATE INDEX colis_preparations_details_Index04
  ON colis_preparations_details
  (Nombre_Cartons);

CREATE INDEX colis_preparations_details_Index05
  ON colis_preparations_details
  (Nombre_Pieces);

CREATE INDEX colis_preparations_details_Index06
  ON colis_preparations_details
  (Type);

CREATE INDEX colis_preparations_details_Index07
  ON colis_preparations_details
  (Gencod_Piece);

CREATE INDEX colis_preparations_details_Index08
  ON colis_preparations_details
  (Gencod_Article);

CREATE INDEX colis_preparations_details_Index09
  ON colis_preparations_details
  (Code_Article);

CREATE INDEX colis_preparations_details_Index10
  ON colis_preparations_details
  (Travee);

CREATE INDEX colis_preparations_details_Index11
  ON colis_preparations_details
  (Id_Bon_Preparation, Gencod_Article, Code_Article, Type);

CREATE TABLE colis_preparations_items_scannes (
  Id_Colis_Preparation_Item_Scanne  integer PRIMARY KEY AUTOINCREMENT,
  Id_Bon_Preparation                varchar(50),
  Type                              varchar(10),
  Gencod_Article                    varchar(250),
  Code_Article                      varchar(250),
  Gencod_Piece                      varchar(250),
  Code_Barre                        varchar(250),
  Quantite_Pieces                   integer,
  Utilisateur                       varchar,
  Date_Preparation                  datetime,
  Original                          boolean
);

CREATE INDEX colis_preparations_cartons_scannes_Index01
  ON colis_preparations_items_scannes
  (Id_Bon_Preparation, Gencod_Article);

CREATE INDEX colis_preparations_cartons_scannes_Index02
  ON colis_preparations_items_scannes
  (Id_Bon_Preparation);

CREATE INDEX colis_preparations_cartons_scannes_Index03
  ON colis_preparations_items_scannes
  (Id_Bon_Preparation, Quantite_Pieces);

CREATE INDEX colis_preparations_cartons_scannes_Index04
  ON colis_preparations_items_scannes
  (Quantite_Pieces);

CREATE INDEX colis_preparations_cartons_scannes_Index05
  ON colis_preparations_items_scannes
  (Type);

CREATE INDEX colis_preparations_cartons_scannes_Index06
  ON colis_preparations_items_scannes
  (Id_Bon_Preparation, Type, Gencod_Article, Code_Article);

CREATE INDEX colis_preparations_cartons_scannes_Index07
  ON colis_preparations_items_scannes
  (Id_Bon_Preparation, Type, Gencod_Article, Gencod_Piece);

CREATE INDEX colis_preparations_cartons_scannes_Index08
  ON colis_preparations_items_scannes
  (Gencod_Article);

CREATE INDEX colis_preparations_cartons_scannes_Index09
  ON colis_preparations_items_scannes
  (Gencod_Piece);

CREATE INDEX colis_preparations_cartons_scannes_Index10
  ON colis_preparations_items_scannes
  (Code_Barre);

CREATE INDEX colis_preparations_items_scannes_Index01
  ON colis_preparations_items_scannes
  (Id_Bon_Preparation, Type, Gencod_Article, Gencod_Piece, Code_Barre);

CREATE INDEX colis_preparations_items_scannes_Index02
  ON colis_preparations_items_scannes
  (Original);

CREATE INDEX colis_preparations_items_scannes_Index03
  ON colis_preparations_items_scannes
  (Code_Article);

CREATE TRIGGER colis_preparations_items_scannes_Trigger01
  AFTER DELETE
  ON colis_preparations_items_scannes
BEGIN
    DELETE FROM
        [Colis_Preparations_cartons_ouverts]
    WHERE
        [Id_Colis_Preparation_Item_Scanne] = [OLD].[Id_Colis_Preparation_Item_Scanne];
END;

CREATE TABLE preferences (
  Id_Preference  varchar(50),
  Value          varchar(250),
  /* Keys */
  CONSTRAINT sqlite_autoindex_preferences_1
    PRIMARY KEY (Id_Preference)
);

CREATE TABLE utilisateurs (
  Identifiant   varchar(250),
  Nom_Complet   varchar(250),
  Mot_De_Passe  varchar(250)
);

CREATE INDEX utilisateurs_Index01
  ON utilisateurs
  (Identifiant);



CREATE VIEW colis_preparations_cartons_ouverts_ws
AS
SELECT 
  Id_Bon_Preparation,
  Gencod_Article,
  Gencod_Piece,
  Code_Barre,
  Colisage,
   SUM(Prepare) AS Prepare
FROM
  colis_preparations_cartons_ouverts
GROUP BY
  Id_Bon_Preparation,
  Gencod_Article,
  Gencod_Piece,
  Code_Barre;

CREATE VIEW colis_preparations_cartons_total
AS
SELECT 
 Id_Bon_Preparation,
 type,
 Gencod_Article,
 Code_article,
 COUNT(Gencod_Article) AS Total_Cartons
FROM
  colis_preparations_items_scannes
WHERE
 
  Type in ('C','R')
GROUP BY
  Id_Bon_Preparation,
  type,
  Gencod_Article,
  code_article;

CREATE VIEW colis_preparations_items_scannes_ws
AS
SELECT 
  Id_Bon_Preparation,
  type,
  Gencod_Article,
  Code_Article,  
  Gencod_Piece,
  Code_Barre,
  Sum(Quantite_Pieces) as Quantite_Pieces,
  Date_Preparation,
  utilisateur
FROM
  colis_preparations_items_scannes
GROUP BY
  Id_Bon_Preparation,
  type,
  Gencod_Article,
  Code_Article,
  Gencod_Piece,
  Code_Barre;

CREATE VIEW colis_preparations_pieces_total
AS
SELECT 
Id_Bon_Preparation,
  Type,
  Gencod_Article,
  Gencod_Piece,
 SUM(Quantite_Pieces) AS Total_Pieces
FROM
  colis_preparations_items_scannes
WHERE
  Type = 'P'
GROUP BY
  Id_Bon_Preparation,
  Type,
  Gencod_Article,
  Gencod_Piece;
  
CREATE VIEW colis_preparations_details_restants
AS
SELECT 
  colis_preparations_details.*,
  
  
  (colis_preparations_details.Nombre_Cartons - IfNull(colis_preparations_cartons_total.Total_Cartons,0)) as Nombre_Cartons_Restants,
  (colis_preparations_details.Nombre_Pieces - IfNull(colis_preparations_pieces_total.Total_Pieces,0)) as Nombre_Pieces_Restantes,
  
  colis_preparations_details.Total_Pieces - ((IfNull(colis_preparations_cartons_total.Total_Cartons,0)* colis_preparations_details.Colisage) + IfNull(colis_preparations_pieces_total.Total_Pieces,0)) as Total_Pieces_Restantes   
FROM
  colis_preparations_details
  LEFT OUTER JOIN colis_preparations_cartons_total ON (colis_preparations_details.Id_Bon_Preparation = colis_preparations_cartons_total.Id_Bon_Preparation
  and colis_preparations_details.Gencod_Article = colis_preparations_cartons_total.Gencod_Article  
  and colis_preparations_details.Code_Article = colis_preparations_cartons_total.Code_Article
  and colis_preparations_details.Type = colis_preparations_cartons_total.Type )
 

 
  LEFT OUTER JOIN colis_preparations_pieces_total ON (colis_preparations_details.Id_Bon_Preparation = colis_preparations_pieces_total.Id_Bon_Preparation
  and colis_preparations_details.Gencod_Article = colis_preparations_pieces_total.Gencod_Article 
  and colis_preparations_details.Gencod_Piece = colis_preparations_pieces_total.Gencod_Piece 
  and colis_preparations_details.Type = colis_preparations_pieces_total.Type  
  )

  WHERE not((colis_preparations_details.Nombre_Cartons = 0) and (colis_preparations_details.Nombre_Pieces = 0))

ORDER BY
colis_preparations_details.ID_Colis_Preparation_Detail;
Top
swierzbicki
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Re: SQLite Provider / TUniScript and bracket issue

Post by swierzbicki » Wed 12 Aug 2015 15:56

After looking deeper, it appear that TUniSCRIPT doesn't correctly handle "BEGIN" "END". There is an issue with the SQL parser.

Script Example :

Code: Select all

CREATE TRIGGER tgrAferDelete_colis_inventaires
  AFTER DELETE
  ON colis_inventaires
BEGIN
    DELETE FROM
        colis_inventaires_details
    WHERE
        Id_Inventaire = OLD.Id_Inventaire
END
DBMonitor show that this script is executed in 2 steps :

Code: Select all

 CREATE TRIGGER tgrAferDelete_colis_inventaires
  AFTER DELETE
  ON colis_inventaires
BEGIN
    DELETE FROM
        colis_inventaires_details
    WHERE
        Id_Inventaire = OLD.Id_Inventaire
and finally

Code: Select all

END
Top
MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: SQLite Provider / TUniScript and bracket issue

Post by MaximG » Thu 13 Aug 2015 07:56

Thank you for the information. We have reproduced the problem and fixed the bug. The fix will be included in the next UniDAC version. As a workaround, you can
replace the ';' separator between different SQL statements in the script with any other (e.g., '/') and specify it in the Delimiter property of the UniScript component: Delimiter := '/'
Top
swierzbicki
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Re: SQLite Provider / TUniScript issue

Post by swierzbicki » Thu 13 Aug 2015 08:43

As a workaround, you can replace the ';' separator between different SQL statements in the script with any other (e.g., '/') and specify it in the Delimiter property of the UniScript component: Delimiter := '/'
Thank you
Top
Post Reply

Return to “Universal Data Access Components”