Squamis 0 Posted January 16 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? Share this post Link to post
programmerdelphi2k 75 Posted January 16 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. Share this post Link to post
Squamis 0 Posted January 16 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...; Share this post Link to post
Stano 107 Posted January 16 34 minutes ago, Squamis said: No. I mean fully different queries. insert into table1....; delete from table2...; The first point says something else. Share this post Link to post
Squamis 0 Posted January 16 (edited) 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 Share this post Link to post
Dmitry Arefiev 91 Posted January 16 I am not sure, but try to set ResourceOptions.DirectExecute to True Share this post Link to post
Brian Evans 25 Posted January 16 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. Share this post Link to post
programmerdelphi2k 75 Posted January 16 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! Share this post Link to post
Virgo 9 Posted January 16 TFDScript is for executing SQL scripts AFAIK. https://docwiki.embarcadero.com/RADStudio/Sydney/en/Executing_SQL_Scripts_(FireDAC) Share this post Link to post
Squamis 0 Posted January 16 (edited) 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 Share this post Link to post
weirdo12 5 Posted January 17 (edited) 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 Share this post Link to post