Brewsterware

January 8, 2022

A look at the SysDa query API

Filed under: 365 for Finance and Operations — Joe Brewer @ 12:31 pm

A new query API was introduced in PU22 which enables a database query to be generated and executed through a lightweight framework. This post is not meant to be a comprehensive analysis of all functionality that this framework provides, rather a look at some of the functionality in order to encourage you to look deeper into the code to help you understand what it can and cannot do.

The official Microsoft documentation can be found here: Access data by using the SysDa classes – Finance & Operations | Dynamics 365 | Microsoft Docs. Of course the best documentation is the code itself and I would recommend using the “find references” and metadata search functionality to find examples and check for any best practice violations when writing your own code as there have been subtle changes changes to the API since it has been released.

The API, which is nicknamed SysDa, is reportedly faster at generating TSQL than the current Query api and also supports set based operations. Set based deletes using multiple are possible with this API which is particularly useful since the new “in” keyword that was introduced a while ago only currently supports enums.  

Microsoft are looking to replace select statements with this new api since it will make queries easier to extend – macros defining field lists or joins to inventDim will become a thing of the past. 

Here is a brief analysis of the API: 

There are five statement classes for finding, searching, updating, deleting and inserting data that extend from the class SysDaDataAccessStatement. These are used to execute SysDa*Object classes that contain queries. 

SysDaFindStatement – the find() method takes a parameter of an instance of the class SysDaFindObject. Here is a quick example: 

class SysDaFindExample
{
    public static void main(Args _args)
    {
        CustTable custTable;
        SysDaFindObject sysDaFind;
        SysDaQueryObject sysDaQuery;
        SysDaFindStatement sysDaFindStatement;
        CustAccount accountNumber = "34318396";

        sysDaQuery = new SysDaQueryObject(custTable);

        sysDaQuery.firstOnlyHint = true;

        sysDaQuery.whereClause(
            new SysDaEqualsExpression(
                new SysDaFieldExpression(custTable, fieldStr(CustTable, AccountNum)),
                new SysDaValueExpression(queryValue(accountNumber))));

        sysDaFind = new SysDaFindObject(sysDaQuery);

        info(sysDaFind.toString());

        sysDaFindStatement = new SysDaFindStatement();
		sysDaFindStatement.find(sysDaFind);

        if (custTable)
        {
            info('found the customer!');
        }
    }
}

Note that after find() method is executed on line 25, the CustTable buffer that the SysDaQueryObject is initialised with, is populated with the result of the query. Also the toString() method on the SysDaFindObject class, shown on line 22 is useful for viewing the sql statement for debugging purposes.

SysDaSearchStatement – this works in a similar way to QueryRun and is used to loop over a query result to return records. The nextRecord() method takes a parameter of an instance of the class SysDaSearchObject. The SysDaSearchObject class contains the query that SysDaSearchStatement uses – it is a bit like the Query class that we are all currently aware of.

Aggregate classes – all extend from SysDaAggregateProjectionField and SysDaProjectionField

  • SysDaAvgOfField
  • SysDaCountOfField
  • SysDaSumOfField
  • SysDaMaxOfField
  • SysDaMinOfField

These classes are used by the SysDaSelection class which is used for adding a field list to a query. Here is a simple example (note the add() and addCount() methods):

class SysDaFieldListExample 
{
	public static void main(Args _args)
	{
		CustTable custTable;
		SysDaFindObject sysDaFind;
        SysDaQueryObject sysDaQuery;
        SysDaFindStatement sysDaFindStatement;
		
		sysDaQuery = new SysDaQueryObject(custTable);
		
		sysDaQuery.firstOnlyHint = true;
		
		sysDaQuery.projection().add(fieldStr(CustTable, CustGroup))
								.addCount(fieldStr(CustTable, RecId));
		
		sysDaQuery.groupByClause(new SysDaGroupBys().add(fieldStr(CustTable, CustGroup)));
		
		sysDaFind = new SysDaFindObject(sysDaQuery);
		
		info(sysDaFind.toString());
		
		sysDaFindStatement = new SysDaFindStatement();
		sysDaFindStatement.execute(sysDaFind);
	}
}

These two classes add group by and order by clauses to the SysDaQueryObject class: 

SysDaGroupBys
SysDaOrderBys

Expression classes – all extend from SysDaBinaryExpression and SysDaQueryExpression 

The two expression classes below can be used to combine query expressions:

SysDaAndExpression
SysDaOrExpression 

The following expression classes work a bit like QueryBuildRange to filter data. These classes are all child classes of SysDaBinaryExpression. They all take two parameters – a field class instance and a value class instance: 

