Example Database Deletion from Datasets

William Sellick

New Member
Progress Version: 11.7.15

I'm trying to work out the correct way to delete data from my database using the generic handling within Progress. My data model has multiple linked tables so I have chosen to use datasets to represent it. For example

Table1
|
__ChildTable1

Code:
    DEFINE TEMP-TABLE ttTable1
        FIELD id                AS CHARACTER
        FIELD field1              AS CHARACTER
        INDEX idx-main IS PRIMARY UNIQUE id.
    
    DEFINE TEMP-TABLE ttChildTable1
        FIELD id                AS CHARACTER
        FIELD ttTable1Id        AS CHARACTER
        FIELD field1              AS CHARACTER
        INDEX idx-main IS PRIMARY UNIQUE id.

    DEFINE DATASET dsExample
        DATA-RELATION drCon FOR ttTable1, ttChildTable1 RELATION-FIELDS(id,ttTable1Id) NESTED.

Ideally I don't want to refer to these tables directly but to use a generic way to reference them. So something similar to:

Code:
CREATE DATASET hDataSet.
hDataSet:CREATE-LIKE(DATASET dsExample:HANDLE).

CREATE DATA-SOURCE hDataSource.
CREATE BUFFER hDbBuffer FOR TABLE "ActualTableName".
hDataSource:ADD-SOURCE-BUFFER(hDbBuffer, "primaryKey1").
hDataSet:GET-BUFFER-HANDLE("ttTable1"):ATTACH-DATA-SOURCE(hDataSource).
// Get the query criteria based on the parameters
hDataSource:FILL-WHERE-STRING = "WHERE ..."
      
CREATE DATA-SOURCE hChildDataSource.
CREATE BUFFER hChildDbBuffer FOR TABLE "ActualChildTableName".
hChildDataSource:ADD-SOURCE-BUFFER(hChildDbBuffer, "primaryKeyName").
hDataSet:GET-BUFFER-HANDLE("ttChildTable1"):ATTACH-DATA-SOURCE(hChildDataSource).
hDataSet:FILL().

I have the above code working. It connects to the database, queries for the correct things and populates all the buffers required. I would like to use these buffers to perform a delete operation so basically deleting each of the records brought back.

I thought this would be straightforward to query for all data returned in the dataset with an exclusive lock and the call a buffer delete. So something similar to:

Code:
DO TRANSACTION:
    DO iBuff = 1 TO hDataSet:NUM-BUFFERS:
        hBufferHandle = hDataSet:GET-BUFFER-HANDLE(iBuff).
        CREATE QUERY hQueryHandle.
        hQueryHandle:SET-BUFFERS(hBufferHandle).
        hQueryHandle:QUERY-PREPARE("for each " + hBufferHandle:NAME + " EXCLUSIVE-LOCK").
        hQueryHandle:QUERY-OPEN.

        REPEAT:
            hQueryHandle:GET-NEXT().
            IF hQueryHandle:QUERY-OFF-END THEN LEAVE.
            hQueryHandle:GET-BUFFER-HANDLE(hBufferHandle:NAME):BUFFER-DELETE ().
        END.
        hQueryHandle:QUERY-CLOSE ().
    END.
END.

I've tried the above code. I don't get any runtime errors and the buffer-delete function returns TRUE (which I thought it meant it succeeded) however the record is still in the database. Can anyone see what I have done wrong here? Do I need to tackle this in a completely different way?

Thanks,
William
 

Osborne

Active Member
I have never used anything like this before so am not sure if this is correct/works for deleting buffers:

Code:
hQueryHandle:GET-BUFFER-HANDLE(hBufferHandle:NAME):BUFFER-DELETE ().

Normally, the following is all that you require:

Code:
hBufferHandle:BUFFER-DELETE().

Does that work?

Another thing to note is if the dataset contains a lot of buffers you are creating a lot of dynamic query objects in one go. Always best to clean up so add the following after hQueryHandle:QUERY-CLOSE ().:

Code:
DELETE OBJECT hQueryHandle.
ASSIGN hQueryHandle = ?
       hBufferHandle = ?.
 

William Sellick

New Member
I have previously tried both formats for the deletion but neither do an actual delete. Both don't throw an error and return TRUE for the command but neither do the actual delete. Do you have any thoughts on what else I can try?

Regarding the clean-up I do have code doing that in my classes however I didn't think it was necessary to include that on my post.
 

Osborne

Active Member
Fair enough and understand regarding the clean up.

The only thing I can think to try is limit the transaction block and not read the records with exclusive locks. Although not quite what you are doing this gives some pointers:


So read the records with NO-LOCK and move the transaction block down and delete similar to:

Code:
DO TRANSACTION:
   QueryHandle:GET-CURRENT(EXCLUSIVE-LOCK).
   hBufferHandle:BUFFER-DELETE().
END.

If this does not work then it may be due to the way datasets work and something extra is required for deleting records. Hopefully someone with more knowledge of datasets knows the answer.
 
Top