Existing Unique Index Warning

[ChezJfrey]

The following code reads a text file to import records. I receive modal pop-ups with a warning when a unique record already exists. I would like to just ignore these and move to the next record, but I am unable. Is there any way to do this without expressly finding whether the unique record key already exists?

INPUT from value("C:\TestDir\TestFile").

REPEAT ON ERROR UNDO, NEXT:
IMPORT TestTable NO-ERROR.
END.

INPUT CLOSE.

I tested to determine when I receive the warning and it is not in the IMPORT statement but apparently at the beginning of the next iteration of the REPEAT. I assume this is the point where the last transaction is comitted. I also assumed that the ON ERROR UNDO, NEXT would circumvent this problem. . . but was obvously wrong.

 

[Samj]

Jeff

How about:

INPUT FROM VALUE("C:\TestDir\TestFile").

Load-Block:
REPEAT ON ERROR UNDO Load-Block, NEXT Load-Block TRANSACTION:
CREATE TestTable NO-ERROR.
IF ERROR-STATUS:ERROR THEN
UNDO Load-Block, NEXT Load-Block.
IMPORT TestTable NO-ERROR.
IF ERROR-STATUS:ERROR THEN
UNDO Load-Block, NEXT Load-Block.
END.

INPUT CLOSE.

 

[ChezJfrey]

INPUT FROM VALUE("C:\TestDir\TestFile").

Load-Block:
REPEAT ON ERROR UNDO Load-Block, NEXT Load-Block TRANSACTION:
MESSAGE "Iteration begin" VIEW-AS ALERT-BOX.
CREATE TestTable NO-ERROR.
IF ERROR-STATUS:ERROR THEN
UNDO Load-Block, NEXT Load-Block.
IMPORT TestTable NO-ERROR.
IF ERROR-STATUS:ERROR THEN
UNDO Load-Block, NEXT Load-Block.
MESSAGE "Iteration end" VIEW-AS ALERT-BOX.
END.

INPUT CLOSE.

I tried the above code and I still receive the same message, "TestTable already exists with this unique index value. (2471)." The message appears after the first encounter of the "Iteration end" alert and before the next "Iteration begin" alert. Again, I would like to not have to expressly find if the unique record exists. I just want to skip those records and move to the next. Does anyone have another suggestion?

 

[M-HT]

Try adding these statements:
RELEASE TestTable NO-ERROR.
IF ERROR-STATUS:ERROR THEN
UNDO Load-Block, NEXT Load-Block.
before the END. statement in previous algorithm.

 

[Samj]

I've tried this numerous times and the only time I could get an error was when I removed the value from the unique field in two or more of the records. Then the end statement for the transaction block would give me error number 132 on the second, third... records with a blank unique value.

 

[ChezJfrey]

Continuing My Existing Index Problem

Thanks for the help and sorry for the delay in responding, I've been on a 3 week vacation.

Anyway, I believe this to be the problem as well. I am trying to port data from a db on our MS platform to a db on our AS400. The tables are identical (structure, keys/indices) within each db and I use 'Export' to write records to a text file and 'Import' from this file. I use the default delimeter/format for these commands. This problem exists for only one table (which contains 100+ fields and 54 mb data). I find that some of the records in the AS400 appear with primary index fields missing or erroneous data, so it may be a problem in the parsing. The originating table does not have any data problems like this, only the destination. Is there a known bug with these commands?

I don't hard code a search for existing records because I delete all records prior to import. I also created an interface that allows an administrator to choose specific tables to port. Therefore, I dynamically create procedures to export/import and don't know the fields that make up the primary key in order to search for it. But since the originating table has a unique constraint that matches the destination, I shouldn't need it anyway. So, knowing what I'm trying to accomplish, is there an easier/better way?

 

[ChezJfrey]

Not Gonna Happen

OK, I finally resigned myself to finding the unique, primary key for the tables at run-time and checking the existence of each record prior to import. A bit slower, but it'll have to do. Thanks to those who responded.