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
Ideally I don't want to refer to these tables directly but to use a generic way to reference them. So something similar to:
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:
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
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