FirebirdSQL - Multiple insert statement with TUniSQL

[ertank]

Hello,

I am using Delphi 10.3.3 and UniDAC 8.1.2.

I have an application which can be selectively used with SQL Server or PostgreSQL. Now, I am required to add FirebirdSQL to available database platforms.

I have used more than one TUniSQL component to insert data into multiple tables in multiple insert statements. As this hits a lot of components I do not want to change all relevant code underneath.

An example multiple statement insert in a single TUniSQL is:

Code: Select all

insert into carihar(id, belgetarihi, fiilitarih, sezon, carikod, belgeno, kaynakbelgeno, tutar, kaydeden, kayitzamani, iptal, belgeturu, islemturu)
values(:id, :belgetarihi, :fiilitarih, :sezon, :carikod, :belgeno, :kaynakbelgeno, :tutar, :kaydeden, {current_timestamp}, 0, :belgeturu, 'B');

insert into carihardet(id, belgetarihi, fiilitarih, sezon, carikod, belgeno, kaynakbelgeno, tutar, kaydeden, kayitzamani, aciklama, iptal, belgeturu, islemturu, islemkaynakkodu)
values(:id, :belgetarihi, :fiilitarih, :sezon, :carikod, :belgeno, :kaynakbelgeno, :tutar, :kaydeden, {current_timestamp}, :aciklama, 0, :belgeturu, 'B', :islemkaynakkodu);

Above works fine with SQL Server and PosrtgreSQL so far. It simply fails with FirebirdSQL with following error:

Code: Select all

First chance exception at $765635D2. Exception class EIBCError with message
'Dynamic SQL Error
SQL error code = -104
Token unknown - line 4, column 1
insert'.

Any help is appreciated.

Thanks & regards,
Ertan

[ViktorV]

This error is returned by Firebird, because the server does not allow you to execute the queries you specified without using the execute block. You can verify this by running the queries you have provided using the standard means, for example, isql.exe Firebird utility.
Therefore, you should use the queries you specified in the execute block structure.

[ertank]

I have tried following and I am still receiving error. This time error is different:

Code: Select all

execute block
as
begin
insert into carihar(id, belgetarihi, fiilitarih, sezon, carikod, belgeno, kaynakbelgeno, tutar, kaydeden, kayitzamani, iptal, belgeturu, islemturu)
values(:id, :belgetarihi, :fiilitarih, :sezon, :carikod, :belgeno, :kaynakbelgeno, :tutar, :kaydeden, {current_timestamp}, 0, :belgeturu, 'B');

insert into carihardet(id, belgetarihi, fiilitarih, sezon, carikod, belgeno, kaynakbelgeno, tutar, kaydeden, kayitzamani, aciklama, iptal, belgeturu, islemturu, islemkaynakkodu)
values(:id, :belgetarihi, :fiilitarih, :sezon, :carikod, :belgeno, :kaynakbelgeno, :tutar, :kaydeden, {current_timestamp}, :aciklama, 0, :belgeturu, 'B', :islemkaynakkodu);
end

In dbMonitor, I see all parameters are filled in, yet I receive following error:

Code: Select all

Dynamic SQL Error
SQL error code = -206
Column unknown
ID
At line 5, column 8

I think I am using execute block wrong. I do not know what is wrong though.

I do have ID column in both tables. Their extracted create statements are as following:

Code: Select all

CREATE TABLE CARIHAR (
  AUTOINC           BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
  ID                VARCHAR(38) NOT NULL,
  SEZON             SMALLINT,
  BELGETARIHI       DATE,
  BELGESAATI        TIME,
  FIILITARIH        DATE,
  BELGENO           VARCHAR(30),
  KAYNAKBELGENO     VARCHAR(30),
  BELGETURU         VARCHAR(10),
  ISLEMTURU         VARCHAR(2),
  CARIKOD           VARCHAR(40),
  ORAN              DOUBLE PRECISION,
  TUTAR             NUMERIC(18,4),
  DURUM             VARCHAR(2),
  IPTAL             SMALLINT,
  IPTALKODU         VARCHAR(10),
  KAYITZAMANI       TIMESTAMP,
  KAYDEDEN          VARCHAR(40),
  DEGISIKLIKZAMANI  TIMESTAMP,
  DEGISTIREN        VARCHAR(40)
);

