[jira] [Commented] (NIFI-14869) Add UPSERT support for MS SQL Server using MERGE in PutDatabaseRecord

["adel wageeh (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/NIFI-14869?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18097426#comment-18097426 ] 

adel wageeh commented on NIFI-14869:
------------------------------------

Hello, [~exceptionfactory] . PR #10218 was closed due to lack of review bandwidth, so I've opened a new PR (#11442) with the same approach, addressing the test implementation and Checkstyle feedback you mentioned: [https://github.com/apache/nifi/pull/11442]. Happy to make further changes whenever you have cycles.

> Add UPSERT support for MS SQL Server using MERGE in PutDatabaseRecord
> ---------------------------------------------------------------------
>
>                 Key: NIFI-14869
>                 URL: https://issues.apache.org/jira/browse/NIFI-14869
>             Project: Apache NiFi
>          Issue Type: Improvement
>          Components: Extensions
>    Affects Versions: 2.2.0
>         Environment: Windows 11, Java 21.0.5, NiFi 2.2.0, MS SQL Server 2012+
>            Reporter: adel wageeh
>            Priority: Major
>              Labels: MSSql, putdatabaserecord, upsert
>   Original Estimate: 168h
>          Time Spent: 40m
>  Remaining Estimate: 167h 20m
>
> *Problem:*
> The {{PutDatabaseRecord}} processor in NiFi 2.2.0 does not support the UPSERT statement type for MS SQL Server dialects (2008 or 2012+), resulting in a validation error: "UPSERT not supported with Database Dialect". Although MS SQL Server does not have a native UPSERT statement, equivalent functionality is available via the MERGE statement. This limitation restricts dynamic workflows, such as data masking integrations, where upsert operations are required without custom scripts or workarounds.
> *Solution:*
> To address this limitation, new services were implemented: {{MSSQLDatabaseDialectService}} and {{{}MSSQL2008DatabaseDialectService{}}}, both conforming to the {{DatabaseDialectService}} API. These services:
>  * Indicate support for UPSERT operations by including UPSERT in the set of supported statement types.
>  * Generate MERGE statements for UPSERT operations, utilizing table and column definitions as well as primary key information to ensure correct matching and updating/inserting of records.
>  * Integrate with NiFi processors, such as {{{}PutDatabaseRecord{}}}, enabling direct upsert operations for MS SQL Server without the need for custom scripts.
>  * Align with NiFi’s architectural direction by replacing the legacy {{DatabaseAdapter}} approach with the new {{DatabaseDialectService}} API, providing a more maintainable and extensible solution for database dialect support.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)