CODEWITHSUNDEEP
sqlselectaxaptax++dynamics-365-operations
Brian Kessler
Brian Kessler

Reputation: 2327

How can use executeQueryWithParameters with SQLBuilderSelectExpression to join an x++/sql statement in Microsoft Dynamics?

In Dynamics 365 for Finance and Operations, they describe a method of creating SQL statements "as objects, as opposed to text", but this is somewhat of a lie. They use the objects to create the text which then populates str sqlStatement = selectExpr.getExpression(null);

This sqlStatement would then feed the obsolete statement.executeQuery(sqlStatement);.

I can make the warning go away by using executeQueryWithParameters() with an empty map (SqlParams::create()) as the second parameter, but this seems to be "cheating".

Is there a way I can/should refactor the following to populate the map correctly?

        SQLBuilderSelectExpression selectExpression = SQLBuilderSelectExpression::construct();
        selectExpression.parmUseJoin(true);
        SQLBuilderTableEntry vendTable = selectExpression.addTableId(tableNum(VendTable));
        SQLBuilderTableEntry dirPartyTable = vendTable.addJoinTableId(tableNum(DirPartyTable));
        SQLBuilderFieldEntry accountNum = vendTable.addFieldId(fieldNum(VendTable, AccountNum));
        SQLBuilderFieldEntry name = dirPartyTable.addFieldId(fieldNum(DirPartyTable, Name));
        SQLBuilderFieldEntry dataAreaId = vendTable.addFieldId(fieldNum(VendTable, dataAreaId));
        SQLBuilderFieldEntry blocked = vendTable.addFieldId(fieldNum(VendTable, Blocked));
        vendTable.addRange(dataAreaId, curext());
        vendTable.addRange(blocked, CustVendorBlocked::No);

        selectExpression.addSelectFieldEntry(SQLBuilderSelectFieldEntry::newExpression(accountNum, 'AccountNum'));
        selectExpression.addSelectFieldEntry(SQLBuilderSelectFieldEntry::newExpression(name, 'Name'));
        str sqlStatement = selectExpression.getExpression(null);

        // FIXME:
        ResultSet resultSet = statement.executeQueryWithParameters(sqlStatement, SqlParams::create());

Upvotes: 1

Views: 1978

Answers (2)

Leckie G.
Leckie G.

Reputation: 21

Regardless of what OP is trying to do with the query, the answer to the question of "how do I correctly replace executeQuery with executeQueryWithParameters" can be found in the following article.

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-ref/query-with-parameters

The new *WithParameters APIs were introduced as a way to mitigate sql injection attacks which may occur when building up sql strings manually with un-sanitized sql parameters as input.

Snippet of the code example from above doc shows how to correctly populate the map to match the sql statement:

    str sql = @"
        UPDATE Wages
        SET Wages.Wage = Wages.Wage * @percent
        WHERE Wages.Level = @Level";

    Map paramMap = SqlParams::create();
    paramMap.add('percent', 1.1);        // 10 percent increase
    paramMap.add('Level', 'Manager');    // Management increase

    int cnt = statement.executeUpdateWithParameters(sql, paramMap);

Upvotes: 2

Alex Kwitny
Alex Kwitny

Reputation: 11564

Below is how you would write your code as a standard X++ query. However, I must note that what you're doing may not be the best approach.

DirPartyTable is a special table in AX as it supports inheritance, so you should make sure you fully understand the framework. See:

Code:

VendTable               vendTable;
DirPartyTable           dirPartyTable;

while select AccountNum from vendTable
    where vendTable.Blocked             == CustVendorBlocked::No
        // DataAreaId along with Partition, are automatically included in the query context depending
        // on the company context you're executing the code from
        // && vendTable.dataAreaId         == curext()
join Name from dirPartyTable
    where dirPartyTable.RecId           == vendTable.Party
{        
    info(strFmt("Account: %1; Name: %2", vendTable.AccountNum, dirPartyTable.Name));
}

Regarding an AOT query, look in the AOT at \Queries\VendTableListPage and expand the data sources and learn from it.

Upvotes: 1

Related Questions