Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: FireDAC / Array DML


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


Permlink Replies: 17 - Last Post: Mar 6, 2018 2:21 PM Last Post By: Jeff Overcash (... Threads: [ Previous | Next ]
Paul Jackson

Posts: 61
Registered: 3/2/17
FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 25, 2018 5:30 PM
I'm on Toyko 10.2.2 Ent

So I have this loop to do quicker update to a PG database. The problem is the entire table is being updated not just the the limit imposed by the user by setting
int rec_cnt


CustomersAssignUpdates is an FDQuery with this SQL, both params are set as ptInput and ftString and ftInteger respectively:
UPDATE CUSTOMERS
SET cur_status = :cur_status, employees_id = :employees_id


			
try			
{
     JDModule->CustomersAssignUpdates->Params->ArraySize=rec_cnt;
 
     for (auto i = 0; i < rec_cnt-1; i++)
	 {
		ProgressBar1->Position=i;
 
		JDModule->CustomersAssignUpdates->ParamByName("employees_id")->AsIntegers[i]=id;
		JDModule->CustomersAssignUpdates->ParamByName("cur_status")->AsStrings[i]=theStatus;
	}
	JDModule->CustomersAssignUpdates->Execute(rec_cnt);
} catch (const Exception& e)


It should be just updating rec_cnt number of records, but it doesn't, it sets all rows to the given values?

Any ideas, or better, what am I doing wrong. Thank you for any help.

--
-Paul
Mark Richards

Posts: 42
Registered: 12/8/99
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 26, 2018 3:43 AM   in response to: Paul Jackson in response to: Paul Jackson
Paul Jackson wrote:
I'm on Toyko 10.2.2 Ent

So I have this loop to do quicker update to a PG database. The problem is the entire table is being updated not just the the limit imposed by the user by setting
int rec_cnt


CustomersAssignUpdates is an FDQuery with this SQL, both params are set as ptInput and ftString and ftInteger respectively:
UPDATE CUSTOMERS
SET cur_status = :cur_status, employees_id = :employees_id


			
try			
{
     JDModule->CustomersAssignUpdates->Params->ArraySize=rec_cnt;
 
     for (auto i = 0; i < rec_cnt-1; i++)
	 {
		ProgressBar1->Position=i;
 
		JDModule->CustomersAssignUpdates->ParamByName("employees_id")->AsIntegers[i]=id;
		JDModule->CustomersAssignUpdates->ParamByName("cur_status")->AsStrings[i]=theStatus;
	}
	JDModule->CustomersAssignUpdates->Execute(rec_cnt);
} catch (const Exception& e)


It should be just updating rec_cnt number of records, but it doesn't, it sets all rows to the given values?

Any ideas, or better, what am I doing wrong. Thank you for any help.

--
-Paul

You need to specify a WHERE clause to narrow done the records which will be changed
Paul Jackson

Posts: 61
Registered: 3/2/17
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 26, 2018 4:46 PM   in response to: Mark Richards in response to: Mark Richards
Thank you Mark.

Hmm. Okay, I'm missing something here. What's the point in having a loop counter and looping through setting AsXxxs[ndx] after you narrow the set before hand.

So let's say I use a WHERE clause and limit the result set to 1000 records. Now I only want to set the first 100, if I used the same logic below it will still set the entire 1000 records.

Perhaps I'm not using the Array DML concept correctly. I'll play around and implement a WHERE clause to see if it achieves the goal.

Mark Richards wrote:
Paul Jackson wrote:
I'm on Toyko 10.2.2 Ent

So I have this loop to do quicker update to a PG database. The problem is the entire table is being updated not just the the limit imposed by the user by setting
int rec_cnt


CustomersAssignUpdates is an FDQuery with this SQL, both params are set as ptInput and ftString and ftInteger respectively:
UPDATE CUSTOMERS
SET cur_status = :cur_status, employees_id = :employees_id


			
try			
{
     JDModule->CustomersAssignUpdates->Params->ArraySize=rec_cnt;
 
     for (auto i = 0; i < rec_cnt-1; i++)
	 {
		ProgressBar1->Position=i;
 
		JDModule->CustomersAssignUpdates->ParamByName("employees_id")->AsIntegers[i]=id;
		JDModule->CustomersAssignUpdates->ParamByName("cur_status")->AsStrings[i]=theStatus;
	}
	JDModule->CustomersAssignUpdates->Execute(rec_cnt);
} catch (const Exception& e)


It should be just updating rec_cnt number of records, but it doesn't, it sets all rows to the given values?

Any ideas, or better, what am I doing wrong. Thank you for any help.

--
-Paul

You need to specify a WHERE clause to narrow done the records which will be changed

--
-Paul
Andy Walker

Posts: 72
Registered: 1/20/01
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 27, 2018 4:34 AM   in response to: Paul Jackson in response to: Paul Jackson
Hi Paul,

I'm a bit confused by your approach. The Array DML examples use INSERT as a way to reduce traffic between client/server so that commands are sent as a batch. This batch is based on the internal array that you have setup in code, based on the records you are INSERTing.

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Array_DML_(FireDAC)

If your intention is to update all records in the table with the same value then you can do this with a single update command anyway "UPDATE TableX SET x=y, a=b"

What are you trying to achieve by using Array DML because I'm not convinced that your approach would improve performance and if you are setting rec_cnt at the start of processing then what happens to records that might be added or deleted during processing?

Andy
Paul Jackson

Posts: 61
Registered: 3/2/17
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 27, 2018 2:29 PM   in response to: Andy Walker in response to: Andy Walker
Thank you Andy.

Yes I may be using the incorrect approach. So one shouldn't use batch processing for updating? I've read the wiki and seen the youtube from "FireDAC Array DML with PawelGlowacki." It seemed to me that it would also work with updates?

I have a CRM DB that for now has 4985 records in it. But it will eventually house many thousands of records. The way it is set up is a manager will import whatever list of leads they purchase, in this case it was the 4K. On import, using the DML, very fast, I'm setting an "employee id" to a default and a "status" to a default.

When the mgr is ready to "give" a set of records to the telemarketers, they assign a set of records to a given employee along with an updated status. My system only allows employees to see records assigned to them, unless you are a mgr, in which case you have the ability to see all.

I was thinking that anything that has mass inserts/updates, they could potentially assign 1000's of records to a given employee at any one time, would benefit from DML. In this case it would obviously be long a running process and I may achieve better performance using the Array DML approach, like when I import.

The reason I chose to use the DML approach is because it was taking too long to update the employee id and status in a loop. For say 100 records it was taking on the order of 30--45 seconds...

The loop was pretty simple. I'd put it here but I've already refactored and removed that code in lieu of the DML statements.

But it was something like below. The reason for the Edit() is because at the beginning and indeed at each iteration of the loop the table was being closed ? Don't know why? The reason for FindFirst() and FindNext() is because the records are potentially being filtered.

The records in the program are segregated from each other by a master id, then further by employee id's. So in this case, I don't see any issues where records are added to or updated while the algorithm takes place. However, that could be a potential problem later and I thank you for pointing it out! I totally missed that.

I thank you so much for your time.

JDModule->CustomersTable->FindFirst();
 
for (auto i = 0; i < rec_cnt-1; i++)
{
     JDModule->CustomersTable->Edit();
     JDModule->CustomersTable->FieldByName("employees_id")->AsInteger=id;
     JDModule->CustomersTable->FieldByName("cur_status")->AsString=theStatus;
     JDModule->CustomersTable->Post();
}
 
JDModule->CustomersTable->FindNext();

Andy Walker wrote:
Hi Paul,

I'm a bit confused by your approach. The Array DML examples use INSERT as a way to reduce traffic between client/server so that commands are sent as a batch. This batch is based on the internal array that you have setup in code, based on the records you are INSERTing.

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Array_DML_(FireDAC)

If your intention is to update all records in the table with the same value then you can do this with a single update command anyway "UPDATE TableX SET x=y, a=b"

What are you trying to achieve by using Array DML because I'm not convinced that your approach would improve performance and if you are setting rec_cnt at the start of processing then what happens to records that might be added or deleted during processing?

Andy

--
-Paul
Andy Walker

Posts: 72
Registered: 1/20/01
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 27, 2018 4:13 PM   in response to: Paul Jackson in response to: Paul Jackson
Paul Jackson wrote:
Thank you Andy.

Yes I may be using the incorrect approach. So one shouldn't use batch processing for updating? I've read the wiki and seen the youtube from "FireDAC Array DML with PawelGlowacki." It seemed to me that it would also work with updates?

I think it could work but I initially thought you were trying to update all records in a table whereas I believe you are trying to update only "filtered" records in a Table?

I have a CRM DB that for now has 4985 records in it. But it will eventually house many thousands of records. The way it is set up is a manager will import whatever list of leads they purchase, in this case it was the 4K. On import, using the DML, very fast, I'm setting an "employee id" to a default and a "status" to a default.

How is FDQuery initially populated with the required records? You have said that the SQL contains an UPDATE command so how do you populate FDQuery to begin with?

When the mgr is ready to "give" a set of records to the telemarketers, they assign a set of records to a given employee along with an updated status. My system only allows employees to see records assigned to them, unless you are a mgr, in which case you have the ability to see all.

I was thinking that anything that has mass inserts/updates, they could potentially assign 1000's of records to a given employee at any one time, would benefit from DML. In this case it would obviously be long a running process and I may achieve better performance using the Array DML approach, like when I import.

The alternative is to compile a list of the master IDs and then issue a command like, "UPDATE CustomersTable SET employees_id=" + String(id) + ", cur_status='" + theStatus + "' WHERE master_id IN (" + listOfIDs + ")"

The reason I chose to use the DML approach is because it was taking too long to update the employee id and status in a loop. For say 100 records it was taking on the order of 30--45 seconds...

This is a trivial number of records and shouldn't take that long.

The loop was pretty simple. I'd put it here but I've already refactored and removed that code in lieu of the DML statements.

But it was something like below. The reason for the Edit() is because at the beginning and indeed at each iteration of the loop the table was being closed ? Don't know why? The reason for FindFirst() and FindNext() is because the records are potentially being filtered.

The records in the program are segregated from each other by a master id, then further by employee id's So in this case, I don't see any issues where records are added to or updated while the algorithm takes place. However, that could be a potential problem later and I thank you for pointing it out! I totally missed that.

I thank you so much for your time.

JDModule->CustomersTable->FindFirst();
 
for (auto i = 0; i < rec_cnt-1; i++)
{
     JDModule->CustomersTable->Edit();
     JDModule->CustomersTable->FieldByName("employees_id")->AsInteger=id;
     JDModule->CustomersTable->FieldByName("cur_status")->AsString=theStatus;
     JDModule->CustomersTable->Post();
}
 
JDModule->CustomersTable->FindNext();


If that was your code, and rec_cnt was 4000 then it looks like you were updating each record in CustomersTable 4000 times. Edit will put the dataset into edit mode and allow you to change the values of the active record. So you are looping around 4000 times and editing the same record each time. FindNext will then move you to the next record. What you need is something like this;

if (JDModule->CustomersTable->FindFirst()){
   do {
        JDModule->CustomersTable->Edit();
        JDModule->CustomersTable->FieldByName("employees_id")->AsInteger=id;
        JDModule->CustomersTable->FieldByName("cur_status")->AsString=theStatus;
        JDModule->CustomersTable->Post();
   }
   while (JDModule->CustomersTable->FindNext());
}


This will take you to the First filtered record and make it active, update the values, post them to the DB and then FindNext. If FindNext locates another record then the process is repeated. FindNext will return false if there are no further records and the loop will exit.

http://docwiki.embarcadero.com/Libraries/Tokyo/en/Data.DB.TDataSet.FindNext

See if that works and is any quicker? If not then answer the above questions and I'll take a look to see if I can figure out why the DML approach isn't working but I'll need to know how you are populating and filtering the CustomersAssignUpdates FDQuery.

Andy
Paul Jackson

Posts: 61
Registered: 3/2/17
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 27, 2018 6:07 PM   in response to: Andy Walker in response to: Andy Walker
Andy, I'm sorry sir, the code I sent was a quick note. I was looping as you showed, as in the FindFirst and FindNext were part of the loop, so it was setting the "next" record correctly. It was just taking too long.

This is long winded as well, so please bear with me but I'd like to explain exactly what's happening from the users POV.

I think it could work but I initially thought you were tying to update all records in a table whereas I believe you are trying to update only "filtered" records in a Table?
Yes, it does get filtered as they work. When the manager opens an AssignmentForm, I have all records filtered where "status" is equal to <UNASSIGNED> and "employee_id" is set to "DEFAULT EMPLOYEE". This data is taken from another data set, CustomersTable, which is populated with:
SELECT * FROM customers
WHERE franchise_id = :franchise_id


As these records are retrieved from the database, I set the "status" and "emp_id" to the default values, and the underlying database is updated accordingly.

The AssignmentForm is where I'm using the loop. This form has an edit box that allows the manager to set the value for the loop, and two ComboBoxes which allow for filtering on the "status" and "emp_id." When this form is opened the user is staring at a list of records, from the CustomersTable that match the criteria of "DEFAULT EMPLOYEE" for emp_id and "<UNASSIGNED>" for the status.

How is FDQuery initially populated with the required records? You have said that the SQL contains an UPDATE command so how do you populate FDQuery to begin with?

So I'm actually using two FDQuery's to work with the data. The initial query is as stated above, the update query simply has :
UPDATE customers
SET cur_status = :cur_status, employees_id = :employees_id


Again I'm using the base table with more than one FDQuery component and in fact I have several.

The alternative is to compile a list of the master IDs and then issue a command like, "UPDATE CustomersTable SET employees_id=" + String(id) + ", cur_status='" + theStatus + "' WHERE master_id IN (" + listOfIDs + ")"

This is good advice, it may need to be done, if I can't get the DML working correctly.

This is a trivial number of records and shouldn't take that long.

Agreed!

What you need is something like this;

Again I'm sorry the loop was actually updating the correct record.


Yep I have read and re-read the TDataSet wiki's :) Many hours spent reviewing the wiki's.

See if that works and is any quicker? If not then answer the above questions and I'll take a look to see if I can figure out why the DML approach isn't working but I'll need to know how you are populating and filtering the CustomersAssignUpdates FDQuery.

Andy
Below are the actual code excerpt's from my program, I thought I had tossed the original loop but I didn't so here are both.

First loop, this original version was not updating the status field, it was still working but taking too long:
void __fastcall TAssignForm::btAssignClick(TObject *Sender)
{
 
	int rec_cnt=StrToInt(seNumToAssign->Text);
	if(rec_cnt > 0 || AssignedNameDBLookup->Text !="")
	{
		int empID;
		bool locateSuccess;
 
		ProgressBar1->Min=1;
		ProgressBar1->Max=rec_cnt;
 
		String name = AssignedNameDBLookup->Text;
 
		locateSuccess = JDModule->EmployeesTable->Locate("full_name", name);
 
		if(locateSuccess)
		{
			JDModule->CustomersTable->Open();
			int id = JDModule->EmployeesTable->FieldByName("employees_id")->AsInteger;
			JDModule->CustomersTable->FindFirst();
 
			for (auto i = 0; i < rec_cnt; i++)
			{
				ProgressBar1->Position=i;
				JDModule->CustomersTable->Edit();
				JDModule->CustomersTable->FieldByName("employees_id")->AsInteger=id;
 
				JDModule->CustomersTable->FindNext();
			}
			ShowMessage("Completed.");
		}
	}
	else
		ShowMessage("The number to assign cannot be 0\n and/or the assignee cannot be blank");
 
	lbTotRecs->Text = IntToStr(JDModule->CustomersTable->RecordCount);
 
}


The second loop, the ChangeStatusForm is a modal which upon mrOk, contains the employee_id and status values. This works very fast, the progress bar of course updates immediately, the execute takes only a few seconds to return, but sets all 4529 records no matter what the rec_cnt is set to, as long as it's greater than 1. Also, I have to comment out the transaction and commit or it doesn't set the values at all. This tells me there must be FDQuery1ExecuteError's taking place. Currently I'm not doing anything with exceptions.

void __fastcall TAssignForm::btChangeStatusClick(TObject *Sender)
{
 
	int rec_cnt=StrToInt(seNumToAssign->Text);
	if(rec_cnt != 0)
	{
		int empID;
	
		ProgressBar1->Min=1;
		ProgressBar1->Max=rec_cnt;
 
		ChangeStatusForm->ShowModal();
 
		String theStatus = ChangeStatusForm->theStatus;
		int id = ChangeStatusForm->emp_id;
 
		if(theStatus != "" && id !=0)
		{
			TCursor Save_Cursor=Screen->Cursor;
			try
			{
				JDModule->DBConnection->StartTransaction();
				Screen->Cursor = crHourGlass;
				try
				{
					JDModule->CustomersAssignUpdates->Params->ArraySize=rec_cnt;
 
					for (auto i = 0; i < rec_cnt-1; i++)
					{
						ProgressBar1->Position=i+1;
 
						JDModule->CustomersAssignUpdates->ParamByName("employees_id")->AsIntegers[i]=id;
						JDModule->CustomersAssignUpdates->ParamByName("cur_status")->AsStrings[i]=theStatus;
 
					}
 
				JDModule->CustomersAssignUpdates->Execute(rec_cnt);
 
				} catch (const Exception& e) {
				   ShowMessage("Unable to update table: " + e.Message);
				}
			}
			__finally
			{
			   Screen->Cursor = Save_Cursor;
			   JDModule->DBConnection->Commit();
			}
		}
		lbTotRecs->Text = IntToStr(JDModule->CustomersTable->RecordCount);
		ProgressBar1->Position=0;
		ShowMessage("Complete");
	}
	else
		ShowMessage("Number to assign cannot be 0");
}
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 28, 2018 12:40 AM   in response to: Paul Jackson in response to: Paul Jackson
On 2/27/2018 9:07 PM, Paul Jackson wrote:
Andy, I'm sorry sir, the code I sent was a quick note. I was looping as you showed, as in the FindFirst and FindNext were part of the loop, so it was setting the "next" record correctly. It was just taking too long.

This is long winded as well, so please bear with me but I'd like to explain exactly what's happening from the users POV.

I think it could work but I initially thought you were tying to update all records in a table whereas I believe you are trying to update only "filtered" records in a Table?
Yes, it does get filtered as they work. When the manager opens an AssignmentForm, I have all records filtered where "status" is equal to <UNASSIGNED> and "employee_id" is set to "DEFAULT EMPLOYEE". This data is taken from another data set, CustomersTable, which is populated with:
SELECT * FROM customers
WHERE franchise_id = :franchise_id


As these records are retrieved from the database, I set the "status" and "emp_id" to the default values, and the underlying database is updated accordingly.

The AssignmentForm is where I'm using the loop. This form has an edit box that allows the manager to set the value for the loop, and two ComboBoxes which allow for filtering on the "status" and "emp_id." When this form is opened the user is staring at a list of records, from the CustomersTable that match the criteria of "DEFAULT EMPLOYEE" for emp_id and "<UNASSIGNED>" for the status.

How is FDQuery initially populated with the required records? You have said that the SQL contains an UPDATE command so how do you populate FDQuery to begin with?

So I'm actually using two FDQuery's to work with the data. The initial query is as stated above, the update query simply has :
UPDATE customers
SET cur_status = :cur_status, employees_id = :employees_id


Aren't you missing your where clause there? Each time you are updating all the
records.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Andy Walker

Posts: 72
Registered: 1/20/01
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 28, 2018 1:08 AM   in response to: Paul Jackson in response to: Paul Jackson
So I'm actually using two FDQuery's to work with the data. The initial query is as stated above, the update query simply has :
UPDATE customers
SET cur_status = :cur_status, employees_id = :employees_id


OK, now I understand what you are trying to achieve and as Mark and Jeff have pointed out you just need a where clause in your SQL using a unique key on your record. From what you have said this is probably master_id ?

UPDATE customers
SET cur_status = :cur_status, employees_id = :employees_id WHERE master_id = :master_id


Your code at the moment is updating every record in the TABLE for each record in the filtered dataset.

Andy
Paul Jackson

Posts: 61
Registered: 3/2/17
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 28, 2018 1:58 PM   in response to: Andy Walker in response to: Andy Walker
Alright, I do get the idea of the WHERE clause. In my case there are many master_ids and those are Fk's in many many customer records.

In any event, I agree with the logic in the posts, the missing WHERE clause must be whats causing the issue. I'll come up with another identifying variable and use it in a WHERE clause.

However, let me through this back out at you guys.

Why is the Params->ArraySize variable and the loop counter rec_cnt seemingly being ignored?
JDModule->CustomersAssignUpdates->Params->ArraySize=rec_cnt;


What I understand from the wiki and other work I've done with INSERT's, the Params->ArraySize is the allocated buffer holding the data to be processed. If I set it the ArraySize to be of size 10, and it goes to work on 4000+ records, why am I not getting index out of range or even memory access errors?

If the rec_cnt is set to 10 it should only process the loop 10 times and set the Params for 10 records. Instead it loops 10 times, setting all 4K, 10 times.

Let me try to work on a WHERE clause and I'll mark answered to the question. But I'd still like to understand how the array is not throwing exceptions?

Thank you all.

Andy Walker wrote:
So I'm actually using two FDQuery's to work with the data. The initial query is as stated above, the update query simply has :
UPDATE customers
SET cur_status = :cur_status, employees_id = :employees_id


OK, now I understand what you are trying to achieve and as Mark and Jeff have pointed out you just need a where clause in your SQL using a unique key on your record. From what you have said this is probably master_id ?

UPDATE customers
SET cur_status = :cur_status, employees_id = :employees_id WHERE master_id = :master_id


Your code at the moment is updating every record in the TABLE for each record in the filtered dataset.

Andy

--
-Paul
Andy Walker

Posts: 72
Registered: 1/20/01
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 28, 2018 3:20 PM   in response to: Paul Jackson in response to: Paul Jackson
Paul Jackson wrote:
Alright, I do get the idea of the WHERE clause. In my case there are many master_ids and those are Fk's in many many customer records.

In any event, I agree with the logic in the posts, the missing WHERE clause must be whats causing the issue. I'll come up with another identifying variable and use it in a WHERE clause.

However, let me through this back out at you guys.

Why is the Params->ArraySize variable and the loop counter rec_cnt seemingly being ignored?
JDModule->CustomersAssignUpdates->Params->ArraySize=rec_cnt;


What I understand from the wiki and other work I've done with INSERT's, the Params->ArraySize is the allocated buffer holding the data to be processed. If I set it the ArraySize to be of size 10, and it goes to work on 4000+ records, why am I not getting index out of range or even memory access errors?

If the rec_cnt is set to 10 it should only process the loop 10 times and set the Params for 10 records. Instead it loops 10 times, setting all 4K, 10 times.

This code will loop rec_cnt number of times starting at record 0 and ending at rec_cnt-1.

for (auto i = 0; i < rec_cnt-1; i++)
{
	ProgressBar1->Position=i+1;
 
	JDModule->CustomersAssignUpdates->ParamByName("employees_id")->AsIntegers[i]=id;
	JDModule->CustomersAssignUpdates->ParamByName("cur_status")->AsStrings[i]=theStatus;
 
} 


If rec_cnt is 10 then this code will loop through the first 10 records (0..9) and set the employees_id and cur_status

JDModule->CustomersAssignUpdates->Execute(rec_cnt);


For each of those 10 records the above code will execute the SQL "UPDATE customers SET cur_status = :cur_status, employees_id = :employees_id"

This will update all records in the table, 10 times. It will use the employees_id and the cur_status from each record to set the values for all records. This is exactly what you are seeing? If you add a unique WHERE clause then it will only update the single record that you have edited.

You are telling the query to update all records in the table based on the employees_id and cur_status you set for record 1
Then update all records in the table based on the employees_id and cur_status you set for record 2
Then update all records in the table based on the employees_id and cur_status you set for record 3
etc.

Putting in a WHERE clause will restrict the records that are UPDATEd.

Andy
Paul Jackson

Posts: 61
Registered: 3/2/17
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 28, 2018 4:54 PM   in response to: Andy Walker in response to: Andy Walker
Wow. Yes, I see it now! Excellent, and I thank you very much sir.

Best regards,
-Paul

Andy Walker wrote:
Paul Jackson wrote:
Alright, I do get the idea of the WHERE clause. In my case there are many master_ids and those are Fk's in many many customer records.

In any event, I agree with the logic in the posts, the missing WHERE clause must be whats causing the issue. I'll come up with another identifying variable and use it in a WHERE clause.

However, let me through this back out at you guys.

Why is the Params->ArraySize variable and the loop counter rec_cnt seemingly being ignored?
JDModule->CustomersAssignUpdates->Params->ArraySize=rec_cnt;


What I understand from the wiki and other work I've done with INSERT's, the Params->ArraySize is the allocated buffer holding the data to be processed. If I set it the ArraySize to be of size 10, and it goes to work on 4000+ records, why am I not getting index out of range or even memory access errors?

If the rec_cnt is set to 10 it should only process the loop 10 times and set the Params for 10 records. Instead it loops 10 times, setting all 4K, 10 times.

This code will loop rec_cnt number of times starting at record 0 and ending at rec_cnt-1.

for (auto i = 0; i < rec_cnt-1; i++)
{
	ProgressBar1->Position=i+1;
 
	JDModule->CustomersAssignUpdates->ParamByName("employees_id")->AsIntegers[i]=id;
	JDModule->CustomersAssignUpdates->ParamByName("cur_status")->AsStrings[i]=theStatus;
 
} 


If rec_cnt is 10 then this code will loop through the first 10 records (0..9) and set the employees_id and cur_status

JDModule->CustomersAssignUpdates->Execute(rec_cnt);


For each of those 10 records the above code will execute the SQL "UPDATE customers SET cur_status = :cur_status, employees_id = :employees_id"

This will update all records in the table, 10 times. It will use the employees_id and the cur_status from each record to set the values for all records. This is exactly what you are seeing? If you add a unique WHERE clause then it will only update the single record that you have edited.

You are telling the query to update all records in the table based on the employees_id and cur_status you set for record 1
Then update all records in the table based on the employees_id and cur_status you set for record 2
Then update all records in the table based on the employees_id and cur_status you set for record 3
etc.

Putting in a WHERE clause will restrict the records that are UPDATEd.

Andy

--
-Paul
Paul Jackson

Posts: 61
Registered: 3/2/17
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 2, 2018 9:37 AM   in response to: Andy Walker in response to: Andy Walker
Hello All,

So here's what I was looking for and it works so far in SQL. I'll put it into an code tonight but this shows what I am trying to achieve. The user needs to be able to limit the records updated by a value entered not by an identifying ID of any sort. Once this SQL is executed, the user will then use the UI to filer out that data, there by going to the next set updating as needed. So it allows a Manager to start with a large set of however many, 4K + records, set the first 100 to a given employee, filter those out, go to the next XxX, set those and so on, and so here's the simple SQL, that implements the WHERE clause needed:

 UPDATE "CUSTOMERS"
 SET cur_status=:cur_status, employees_id = :employees_id
 WHERE id IN (
		 SELECT customer_id FROM "CUSTOMERS"
		 ORDER BY customer_id ASC
		 LIMIT  <theLimitingVariable>
 )


Thanks for all the help.

Andy Walker wrote:
Paul Jackson wrote:
Alright, I do get the idea of the WHERE clause. In my case there are many master_ids and those are Fk's in many many customer records.

In any event, I agree with the logic in the posts, the missing WHERE clause must be whats causing the issue. I'll come up with another identifying variable and use it in a WHERE clause.

However, let me through this back out at you guys.

Why is the Params->ArraySize variable and the loop counter rec_cnt seemingly being ignored?
JDModule->CustomersAssignUpdates->Params->ArraySize=rec_cnt;


What I understand from the wiki and other work I've done with INSERT's, the Params->ArraySize is the allocated buffer holding the data to be processed. If I set it the ArraySize to be of size 10, and it goes to work on 4000+ records, why am I not getting index out of range or even memory access errors?

If the rec_cnt is set to 10 it should only process the loop 10 times and set the Params for 10 records. Instead it loops 10 times, setting all 4K, 10 times.

This code will loop rec_cnt number of times starting at record 0 and ending at rec_cnt-1.

for (auto i = 0; i < rec_cnt-1; i++)
{
	ProgressBar1->Position=i+1;
 
	JDModule->CustomersAssignUpdates->ParamByName("employees_id")->AsIntegers[i]=id;
	JDModule->CustomersAssignUpdates->ParamByName("cur_status")->AsStrings[i]=theStatus;
 
} 


If rec_cnt is 10 then this code will loop through the first 10 records (0..9) and set the employees_id and cur_status

JDModule->CustomersAssignUpdates->Execute(rec_cnt);


For each of those 10 records the above code will execute the SQL "UPDATE customers SET cur_status = :cur_status, employees_id = :employees_id"

This will update all records in the table, 10 times. It will use the employees_id and the cur_status from each record to set the values for all records. This is exactly what you are seeing? If you add a unique WHERE clause then it will only update the single record that you have edited.

You are telling the query to update all records in the table based on the employees_id and cur_status you set for record 1
Then update all records in the table based on the employees_id and cur_status you set for record 2
Then update all records in the table based on the employees_id and cur_status you set for record 3
etc.

Putting in a WHERE clause will restrict the records that are UPDATEd.

Andy

--
-Paul
Andy Walker

Posts: 72
Registered: 1/20/01
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 2, 2018 2:50 PM   in response to: Paul Jackson in response to: Paul Jackson
Paul Jackson wrote:
Hello All,

So here's what I was looking for and it works so far in SQL. I'll put it into an code tonight but this shows what I am trying to achieve. The user needs to be able to limit the records updated by a value entered not by an identifying ID of any sort. Once this SQL is executed, the user will then use the UI to filer out that data, there by going to the next set updating as needed. So it allows a Manager to start with a large set of however many, 4K + records, set the first 100 to a given employee, filter those out, go to the next XxX, set those and so on, and so here's the simple SQL, that implements the WHERE clause needed:

 UPDATE "CUSTOMERS"
 SET cur_status=:cur_status, employees_id = :employees_id
 WHERE id IN (
		 SELECT customer_id FROM "CUSTOMERS"
		 ORDER BY customer_id ASC
		 LIMIT  <theLimitingVariable>
 )

This doesn't look right to me still. This will still be updating many records for a single edit. Is that your intention? Should a single edit only update a single record in the DB?

If they edit 100 records then this will update 100 records 100 times.

You don't need the ORDER BY in the WHERE clause, it serves no purpose and will slow down the update.

This also suggests that the CUSTOMERS table has an id field and a customer_id field? Is that right?

If you include the customer_id in the original SELECT query then you will have your unique identifier and your update can just be;

UPDATE "CUSTOMERS"
SET cur_status=:cur_status, employees_id = :employees_id
WHERE id = :customer_id


What is your original SELECT query?

Andy

Edited by: Andy Walker on Mar 2, 2018 2:59 PM
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 3, 2018 11:08 AM   in response to: Paul Jackson in response to: Paul Jackson
On 3/2/2018 12:37 PM, Paul Jackson wrote:
Hello All,

So here's what I was looking for and it works so far in SQL. I'll put it into an code tonight but this shows what I am trying to achieve. The user needs to be able to limit the records updated by a value entered not by an identifying ID of any sort. Once this SQL is executed, the user will then use the UI to filer out that data, there by going to the next set updating as needed. So it allows a Manager to start with a large set of however many, 4K + records, set the first 100 to a given employee, filte
r those out, go to the next XxX, set those and so on, and so here's the simple SQL, that implements the WHERE clause needed:

  UPDATE "CUSTOMERS"
  SET cur_status=:cur_status, employees_id = :employees_id
  WHERE id IN (
		 SELECT customer_id FROM "CUSTOMERS"
		 ORDER BY customer_id ASC
		 LIMIT  <theLimitingVariable>
  )


Thanks for all the help.

This still looks wrong. No matter what they filter you will still only be doing
the fist X number of customer ID's. Your filtering criteria is not part of the
IN select. so if they do the first 100 then Filter then do the next 100 you are
going to do the first 100 again. Your SQL is executing on the server not on the
result set (filtered) you have locally.

If you are going to do it from a client side filter you need to build your IN
clause dynamically from the filter and execute based on what they filtered.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Paul Jackson

Posts: 61
Registered: 3/2/17
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 3, 2018 3:07 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Hi Jeff,

Well, you are right sir. It doesn't work as is. Here's what I did in code last night - and it calculated as you said, so I'm going to need to change the WHERE IN clause to work, as you suggest.

Anyway, I'm getting closer, as it does do the update LIMIT times. But I believe, instead of updating the whole 4K records recordCount times, it is still doing the update LIMIT times for each record recordCount times. So that said, when I get the final WHERE clause completed it should calculate correctly.

Please any other suggestions would be most appreciated. Thank you all for your comments.

CustomersAssignUpdater->SQL->Text=
"UPDATE CUSTOMERS \
      SET cur_status = :cur_status, employees_id = :employees_id \
      WHERE company_id IN \
          (SELECT company_id FROM CUSTOMERS \
           ORDER BY company_id ASC \
            LIMIT " + IntToStr( recordCount ) + " )";
 
CustomersAssignUpdater->Params->ArraySize=recordCount;
 
for (auto i = 0; i < recordCount-1; i++)
{
    CustomersAssignUpdater->ParamByName("employees_id")->AsIntegers[i]=employeeID;
    CustomersAssignUpdater->ParamByName("cur_status")->AsStrings[i]=theStatus;
}
 
CustomersAssignUpdater->Execute(recordCount);

Jeff Overcash (TeamB) wrote:
On 3/2/2018 12:37 PM, Paul Jackson wrote:


This still looks wrong. No matter what they filter you will still only be doing
the fist X number of customer ID's. Your filtering criteria is not part of the
IN select. so if they do the first 100 then Filter then do the next 100 you are
going to do the first 100 again. Your SQL is executing on the server not on the
result set (filtered) you have locally.

If you are going to do it from a client side filter you need to build your IN
clause dynamically from the filter and execute based on what they filtered.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

--
-Paul

Edited by: Paul Jackson on Mar 3, 2018 6:08 PM

Edited by: Paul Jackson on Mar 3, 2018 6:08 PM
Mark Richards

Posts: 42
Registered: 12/8/99
Re: FireDAC / Array DML  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 4, 2018 4:30 AM   in response to: Paul Jackson in response to: Paul Jackson
It would help considerably if you could provide us with the DDL (table structure showing field names, types and keys) for the table you wish to update
Further as I do not know what your database back-end is, I am unable to know if your database supports LIMIT in a subquery?

I assume you have access to a command line or GUI query editor / result for your database?

As we do not know the default settings for the table when the records are initially inserted, let me assume the following can be applied:
The records inserted relate to:
A known company id (for example 666)
Assigned a default cur_status of <UNASSIGNED>
Assigned a non exiting employeesID (e.g. 0)

The main problem with providing a solution is that you have to uniquely identify each record you wish to be updated.

I will use the following assumptions as an example...
You are wanting to assign the first 100 records for company_id 666 to employees_id 101 and set the cur_status to <ASSIGNED>

If your database ALLOWS the keyword LIMIT in a subquery

// an example to simply SHOW the id's that could be updated...
SELECT id FROM CUSTOMERS WHERE company_id = 666 AND employees_id = 0 AND cur_status = '<UNASSIGNED>' LIMIT 100


When you are satisfied that the select statement is returning the correct range, add it back to the UPDATE statement

UPDATE CUSTOMERS SET cur_status = '<ASSIGNED>', employees_id = 101 WHERE company_id IN 
(SELECT id FROM customers WHERE company_id = 666 AND employee_id = 0 AND cur_status = '<UNASSIGNED>' LIMIT 100)


To use the above in c++ code
String currentStatus = "<ASSIGNED>";
int companyID        = 666;
int employeesID      = 101;
int recordCount      = 100;
 
FDQuery->SQL->Text = Sysutils::Format("UPDATE customers SET \
   cur_status = %s, \
   employees_id = %d \
   WHERE company_id IN \
   ( SELECT id FROM customers WHERE \
      company_id = %d AND \
      employees_id = 0 AND \
      cur_status = '<UNASSIGNED>' \
      LIMIT %d \
   )",
   ARRAYOFCONST((Sysutils::QuotedStr(currentStatus), employeesID, companyID, recordCount)));
 

If your database DOES NOT ALLOW the keyword LIMIT in a subquery

In this case, the SQL is slightly more involved as it uses a temporary table

UPDATE customers, 
 (SELECT id FROM customers WHERE company_id = 666 AND employee_id = 0 AND cur_status = '<UNASSIGNED>' ORDER BY id LIMIT 100) AS temp_table 
 SET customers.employee_id = 101, customers.cur_status = '<ASSIGNED>' WHERE customers.id = temp_table.id


A c++ code example...
String currentStatus = "<ASSIGNED>";
int companyID        = 666;
int employeesID      = 101;
int recordCount      = 100;
 
FDQuery->SQL->Text = Sysutils::Format("UPDATE customers, \
   ( SELECT id FROM customers \
	  WHERE company_id = %d \
	  AND employees_id = 0 \
	  AND cur_status = '<UNASSIGNED>' \
	  ORDER BY id \
	  LIMIT %d \
   ) AS temp_table \
   SET customers.employees_id = %d, \
   customers.cur_status = %s \
   WHERE customers.id = temp_table.id",
   ARRAYOFCONST((companyID, recordCount, employeesID, Sysutils::QuotedStr(currentStatus))));

If you can provide the database structure it will help us narrow it down for you!

Edited by: Mark Richards on Mar 4, 2018 7:14 AM

Edited by: Mark Richards on Mar 4, 2018 8:35 AM

Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: FireDAC / Array DML [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 6, 2018 2:21 PM   in response to: Paul Jackson in response to: Paul Jackson
On 3/3/2018 6:09 PM, Paul Jackson wrote:
Hi Jeff,

Well, you are right sir. It doesn't work as is. Here's what I did in code last night - and it calculated as you said, so I'm going to need to change the WHERE IN clause to work, as you suggest.

Anyway, I'm getting closer, as it does do the update LIMIT times. But I believe, instead of updating the whole 4K records recordCount times, it is still doing the update LIMIT times for each record recordCount times. So that said, when I get the final WHERE clause completed it should calculate correctly.

Please any other suggestions would be most appreciated. Thank you all for your comments.

CustomersAssignUpdater->SQL->Text=
"UPDATE CUSTOMERS \
       SET cur_status = :cur_status, employees_id = :employees_id \
       WHERE company_id IN \
           (SELECT company_id FROM CUSTOMERS \
            ORDER BY company_id ASC \
             LIMIT " + IntToStr( recordCount ) + " )";
 
CustomersAssignUpdater->Params->ArraySize=recordCount;
 
for (auto i = 0; i < recordCount-1; i++)
{
     CustomersAssignUpdater->ParamByName("employees_id")->AsIntegers[i]=employeeID;
     CustomersAssignUpdater->ParamByName("cur_status")->AsStrings[i]=theStatus;
}
 
CustomersAssignUpdater->Execute(recordCount);



I think you are misusing the array DML here. you are doing a single mass update
on the server. The array DML is meant to do a group of single record updates in
one mass burst.

Look at what you wrote here. You have an Update SQL that's restricting where
clause will always be the same so will always select the same records. You have
a loop to assign your employeeID and status to the same value for all the
records to update. You then tell it to execute this on the server to run
RecordCount number of times. but the very first SQL that executes will set all
the value you intend to set and the next 2-RecordCount executions just do
exactly the same thing as the first one did.

When doing individual updates (non Array DML API) you have the additional
overhead of network calls for each plus indexes and stuff are updated during
each execution. Array API's do 1 network call and usually do a bulk update of
the indexes (not all DB's do this optimization).

So here is basically what you are doing (employeeID of 2, Status of 'Good',
RecordCount of 5

Array # SQL as executed on the Server
1 UPDATE CUSTOMERS
SET cur_status = 'Good', employees_id = 2
WHERE company_id IN (SELECT company_id FROM CUSTOMERS
ORDER BY company_id ASC
LIMIT 5)
2 UPDATE CUSTOMERS
SET cur_status = 'Good', employees_id = 2
WHERE company_id IN (SELECT company_id FROM CUSTOMERS
ORDER BY company_id ASC
LIMIT 5)
3 UPDATE CUSTOMERS
SET cur_status = 'Good', employees_id = 2
WHERE company_id IN (SELECT company_id FROM CUSTOMERS
ORDER BY company_id ASC
LIMIT 5)
4 UPDATE CUSTOMERS
SET cur_status = 'Good', employees_id = 2
WHERE company_id IN (SELECT company_id FROM CUSTOMERS
ORDER BY company_id ASC
LIMIT 5)
5 UPDATE CUSTOMERS
SET cur_status = 'Good', employees_id = 2
WHERE company_id IN (SELECT company_id FROM CUSTOMERS
ORDER BY company_id ASC
LIMIT 5)

That array is sent to the server (SQL + params array data) and then all 5 are
executed but there is no impact on 2-5 so that is just a waste of time.
Everything that would be changed was changed by the very first SQL in the array
(note params are not macro expansions, but their values being sent will be the
same so this is just an example of what the server will execute)

So just using the non Array version has the same impact but much faster as it is
doing 80% less work.

CustomersAssignUpdater->SQL->Text=
"UPDATE CUSTOMERS \
       SET cur_status = :cur_status, employees_id = :employees_id \
       WHERE company_id IN \
           (SELECT company_id FROM CUSTOMERS \
            ORDER BY company_id ASC \
             LIMIT " + IntToStr( recordCount ) + " )";
 
CustomersAssignUpdater->ParamByName("employees_id")->AsInteger = employeeID;
     CustomersAssignUpdater->ParamByName("cur_status")->AsString = theStatus;
 
CustomersAssignUpdater->ExecuteSQL;

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02