topic Re: DB insert after DB delete in a single program on the same table in Application Development and Automation Discussions

DB insert after DB delete in a single program on the same table

raja_ksravankumarreddy — Fri, 23 Feb 2007 05:15:43 GMT

Hi All,

A program is first deleting some records from a databse table ( assume Table1)

and it is trying to insert the same records back to the table Table1 with the same

primary keys.

This program is defective. It does not check against the table

Table1 to verify that the commit work is completed after the delete,

As a result sometimes when the database performance is slow or the

record is locked by some other user, is it is trying to insert records

into the table with the same primary keys even before the delete work

is committed to the database. The program is unable to insert records

into the table with the same primary keys and hence terminating it and

creating the Short Dump Message.

The delete is committed to the database even after the program is

terminated with a Short Dump Message. This results in the old records

being deleted without the new updated records being inserted into the

table Table1.

Please suggest a solution this problem.

Re: DB insert after DB delete in a single program on the same table

seshatalpasai_madala — Fri, 23 Feb 2007 05:29:45 GMT

HI,

The solution will be like First LOCK ALL THE RECORDS you want to delete, then do a DATABASE COMMIT, then LOCK ALL THE RECORDS again (You can lock records that does not exist) then INSERT THE RECORDS and do a DATABASE COMMIT.

In your case delete is happening since there is an implicit database commit when ever a program ends.

Regards,

Sesh

Re: DB insert after DB delete in a single program on the same table

Former Member — Fri, 23 Feb 2007 05:35:14 GMT

Hi,

You need to create a ENQUEUE Function module for this one, nad before doing the Deletion Lock the table/entries using the above function module and do the deletion and insert the records then write the fucntion moduel DEQUEUE to release the lock

secondly, before inserting the records, why don't you check whehter the record is already there in the table with the same primary keu, if the record is existed with the same primay key then do not insert that record.

Regards

Sudheer

Re: DB insert after DB delete in a single program on the same table

Former Member — Fri, 23 Feb 2007 11:25:14 GMT

Instead of using COMMIT WORK after your delete, use; COMMIT WORK AND WAIT. This will allow the commit to complete before the next process starts (your insert).

MattG

Re: DB insert after DB delete in a single program on the same table

Former Member — Fri, 23 Feb 2007 14:14:12 GMT

Don't do the commit after the delete.

Rob