执行Buffer-Copy前如何检查记录是否存在（不依赖数据字典唯一键）

Hey there! Great question—avoiding reliance on predefined unique keys for this check makes total sense if you need flexibility. Here's a practical, dynamic approach to verify if a target record exists before running BUFFER-COPY, using Progress ABL's built-in dynamic query and buffer capabilities:

Core Idea #

We’ll build a dynamic query against your target buffer (hDBBuffer) that matches field values from the current source record (hBuffer). You’ll define which fields count as "duplicate identifiers" (since without a unique key, you need to specify what makes a record "already existing").

Modified Code Example #

Integrate this logic into your existing loop:

DEFINE VARIABLE hCheckQuery AS QUERY NO-UNDO.
DEFINE VARIABLE cCheckQueryStr AS CHARACTER NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE cField AS CHARACTER NO-UNDO.
// Customize this list to your duplicate-check criteria!
DEFINE VARIABLE cMatchFields AS CHARACTER NO-UNDO INITIAL "customer_id,order_date".

// Your existing source query setup
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hBuffer).
hQuery:QUERY-PREPARE("FOR EACH " + hBuffer:NAME + " NO-LOCK ").
hQuery:QUERY-OPEN().
hQuery:GET-FIRST().

DO WHILE NOT hQuery:QUERY-OFF-END:
    // Build the WHERE clause for the existence check
    cCheckQueryStr = "FOR EACH " + hDBBuffer:NAME + " NO-LOCK WHERE ".
    
    // Loop through match fields to construct the condition
    DO i = 1 TO NUM-ENTRIES(cMatchFields):
        cField = TRIM(ENTRY(i, cMatchFields)).
        
        // Handle different data types to avoid query syntax errors
        CASE hBuffer:BUFFER-FIELD(cField):DATA-TYPE:
            WHEN "CHARACTER" THEN
                cCheckQueryStr = cCheckQueryStr + 
                    cField + " = '" + REPLACE(hBuffer:BUFFER-FIELD(cField):BUFFER-VALUE, "'", "''") + "'".
            WHEN "DATE", "DATETIME", "DATETIME-TZ" THEN
                cCheckQueryStr = cCheckQueryStr + 
                    cField + " = " + QUOTER(hBuffer:BUFFER-FIELD(cField):BUFFER-VALUE).
            WHEN "INTEGER", "DECIMAL", "FLOAT" THEN
                cCheckQueryStr = cCheckQueryStr + 
                    cField + " = " + STRING(hBuffer:BUFFER-FIELD(cField):BUFFER-VALUE).
            OTHERWISE
                // Adjust for logical or custom types as needed
                cCheckQueryStr = cCheckQueryStr + 
                    cField + " = " + STRING(hBuffer:BUFFER-FIELD(cField):BUFFER-VALUE).
        END CASE.
        
        // Add AND separator for all but the last field
        IF i < NUM-ENTRIES(cMatchFields) THEN
            cCheckQueryStr = cCheckQueryStr + " AND ".
    END.

    // Prepare and execute the check query
    CREATE QUERY hCheckQuery.
    hCheckQuery:SET-BUFFERS(hDBBuffer).
    hCheckQuery:QUERY-PREPARE(cCheckQueryStr).
    hCheckQuery:QUERY-OPEN().

    // Check if a matching record exists
    IF hCheckQuery:GET-FIRST() = ? THEN
    DO:
        // No existing record—proceed with copy
        DO TRANSACTION ON ERROR UNDO:
            hDBBuffer:BUFFER-CREATE().
            hDBBuffer:BUFFER-COPY(hBuffer).
            MESSAGE "Record copied successfully." VIEW-AS ALERT-BOX INFORMATION.
        END.
    END.
    ELSE
    BEGIN
        MESSAGE "Target record already exists—skipping copy." VIEW-AS ALERT-BOX INFORMATION.
    END.

    // Clean up the check query
    hCheckQuery:QUERY-CLOSE().
    DELETE OBJECT hCheckQuery.

    // Move to the next source record
    hQuery:GET-NEXT().
END.

// Clean up your main source query
hQuery:QUERY-CLOSE().
DELETE OBJECT hQuery.

Key Notes #

• Customize cMatchFields: This is non-negotiable—list fields that uniquely identify a record for your use case (e.g., customer_id,order_number). Using the right fields ensures you don’t miss duplicates or trigger false positives.
• Data Type Handling: The code covers common types, but you may need to tweak it for edge cases (like logical fields or custom user-defined types) to avoid query errors.
• Performance: If your target table is large, add indexes on your cMatchFields—even without a data dictionary unique key, indexes will drastically speed up the existence check.
• Reusability: This dynamic approach works with any buffer, so you can reuse it across different tables without rewriting hardcoded queries.

内容的提问来源于stack exchange，提问作者Gaetano Herman