CREATE TABLE CARIHARDET (
  AUTOINC           BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
  ID                VARCHAR(38) NOT NULL,
  SEZON             SMALLINT,
  BELGETARIHI       DATE,
  BELGESAATI        TIME,
  FIILITARIH        DATE,
  BELGENO           VARCHAR(30),
  KAYNAKBELGENO     VARCHAR(30),
  BELGETURU         VARCHAR(10),
  ISLEMTURU         VARCHAR(2),
  ISLEMKAYNAKKODU   INTEGER,
  CARIKOD           VARCHAR(40),
  ADRESKODU         VARCHAR(40),
  SATIRNO           INTEGER,
  ORJMIKTAR         NUMERIC(18,4),
  MIKTAR            NUMERIC(18,4),
  BIRIMFIYAT        NUMERIC(18,4),
  TUTAR             NUMERIC(18,4),
  IPTAL             SMALLINT,
  IPTALKODU         VARCHAR(10),
  ACIKLAMA          VARCHAR(300),
  KAYITZAMANI       TIMESTAMP,
  KAYDEDEN          VARCHAR(40),
  DEGISIKLIKZAMANI  TIMESTAMP,
  DEGISTIREN        VARCHAR(40)
);

[ViktorV]

To solve the issue you can use next SQL:

Code: Select all

EXECUTE block (ID VARCHAR(38) = :ID,
  SEZON SMALLINT = :SEZON,
  BELGETARIHI DATE = :BELGETARIHI,
  BELGESAATI TIME = :BELGESAATI,
  FIILITARIH DATE = :FIILITARIH,
  BELGENO VARCHAR(30) = :BELGENO,
  KAYNAKBELGENO     VARCHAR(30) = :KAYNAKBELGENO,
  BELGETURU         VARCHAR(10) = :BELGETURU,
  ISLEMTURU         VARCHAR(2) = :ISLEMTURU,
  ISLEMKAYNAKKODU   INTEGER = :ISLEMKAYNAKKODU,
  CARIKOD           VARCHAR(40) = :CARIKOD,
  ADRESKODU         VARCHAR(40) = :ADRESKODU,
  SATIRNO           INTEGER = :SATIRNO,
  ORJMIKTAR         NUMERIC(18,4) = :ORJMIKTAR,
  MIKTAR            NUMERIC(18,4) = :MIKTAR,
  BIRIMFIYAT        NUMERIC(18,4) = :BIRIMFIYAT,
  TUTAR             NUMERIC(18,4) = :TUTAR,
  IPTAL             SMALLINT = :IPTAL,
  IPTALKODU         VARCHAR(10) = :IPTALKODU,
  ACIKLAMA          VARCHAR(300) = :ACIKLAMA,
  KAYITZAMANI       TIMESTAMP = :KAYITZAMANI,
  KAYDEDEN          VARCHAR(40) = :KAYDEDEN,
  DEGISIKLIKZAMANI  TIMESTAMP = :DEGISIKLIKZAMANI,
  DEGISTIREN        VARCHAR(40) = :DEGISTIREN
  )
AS
BEGIN
INSERT INTO CARIHAR(ID, BELGETARIHI, FIILITARIH, SEZON, CARIKOD, BELGENO, KAYNAKBELGENO, TUTAR, KAYDEDEN, KAYITZAMANI, IPTAL, BELGETURU, ISLEMTURU)
VALUES(:ID, :BELGETARIHI, :FIILITARIH, :SEZON, :CARIKOD, :BELGENO, :KAYNAKBELGENO, :TUTAR, :KAYDEDEN, CURRENT_TIMESTAMP, 0, :BELGETURU, 'B');

INSERT INTO CARIHARDET(ID, BELGETARIHI, FIILITARIH, SEZON, CARIKOD, BELGENO, KAYNAKBELGENO, TUTAR, KAYDEDEN, KAYITZAMANI, ACIKLAMA, IPTAL, BELGETURU, ISLEMTURU, ISLEMKAYNAKKODU)
VALUES(:ID, :BELGETARIHI, :FIILITARIH, :SEZON, :CARIKOD, :BELGENO, :KAYNAKBELGENO, :TUTAR, :KAYDEDEN, CURRENT_TIMESTAMP, :ACIKLAMA, 0, :BELGETURU, 'B', :ISLEMKAYNAKKODU);
END

See more details in the Firebird documentation: https://firebirdsql.org/refdocs/langref ... block.html

[ertank]

This worked fine.

Thanks.

[ViktorV]

Thank you for the interest to our product.
It is good to see that the problem has been solved.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.