Firebird Merge SQL command and .net FbBatchExecution

Question

场景：

• 火鸟数据库
• Winform Application (.NET)
• 文本文件中的 SQL 脚本
• FbBatchExecution(firebird .net 提供程序类，用于从 c# 执行 SQL 脚本)

鉴于此表结构...

CREATE TABLE MYMASTERTABLE (
PRJ_PK         INTEGER NOT NULL,
ID_PK          INTEGER NOT NULL,
FIELDTOUPDATE  INTEGER,
DESCRIPTION    VARCHAR(20) NOT NULL
);
ALTER TABLE MYMASTERTABLE ADD CONSTRAINT PK_MYMASTERTABLE PRIMARY KEY (PRJ_PK, ID_PK);
CREATE TABLE MYDETAILSTABLE (
PRJ_FK      INTEGER NOT NULL,
ID_FK       INTEGER NOT NULL,
ID_ITEM_PK  INTEGER NOT NULL,
MYFIELD1    INTEGER
);
ALTER TABLE MYDETAILSTABLE ADD CONSTRAINT PK_MYDETAILSTABLE PRIMARY KEY (PRJ_FK, ID_FK, ID_ITEM_PK);

。填充了这些值...

UPDATE OR INSERT INTO MYMASTERTABLE (PRJ_PK, ID_PK, FIELDTOUPDATE, DESCRIPTION) VALUES (1, 1, NULL, 'My Item 1') MATCHING (PRJ_PK, ID_PK);
UPDATE OR INSERT INTO MYMASTERTABLE (PRJ_PK, ID_PK, FIELDTOUPDATE, DESCRIPTION) VALUES (1, 2, NULL, 'My Item 2') MATCHING (PRJ_PK, ID_PK);
UPDATE OR INSERT INTO MYMASTERTABLE (PRJ_PK, ID_PK, FIELDTOUPDATE, DESCRIPTION) VALUES (2, 1, NULL, 'Another Item 1') MATCHING (PRJ_PK, ID_PK);
UPDATE OR INSERT INTO MYMASTERTABLE (PRJ_PK, ID_PK, FIELDTOUPDATE, DESCRIPTION) VALUES (2, 2, NULL, 'Another Item 2') MATCHING (PRJ_PK, ID_PK);
UPDATE OR INSERT INTO MYMASTERTABLE (PRJ_PK, ID_PK, FIELDTOUPDATE, DESCRIPTION) VALUES (2, 3, NULL, 'Third') MATCHING (PRJ_PK, ID_PK);
UPDATE OR INSERT INTO MYMASTERTABLE (PRJ_PK, ID_PK, FIELDTOUPDATE, DESCRIPTION) VALUES (2, 4, NULL, 'Fourth') MATCHING (PRJ_PK, ID_PK);
UPDATE OR INSERT INTO MYDETAILSTABLE (PRJ_FK, ID_FK, ID_ITEM_PK, MYFIELD1) VALUES (1, 1, 1, 4) MATCHING (PRJ_FK, ID_FK, ID_ITEM_PK);
UPDATE OR INSERT INTO MYDETAILSTABLE (PRJ_FK, ID_FK, ID_ITEM_PK, MYFIELD1) VALUES (1, 1, 2, 5) MATCHING (PRJ_FK, ID_FK, ID_ITEM_PK);
UPDATE OR INSERT INTO MYDETAILSTABLE (PRJ_FK, ID_FK, ID_ITEM_PK, MYFIELD1) VALUES (1, 1, 3, 1) MATCHING (PRJ_FK, ID_FK, ID_ITEM_PK);
UPDATE OR INSERT INTO MYDETAILSTABLE (PRJ_FK, ID_FK, ID_ITEM_PK, MYFIELD1) VALUES (1, 1, 4, 7) MATCHING (PRJ_FK, ID_FK, ID_ITEM_PK);
UPDATE OR INSERT INTO MYDETAILSTABLE (PRJ_FK, ID_FK, ID_ITEM_PK, MYFIELD1) VALUES (2, 1, 2, 4) MATCHING (PRJ_FK, ID_FK, ID_ITEM_PK);
UPDATE OR INSERT INTO MYDETAILSTABLE (PRJ_FK, ID_FK, ID_ITEM_PK, MYFIELD1) VALUES (2, 2, 2, 2) MATCHING (PRJ_FK, ID_FK, ID_ITEM_PK);
UPDATE OR INSERT INTO MYDETAILSTABLE (PRJ_FK, ID_FK, ID_ITEM_PK, MYFIELD1) VALUES (2, 1, 1, 5) MATCHING (PRJ_FK, ID_FK, ID_ITEM_PK);
UPDATE OR INSERT INTO MYDETAILSTABLE (PRJ_FK, ID_FK, ID_ITEM_PK, MYFIELD1) VALUES (2, 1, 3, 10) MATCHING (PRJ_FK, ID_FK, ID_ITEM_PK);

。我在文本文件中有一个MERGE SQL 命令，我可以从 IbExpert 执行它而不会出错。

MERGE INTO mymastertable AS MMT
USING (
SELECT
MYDETAILSTABLE.PRJ_FK AS PRJ_FK,
MYDETAILSTABLE.ID_FK AS ID_FK,
Sum(MYFIELD1) AS TheSum
FROM
MYDETAILSTABLE
GROUP BY
MYDETAILSTABLE.PRJ_FK,
MYDETAILSTABLE.ID_FK
) AS MyDetails
ON MyDetails.PRJ_FK = MMT.PRJ_PK AND MyDetails.ID_FK = MMT.ID_PK
WHEN MATCHED then
update set
MMT.FIELDTOUPDATE = MyDetails.TheSum

这个 MERGE 命令基本上是 UPDDATE 查询的替代品，该查询在 JOIN 中具有 2 个表的源，不能在 Firebird 数据库中执行。

当我尝试从我的应用程序中执行用c# 编写的相同脚本时，MERGE 命令不起作用。 错误是：

无法确定 SQL 语句的类型

C#应用程序有一个内置的火鸟数据库更新引擎，它像一个魅力一样工作了很久(至少8年)。这是我第一次使用 MERGE 命令，因为我需要使用详细信息表中字段值的 SUM 更新主节点的字段表(主-细节关系已到位)。

我无法使用 FbBatchExecution 使此合并命令工作

编辑问题在于 SQL 脚本的解析，而不是它的执行。

ScriptFileName = @"c:anypathTestUpdate.txt";    
if (File.Exists(ScriptFileName))
{
StreamReader sr = File.OpenText(ScriptFileName);
FbScript script = new FbScript(sr.ReadToEnd());
script.Parse(); //THIS WILL CAUSE AN EXCEPTION
FbConnection cn = fbm.GetConnection();
FbBatchExecution fbe = new FbBatchExecution(cn);
fbe.AppendSqlStatements(script);
fbe.Execute();
}

堆栈跟踪：

in FirebirdSql.Data.Isql.FbScript.Parse() in Test.UpgradeDatabase_NEW(Int32 UpgradeTo) in C：\Users\francesco.giossi\Documents\testApp\Test.cs：row 853

.Net 框架：4.6.1

火鸟 .Net 提供程序：5.12.1.0

火鸟数据库：2.5

任何想法或解决方法？

我尝试了更新...哪里存在，但我即使在 IBExpert 也无法工作，所以我放弃了。

溶液

。

StreamReader sr = File.OpenText(ScriptFileName);
FbScript script = new FbScript(sr.ReadToEnd());
script.UnknownStatement += Script_UnknownStatement;
script.Parse();
FbConnection cn = fbm.GetConnection();
FbBatchExecution fbe = new FbBatchExecution(cn);

。

private void Script_UnknownStatement(object sender, UnknownStatementEventArgs e)
{
//TODO Look for MERGE command in e.Statement
e.NewStatementType = SqlStatementType.Update;
e.Handled = true;
}

Answer 1

将其添加到跟踪器。分析器无法识别MERGE语句。

作为一种快速解决方法，您可以处理UnknownStatement事件并提供即SqlStatementType.Update.