sql - 在 Microsoft Dynamics 中使用 executeQueryWithParameters 加入 x++/sql 语句时,如何获得正确的语法?
问题描述
我目前正在查看Dynamics 365 for Finance and Operations Development Cookbook - Fourth Edition中“执行直接 SQL 语句”的示例。
在文本中,以下内容用于填充str 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));
执行
resultSet = statement.executeQuery(sqlStatement);
但是,Visual Studio 警告我这是过时的,所以我想用statement.executeQueryWithParameters(sqlStatement, valueByKeyMap);
...当然,要做到这一点,我需要创建地图,这似乎没有我预期的那么简单。
目前我已经分配了这个:
str sqlStatement = @'SELECT @accountNumberField, @nameField' +
' FROM @vendTable v' +
' JOIN @partyTable p' +
' ON (@vendTable.@partyField = @partyTable.@recId)' +
' WHERE (@dataAreaIdField = @dataArea)' +
' AND (@blockedField = @notBlocked)';
然后像这样填充地图:
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));
但是,当代码执行时,我得到[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@partyField'.
预期的语法是什么,我应该如何解决这个问题?
解决方案
我不熟悉 MS 动态编程。使用 c# 字符串插值,有点
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);
推荐阅读
- actionscript-3 - 无法在 Firefox 中单击我的游戏 Flash 上的按钮
- javascript - VueJS:反应式对象数组
- android - 使用生物识别 androidx 库崩溃
- javascript - 如何在动态表中使用datalist?
- python - 用于缺乏效率的重复性任务的 Selenium (python) 脚本
- asp.net-mvc - 令牌获取期间未找到缓存令牌
- amazon-ec2 - 如何在 Amazon EC2 或 SageMaker 上安装 Vowpal Wabbit?(亚马逊Linux)
- javascript - 如何删除里面的“自动添加的跨度”
- r - 列中 R 中的时间序列
- r - 如何在操作系统 UBUNTU 上的 R 中安装“quantmod”包?