Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Firebird - combine Array DML feature with returning clause


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Nov 24, 2016 12:38 AM Last Post By: Dmitry Arefiev
Ján Kolár

Posts: 7
Registered: 6/23/12
Firebird - combine Array DML feature with returning clause  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 16, 2016 3:18 AM
Does FireDAC support combination of array DML inserts with Firebird returning clause ? Consider for example following command:

insert into Customers (RegionID, Name, Note) values (:RegionID, :Name, :Note) returning ID

By using "returning" clause I can read primary key of new record. Is this possible when inserting new rows using array DML ? It would be very handy for those cases where primary keys are auto-generated for example in before insert trigger. Application doesn't have reliable way how to identify new records in such cases without using returning clause. I could for example remember last value of primary key (before insert), or call "select max(ID) ..." but this is not reliable in multi-user environment.

Edited by: Ján Kolár on Nov 16, 2016 3:19 AM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Firebird - combine Array DML feature with returning clause  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 17, 2016 4:32 AM   in response to: Ján Kolár in response to: Ján Kolár
Does FireDAC support combination of array DML inserts with Firebird returning clause ?

Honestly, never tried. But it should work.

--
With best regards,
Dmitry

Ján Kolár

Posts: 7
Registered: 6/23/12
Re: Firebird - combine Array DML feature with returning clause  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 23, 2016 6:27 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Apparently there is problem with this. If I add the "returning" clause and call Execute method like this:

query_insert->Execute(insert_count, 0);

I get exception with following error message:

[FireDAC][Phys][FB]-310. Cannot execute command returning result set. Hint: use Open method for SELECT-like commands.

Therefore I try to change above command like this:
query_insert->OpenOrExecute();

The result is that Array DML is not functioning. The above command always insert only one record. I can read its primary key value but this is not what I want. I wanted to insert 100 records in one step and read primary keys of all 100 new records in one step (i.e. minimize communication with database engine and increase performance i.e. number of inserts per second).

Dmitry Arefiev wrote:
Does FireDAC support combination of array DML inserts with Firebird returning clause ?

Honestly, never tried. But it should work.

--
With best regards,
Dmitry

Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Firebird - combine Array DML feature with returning clause  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 24, 2016 12:08 AM   in response to: Ján Kolár in response to: Ján Kolár
I tested and with some tricks it works:
  FDQuery1.SQL.Text := 'insert into Test (name) values (:name) returning id {into :id}';
  FDQuery1.Params[1].ParamType := ptOutput;
  FDQuery1.Params.ArraySize := 10;
  for i := 1 to FDQuery1.Params.ArraySize do
    FDQuery1.Params[0].AsStrings[i - 1] := 'Str' + i.ToString;
  FDQuery1.Execute(FDQuery1.Params.ArraySize, 0);


http://docwiki.embarcadero.com/RADStudio/Berlin/en/RETURNING_Unified_Support_(FireDAC)

PS: In next RAD Studio version setting parameter type to ptOutput will be not required.

--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02