[HDBProcedure] UPDATE FROM not supported

[ThuF]

Migration solution:

• [HDBProcedure] UPDATE FROM not supported #426 (comment)

Steps to reproduce:

• Create a new project named Demo
• Create an hdbprocedure file called DemoProcedure
• Paste the following content inside the file and publish it

PROCEDURE "Demo::DemoProcedure" (IN STATUS NVARCHAR(100), IN NUM NVARCHAR(4))
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   DEFAULT SCHEMA DBADMIN
   AS
BEGIN
    UPDATE "Demo::TestTable" 
    SET "Status" = :STATUS
    FROM "Demo::TestTable" 
        WHERE "num" =:NUM;
END

Error:

SAP DBTech JDBC: [7]: feature not supported: UPDATE FROM statement isn't supported

StackTrace:

2022-02-11 10:42:30.363 [ERROR] [pool-1826-thread-1] c.s.x.hdb.ds.facade.XSKHDBCoreFacade - SAP DBTech JDBC: [7]: feature not supported: UPDATE FROM statement isn't supported
com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [7]: feature not supported: UPDATE FROM statement isn't supported
        at com.sap.db.jdbc.exceptions.SQLExceptionSapDB._newInstance(SQLExceptionSapDB.java:209) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.newInstance(SQLExceptionSapDB.java:42) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.packet.HReplyPacket._buildExceptionChain(HReplyPacket.java:841) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.packet.HReplyPacket.getSQLExceptionChain(HReplyPacket.java:158) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.packet.HPartInfo.getSQLExceptionChain(HPartInfo.java:39) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.ConnectionSapDB._receive(ConnectionSapDB.java:4786) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.ConnectionSapDB.exchange(ConnectionSapDB.java:2022) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.PreparedStatementSapDB._prepare(PreparedStatementSapDB.java:2961) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.PreparedStatementSapDB._doParse(PreparedStatementSapDB.java:2854) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.PreparedStatementSapDB.<init>(PreparedStatementSapDB.java:166) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.PreparedStatementSapDB9.<init>(Unknown Source) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.HanaPreparedStatement.<init>(Unknown Source) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.HanaPreparedStatementClean.<init>(Unknown Source) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.HanaPreparedStatementClean.newInstance(Unknown Source) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.ConnectionSapDB9._prepareStatement(Unknown Source) ~[ngdbc-2.11.14.jar:na]
        at com.sap.db.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:369) ~[ngdbc-2.11.14.jar:na]
        at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:696) ~[commons-dbcp2-2.9.0.jar:2.9.0]
        at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:696) ~[commons-dbcp2-2.9.0.jar:2.9.0]
        at org.eclipse.dirigible.database.api.wrappers.WrappedConnection.prepareStatement(WrappedConnection.java:447) ~[dirigible-database-api-6.1.23.jar:na]
        at com.sap.xsk.hdb.ds.processors.AbstractXSKProcessor.executeSql(AbstractXSKProcessor.java:40) ~[xsk-modules-engines-hdb-0.14.0-SNAPSHOT.jar:na]
        at com.sap.xsk.hdb.ds.processors.hdbprocedure.HDBProcedureCreateProcessor.execute(HDBProcedureCreateProcessor.java:54) ~[xsk-modules-engines-hdb-0.14.0-SNAPSHOT.jar:na]
        at com.sap.xsk.hdb.ds.processors.hdbprocedure.HDBProcedureCreateProcessor.execute(HDBProcedureCreateProcessor.java:32) ~[xsk-modules-engines-hdb-0.14.0-SNAPSHOT.jar:na]
        at com.sap.xsk.hdb.ds.service.manager.IXSKProceduresManagerService.createDataStructure(IXSKProceduresManagerService.java:80) ~[xsk-modules-engines-hdb-0.14.0-SNAPSHOT.jar:na]
        at com.sap.xsk.hdb.ds.service.manager.IXSKProceduresManagerService.createDataStructure(IXSKProceduresManagerService.java:31) ~[xsk-modules-engines-hdb-0.14.0-SNAPSHOT.jar:na]
        at com.sap.xsk.hdb.ds.facade.XSKHDBCoreFacade.updateEntities(XSKHDBCoreFacade.java:492) ~[xsk-modules-engines-hdb-0.14.0-SNAPSHOT.jar:na]
        at com.sap.xsk.hdb.ds.synchronizer.XSKDataStructuresSynchronizer.synchronize(XSKDataStructuresSynchronizer.java:236) ~[xsk-modules-engines-hdb-0.14.0-SNAPSHOT.jar:na]
        at org.eclipse.dirigible.core.scheduler.api.AbstractSynchronizerJob$1.run(AbstractSynchronizerJob.java:52) ~[dirigible-core-scheduler-6.1.23.jar:na]
        at org.eclipse.dirigible.commons.timeout.TimeLimited$1.call(TimeLimited.java:38) ~[dirigible-commons-timeout-6.1.23.jar:na]
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[na:na]
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
        at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]

