Inserting a duplicate row in SQL table not giving any exception

[Former Member]

hi All,

I am inserting into an SQL table through my abap program. If i try to insert any duplicate entries into SQL table ideally it should give "Violation of PRIMARY KEY constraint" error. But my program is ending with sy-subrc = 0. I have placed the insert statement inside the try catch block. Please help me solve this problem.

Regards,

Devaiah

[Former Member]

Try to raise exception on MS SQL level like this:

try.
    EXEC SQL
      begin try
        insert into TABLENAME ...
      end try
      begin catch
         raiserror('insert failed',11,1)
      end catch
    ENDEXEC.
catch cx_sy_native_sql_error into gv_oref.
   gv_txterror = gv_oref->get_text( ). 
   message gv_txterror type 'I'.
endtry.

This way the caller will be informed about insertion error.

[former_member438956]

Hi,

Which table ru trying to insert duplicate rows? there might be a possibility tht u might hve define secondary indexes and it is taking precedence over ur primary indexes.

Please chk in SE11 wht r ur primary keys nd wht r ur secondary keys?

Also if possibel try to provide the exact data which ur tring to insert.

Regards,

Anil N.

Moderator message: check your keyboard or fingers, please do not use SMS speak.

Edited by: Thomas Zloch on Mar 14, 2011 3:30 PM

[Former Member]

Forget the secondary key...that's meaningless in this case.... if your values duplicate an entry of the entire primary key you should indeed get an error....are you supplying exact duplicate values, including client/mandt? Probably not....

Suggest you provide forum with primary key details, your data values and your code, if unable to resolve.

[JanStallkamp]

Hi.

Is there really a primary key on that table? Not only being defined but existing in the database? Are the inserted lines really duplicates (client? some kind of timestamp?) and are they really written to the database? I

If a primary key is existing on the database and the database is still accepting duplicate entries without an error message you are facing a severe database issue. But this kind of issues are quite rare, so I would check first if this is really the case.

/Jan

[Clemenss]

Hi Devaiah,

This is a real F1 question. Please put cursor on INSERT staement in the program and press F1.

You will get a SY-SUBRC = 4 if the record is not inserted because it already exists in the database. This is not an Exception.

Probably the program will contnue to process other statements that result in SY-SUBRC = 0.

The TRY .. CATCH Block is completelly useless in this case.

If you insert FROM TABLE <itab> then you will get an exception that may be caught or causes a dump if not caught. Here the addition ACCEPTING DUPLICATE KEYS will prevent the exception and also give a SY-SUBRC = 4.

Regards,

Clemens

[Former Member]

hi all,

Thank you all for your reply.

I am trying to insert into a MS SQL table through DBCON. I am really sorry as i forgot to mention this.

I am using the following code.

try.
    EXEC SQL
        insert into TABLENAME
         ( 
           COLUMN1
           COLUMN2
         )
           VALUES
         (
            :VALUE1
            :VALUE2
          )  

    ENDEXEC.
catch cx_sy_native_sql_error into gv_oref.
   gv_txterror = gv_oref->get_text( ). 
   message gv_txterror type 'I'.
endtry.

I know that one of the option is to put a select query and check if the entry exists, but it will lead to serious performance

issue as the number are entries to be inserted are very high. We will be running this in the background, so if any error comes i want to show to the user in the log. Right now its not inserting the duplicate row, only problem is its not giving any kind of error. Please help me with this.

Regards,

Devaiah

[SuhaSaha]

Hello Devaiah,

I would ask you to take a look at ADBC classes CL_SQL* in this case.

The method EXECUTE_UPDATE of class CL_SQL_PREPARED_STATEMENT propagates the exceptions CX_SQL_EXCEPTION &

CX_PARAMETER_INVALID.

I think you can make use of these exception objects to log the errors, if any. You can refer to the program ADBC_DEMO to know how the ADBC classes have been implemented.

BR,

Suhas

[Former Member]

Try to raise exception on MS SQL level like this:

try.
    EXEC SQL
      begin try
        insert into TABLENAME ...
      end try
      begin catch
         raiserror('insert failed',11,1)
      end catch
    ENDEXEC.
catch cx_sy_native_sql_error into gv_oref.
   gv_txterror = gv_oref->get_text( ). 
   message gv_txterror type 'I'.
endtry.

This way the caller will be informed about insertion error.

[Former Member]

hi Kovalev,

Thank you for your reply. I did as you said and it worked perfectly.

Awarded you with full points.

Once again thank you all for your inputs.

Regards,

Devaiah