SysDaLessThanExpression
SysDaLessThanOrEqualsExpression
SysDaLikeExpression
SysDaNotEqualsExpression
SysDaEqualsExpression
SysDaGreaterThanExpression
SysDaGreaterThanOrEqualsExpression 

The following classes can be used as a parameters for the above classes to provide a field and a value 

SysDaFieldExpression – this class is used a bit like the fieldNum() method
SysDaValueExpression – this class is used a bit like the queryValue() method 

There are six other expression classes which allow calculations to be done in the where clause. Since these are children of the class SysDaBinaryExpression, they can be nested.

SysDaIntDivExpression
SysDaDivideExpression
SysDaPlusExpression
SysDaMinusExpression
SysDaModExpression
SysDaMultiplyExpression 

Here is an example:

class SysDaExpressionExample 
{        
    public static void main(Args _args) 
	{        
        SalesLine salesLine; 
        SysDaSearchObject sysDaSearch; 
        SysDaQueryObject sysDaQuery; 
        SysDaSearchStatement sysDaSearchStatement; 
  
        sysDaQuery = new SysDaQueryObject(salesLine); 
        
        sysDaQuery.firstOnlyHint = SysDaFirstOnlyHint::FirstOnly10; 
  
        sysDaQuery.projection().add(fieldStr(SalesLine, SalesId)) 
                            	.add(fieldStr(SalesLine, LineNum)); 
  
        sysDaQuery.orderByClause(new SysDaOrderBys().addDescending(fieldStr(SalesLine, SalesId))); 
  
        sysDaQuery.whereClause( 
            new SysDaEqualsExpression( 
                new SysDaMultiplyExpression( 
                    new SysDaFieldExpression(salesLine, fieldStr(SalesLine, SalesQty)), 
                    new SysDaFieldExpression(salesLine, fieldStr(SalesLine, SalesPrice)) 
            	), 
                new SysDaFieldExpression(salesLine, fieldStr(SalesLine, LineAmount)) 
        	)); 
  
        sysDaSearch = new SysDaSearchObject(sysDaQuery); 
  
    	info(sysDaSearch.toString()); 
  
        sysDaSearchStatement = new SysDaSearchStatement(); 
    
        while (sysDaSearchStatement.nextRecord(sysDaSearch)) 
    	{ 
        	info(strFmt("%1 %2", salesLine.SalesId, salesLine.LineNum)); 
    	} 
	} 
} 

Note that if you wanted to search over specific companies or all companies, you can use the following syntax after line 28 above:

sysDaSearch.crossCompany(new SysDaCrossCompanyContainer(['USMF', 'DAT']));
sysDaSearch.crossCompany(new SysDaCrossCompanyAll());

SysDaDeleteStatement – the delete() method takes a parameter of an instance of the class SysDaDeleteObject. One of the advantages of using this over the sql syntax that X++ offers is that it is possible to supply a collection of keys to an expression. The “in” keyword can do this, however this currently works for enums and not strings or numeric data. Here is an example:

class SysDaDeleteExample
{
    public static void main(Args _args)
    {
        Set recIds = new Set(Types::Int64);

        recIds.add(1);
        recIds.add(2);
        recIds.add(3);
        recIds.add(4);

        SysDaDeleteExample::deleteRecords(tableNum(CustTable), fieldStr(CustTable, RecId), recIds);
    }

    public static void deleteRecords(TableId _tableId, FieldName _keyField, Set _keys, int _expressionLimit = 100)
    {
        DictTable dictTable;
        Common record;
        SysDaQueryObject query;
        SysDaQueryExpression expression;
        SysDaDeleteObject deleteObject;
        SysDaDeleteStatement statement;
        SetIterator iterator;
		int counter;

        dictTable = new DictTable(_tableId);
        record = dictTable.makeRecord();

        query = new SysDaQueryObject(record);
		statement = new SysDaDeleteStatement();

        iterator = new SetIterator(_keys);
		counter = _expressionLimit;

        while (iterator.more())
        {
            if (!expression)
            {
                expression = new SysDaEqualsExpression(
                    new SysDaFieldExpression(record, _keyField),
                    new SysDaValueExpression(iterator.value()));
            }
            else
            {
                expression = expression.or(new SysDaEqualsExpression(
                    new SysDaFieldExpression(record, _keyField),
                    new SysDaValueExpression(iterator.value())));
            }
			
			counter -= 1;
			
			if (counter <= 0)
			{
				query.whereClause(expression);
			
				deleteObject = new SysDaDeleteObject(query);
				statement.delete(deleteObject);
		
				expression = null;
				counter = _expressionLimit;
			}

            iterator.next();
        } 

        if (expression)
        {
            query.whereClause(expression);
			
			deleteObject = new SysDaDeleteObject(query);
			statement.delete(deleteObject);
        }
    }
}

No Comments

No comments yet.

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress