topic Re: Catching failed db inserts in Application Development and Automation Discussions

Catching failed db inserts

Former Member — Wed, 18 Jun 2008 00:17:24 GMT

Hi,

A quickie...

If I use the statement

INSERT zSTOCKOUT FROM TABLE itab_stockouts.

how would I avoid a short dump if the table already contains a row with the unique key?

I tried following the statement by sy-subrc NE 0, but it still short dumps before it even evaluates my statement.

I don't want to go through the whole itab to do a check first if I can help it.

Regards

Charl

Re: Catching failed db inserts

Former Member — Wed, 18 Jun 2008 00:34:30 GMT

Use ACCEPTING DUPLICATE KEYS

INSERT zSTOCKOUT FROM TABLE itab_stockouts ACCEPTING DUPLICATE KEYS .

Please reward points.

Thanks

Romit

Edited by: Romit Kewalramani on Jun 18, 2008 2:41 AM

Re: Catching failed db inserts

Former Member — Wed, 18 Jun 2008 06:30:54 GMT

Hi,

The best way use the Modify statement .

If it finds the keys then it will update the record or it will inser a new record.

I hope this will help you.

Help children of U.N World Food Program by rewarding them and encourage others to answer your queries

Re: Catching failed db inserts

prasanth_kasturi — Wed, 18 Jun 2008 06:40:40 GMT

hi,

Its better to do with MODIFY statemnt

Modify acts both as insert and update

if a row exists it acts as update otherwise as update

hope i am clear

regards

prasanth

Re: Catching failed db inserts

Former Member — Wed, 18 Jun 2008 06:49:37 GMT

So there is no way to capture the error without a short dump?

I don't really need it to modify. If it is there it doesn't need to do anything.

Thanks for the suggestions though

Re: Catching failed db inserts

christine_evans — Wed, 18 Jun 2008 09:00:43 GMT


> So there is no way to capture the error without a short dump?
> 
> I don't really need it to modify. If it is there it doesn't need to do anything.
> Thanks for the suggestions though

You can use a TRY ..... ENDTRY to catch the exception eg


* For use with class based exception CX_SY_OPEN_SQL_DB.
  DATA:
  ex_check_os       TYPE REF TO cx_sy_open_sql_db,
  ex_check_rs       TYPE REF TO cx_sy_native_sql_error,
  ex_result(200)    TYPE C,
  ex_text           TYPE STRING.

  CLEAR: ex_check_os, ex_text.
   SORT gt_zswift_map BY ebeln swift_line_no.
   TRY.
     INSERT zswift_map
     FROM TABLE gt_zswift_map.
     CATCH CX_SY_OPEN_SQL_DB INTO ex_check_os.
     ex_text = ex_check_os->get_text( ).
     ROLLBACK WORK.
     w_stop_job = 'X'.
     CONCATENATE 'ERROR' ex_text INTO ex_text SEPARATED BY SPACE.
     MESSAGE ex_text TYPE 'I'.
     IF w_stop_job = 'X'.
        RETURN.
     ENDIF.
   ENDTRY.

The other way of avoiding a dump would be to check for the existence of a record before trying to insert it, which is effectively (I would think) what MODIFY does. I would NEVER use ACCEPTING DUPLICATE ENTRIES, because I think you need to know whether a record is new or if it already exists.

Re: Catching failed db inserts

Former Member — Wed, 18 Jun 2008 13:04:10 GMT

I like this TRY command, never seen it before.

That will work I think.

I'll give it a shot and read up about the command in general.

Although I am still puzzled why the help says INSERT sets sy-subrc to 4 when a duplicate is found, but you can't do anything about it.

Bet that try command will solve many such situations.

Thanks again, that does it for me.