Page 1 of 1
UPDATE OR INSERT INTO in TIBCLoader
Posted: Fri 31 Dec 2010 13:06
by macma77
Is there a possibility to implement "UPDATE OR INSERT INTO" syntax in TIBCLoader?
Regards,
Macma
Posted: Tue 04 Jan 2011 13:30
by AndreyZ
Hello,
Please describe the purpose of such functionality so that we are able to help you implement it.
You cannot use the "Update" or "Insert" statements syntax in the TIBCLoader component. The TIBCLoader component serves only for loading data into the database table, and for this you can use the OnGetColumnData and OnPutData events. You can look at the example of using the TIBCLoader component in IBDACDemo.
Posted: Wed 05 Jan 2011 07:53
by macma77
Hello
My application is typical SCADA.
I have table with some data (with primary key). I have to update this table (usually all records) with fresh values every 5s or less. If some record is missing I have to insert it.
I also have second table with history of this changes (primary key with some values and timestamp). Today we do this by executing procedure for every record.
I would like to get ability to chose between "INSERT" and new firebird syntax "UPDATE OR INSERT" when creating EXECUTE BLOCK in TIBCLoader
http://www.firebirdsql.org/refdocs/lang ... nsert.html.
I go further and think that It could be also "execute procedure" like in my case. Now I have to execute procedure for every record one by one. Probably executing this procedure in EXECUTE BLOCK give us a lot more performance.
Regrads,
Macma
Posted: Wed 05 Jan 2011 17:13
by AndreyZ
You can use the TCRBatchMove component to transfer data between datasets. Here is an example:
Code: Select all
CRBatchMove.Mode := bmAppendUpdate;
CRBatchMove.Source := IBCTable1;
CRBatchMove.Destination := IBCTable2;
CRBatchMove.Execute;
In the bmAppendUpdate mode records in the destination dataset are replaced with the matching records from the source dataset. If there is no matching record in the destination dataset, the record will be appended to it.
Posted: Wed 05 Jan 2011 20:45
by macma77
Hello
I don't think that performance of CRBatchMove would be sufficient enough.
Are TCRBatchMove using EXECUTE BLOCK internally ?
My conception is to expand functionality of TIBCLoader to be able to do thing like this:
Code: Select all
EXECUTE BLOCK (p1, p2, p3, p4, ...., pn-2,pn-1, pn )
AS
BEGIN
execute procedure AppendUpdate(p1,p2,p3);
execute procedure AppendUpdate(...);
.
.
.
execute procedure AppendUpdate(pn-2,pn-1,pn);
END
AppendUpdate is some procedure do insert or update data (or do anything else).
or at least
Code: Select all
EXECUTE BLOCK (p1, p2, p3, p4, ...., pn-2,pn-1, pn )
AS
BEGIN
UPDATE OR INSERT INTO TABLEX(x,y,x) values (p1,p2,p3);
.
.
.
UPDATE OR INSERT INTO TABLEX(x,y,x) values (pn-2,pn-1,pn);
END
The second example could be easily implemented.
Regards,
Macma
Posted: Thu 06 Jan 2011 12:12
by AndreyZ
We will investigate the possibility of adding this functionality in the future. As soon as we solve this question we will let you know.
Posted: Wed 06 Apr 2011 09:03
by macma77
Any news about possibility of adding this functionality?
Posted: Wed 06 Apr 2011 09:26
by AndreyZ
We will add this functionality in one of the next IBDAC builds.
Posted: Wed 04 May 2011 08:21
by Dimon
New build of IBDAC version 3.60.0.22 is available for download now. TIBCLoader.InsertMode property to allow using "UPDATE OR INSERT INTO" syntax for loading data was added in this build.