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

Question

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());

What exactly are you trying to achieve? In what context do you want use the query? Why not use X++ query syntax or an AOT query? In general you do rarely need to use direct SQL. — Jan B. Kjeldsen, Jun 23 '21 at 19:33
I'm just trying to learn the platform, what I can do, how I can express things, what causes errors and warnings, how to clean them up, etc. What I am exactly trying to achieve is to remove the warning I would get from `statement.executeQuery(sqlStatement);` — Brian Kessler, Jun 23 '21 at 23:13
@BrianKessler - your situation sounds pretty typical, where I'd guess you're a sharp developer, very familiar with SQL, new to AX/D3FO, but the way D3FO performs some things seems absurd to you, but there are legitimate technical and business reasons they're done that way that will eventually make sense. Far too many things for me to type in a comment. You should just throw away the code you've written above and do a proper `x++` query or use an `AOT Query` object as Jan said. An `x++` query would be the simplest for you to learn. — Alex Kwitny, Jun 24 '21 at 00:56
@AlexKwitny, actually most of my experience is with Salesforce, but I'm fed up with the anti-intellectual cult surrounding that platform so looking to find a different niche. I haven't gotten that deep into D3FO where I would consider many things absurd yet (actually, I think, even when frustrating, it makes a lot of sense). Right now, I'm just trying to learn what tools re are my disposal. While technically it would not be an accurate answer to the question, it would be appreciated if you'd demonstrate how to do the above as a proper "x++ query" and/or an "AOT Query". — Brian Kessler, Jun 24 '21 at 07:30

score 2 · Answer 1 · answered Oct 28 '22 at 23:22

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);

score 1 · Answer 2 · answered Jun 24 '21 at 18:00

1

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.

answered Jun 24 '21 at 18:00

Alex Kwitny

11,211
2
49
71

What I'm doing is just learning in the best way I know how, but I'm open to suggestions. :-) Cheers for the information. It's interesting for me that there is no special name for using such a DSL. Salesforce Apex has something similar but they call it "SOQL", though this may have something to do with SOQL queries having relevance even outside the Apex context (i.e. when using various APIs). Not sure what you mean by the table supporting inheritance, but I'll follow up on the links. (Tables seem a lot like Salesforce SObjects in some ways, but more abstract and more flexible in others.) – Brian Kessler Jun 25 '21 at 08:20
1

@BrianKessler regarding inheritance, think similar to class inheritance. A few tables to look at (from memory), look at `DirPartyTable`, `DirPerson`, `DirOrganizationBase`, `DirOrganization` and specifically look at some `RecId`s. – Alex Kwitny Jun 25 '21 at 15:30
So, essentially the base table has a bunch of fields (and maybe some methods) and each level of inheritance can add on some more? Are they stored in the same place (so if we select the base, we will also select instances of extension? If so, can we distinguish in context which record types we've selected? Can this result in gotcha situations where you are working with the wrong data type? I guess, as with class inheritance, it can result in unexpected behaviours if you don't know everything being inherited (which is why composition should be preferred....) – Brian Kessler Jun 25 '21 at 16:17
1

@BrianKessler take a look at https://learn.microsoft.com/en-us/dynamicsax-2012/developer/table-inheritance-overview . Your question is too nuanced for a succinct answer, but often it's simpler to just play around with the tables for a better understanding. There are *not* many tables using inheritance though, so my concern regarding your question about general AX/D3FO querying was that you just happen to be using a rare table and it was an added variable to complicate your `x++` query learning. – Alex Kwitny Jun 25 '21 at 17:18

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

2 Answers2