How to use IBCQuery in Batch Operation in C++ Builder

Discussion of open issues, suggestions and bugs regarding IBDAC (InterBase Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
sugi
Posts: 43
Joined: Wed 07 Dec 2016 02:05

How to use IBCQuery in Batch Operation in C++ Builder

Post by sugi » Wed 07 Dec 2016 02:17

Hi all,

I am using IBDAC in C++ Builder 6. When tried Batch operation, I found and example in the help file, but it was in Delphi format, so I changed it into like this :

Code: Select all

IBCQuery1->Close();
IBCQuery1->SQL->Clear();
IBCQuery1->SQL->Text = "update or insert into sync2(id,sql) values(:id,:sql)";

IBCQuery1->Params->Items[0]->DataType = ftInteger;
IBCQuery1->Params->Items[1]->DataType = ftString;

IBCQuery1->Params->ValueCount = 2000;

i=0;
while(!QNJSSync->Eof)
{
    Id  = QNJSSync->FieldByName("Id")->AsInteger;
    SQL = QNJSSync->FieldByName("SQL")->AsString;
    IBCQuery1->Params->Items[0]->AsInteger = Id;
    IBCQuery1->Params->Items[1]->AsString  = SQL;
    i++;
   QNJSSync->Next();
}
if(!Connection->InTransaction) Trs->StartTransaction();
      
IBCQuery1->Execute(1000,0);
Trs->Commit();
or with these codes

Code: Select all

    IBCQuery1->Close();
    IBCQuery1->SQL->Clear();
    IBCQuery1->SQL->Text = "update or insert into sync2(id,sql) values(:id,:sql)";

    IBCQuery1->ParamByName("id")->DataType = ftInteger;
    IBCQuery1->ParamByName("sql")->DataType = ftString;

    IBCQuery1->Params->ValueCount = 2000;

    i=0;
    while(!QNJSSync->Eof)
    {
        Id  = QNJSSync->FieldByName("Id")->AsInteger;
        SQL = QNJSSync->FieldByName("SQL")->AsString;
        IBCQuery1->ParamByName("id")->AsInteger = Id;
        IBCQuery1->ParamByName("SQL")->AsString = SQL;
        i++;
       QNJSSync->Next();
    }
    if(!Connection->InTransaction) Trs->StartTransaction();
         
    IBCQuery1->Execute(1000,0);
    Trs->Commit();
all got an same error :
"Invalid batch parameters count"

Where did I done wrong?

Thanks in advance.
Sugi.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: How to use IBCQuery in Batch Operation in C++ Builder

Post by ViktorV » Wed 07 Dec 2016 11:24

You should use the following code to set parameters value:

Code: Select all

  IBCQuery1->Params->Items[0]->Values[i]->AsInteger = Id;
  IBCQuery1->Params->Items[1]->Values[i]->AsString  = SQL;

sugi
Posts: 43
Joined: Wed 07 Dec 2016 02:05

Re: How to use IBCQuery in Batch Operation in C++ Builder

Post by sugi » Thu 08 Dec 2016 00:36

Thanks, it works OK & fast.

About 1900 records finished in +/- 6 seconds.

sugi
Posts: 43
Joined: Wed 07 Dec 2016 02:05

Re: How to use IBCQuery in Batch Operation in C++ Builder

Post by sugi » Thu 08 Dec 2016 02:29

After another test, I found some situation :
1 ) Even the operation using a transaction, the changed still happen even transaction was not committed.

This is my codes

Code: Select all

if(!Connection->InTransaction) Trs->StartTransaction();
IBCQuery1->Execute(1000,0);
Trs->Commit();
After a call to IBCQuery1->Execute(1000,0), data has already inserted even it was not committed.

Is it possible to control insert operation so it will only inserted if Transaction is committed. Changed IQCQuery1->CachedUpdates = true didn't have effect.

2) Application crashed when tried to Insert 292426 records, Set IBCQuery->UniDirectional = true, on TaskManager, Application consume about 600MB of RAM.

Error Message was :
Out Memory

There are plenty RAM in my PC. how many records could handle in batch operation?

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: How to use IBCQuery in Batch Operation in C++ Builder

Post by ViktorV » Fri 09 Dec 2016 13:31

1. At the moment UniDAC when working with Firebird (InterBase) executes batch operations in the context of a separate transaction to which a user has no access. We will consider a possibility of changing this behavior in one of the next UniDAC builds.
2. In order to get a detailed answer, please compose a small sample demonstrating the described behavior and send it using the contact form https://www.devart.com/company/contactform.html including scripts for creating database objects.

sugi
Posts: 43
Joined: Wed 07 Dec 2016 02:05

Re: How to use IBCQuery in Batch Operation in C++ Builder

Post by sugi » Sat 10 Dec 2016 04:38

Hi,

Sorry to say, I found that this batch operation is not safe.

I got corrupted database when testing this operation, here what I done :
1) The batch operation was doing using internet connection, means that the target table is on other computer connected to the soure table via internet
2) insert 20000 records IBCQuery->Execute(20000)
3) During the insert process, before it finished internet connection down/disconnet
4) The application raised an error, and no record inserted in the target table
5) After internet connection reestablished, rerun again the application and got an error Violation of Primary key/unique constraint, which is impossible caused I am sure no duplicate values sent to the target table. I even do a test by sending only 1 record to the empty target table, and the same error message raised.

Failed batch operation made Firebird database corrupt.

After Backup & restoring again Firebird database, the error gone, but, this command IBCQuery->Execute(1), runs without error but no records inserted. Execute more than 1 record, run fine. Strange??

Too bad, I need this feature, but can't use it, it is to risky.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: How to use IBCQuery in Batch Operation in C++ Builder

Post by ViktorV » Tue 13 Dec 2016 13:07

In order to get a detailed answer, please compose a small sample demonstrating the described behavior and send it using the contact form https://www.devart.com/company/contactform.html including scripts for creating database objects.

sugi
Posts: 43
Joined: Wed 07 Dec 2016 02:05

Re: How to use IBCQuery in Batch Operation in C++ Builder

Post by sugi » Wed 14 Dec 2016 02:05

Hi,

It is hard to compose a demo, caused like I said, the app doing batch insert from Comp A to Comp B connected via internet connection & number of records is big, 20000 records.

When IBCQuery->Execute(20000) issued, during the the process, internet connection broke down unexpectedly, this caused unfinished transaction in Firebird. That's why on the second issued of IBCQuery->Execute(1), Firebird raised an error of Violation Primary Key, caused there is a key from old failed transaction that makes value duplicate.

If you let user handle transaction manually on batch operation, may be there no error like this.

For now, I don't use batch operation anymore, even in LAN environment, it is not save.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: How to use IBCQuery in Batch Operation in C++ Builder

Post by ViktorV » Wed 14 Dec 2016 10:58

We will try to add a user access to the transaction, in the context of which batch operations are executed, in the next IBDAC release.

sugi
Posts: 43
Joined: Wed 07 Dec 2016 02:05

Re: How to use IBCQuery in Batch Operation in C++ Builder

Post by sugi » Thu 15 Dec 2016 02:08

ViktorV wrote:We will try to add a user access to the transaction, in the context of which batch operations are executed, in the next IBDAC release.
That's great. Batch operation has great potential in speed performance, in my test, at least 10x faster than non batch operation.

If you could make it save, surely it will add a valuable value to IBDAC.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: How to use IBCQuery in Batch Operation in C++ Builder

Post by ViktorV » Thu 15 Dec 2016 09:06

Thank you for being interested in our products.
We will inform you when we have any results.
Feel free to contact us if you have any further questions about our products.

Post Reply