Direct Database Updates with PXDatabase

As developers, we often hear, “Don’t update the database directly.” Acumatica uses several layers of data management, and we are trained to work with views to enable all of the integrity checks and event handlers. For more than 2-1/2 years, I’ve subscribed to that guidance and never realized how often Acumatica breaks that rule in the Code Repository. Recent work on Physical Inventory opened my eyes to Acumatica’s use of PXDatabase to bypass all of the protections and event handlers that I’ve come to rely on. In all honesty, it caught me by such surprise that I assumed at first that it must be some legacy code that was never updated. Upon further research, I learned a lot more than I expected.

Let’s take a look at the code in INCountEntry.cs shown below. In this use case, the database is directly updated in the INPIDetail_RowUpdating event handler. More on that later.

using (PXTransactionScope sc = new PXTransactionScope())
{
	if (!PXDatabase.Update<INPIDetail>(
		new PXDataFieldAssign(typeof(INPIDetail.status).Name,
			PXDbType.VarChar, d.Status),
		new PXDataFieldAssign(typeof(INPIDetail.physicalQty).Name,
			PXDbType.Decimal, d.PhysicalQty),
		new PXDataFieldAssign(typeof(INPIDetail.varQty).Name,
			PXDbType.Decimal, d.VarQty),
		new PXDataFieldRestrict(typeof(INPIDetail.pIID).Name,
			PXDbType.VarChar, d.PIID),
		new PXDataFieldRestrict(typeof(INPIDetail.lineNbr).Name,
			PXDbType.Int, d.LineNbr),
		new PXDataFieldRestrict(typeof(INPIDetail.Tstamp).Name,
			PXDbType.Timestamp, 8, this.TimeStamp, PXComp.LE)))
	{
		throw new PXException(ErrorMessages.RecordUpdatedByAnotherProcess,
			typeof (INPIDetail).Name);
	}

	PXDatabase.Update<INPIHeader>(
		new PXDataFieldAssign(typeof(INPIHeader.totalPhysicalQty).Name,
			PXDbType.Decimal, (d.PhysicalQty ?? 0) - (o.PhysicalQty ?? 0))
		{
			Behavior = PXDataFieldAssign.AssignBehavior.Summarize
		},
		new PXDataFieldAssign(typeof(INPIHeader.totalVarQty).Name,
			PXDbType.Decimal, (d.VarQty ?? 0) - (o.VarQty ?? 0))
		{
			Behavior = PXDataFieldAssign.AssignBehavior.Summarize
		},
		new PXDataFieldRestrict(typeof(INPIHeader.pIID).Name,
			PXDbType.VarChar, d.PIID));

	sc.Complete();
}

Before getting into some of the depths of this code, let’s focus on some basic structure first.

Notice that this code starts with a transaction scope. We want to ensure data integrity, so if one database update fails, it all needs to fail. Remember to complete the transaction at the end of the block.
PXDatabase contains the method Update that will return a boolean to indicate if the update was successful. If the update fails, we want to throw an exception and skip the update to the header.
Fields are updated via PXDataFieldAssign, and the selection of records to update is controlled by PXDataFieldRestrict.
Fields can be updated to set the value (default) or to summarize by adding the value provided to the current value of the field. It is important to note that a field value of null cannot be summarized as null + anything results in the field remaining null.

As you can see, each field is identified by the column name, the type of database field, and the value to be inserted. The addition of Behavior = PXDataFieldAssign.AssignBehavior.Summarize adjusts the behavior to add (summarize) the value instead of setting to the value.

Also, note the special use for timestamp. To be honest, I have not looked into the usage as clearly the special field simply should be handled as shown. In the case of the Count Entry graph, a variable is used to hold a TimeStamp value, so that variable is what has been passed in for setting the value. As you surely recall, timestamp is a special field for managing whether the record has been updated and therefore requires an error to the user to ensure data integrity.

When making direct database updates, one must determine if it is necessary to disable the page protection that requires saving or confirming discard by masking the dirty cache. In the PI Count Entry graph, we have the following to use as an example.

protected virtual void INPIDetail_RowUpdated(PXCache sender, PXRowUpdatedEventArgs e)
{
	sender.SetStatus(e.Row, PXEntryStatus.Notchanged);
	sender.IsDirty = false;
	PIDetail.View.RequestRefresh();
	this.SelectTimeStamp();
}

