topic Re: Overwrite database record - Table has only 1 field and record in Application Development and Automation Discussions

Overwrite database record - Table has only 1 field and record

Former Member — Mon, 15 Dec 2008 22:57:10 GMT

Hi,

How do you overwrite a record in a database table?

The database table that we have only has one field and this field is used to count the print number of a specific label. This table will only have 1 record.

The field is also used as part of an id.

So during each program run, I will select the current number in the field, add to it in my program, then will need to overwrite the current value in the database table with the new value.

Please suggest if something different should be done.

Thanks,

John

Re: Overwrite database record - Table has only 1 field and record

Former Member — Mon, 15 Dec 2008 23:19:53 GMT

You can Insert a new record with new ID into the table .

Then you can go ahead and delete the record with old ID .

You will have to use these two steps as you would not be able to modify the key fields

Re: Overwrite database record - Table has only 1 field and record

Former Member — Mon, 15 Dec 2008 23:29:45 GMT

Hi,

Since the table only has one field, this would be the primary key of the table. And you cannot overwrite data in the primary key. Hence you will have to delete the existing record first and then add a new record with the incremented value.

Make sure you lock the table before doing these operations and then unlock it once done.

regards,

Advait

Re: Overwrite database record - Table has only 1 field and record

Former Member — Mon, 15 Dec 2008 23:50:59 GMT

How do you lock/unlock from a program?

Re: Overwrite database record - Table has only 1 field and record

Former Member — Tue, 16 Dec 2008 00:10:03 GMT

Hi,

You will have to create a lock object for your database table. This creates 2 function modules starting with ENQUEUE_(name of the lock object) to lock a database table/record and DEQUEUE_(name of the lock object) to unlock the database table/record.

Refer to the documentation on lock objects [here|http://help.sap.com/saphelp_nw04/helpdata/en/a2/3547360f2ea61fe10000009b38f839/frameset.htm]

regards,

Advait

Re: Overwrite database record - Table has only 1 field and record

Former Member — Tue, 16 Dec 2008 00:16:59 GMT

Thanks.

Is it necessary to lock the table?

Re: Overwrite database record - Table has only 1 field and record

Former Member — Tue, 16 Dec 2008 00:24:06 GMT

Well, If you want that the data should not be overwritten by other program ,or imagine if same program runs twice and both update the data at the sametime, you will have inconsistent data. So to avoid that , it is a good practice to lock the table before updates and unlock it after updates.

regards,

Advait

Re: Overwrite database record - Table has only 1 field and record

Former Member — Tue, 16 Dec 2008 00:27:38 GMT

Thank You. I'll add a lock object then.

Pretty much every label will delete the record from the table using

DELETE FROM <DB TABLE> CLIENT SPECIFIED WHERE MANDT = SY-MANDT.

UPDATE <DB TABLE> FROM <HEADER>.

Re: Overwrite database record - Table has only 1 field and record

Former Member — Tue, 16 Dec 2008 00:34:33 GMT

You will need to do a delete and then insert instead of delete and update. Update wont work in this case.


"Lock DB table
DELETE FROM <DB TABLE> CLIENT SPECIFIED WHERE MANDT = SY-MANDT.

INSERT <DB TABLE> FROM <HEADER>.
"Unlock DB table.

regards,

Advait