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 237]

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 143]

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, 2023 by Squamis

[Dmitry Arefiev 101]

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

[Brian Evans 105]

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 237]

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 18]

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, 2023 by Squamis

[weirdo12 19]

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, 2023 by weirdo12

[Squamis 0]

Already 🙂 Thanks

[Squamis 0]

Next problem

I very often use next type of queries

select id from orders where id = any(:id)

and set parameter like '{1,2}' for example

but FireDAC can't recognize that paramtype

 

I should rewrite sql-query like this

select id from orders where id = any(cast(:id as bigint[]))

but in this mode query freezes...

What you can recommend for me?

[Brian Evans 105]

There is an example of using array parameters for PostgreSQL.  Basically the ArrayType property is set to atTable and the method of assigning the values is like other arrays. 

FireDAC.PostgreSQL Arrays Sample - RAD Studio Code Examples (embarcadero.com)

[Squamis 0]

In this sample I can't find sample with using query with parameters

[Stano 143]

I don't know Postgre. Well, it wouldn't help in (...)

[Brian Evans 105]

24 minutes ago, Squamis said:

In this sample I can't find sample with using query with parameters

It does an INSERT using parameters with the third parameter :items being an array parameter it puts four values in for each of the five INSERTS it does (the for loop). 

Edited February 10, 2023 by Brian Evans

[programmerdelphi2k 237]

49 minutes ago, Stano said:

I don't know Postgre. Well, it wouldn't help in (...)

if wouldnt help, please refrain from posting messages without any contribution.  😂

[Dmitry Arefiev 101]

If something is freezing, then it is not good. Please report to QP, all together with original issue.