Kbase 20150: Create, Update and Delete Records Using Dynamic Buffers
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/04/2005 |
|
Status: Verified
FACT(s) (Environment):
Progress 9.x
SYMPTOM(s):
Errors creating, updating and deleting records using dynamic buffers.
Unable to CREATE/DELETE unless a TRANSACTION is running. (7345)
QUERY GET methods with exclusive lock requires a transaction. (7359)
<field name> in ADD-LIKE-COLUMN must have a table qualifier. (9158)
CAUSE:
In tables and static buffers, you create, delete and update by making reference to the name of the table or static buffer. With dynamic buffers and queries, you must use their corresponding internal methods.
FIX:
When you use a dynamic query and dynamic buffer, there is no reference to a specific field or to a specific table. These dynamic objects have methods and properties to find, create, delete or update any record.
There are several methods to find a record using a dynamic query. Follow these steps to open a dynamic query:
1) Define a widget-handle variable for the query object and another one for the buffer object:
DEFINE VARIABLE hQuery AS WIDGET-HANDLE NO-UNDO.
DEFINE VARIABLE hBuffer AS WIDGET-HANDLE NO-UNDO.
2) Create the buffer object:
CREATE BUFFER hBuffer FOR TABLE 'customer'.
3) Create the query object:
CREATE QUERY hQuery.
4) Set the buffers to the query using the query method:
hQuery:SET-BUFFERS(hBuffer).
5) Set the query selection criterea using the query-prepare method:
hQuery:QUERY-PREPARE('FOR EACH customer NO-LOCK').
6) Open the query using the query method:
hQuery:QUERY-OPEN().
When the query is opened, a dynamic browse could be associated to it, or the following method could be used to select a specific record:
:GET-FIRST()
:GET-PREV()
:GET-NEXT()
:GET-LAST()
:REPOSITION-BACKWARD()
:REPOSITION-FORWARD()
:REPOSITION-TO-ROW()
:REPOSITION-TO-ROWID()
Because the buffer handle is associated to buffer query, when the query is repositioned the buffer object has the pointer to the selected record.
Therefore, all the methods to create, update or delete records are applied to the buffer object. These methods must be in a TRANSACTION block.
The field handle is required to reference a field. The property BUFFER-FIELD() could be used to get the field handle of the buffer:
= hBuffer:BUFFER-FIELD('name').
To create a record:
The BUFFER-CREATE() method is used to create a record in a dynamic buffer. It returns a value of TRUE or FALSE according to the result of record creation:
DO TRANSACTION:
IF hBuffer:BUFFER-CREATE() THEN
ASSIGN ..............
ELSE
MESSAGE 'Error in create record'.
END.
hBuffer:BUFFER-RELEASE().
To update a record:
The record to be updated must be read in EXCLUSVE-LOCK Mode:
DO TRANSACTION:
hQuery:GET-CURRENT(EXCLUSIVE-LOCK).
ASSIGN..............
END.
hBuffer:BUFFER-RELEASE(). To delete a record:
The record to be deleted must be read in EXCLUSVE-LOCK Mode:
DO TRANSACTION:
hQuery:GET-CURRENT(EXCLUSIVE-LOCK).
hBuffer:BUFFER-DELETE().
 .; END.
