One of the major problems we faced when we embarked on the extensibility journey, was that X++ select statements are not extensible. If someone wants to add a new range, a join, a field in the field list – it is not possible. So far our strategy has been to convert these statements to good-old Queries. This works fine in most cases, but there are short comings:
Queries are somewhat slower
Even when the resulting TSQL is the same, queries need more compute to prepare the TSQL. The execution time in SQL server is of course the same (which often overshadows the overhead).
This is the reason we avoid converting select statements to queries in performance critical paths.
- Queries have a limited set of capabilities.
For example; delete_from is not supported. Update_recordset and insert_recordset are partly supported – via some arcane static methods on SysQuery.
Welcome the new member of the family: SysDa
“Da” is short for Data access. It is a set of new APIs exposing the object graph that the compiler otherwise produces from select statements. As such it has full fidelity – everything you can do in select statements (including delete_from, insert_recordset and update_recordset) is possible.
…and it has the same performance characteristics as select statements.
…and it is extensible.
…and it is available in PU22.
…and here are some examples:
This enables a lot of desired abilities – where the only option previously was macros, such as reuse of field lists, where clauses, joins, etc. For example, we are now considering replacing the InventSum* macros with an extensible alternative.
Pros:
1. It has the same performance characteristics as select statements
2. It is extensible for futher enhancement
3. It is available in from PU22
List:
- Select: SysDaQueryObject, SysDaSearchObject, and SysDaSearchStatement
- Update: SysDaUpdateObject and SysDaUpdateStatement
- Insert: SysDaInsertObject and SysDaInsertStatement
- Delete: SysDaQueryObject, SysDaDeleteObject, and SysDaDeleteStatement
CRUD Operation:
Select:
//Selection - On SYSDA API
VendTable vendTab;
var qe = new SysDaQueryObject(vendTab);
qe.projection()
.add(fieldStr(VendTable, AccountNum))
.add(fieldStr(VendTable, VendGroup));
qe.WhereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(vendTab, fieldStr(VendTable, VendGroup)),
new SysDaValueExpression(10)));
//below statement to get less than or equal to field values
//qe.WhereClause(new SysDaLessThanOrEqualsExpression(
// new SysDaFieldExpression(vendTab, fieldStr(VendTable, VendGroup)),
// new SysDaValueExpression(10)));
//Default order by field is ASC
qe.OrderByClause().addDescending(fieldStr(VendTable, AccountNum)); //for desc
//Type 1
var so = new SysDaSearchObject(qe);
var ss = new SysDaSearchStatement();
while (ss.nextRecord(so))
{
info(vendTab.AccountNum);
}
//Type 2
var fo = new SysDaFindObject(qe);
new SysDaFindStatement().execute(fo);
//Type 3 Using join
PurchLine purchLine;
InventDim Invdim;
var qepl = new SysDaQueryObject(purchLine);
var qedim = new SysDaQueryObject(Invdim);
qepl.joinClause(SysDaJoinKind::InnerJoin,qedim);
//Relation manual
qedim.WhereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(purchLine, fieldStr(PurchLine, InventDimId)),
new SysDaFieldExpression(Invdim, fieldStr(InventDim, InventDimId))));
qepl.WhereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(purchLine, fieldStr(PurchLine, VendAccount)),
new SysDaValueExpression("000013")));
var so1 = new SysDaSearchObject(qepl);
var ss1 = new SysDaSearchStatement();
while (ss1.nextRecord(so1))
{
info(purchLine.ItemId + purchLine.InventDimId);
}
Update:
// Update - ON SysDa API
PurchTable purchTable;
var updateObj = new SysDaUpdateObject(purchTable);
updateObj.settingClause()
.add(fieldStr(PurchTable, PurchName), new SysDaValueExpression( "SAN"));
updateObj.whereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(purchTable, fieldStr(PurchTable, OrderAccount)),
new SysDaValueExpression("000013")));
//Updating the rows.
ttsbegin;
new SysDaUpdateStatement().execute(updateObj);
ttscommit;
Insert:
// Insert - ON SysDa API
sanLanguageTable santab;
var Io = new SysDaInsertObject(santab);
Io.fields()
.add(fieldStr(sanLanguageTable, Name))
.add(fieldStr(sanLanguageTable, Description));
VendGroup source;
var qe = new SysDaQueryObject(source);
var s1 = qe.projection()
.Add(fieldStr(VendGroup, Name))
.Add(fieldStr(VendGroup, VendGroup));
Io.query(qe);
var istate = new SysDaInsertStatement();
ttsbegin;
istate.executeQuery(Io);
ttscommit;
Delete:
// Delete - ON SysDa API
sanLanguageTable santab;
var qe = new SysDaQueryObject(santab);
var s = qe.projection()
.add(fieldStr(sanLanguageTable, Name));
var ds = new SysDaDeleteStatement();
var delobj = new SysDaDeleteObject(qe);
ttsbegin;
ds.executeQuery(delobj);
ttscommit;
//To get no of rrows after deletion
info("Number of rows after deletion: " + any2Str(t.RowCount()));
Important:
You can use the toString() method on SysDaQueryObject, SysDaUpdateObject, SysDaInsertObject, and SysDaQueryObject objects to view the statement that you're building.
Related Objects: (SysDa API)
Base Enum:
SysDaAggregateFieldType
SysDaFirstOnlyHint
SysDaJoinKind
Class:
SysDaAggregateProjectionField
SysDaAndExpression
SysDaAvgOfField
SysDaBinaryExpression
SysDaCountOfField
SysDaCrossCompany
SysDaCrossCompanyAll
SysDaCrossCompanyContainer
SysDaDataAccessStatement
SysDaDeleteObject
SysDaDeleteStatement
SysDaDivideExpression
SysDaEqualsExpression
SysDaFieldExpression
SysDaFindStatement
SysDaGreaterThanExpression
SysDaGreaterThanOrEqualsExpression
SysDaGroupBys
SysDaInsertObject
SysDaInsertStatement
SysDaIntDivExpression
SysDaLessThanExpression
SysDaLessThanOrEqualsExpression
SysDaLikeExpression
SysDaMaxOfField
SysDaMinOfField
SysDaMinusExpression
SysDaModExpression
SysDaMultiplyExpression
SysDaNotEqualsExpression
SysDaOrderBys
SysDaOrExpression
SysDaPlusExpression
SysDaProjectionField
SysDaQueryExpression
SysDaQueryObject
SysDaSearchObject
SysDaSearchStatement
SysDaSelection
SysDaSettingsList
SysDaSumOfField
SysDaUpdateObject
SysDaUpdateStatement
SysDaValueExpression
SysDaValueField
Comments
Post a Comment