From Delphi+PostgresDAC (PostgresSQL) To Delphi+FireDAC (PostgresSQL)

[Squamis 0]

Helllo!

Last week I decided convert my big project from Delphi+PostgresDAC (PostgresSQL) to Delphi+FireDAC (PostgresSQL).

I find a lot of interesting things:

 

1. I can't add several "Insert" sql-queries into one TFDCommand. I've tried to use delimiter ';' - nothing. I want to use one command Execute to run several queries "Insert".

 

2. Some SQL-query works in different way (about speed)

in SQLManager works very fast

in Delphi+PostgresDAC mode works very fast

in Delphi+FireDAC mode - freeze

select * from vdata."systems" where ((fldidxsystem = any(:fldidxsystem::bigint[])) or (:fldidxsystem::bigint[] = '{}'))

only when I rewrite SQL-query - work very fast.

select * from vdata."systems" where ((fldidxsystem = any(:fldidxsystem::bigint[])) or (:fldidxsystem::varchar = '{}'))

Maybe someone have any answers about it?

[programmerdelphi2k 75]

I dont know if help you, but do you try use "Arrays DML"?

https://docwiki.embarcadero.com/RADStudio/Sydney/en/Array_DML_(FireDAC)#:~:text=The Array DML execution technique,for each row in arrays.

[Squamis 0]

2 minutes ago, programmerdelphi2k said:

I dont know if help you, but do you try use "Arrays DML"?

No. I mean fully different queries.

insert into table1....;

delete from table2...;

[Stano 107]

34 minutes ago, Squamis said:

No. I mean fully different queries.

insert into table1....;

delete from table2...;

The first point says something else.

[Squamis 0]

4 minutes ago, Stano said:

The first point says something else

 

May be. But, I mean the next:

I want in one TFDCommand component set several different queries, for example:

insert into table1....;

delete from table2...;

 

and in code just run Qr1.Execute;

 

I don't want create two TFDComponent.

Edited January 16 by Squamis

[Dmitry Arefiev 91]

I am not sure, but try to set ResourceOptions.DirectExecute to True

[Brian Evans 25]

For 1 the help (Command Batches (FireDAC) - RAD Studio (embarcadero.com)) says:

 

PostgreSQL

The commands must be separated by ';'. A batch without parameters can be executed with ResourceOptions.DirectExecute set to True. With PostgreSQL v >= 9.0, use the DO BEGIN END anonymous block construction.

 

 

[programmerdelphi2k 75]

Well, then the "Array DML technique" would allow multiple "DML commands" with parameters to be automated!
so if using:
"insert ....; "delete ...."   we could produce a "multiple executions to 2 commands" ... with each execution of the array!

[Virgo 9]

TFDScript is for executing SQL scripts AFAIK.

https://docwiki.embarcadero.com/RADStudio/Sydney/en/Executing_SQL_Scripts_(FireDAC)

 

[Squamis 0]

Thank for all! Thank for your time!

It is really fine solution fo me:

DO $$
BEGIN
INSERT INTO vdata.sides(fldside_art, fldside_name) VALUES('a','b');
INSERT INTO vdata.sides(fldside_art, fldside_name) VALUES('c','d');
delete from vdata.sides where fldside_art = 'a';
END $$

Tommorow I''l try "Array DML" and "TFDScript"

Edited January 16 by Squamis

[weirdo12 5]

6 hours ago, Squamis said:

Thank for all! Thank for your time!

It is really fine solution fo me:

DO $$
BEGIN
INSERT INTO vdata.sides(fldside_art, fldside_name) VALUES('a','b');
INSERT INTO vdata.sides(fldside_art, fldside_name) VALUES('c','d');
delete from vdata.sides where fldside_art = 'a';
END $$

Tommorow I''l try "Array DML" and "TFDScript"

Use TFDScript with just the INSERT and DELETE statements.

 

Edited January 17 by weirdo12

[Squamis 0]

Already 🙂 Thanks