The following code example shows how to create, delete and update records in a dynamic buffer:
CURRENT-WINDOW:HEIGHT-CHARS = 18.
CURRENT-WINDOW:WIDTH-CHARS = 132.
DEFINE BUTTON bQuit LABEL "&Quit" AUTO-ENDKEY.
DEFINE BUTTON bAdd LABEL "&Add Record".
DEFINE BUTTON bUpdate LABEL "&Update Record".
DEFINE BUTTON bDelete LABEL "&Delete Record".
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hBrowse AS HANDLE NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
DEFINE VARIABLE fName AS CHARACTER NO-UNDO FORMAT "X(30)".
DEFINE VARIABLE fPhone AS CHARACTER NO-UNDO FORMAT "X(20)".
FUNCTION setValues RETURN LOGICAL FORWARD.
CREATE BUFFER hBuffer FOR TABLE "Customer".
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hBuffer).
DEFINE FRAME F1
SKIP(4)
fName LABEL "Name" AT 72 SKIP
fPhone LABEL "Phone" AT 72 SKIP
bAdd AT 72
bUpdate AT 86
bDelete AT 103
bQuit AT 119
WITH SIDE-LABELS SIZE 132 BY 18 THREE-D NO-LABELS.
CREATE BROWSE hBrowse
ASSIGN FRAME = FRAME F1:HANDLE
QUERY = hQuery
TITLE = " "
X = 2
Y = 2
WIDTH = 68
DOWN = 12
VISIBLE = TRUE
SENSITIVE = TRUE
READ-ONLY = YES
COLUMN-SCROLLING = TRUE
SEPARATORS = YES
TRIGGERS:
ON 'VALUE-CHANGED'
DO:
DEFINE VARIABLE hColumn AS WIDGET-HANDLE NO-UNDO.
ASSIGN hColumn = hBrowse:GET-BROWSE-COLUMN(2)
fName:SCREEN-VALUE IN FRAME f1 = hColumn:SCREEN-VALUE
hColumn = hBrowse:GET-BROWSE-COLUMN(3)
fPhone:SCREEN-VALUE = hColumn:SCREEN-VALUE.
END.
END TRIGGERS.
hQuery:QUERY-PREPARE("FOR EACH customer NO-LOCK").
hQuery:QUERY-OPEN.
hBrowse:ADD-LIKE-COLUMN('customer.custnum').
hBrowse:ADD-LIKE-COLUMN('customer.name').
hBrowse:ADD-LIKE-COLUMN('customer.phone').
APPLY 'VALUE-CHANGED':U TO hBrowse.
ON CHOOSE OF bAdd
DO:
IF SELF:LABEL = "&Add Record" THEN
DO:
ASSIGN SELF:LABEL = "&Save Record"
fName:SCREEN-VALUE = ""
fPhone:SCREEN-VALUE = "".
&.nbsp; END.
ELSE
DO TRANSACTION ON STOP UNDO:
DEFINE VARIABLE bCreate AS LOGICAL NO-UNDO.
ASSIGN SELF:LABEL = "&Add Record".
ASSIGN bCreate = hBuffer:BUFFER-CREATE().
IF NOT bCreate OR NOT setValues() THEN
DO:
MESSAGE "Add canceled" VIEW-AS ALERT-BOX.
STOP.
END.
hQuery:QUERY-OPEN().
END.
END.
ON CHOOSE OF bUpdate
DO:
DO TRANSACTION ON STOP UNDO:
hQuery:GET-CURRENT(EXCLUSIVE-LOCK).
IF NOT setValues() THEN
DO:
MESSAGE "Update canceled" VIEW-AS ALERT-BOX.
STOP.
END.
END.
hBuffer:BUFFER-RELEASE().
hBrowse:REFRESH().
END.
ON CHOOSE OF bDelete
DO:
DO TRANSACTION:
hQuery:GET-CURRENT(EXCLUSIVE-LOCK).
hBuffer:BUFFER-DELETE().
END.
hQuery:QUERY-OPEN().
END.
ON CHOOSE OF bQuit
DO:
APPLY "window-close" TO CURRENT-WINDOW.
END.
FUNCTION setValues RETURN LOGICAL:
ASSIGN hField = hBuffer:BUFFER-FIELD('name')
hField:BUFFER-VALUE = fName:SCREEN-VALUE IN FRAME f1
hField = hBuffer:BUFFER-FIELD('phone')
hField:BUFFER-VALUE = fPhone:SCREEN-VALUE NO-ERROR.
RETURN NOT ERROR-STATUS:ERROR.
END FUNCTION.
ENABLE ALL WITH FRAME F1.
WAIT-FOR CLOSE OF CURRENT-WINDOW.
.