Can't add record after duplicate record error.

Why can't a record be added to a TEMP-TABLE after an attempt to add a duplicate record to the TEMP-TABLE fails? Why do FOR EACH statements, of a TEMP-TABLE, fail to find any records after an attempt to add a duplicate record to a TEMP-TABLE?

Jul 19, 2016
Knowledge

Title

URL Name

Can-t-add-record-after-duplicate-record-error

Article Number

000184094

Environment

Product: OpenEdge Version: 11.3.3 and above OS: All supported platforms

Question/Problem Description

The code below demonstrates the problem.
The TEMP-TABLE includes the NO-UNDO attribute.
Any errant transaction performed on this TEMP-TABLE will not be undone.
The code to CREATE TEMP-TABLE records and assign values to the fields in the records is not in a TRANSACTION block.
Once one attempt is made to add a duplicate record to the TEMP-TABLE, all subsequent attempts to add records to the TEMP-TABLE, whether duplicate or not, fail with the error "** <file-name> already exists with <field/value...>. (132)". In addition any FOR EACH FINDS will find no records due to the same problem.

DEF TEMP-TABLE ttCopyDirs NO-UNDO
FIELD dirPath AS CHAR
FIELD copyPatternList AS CHAR
INDEX idx-ttCopyDirs AS PRIMARY UNIQUE dirPath copyPatternList.

/*
* first input parameter is the index value
* second input parameter is just a value
*/
PROCEDURE callme:
DEF INPUT PARAMETER inp1 AS CHARACTER.
DEF INPUT PARAMETER inp2 AS CHARACTER.
CREATE ttCopyDirs.
ASSIGN ttCopyDirs.dirPath = inp1
ttCopyDirs.copyPatternList = inp2.
END PROCEDURE.

PROCEDURE tryme:
DEF INPUT PARAMETER inp1 AS CHARACTER.
END PROCEDURE.

/*
* first parameter is the index value
* second input parameter is just a value */
RUN callme("A","B").
RUN callme("C","B").
RUN callme("C","B"). /*unique key violation*/
RUN callme("D","B").
RUN callme("E","B").
RUN callme("F","B").
RUN callme("F","B"). /*unique key violation*/
RUN callme("G","B").

FOR EACH ttCopyDirs NO-LOCK:
RUN tryme(ttCopyDirs.dirPath).
OUTPUT TO myoutput.log.
PUT ttCopyDirs.dirPath SKIP.
OUTPUT close.
END.
LOG-MANAGER:CLOSE-LOG().

Steps to Reproduce

Clarifying Information

Follows an upgrade from 11.2 to 11.5.
Error does not occur in 11.2.
The behavior described in this article is different for OpenEdge 11.3.3 and later than it is in earlier OpenEdge versions.
Occurs any time duplicate records are added to a TEMP-TABLE.

Error Message

** <file-name> already exists with <field/value...>. (132)

Defect Number

Defect PSC00345963

Enhancement Number

Cause

This is expected behavior. The TEMP-TABLE is defined as NO-UNDO and the code to create and insert a record is not within a transaction. Due to this when an attempt is made to insert a duplicate record, the duplicate record is left in scope. The errant transaction is not undone, so when the buffer is used again for either a FIND or a CREATE, ABL tries to evict the record and tries to write it out. The error occurs again.

The application needs to make sure that it deals with errors and either deletes the record or causes an undo to happen (but then the temp-table must be UNDO, and the application needs a transaction active).

Resolution

Place the CREATE and ASSIGN code in a TRANSACTION block. Remove the NO-UNDO attribute from the TEMP-TABLE.

The code below shows the application updated with the NO-UNDO attribute removed from the TEMP-TABLE and the CREATE and ASSIGN statements placed within a TRANSACTION block. On an attempt to insert a duplicate record in the TEMP-TABLE, the transaction is undone and the system is ready for the next transaction. Subsequent records that are not duplicates are successfully added to the TEMP-TABLE. FOR EACH statements successfully find all records added to the TEMP-TABLE.

DEF TEMP-TABLE ttCopyDirs
FIELD dirPath AS CHAR
FIELD copyPatternList AS CHAR
INDEX idx-ttCopyDirs AS PRIMARY UNIQUE dirPath copyPatternList.

PROCEDURE callme:
DEF INPUT PARAMETER inp1 AS CHARACTER.
DEF INPUT PARAMETER inp2 AS CHARACTER.

/*
* by removing no-uno from TEMP-TABLE and
* doing create and assign in transaction scope,
* duplicate records will not be flushed to tt
* but good records will go to tt
*/
DO TRANSACTION:
CREATE ttCopyDirs.
ASSIGN
ttCopyDirs.dirPath = inp1
ttCopyDirs.copyPatternList = inp2.
END.
END PROCEDURE.

PROCEDURE tryme:
DEF INPUT PARAMETER inp1 AS CHARACTER.
END PROCEDURE.

/*
* first parameter is the index value
* second input parameter is just a value */
RUN callme("A","B").
RUN callme("C","B").
RUN callme("C","B"). /*unique key violation*/
RUN callme("D","B").
RUN callme("E","B").
RUN callme("F","B").
RUN callme("F","B"). /*unique key violation*/
RUN callme("G","B").

FOR EACH ttCopyDirs NO-LOCK:
RUN tryme(ttCopyDirs.dirPath).
OUTPUT to VALUE("myoutput.log") APPEND.
PUT ttCopyDirs.dirPath SKIP.
OUTPUT close.
END.

Workaround

Notes

This is related to a defect found in OpenEdge 11.2 and fixed in 11.3.3.0 and above. In 11.2 if duplicate record validation fails, before the record is disconnected, and depending on which sub-transaction gets undone, index keys could be updated while a record might not be updated. This could leave the table in an inconsistent state.

Keyword Phrase

Last Modified Date

7/19/2016 9:38 AM

Can't add record after duplicate record error.

Files(0)

Was this article helpful?

Was this article helpful?

Can't add record after duplicate record error.

Information

Related Articles