More details could be found:

• [HDBProcedure] UPDATE FROM not supported SAP-archive/xsk#1256

[ThuF]

UPDATE FROM should be changed into MERGE INTO, perhaps during the migration.
https://answers.sap.com/questions/13455643/is-update-from-is-not-supporting-on-spa-hana-cloud.html

[ThuF]

The Hana parser module should be modified to parse UPDATE FROM (Hana platform) statements as now it understands only UPDATE (HANA Cloud).

• HANA Platform syntax: https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.05/en-US/20ff268675191014964add3d17700909.html
• HANA Cloud syntax: https://help.sap.com/viewer/c1d3f60099654ecfb3fe36ac93c121bb/2021_4_QRC/en-US/20ff268675191014964add3d17700909.html

[ThuF]

UPDATE "schema::T"
    SET
        T."DESCRIPTION_EN_US" = INP."cust_Description_en_US",
        T."END_DATE" = INP."mdfSystemEffectiveEndDate",
        T."STATUS" = INP."mdfSystemStatus",
        T."IMPORT_RUN_AUDIT_ID" = :importRunAuditId,
        T."REC_STATUS" = 1
    FROM "...:entities.FO_BU" AS T
        INNER JOIN :inpTable INP
            ON T."EXTERNAL_CODE" = INP."externalCode"
            AND T."START_DATE" = INP."effectiveStartDate";

→

MERGE INTO ".....::entities.FO_BU" AS T
    USING :inpTable INP
        ON (T."EXTERNAL_CODE" = INP."externalCode" AND T."START_DATE" = INP."effectiveStartDate")
    WHEN MATCHED THEN UPDATE
        SET
            T."DESCRIPTION_EN_US" = INP."cust_Description_en_US",
            T."END_DATE" = INP."mdfSystemEffectiveEndDate",
            T."STATUS" = INP."mdfSystemStatus",
            T."IMPORT_RUN_AUDIT_ID" = :importRunAuditId,
            T."REC_STATUS" = 1;

[ThuF]

Sample UPDATE FROM migrations.

Basic:

UPDATE T1
SET COL = ''
FROM "MY_TABLE" AS T1
INNER JOIN :INPUT_TABLE AS T ON T1.ID = T.ID;

→

MERGE INTO "MY_TABLE" AS T1
USING :INPUT_TABLE AS T ON T1.ID = T.ID
WHEN MATCHED THEN UPDATE
SET COL = '';

With WHERE clause

UPDATE T1
SET COL = ''
FROM "MY_TABLE" AS T1
INNER JOIN INPUT_TABLE AS T ON T1.ID = T.ID
WHERE T1.ID <> :V_DATA_ID;

→

MERGE INTO "MY_TABLE" AS T1
USING INPUT_TABLE AS T ON T1.ID = T.ID
WHEN MATCHED AND T1.ID <> :V_DATA_ID THEN UPDATE
SET COL = '';

Without JOIN

UPDATE T1
SET COL = ''
FROM "MY_TABLE" AS T1
WHERE T1.ID <> :V_DATA_ID;

→

UPDATE "MY_TABLE" AS T1
SET COL = ''
WHERE T1.ID <> :V_DATA_ID;