Firebird Merge SQL command and .net FbBatchExecution

Scenario:

• Firebird database
• Winform Application (.NET)
• SQL Script in a text file
• FbBatchExecution (firebird .net provider class to execute SQL scripts from c#)

Given this tables structure...

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

...populated with these values...

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

... I have a MERGE SQL COMMAND in a text file that I can execute from IbExpert with no errors.

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

This MERGE command is basically a substitute of an UPDDATE query having as a source 2 tables in JOIN, which cannot be executed in a Firebird database.

When I try to execute the same script from my application, written in c#, the MERGE command does not work. The error is:

The type of the SQL statement could not be determined

The C# application has a builtin firebird database updater engine which works like a charme since ages (at least 8 years). This is the 1st time I use a MERGE command, because I need to update a Master's field table with the SUM of the values of a field in the Details table (a Master-Details relation is in place).

There is no way I can make this Merge command to work, using FbBatchExecution

EDIT The problem is with the Parse of the SQL script, not its execution.

ScriptFileName = @"c:\anypath\TestUpdate.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();
}

The stackTrace:

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

.Net Framework: 4.6.1

Firebird .Net provider: 5.12.1.0

Firebird Database: 2.5

Any idea or workaround?

I tried the UPDATE ... WHERE EXISTS way, but I can't make it to work even in IBExpert, so I gave up.

SOLUTION

...

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