UPDATE OR INSERT INTO in TIBCLoader

[macma77]

Is there a possibility to implement "UPDATE OR INSERT INTO" syntax in TIBCLoader?

Regards,
Macma

[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.

[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

[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.

[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

[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.

[macma77]

Any news about possibility of adding this functionality?

[AndreyZ]

We will add this functionality in one of the next IBDAC builds.

[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.