How can I get the syntax correct when using executeQueryWithParameters to join an x++/sql statement in Microsoft Dynamics?

Question

I'm currently looking at the example for "Executing a direct SQL statement" in Dynamics 365 for Finance and Operations Development Cookbook - Fourth Edition.

In the text, the following is used to populatestr sqlStatement:

 sqlStatement = 'SELECT %1, %2 FROM %3 ' + 
          'JOIN %4 ON %3.%5 = %4.%6 ' + 
          'WHERE %7 = %9 AND %8 = %10'; 
 
          sqlStatement = strFmt( 
           sqlStatement, 
           fldAccountNum.name(DbBackend::Sql), 
           fldName.name(DbBackend::Sql), 
           tblVendTable.name(DbBackend::Sql), 
           tblDirPartyTable.name(DbBackend::Sql), 
           fldParty.name(DbBackend::Sql), 
           fldRecId.name(DbBackend::Sql), 
           fldDataAreaId.name(DbBackend::Sql), 
           fldBlocked.name(DbBackend::Sql), 
           sqlSystem.sqlLiteral(curext(), true), 
           sqlSystem.sqlLiteral(CustVendorBlocked::No, true)); 

which gets executed with

resultSet      = statement.executeQuery(sqlStatement);

However, Visual Studio warns me this is obsolete, and so I'd like to replace it using statement.executeQueryWithParameters(sqlStatement, valueByKeyMap);

... of course, to do this, I need to create the map, which seems to be less straightforward than I anticipated.

Currently I've assigned this:

str sqlStatement = @'SELECT @accountNumberField, @nameField' +
                ' FROM @vendTable v' +
                ' JOIN @partyTable p' +
                '      ON (@vendTable.@partyField = @partyTable.@recId)' +
                ' WHERE (@dataAreaIdField = @dataArea)' +
                '      AND (@blockedField = @notBlocked)';

and then populated the Map like:

        Map valueByKeyMap = SqlParams::create();

        DictField accountNumberField = new DictField(tableNum(VendTable), fieldNum(VendTable, AccountNum));
        valueByKeyMap.add('accountNumberField', accountNumberField.name(DbBackend::Sql));

        DictField nameField = new DictField(tableNum(DirPartyTable), fieldNum(DirPartyTable, Name));
        valueByKeyMap.add('nameField', nameField.name(DbBackend::Sql));

        DictTable vendTableDictionary = new DictTable(tableNum(VendTable));
        valueByKeyMap.add('vendTable', vendTableDictionary.name(DbBackend::Sql));

        DictTable partyTableDictionary = new DictTable(tableNum(DirPartyTable));
        valueByKeyMap.add('partyTable', partyTableDictionary.name(DbBackend::Sql));

        DictField partyField = new DictField(tableNum(VendTable), fieldNum(VendTable, Party));
        valueByKeyMap.add('partyField', partyField.name(DbBackend::Sql));

        DictField recId = new DictField(tableNum(DirPartyTable), fieldNum(DirPartyTable, RecId));
        valueByKeyMap.add('recId', recId.name(DbBackend::Sql));

        DictField dataAreaIdField = new DictField(tableNum(VendTable), fieldNum(VendTable, DataAreaId));
        valueByKeyMap.add('dataAreaIdField', dataAreaIdField.name(DbBackend::Sql));

        DictField blockedField = new DictField(tableNum(VendTable), fieldNum(VendTable, Blocked));
        valueByKeyMap.add('blockedField', blockedField.name(DbBackend::Sql));
        
        valueByKeyMap.add('dataArea', curext());
        valueByKeyMap.add('notBlocked', _sqlSystem.sqlLiteral(CustVendorBlocked::No, true));

However, when the code executes, the I get [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@partyField'.

What is the expected syntax and how should I fix this?

Answer 1

I'm not familiar with MS dynamics programming. Using c# string interpolation, kind of

String accountNumberField = ...;
String nameField = ... ;
//... other variables for the string interpolation below
String statement =$"SELECT {accountNumberField}, {nameField} 
                  FROM {vendTable} v 
                 JOIN {partyTable} p 
                       ON (v.{partyField} = p.{recId}) 
                  WHERE ({dataAreaIdField} = @dataArea) 
                       AND ({blockedField} = @notBlocked)";

  // Create and set values only Sql parameters `@dataArea` and `@notBlocked` with your tool here. Assotiate them with sqlStatement  which text is statement
  // ...
  
 resultSet  = statement.executeQuery(sqlStatement);