Table Management 2

[dexterdg]

I have a table and i want to edit something on the record and i added a few records and then i change my mind, instead of clicking save i clicked cancel. What would be a good way to revert that already been added records.

NOTE:
[li]considering its a form that do AED(add, edit, delete)[/li]
[li]as it adds it refreshes and populate a grid[/li]
[li]used SQL in AED-ing[/li]
[li]the "SAVE" mentioned above just assures if everything is in place and then closes the form[/li]

thanks
Dexter

 

[Olaf Doschke]

Well, you have to a lot of work if you want to stay with this way, keeping track of what records you added to delete them again, etc.

There is a way to finally save or cancel via TABLEUPDATE (for save) and TABLEREVERT (for canceling), but only if you make use of buffering in the way it's intended to be used. You buffer any change you might want to revert later, including added records and deletions. This is possible using table buffering. You can also call this AED if you limit yourself to one record you appen (add)d, in record buffering mode you can then edit it as long as you want, it won't appear in the DBF file or for other users until you save via leaving the record or cancel (delete) via TABLEREVERT before leaving the record. And when editing existing record the buffering still means, that the DBF file and other user will not see your changes until you leave the record. optimistic vs pessimistic decides between not locking or locking the record when you start editing.

Buffering also has disadvantages, of course: One thing I already said, your changes are not seen by others until you save. This also means,in case of power outages or shotdowns the changes are gone. Another disadvantage is, that TABLEREVERT can not be used in ways to cancel step by step as you know from Word or an imaging/drawing software, in reverse chronolgical order, but you can revert single records (in any order) or you can revert the whole buffer. So also the buffers means needing to keep track of changes, if you want to give users the feature of a multi step cancelling.

But the easiest case of a single record cancelling is the easiest thing to do with buffering.

Buffering has the advantage to work with tables you USE, with updatable views (local or remot) querying data with sql or with sqlexec cursors (once you set some cursor properties you also set for updatable views) and with cursoradapters. In short with everything except with cursor you generate via SQL-SELECT INTO CURSOR. You can make those cursors editable via READWRITE clause, but you need to put back changes via SQL-INSERT/UPDATE/DELETE on your own.

It seems to me you've gone that route and you're therefor lost, well, at least like saving via SQL INSERT/UPDATE/DELETE you also need to do cancelling via these low level commands and you need to keep track of changes yourself. You have to continue that path you began or start from scratch.

Bye, Olaf.

 

[JRB-Bldr]

then i change my mind, instead of clicking save i clicked cancel

A very simple approach to this would be to have a Confirmation Messagebox appear to Confirm that the user did indeed want to Cancel the operation.

If the answer to Confirm was YES, then go ahead.
If the answer to Confirm was NO, then stop that operation before it gets started.

One way to prevent this from becoming a nuisance confirmation would be to set a Form Property 'flag' when something new was done to the table (added/deleted/edited records) and only show the Confirmation Messagebox() asking something like: "You made a Change. Are you Sure you want to Cancel those changes?" and not when nothing was changed.

Another thing that facilitates this is to not make the changes in the "Real" data table, but instead in a Cursor image/copy of the 'Real' table.
Then those changes can be 'thrown away' without impacting the 'Real' table.
And if the changes are Confirmed, then they can be Updated back to the 'Real' table.

Good Luck,
JRB-Bldr

 

[dexterdg]

Wow! thats a GREAT IDEA JRB-Bldr! the later one. Haven't thought of that. Really great! Thanks, Gonna try that.
Sir Olaf, i was thinking of undoing change"s". I knew BUFFERING(), TABLEREVERT() and TABLEUPDATE(). And like you said it undo's a single change. I lacked information in my question, I'll try sir JRB-Bldr idea and see what happens

thanks
Dexter

 

[Olaf Doschke]

Well, the buffer also is comparable to such a image/copy of the real table. A self created cursor has no real advantage. You also still have no undo heap you can use to undo.
Besides BUFFERING() is not a function, it's a reserved word, but neither a function nor a command. Are you sure you don't miss a explanation of how to work with buffers. Do you for example know, how you can also revert a single field of a single record, if you wanted to?

Bye, Olaf.

 

[Mike Lewis]

I agree. Updating a cursor "image" has no advantage over buffering, and would probably need more code.

On the other hand, if you understand the concept but don't feel comfortable with buffering, then by all means go for it. But in general I would think buffering was the preferred approach.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[dexterdg]

i was thinking of undoing change"s".

Q: can buffering undo a number of changes?

i use to set CURSORSETPROP("BUFFERING", 3)
and in every changes i issue TABLEUPDATE(.t.)
in every revert i issue TABLEREVERET(.t.)

will this do in "UNDOING" of a number of changes? e.g. a person added some record, deleted some, edited some, and then revert it all?

i tried the cursor idea and it works, just have to add READWRITE to my cursor query, SELECT MyDBF, ZAP MyDBF, APPEND FROM DBF(MyDBF2). Thats what i did. and i dont feel good about it, also i have some issues to solve regarding of the deletion of record in the grid. i can only return .ACTIVEROW w/c returns NUMERIC and compare it to the auto increment id on my table but since i made a cursor, it has different auto increment id. is there a way to work around it?

thanks
Dexter

 

[Mike Lewis]

