topic Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table in Application Development and Automation Discussions

SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

matt — Tue, 11 Jun 2019 14:17:41 GMT

So, we've got code like this:

DATA my_data type hashed table of ztable with unique key keyfield.
...
DELETE FROM ztable. "#EC CI_NOWHERE
INSERT ztable FROM TABLE my_data.

And at the insert, we're getting the dump SAPSQL_ARRAY_INSERT_DUPREC.

Database is Oracle. ztable contains ~200 records before the delete, my_data is of a similar size.

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

RaymondGiuseppi — Tue, 11 Jun 2019 14:54:45 GMT

Are there duplicates in your own internal table (is keyfield the only primary key with client in db table ?)
Isn't the actual statement INSERT ztable FROM TABLE my_data.

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

Sandra_Rossi — Tue, 11 Jun 2019 15:32:11 GMT

Database unique index, database triggers, ...

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

matt — Wed, 12 Jun 2019 07:04:43 GMT

raymond.giuseppi corrected, thank you.

No, there are no duplicates in my internal table. There can't be - it's hashed. The key of the internal table is identical to the key of the db table.

No database triggers.

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

RaymondGiuseppi — Wed, 12 Jun 2019 07:40:30 GMT

What are the technical settings of the ztable,
Are there some unique secondary indexes
Does a call of db_commit FM between the two statements solve the problem (but why would it be required...)

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

SimoneMilesi — Wed, 12 Jun 2019 08:53:34 GMT

I think you need a commit work after the delete because, otherwise, the command is still between your report and the database.

If you follow in debug the code, stopping 1-2 seconds after the delete, you still have the error?

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

RobertVit — Wed, 12 Jun 2019 09:31:33 GMT

commit in between is missing

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

matt — Wed, 12 Jun 2019 09:34:03 GMT

I'm beginning to think it's a timing issue. But we should be able to stack SQL - otherwise the rollback mechanism is pointless - we'd be unable to do this:

DELETE FROM ztable. "#EC CI_NOWHERE
IF sy-subrc IS INITIAL.
  INSERT ztable FROM TABLE my_data ACCEPTING DUPLICATE ROWS.
  IF sy-subrc IS INITIAL.
    COMMIT WORK.
  ELSE.
    ROLLBACK WORK.
  ENDIF.
ELSE.
  ROLLBACK WORK.
ENDIF.

Maybe I should put the db updates into an update FM.

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

matt — Wed, 12 Jun 2019 12:32:45 GMT

Why should you need a commit within the same database session? If it were true, then the following code would be useless:

DELETE FROM ztable. "#EC CI_NOWHERE
IF sy-subrc IS INITIAL.
  INSERT ztable FROM TABLE my_data ACCEPTING DUPLICATE ROWS.
  IF sy-subrc IS INITIAL.
    COMMIT WORK.
  ELSE.
    ROLLBACK WORK.
  ENDIF.
ELSE.
  ROLLBACK WORK.
ENDIF.

If the insert failed, we'd be left with an empty table, which is not what we want.

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

former_member378318 — Wed, 12 Jun 2019 13:40:52 GMT

The internal table maybe of type hash with a unique key but it could create a duplicate if the DB table key is not equivalent.

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

matt — Wed, 12 Jun 2019 13:53:07 GMT

It is identical.

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

RaymondGiuseppi — Wed, 12 Jun 2019 14:10:55 GMT

If you really want to always prevent any exception, you may be required to read current data in a old_records_itab and match it with new records to identify records to create, update, delete or ignore, then execute the 3 statements. Many update FM expect such internal table as parameters.

Meanwhile, did you check values of sy-subrc and sy-dbcnt after each abap sql statement?

Re: SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

matt — Fri, 21 Jun 2019 08:12:32 GMT

The reason for the dump is that the table was buffered, and it wasn't synced across application servers.

Thanks all for your thoughts.