FireDAC Interbase SQL execution fails when IBScript execution does not

Hello,

I am making a table in Interbase XE3 using FireDAC that looks like the following: 

{code}
CREATE TABLE "CardTypes"
(
  "CardTypeID"	INTEGER DEFAULT 1 NOT NULL,
  "CardTypeName"	VARCHAR(30) NOT NULL,
 PRIMARY KEY ("CardTypeID")
);
/* Generator and Trigger for CardTypeID */
CREATE GENERATOR "GenCardID";
SET GENERATOR "GenCardID" TO 100;
CREATE TRIGGER "IncCardID" FOR "CardTypes"
	BEFORE INSERT AS
	BEGIN
	   NEW."CardTypeID" = GEN_ID("GenCardID", 1);
	END;
INSERT INTO "CardTypes" ("CardTypeName") VALUES ('Club');
INSERT INTO "CardTypes" ("CardTypeName") VALUES ('Spade');
INSERT INTO "CardTypes" ("CardTypeName") VALUES ('Heart');
INSERT INTO "CardTypes" ("CardTypeName") VALUES ('Diamond');
{code}

When run with TFDScript, it gives the error:
{code}
[soEcho] CREATE TABLE "CardTypes"
(
  "CardTypeID"	INTEGE ...
[soCommand] Ok [00:00:00.016].
[soEcho] /* Generator and Trigger for CardTypeID */
CREATE ...
[soCommand] Ok [00:00:00.000].
[soEcho] SET GENERATOR "GenCardID" TO 100;

[soCommand] Ok [00:00:00.000].
[soEcho] CREATE TRIGGER "IncCardID" FOR "CardTypes"
	BEFOR ...
[soError] ERROR: Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char -1
INSERT
{code}

But If I remove the insert statements I get a successful execution. In this case, the TRIGGER is not created. Running with IBScript with the same SQL works as expected. Note that any lines after the trigger will cause an error and stop the execution without creating the trigger. For example, the following will fail:

{code}
CREATE TRIGGER "IncCardID" FOR "CardTypes"
	BEFORE INSERT AS
	BEGIN
	   NEW."CardTypeID" = GEN_ID("GenCardID", 1);
	END;

CREATE TABLE "CheatCodes"
(
  "CheatID" INTEGER NOT NULL,
  "CheatCode" VarChar(30) NOT NULL
);
{code}

Thank you for your time,
Alex
0
Alexander
7/17/2014 5:36:40 PM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

1 Replies
1476 Views

Similar Articles

[PageSpeed] 7

You should use alternative SQL command separator. For example:
{code}
SET CMDSEP /

CREATE TABLE "CardTypes" ...
/

CREATE GENERATOR "GenCardID"
/

SET GENERATOR "GenCardID" TO 100
/

CREATE TRIGGER "IncCardID" ....
/

INSERT INTO "CardTypes"....
/
{code}

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
0
Dmitry
7/18/2014 10:34:17 AM
Reply: