sporadic lost inserts using batchupdates

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
albourgz
Posts: 160
Joined: Wed 06 May 2009 12:17
Location: belgium

sporadic lost inserts using batchupdates

Post by albourgz » Thu 29 Jul 2021 10:13

using c+ builder 10.3.2, unidac 8.3.2, vcl win 32, oracle 12.2
I have a TUniSQL holding

Code: Select all

INSERT into XMLMESSDET(ID, PARENTID, TagName, TagValue, TREETop) VALUES (:1,:2,:3,:4,:5)
I don't know how many values I will have so I set at each batch

Code: Select all

q->Params->ValueCount=1000;
iArr1=0;
adding a row:

Code: Select all

q->Params->Items[0]->Values[iArr1]->AsLargeInt=...
q->Params->Items[1]->Values[iArr1]->AsLargeInt=...
q->Params->Items[2]->Values[iArr1]->AsIUnteger=...
if (s>"")
	q->Params->Items[3]->Values[iArr1]->AsString=s2;
else
	q->Params->Items[3]->Values[iArr1]->Clear();
q->Params->Items[4]->Values[iArr1]->AsLargeInt=...
iArr1++;
if (iArr1==1000) {
	q->Execute();	 // write 1000 first rows
	q->Params->ValueCount=1000;
	iArr1=0;
	}
And at the end:

Code: Select all

if (iArr1>0) {
	try {
		q->Execute(iArr1); // write unwritten rows and return.
	}
	catch(...) {
		iRv=-1;
		q=NULL;
		}
	}
It works 99.99% of the time, but sometimes I have sporadic inserts that were not inserted.
Monitor shows duration n/a, status pending for the insert, so the insert was asked but is pending then commit is done and insert is NOT committed.

I have to tell the same routine is called several time, could it be due to the fact that I usually update less than 20 rows and never get to execute the full 1000 rows?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: sporadic lost inserts using batchupdates

Post by MaximG » Fri 30 Jul 2021 13:41

Please compose the simplest full sample, in which the issue occurs, as well as a script to create the XMLMESSDET table. For your convenience, please use the e-support form https://www.devart.com/company/contactform.html

albourgz
Posts: 160
Joined: Wed 06 May 2009 12:17
Location: belgium

Re: sporadic lost inserts using batchupdates

Post by albourgz » Mon 02 Aug 2021 08:54

As I already use batchUpdates in many cases (where it works), I made many tests to figure out what happens.

I first added this after "prepare"

Code: Select all

		q->Params->Items[0]->ParamType=ptInput;
		q->Params->Items[1]->ParamType=ptInput;
		q->Params->Items[2]->ParamType=ptInput;
		q->Params->Items[3]->ParamType=ptInput;
		q->Params->Items[4]->ParamType=ptInput;
		q->Params->Items[0]->DataType=ftInteger;
		q->Params->Items[1]->DataType=ftInteger;
		q->Params->Items[2]->DataType=ftInteger;
		q->Params->Items[3]->DataType=ftString;
		q->Params->Items[4]->DataType=ftInteger;
It didn't change anything.

Then I replaced the "AsLargeInt=" by "AsInteger=" and then it seems to work perfectly.
Question: what will happen when I will really need a LargeInt? (e.g. 5.000.000.000)?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: sporadic lost inserts using batchupdates

Post by MaximG » Mon 02 Aug 2021 14:47

Can you please clarify if you get any errors when processing tech records which are not inserted at the end of the Batch operation? The sample of the code you provided should work correctly with both properties AsInteger and AsLargeInt.
Can you compose and send us a source code of a small sample which causes described error when using AsLargeInt property?

albourgz
Posts: 160
Joined: Wed 06 May 2009 12:17
Location: belgium

Re: sporadic lost inserts using batchupdates

Post by albourgz » Tue 03 Aug 2021 09:47

I reproduced a problem with a sample.
Where can I send it?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: sporadic lost inserts using batchupdates

Post by MaximG » Wed 11 Aug 2021 17:24

We have carefully investigated the work of the example you have sent and made sure that our components work correctly. The reason for the 'The parameter has different data type' error is the declaration of the parameters of your request, for example: q-> Params-> Items [0] -> DataType = ftInteger;

Kindly note that when using Largeint values, the corresponding query parameter must be declared as follows: q-> Params-> Items [0] -> DataType = ftLargeint;

Post Reply