What’s new in Set based operations in Dynamics Ax 2009
In this blog post we will provide some details on some of the enhancements that were added to the various set based operations in Dynamics Ax 2009.
X++ language contains three operators for set based data manipulation:
1. INSERT_RECORDSET
2. UPDATE_RECORDSET
3. DELETE_FROM
These operators have been significantly enhanced in Dynamics Ax 2009. This blog entry will focus on the new features in these operators.
What’s new in Ax 2009
UPDATE_RECORDSET supports inner/outer join
In previous version of Dynamics Ax, UPDATE_RECORDSET supported single table set based operations as well as exists and not exists join.
In Ax 2009, UPDATE_RECORDSET has been enhanced to support inner and outer joins. The following is an example illustrating the enhancement.
static void UpdateTotals(Args _args)
{
NewTaxes taxTable;
Orders ordersTable;
;
ttsbegin;
UPDATE_RECORDSET ordersTable
SETTING Total = ordersTable.Total + ( ordersTable.Total * ( taxTable.TaxPercent / 100 ) )
JOIN taxTable
WHERE ordersTable.CountryId == taxTable.CountryId;
ttscommit;
}
The above example updates the grand total in the orders table with grand total plus tax amount by joining to the tax table on the country code.
INSERT_RECORDSET supports literals
Literal support for INSERT_RECORDSET is one of the most requested features from our customers. This feature comes in handy especially in the case of upgrade. The following is an example illustrating the enhancements.
static void UpgradeCustomersAndVendors(Args _args)
{
Cust customers;
Vend vendors;
Person personTable;
TypeOfPerson literal;
;
literal = TypeOfPerson::Customer;
ttsbegin;
INSERT_RECORDSET personTable ( Name, Type )
SELECT Name, literal FROM customers ;
ttscommit;
literal = TypeOfPerson::Vendor;
ttsbegin;
INSERT_RECORDSET personTable ( Name, Type )
SELECT Name, literal FROM Vendors ;
ttscommit;
}
The above example inserts data into the person table from customer table and vendor table and stamps each record with a literal value of person type.
RowCount
The application can now get the row count of all the set based operations. The following is an extension of the above UPDATE_RECORDSET example.
static void UpdateTotals(Args _args)
{
NewTaxes taxTable;
Orders ordersTable;
;
ttsbegin;
UPDATE_RECORDSET ordersTable
SETTING Total = ordersTable.Total + ( ordersTable.Total * ( taxTable.TaxPercent / 100 ) )
JOIN taxTable
WHERE ordersTable.CountryId == taxTable.CountryId;
if ( ordersTable.RowCount() > 0 )
{
info ( strfmt (' Number of rows updated %1', ordersTable.RowCount() ) );
}
else
{
info ( 'Tax rates not found. Please populated valid tax rates');
}
ttscommit;
}
When does set based operation turn into row by row operation
In Ax 4, the following will cause the set based operation to downgrade to row by row operation:
1. Table is entire-table cached.
2. Delete/Update/Insert methods, aosValidate* methods are overridden on the target table.
3. Alerts have been setup to be triggered.
4. Database log has been setup.
This list has grown in Ax 2009. The list now includes:
1. Record Level security being enabled. Details are presented below in a separate section.
2. Any of the tables participating in the joins or target or source table is temporary/configuration key being disabled.
3. When assignment operator for INSERT_RECORDSET or UPDATE_RECORDSET contains date time manipulation operations such as adding or subtracting from date time column.
4. AosValidate* methods are overridden on the joined tables. AosValidation has been extended to include joined tables since UPDATE_RECORDSET now supports complex joins.
Set based operations and Record Level Security
Record Level Security (RLS) is appended as a where predicate to all the SELECT statements if RLS is enabled for a given user group, company and the table.
If RLS is not enabled through code, RLS is not applied.
<table>. recordLevelSecurity(<true/false>)
For DELETE_FROM, RLS is always applied as a where predicate. So, enabling RLS does not downgraded DELETE_FROM operation.
For UPDATE_RECORDSET, if RLS is applied on source table or join tables, the operation is not downgraded to row by row operation. But if RLS is enabled on target table, then the set based operation is downgraded to row by row operation so that security can be validated for each row being inserted or updated.
Thanks,
Gandhi Swaminathan