Q: can buffering undo a number of changes?

A: Yes. Set the buffer mode to 4 or 5 (table rather than row buffering). When you do, TABLEUPDATE(0) will commit one record at a time; TABLEUPDATE(1) will commit all the changed records in one swoop. TABLEREVERT(.F.) will revert the current record; TABLEREVERT(.T.) will revert all changed records.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike already has answered your main question. Let me point out an even more important thing:

TABLEUPDATE() can do more than APPEND FROM DBF(MyDBF2). With append you only will add new rows, TABLEUPDATE can insert, update and delete rows your users inserted, updated or deleted while editing the buffer. APPEND does not merge changes to ID2 at that record. You even duplicate records, if you append unchanged records.

Bye, Olaf.

 

[dexterdg]

Ok, so another problem emerges. buffering works fine now but i can't update my grid, i have to update it first so it will display the added record on the grid. Can i do that updating the records on my grid with the AED-ed records before the updatetable() ?

thanks
Dexter

 

[Olaf Doschke]

How can't you update your grid? You can insert into a buffered table, new records are only added to the tablebuffer until a TABLEUPDATE(). I think you still have a misconception about what the buffer is. If you start table buffering a buffer is added to the workarea (not the dbf), it starts empty and with table buffering will not only buffer a current record, but anything you change, any record you change, any record you delete (including both deletion mark and changes before deletion, and any record you add, too, no matter if by INSERT or by APPEND BLANK.

Set Multilocks On
Create Table d:\temp\mytab.dbf (field1 C(10))
CursorSetProp("Buffering",5,"mytab")
Insert into mytab values ("test")
Append Blank
Select * from myTab

What you'll see with the Select * is, that both new records are not yet comitted to the DBF file, it's still empty.
Your grid bound to "mytable" will show the new records already. Acting on the alias "mytable" also looks into the buffered changes. Also look into getfldstate() of new records.

Since VFP9 you cn also decide SQL should also include buffered changes, if you do

Select * from myTab with (Buffering=.t.)

Your grid may just need a Refresh() or SetFocus(), that's all.
Bye, Olaf.

 

[Olaf Doschke]

And to stet the obvious: Bind the Grid to RecordSource = "mytable" and RecordsourceType 0 (Table) or 1 (Alias).

Bye, Olaf.

 

[Olaf Doschke]

It's just now I see you wrote SELECT MyDBF, ZAP MyDBF, APPEND FROM DBF(MyDBF2) and meant

SELECT MyDBF
ZAP MyDBF
APPEND FROM DBF(MyDBF2)

Now imagine multiuser, initial state: Table has several redords user1 starts editing, user2 at about the same time, before user1 saves with your ZAP/APPPEND method. User1 commits hos changes, among them is eg a change in record 4. User 2 does edit other records than 4, adds a few new, perhaps. When user2 finally commits, record4 is back to it's initial change(!)

Your way of doing this is therefore not at all good in multiuser environments.

Besides do this with a table havin 100MB data. You even don't want to read all 100MB into your cursor to finally ZAP the central DBF and refill it.

Besides the performance degradance, every edit has the chance of losing much data, if not even all. In case of a crash after the ZAP your DBF is empty, in case of a crash during the longer and longer taking APPEND your DBF is filled with some, a quarter, half the original data.

You may find this easy and nice for small tables seldom changed by administrative users, but ZAP/APPEND is not a solution for the DBF, it's a problem. ZAP/APPEND is fine for a grid cursor only having a few records in it and needs to be refreshed with newly queried data from the central DBF, as a solution to grid reconstruction, so it's a solution for handling the client side portion of data, but never a solution for the server side DBFs.

Bye, Olaf.

 

[dexterdg]

I like it when you answer my queries. I always learn something.
Gonna try it out

thanks
Dexter

 

[dexterdg]

That "WITH (Buffering=.T.)" is a LIFE SAVER! wooo!

thanks
Dexter

 

[Olaf Doschke]

Well, why do you need it, we lived 10-20 years without it. As the buffer is part of the alias you let a user or code work on, you already have that data, why do you need to query data you already have in an active workarea?

I can add, that I use that clause, too, but not for a standard case. Never mind, if it helps you, it helps you, but perhaps you are doing something, which can be done easier.

Bye, Olaf.

 

[dexterdg]

I was practicing and i made a system that is like a reimbursement form. whereing you can add the {tbl_customer}emp_id, emp_name, deyt, and taym. also {tbl_reimburse_items} item_name, item_code, item price.

After filling out the textboxes for tbl_customer, user will now populate the grid with the AED of items. i used it in the part where i refresh the list on the grid whenever AED is executed. i cant repoputale the items on the list with a cursor[a table:SQL INTO CURSOR:] w/o that "WITH (BUFFERING=.T.)" because of the buffering of the table. it doesnt update[get the updated] --the executed AED.

haha i find it humorous with the "if it helps you, it helps you, but perhaps you are doing something, which can be done easier." maybe there is its just i code a little messy thou it worked thanks!

thanks again
Dexter

 

[Olaf Doschke]

It seems you use RecordsourceType 4 = SQL Statement. Use Alias instead. You bind the grid to the alias and it shows what's in the buffer. There is no need to copy data you already have in an alias into a grid cursor.

Bye, Olaf.