This very simple event handler fires after RowUpdating, which means the manual database update has been completed already. Since the database is now updated, the updated event handler is used to:

Set the status of the cache to Not Changed.
Set the IsDirty flag to false on the cache to tell the UI that the cache does not need to be flushed to the database.
Refresh the cache (and view) with the data that was updated in the database.
In this case, set the TimeStamp variable stored in the graph. This is not necessary in all cases, but it is needed in this use case.

Now that we see HOW the database is updated manually, let’s consider WHY we would take this approach. For more insight, I reached out to some of the Acumatica Developer MVP’s. I’d like to offer a special thanks to the following MVP’s and Acumatica staff who provided much of the insight below: Hughes Beausejour, Brendan Hennelly, Joe Jacob, and Joshua van Hoesen.

Reasons to use direct database updates:

Simply, performance
Generic Inquiries
Avoid multi-threaded conflicts (Another process has modified…)
Prevent unwanted side effects of event handlers and DAC attributes
Convenience, such as when adding functionality might require a seemingly unnecessary rewrite to logic already implemented (be VERY cautious about this approach as it may work in the short term and bite you in later enhancements)
When database changes need to be self-contained, and event handlers might result in undesired influence on the data.
Bulk update of records for upgrades and customizations. For instance, updating 500 records may require 500 update statements, but a single update statement using PXDatabase.Update could update them all at once.
While rare to do, updating fields marked as IsKey. This cannot be done in a view.

Important notes, also from the Acumatica staff and Developer MVP’s:

PXDatabase bypasses all the “freebies” that a developer normally takes for granted, such as PXDefault, Caching, and Event Handlers.
Since PXDatabase does not use the Cache collections, a conflict occurs when there is an uncommitted and conflicting data in the cache.
When using PXDatabase, Business Events ARE NOT TRIGGERED as they would when using PXGraph. This is a big one as some Acumatica customers leverage business events to automate business processes.
PXDatabase is used often in Persist method overrides and RowPersisted event handlers. Ideally, you would want to avoid saving inside the save method, but PXDatabase makes it possible when necessary (such as updating data in some external table) because it won’t generate Persist events which would create an infinite loop.

Again, I would like to thank Hughes Beausejour, Brendan Hennelly, Joe Jacob, and Joshua van Hoesen for their insight above.

To wrap this up, let’s consider why specifically this approach was taken in the PI Count Entry graph. As I have been involved in physical inventories with our company for 20 years, I can assure you that the business process makes this approach a necessary business requirement.

To understand why, consider that typically one person at a time generates the counts and reviews the results. Therefore, these screens can use the typical views-based approach to handling data. However, counts may be entered by 1 or 20 people at the same time. The nature of an Acumatica screen drives that the count would be summarized in the header, and each count would be entered in the grid detail of the screen. In the standard view approach, this means EVERY count makes the screen’s caches dirty. When a 2nd person tries to enter a count at the same time, the 1st person to save wins and the 2nd person gets the error that the record was updated already. This means that all the counts not yet saved will be lost.

To overcome the “1 person at a time” limitation, Acumatica created a count entry screen that manages data 1 grid line at a time. All detail lines of the count are included in the screen, but the RowUpdating event is used to capture the entered data and push it into the database immediately. The RowUpdated event of the same DAC is used to circumvent the dirty cache status and refresh the screen view. The result is that the cache is not left dirty, and a second user entering the line will override the value entered by the 1st user rather than being blocked. Likewise, entry for another detail line will not conflict at all. The behavior seems to create about a half second delay (for the updates to process and the view to refresh) after an entry before the user can safely key the next count. This is only a minor issue if counts were performed on paper and entered continuously in the Count Entry screen. Even then, this slight pause is easily adopted by the end user and encourages user confirmation of the values entered. Historically, I would key an entire sheet and go back to verify, but this is an easy behavior for me to adjust to accommodate the system.

For more examples of PXDatabase.Update and other cool methods of PXDatabase, refer to the Acumatica Code Repository.

Happy coding!

Brian’s Blog

Direct Database Updates with PXDatabase

Leave a Reply Cancel reply