TABLE UPDATE PROBLEM- VISUAL FOXPRO 7

[IvySoe]

I am using Visual Foxpro 7 and foxpro database and foxpro exe is placed on server. user create shortcut from pc to server to use the application.

When updating multiple records of different tables from foxpro Form Save button, data of quantity field holding numeric value of quantity cannot update for some records of these tables.

for example: There is sales invoice sold multiple items - take out quantity from related sales order and good receive.

When delete these multiple sale items from sales invoice, the quantity will be restore back to each sales_order table , and good_receive table relating to individual items.

For the sales invoice of total 10 items which is 10 details records, when delete 5 items, only 3 items of related Sales order and good receive quantity can be restore back and other 2 record never update quantity in sales order and good receive table.
There is no error came up. only data is incorrect in Sales order and good receive table.
I add my code block of processing tables like TableUpdate() in begin and end transaction block along with Aerror() to catch the error. Tables are set buffer mode in form dataEnvironment.
This problem only happen of some records under the same document while the deletion done at the same time for all 5 records. only 2 records out of 5 are wrong.

The problem might be server client connection or any other memory problem or something like this. and i cannot trace it.
Please anybody can help me out.

 

[Olaf Doschke]

It would help t see your code.

>There is no error came up.
A Tablepdate() never causes an error, it returns .T. for success and .F. for conflicts.
What buffer mode do you use? table buffering? How did you set WhereType?

If two transactions start from a certain start quantity and one tableupdate is done after the other you have no conflict, but you're subtracting two quantities frrom the original quantity instead of sequentially processing the subtractions. Transactions alone will not help you here, as they don't start when reading in the original value. With WhereType 3 (key and modified fields) an update of a quantity will cause a conflict, if the quantity has changed from another TableUpdate(), then you'd detect the problem you have.

Bye, Olaf.

 

[IvySoe]

1. tables in dataEnvironment buffer mode is 5.
2. two tables (itrd_fil, itrh_fil) are set buffering mode in Form.init event with
=CURSORSETPROP('Buffering', 5, 'itrh_fil'), =CURSORSETPROP('Buffering', 5, 'itrd_fil') and not included in dataEnvironment.

Following is code block in Form->Save button click event

/****others code doing checking such as restrict empty item, less than zero quantity...
begin transaction
wsuccess = TABLEUPDATE(.T.,.T.,'pgrh_fil') .AND. TABLEUPDATE(.T.,.T.,'pgrd_view') .AND.;
TABLEUPDATE(.T.,.T.,'ppoh_fil') .AND. TABLEUPDATE(.T.,.T.,'ppod_fil') .AND.;
TABLEUPDATE(.T.,.T.,'mptd_fil') .AND. TABLEUPDATE(.T.,.T.,'itrh_fil') .AND. TABLEUPDATE(.T.,.T.,'itrd_fil')
update pgrd_fil set pgrd_line = pgrd_dmln where pgrd_trncd = pgrh_fil.pgrh_trncd and ;
pgrd_docno = pgrh_fil.pgrh_docno

WS2 = TABLEUPDATE(.T.,.T.,'pgrd_fil')

if glErr OR !(wsuccess .AND. WS2)
MESSAGEBOX( 'One or more of the tables failed to update. Cancelling transaction.')
rollback
glErr = .f.
TABLEREVERT(.T.,'pgrh_fil')
TABLEREVERT(.T.,'pgrd_view')
TABLEREVERT(.T.,'pgrd_fil')
TABLEREVERT(.T.,'ppoh_fil')
TABLEREVERT(.T.,'ppod_fil')
TABLEREVERT(.T.,'itrh_fil')
TABLEREVERT(.T.,'itrd_fil')
TABLEREVERT(.T.,'mptd_fil')
ELSE
end transaction
endif

 

[Olaf Doschke]

You use Tableupdate with lForce=.T. option, you'll never get conflicts reported.

Bye, Olaf.

 

[IvySoe]

I will try with IForce=.F.

FYI:
the process is : When deleting records from sivd_fil (has reference record in pgrd_fil, and ssod_fil)
1. look for related record in pgrd_fil,ssod_fil and restore back quantity (did not delete record or anything else, just seek and update data if found)
2. then delete (mark as delete, records can still see with set delete off) in sivd_fil
pgrd_fil and ssod_fil are used to seek record and update one data field of that record.

I write on error function call in Save click event
on error lErrHlr()
public glErr, gcErrMsg
gcErrMsg = ""
glErr = .f.

--and function in another main prg

function lErrHlr()
local laErr(1)
aError( laErr )
gcErrMsg = laErr(2)
glErr = .t.
messagebox( gcErrMsg )
RETURN

 

[mjcmkrsr]

Hi,

Besides your chaining is wrong. ROLLBACK comes AFTER END TRANSACTION. See pseudocode below (from Hacker's Guide to VFP 7)

* Save invoice header and details in a transaction.
* This is only a sketch of the real code, which would
* probably include various error checking and handling.
BEGIN TRANSACTION
 
lGoOn = .T.
 
SELECT header
IF NOT TABLEUPDATE()
   lGoOn = .F.
ENDIF
 
IF lGoOn
   SELECT detail
 
   IF NOT TABLEUPDATE(.t.,.t.)
      lGoOn = .F.
   ENDIF
ENDIF
 
IF lGoOn
   END TRANSACTION
ELSE 
   ROLLBACK
   =TABLEREVERT(.f.,"header")
   =TABLEREVERT(.t.,"detail")
ENDIF

Explanation (from same book)

You may be confused by the calls to TableRevert() after the Rollback—we were at first, too. What's going on here is that updating is normally a two-step process. The user updates the buffers by making changes (through whatever interface you provide). Then, calls to TableUpdate() copy the changes from the buffers to the actual tables. When you use transactions, you add another layer to that. TableUpdate() copies the changes from the buffers to another set of buffers (call them "transaction buffers") and END TRANSACTION copies from those buffers to the actual tables.

When you issue ROLLBACK, the changes are cleared from the transaction buffers, but the original buffers still contain the changed data. The TableRevert() calls discard those changes. In a real application, you probably wouldn't just give up and revert your changes like that, but would try to solve whatever problems prevented the save, and try again.

hth
Marc

 

[IvySoe]

Thanks Olaf and mjcmkrsr, I update my code as your advice and will wait and see the result a few days. and will post the result again.

 

[mjcmkrsr]

Hi,
You might want to try this code

begin transaction
wsuccess = .T.

if not (TABLEUPDATE(.T.,.F.,'pgrh_fil') ... )
[indent]wsuccess = .F.[/indent]
endif

if wsuccess
[indent]update pgrd_fil set pgrd_line = pgrd_dmln where pgrd_trncd = pgrh_fil.pgrh_trncd and ;
pgrd_docno = pgrh_fil.pgrh_docno[/indent]

[indent]If not(TABLEUPDATE(.T.,.F.,'pgrd_fil')[/indent]
[indent][indent]wsuccess = .F.[/indent][/indent]
[indent]endif[/indent]
endif

if wsuccess
[indent]end transaction[/indent]
else
MESSAGEBOX( 'One or more of the tables failed to update. Cancelling transaction.')
rollback
TABLEREVERT(.T.,'pgrh_fil')
...

endif

hth
MK

 

[IvySoe]

thank you mjcmkrsr. I put end transaction first.

 

[Olaf Doschke]

On error will never occur, also with lForce = .F., if the tableupdate() function returns .F. you have to look into AERROR. If you update many tables as you do the info you get will only be about the last conflict, also without notifying which recno. Take a deeper look into Tableupdate() parameterization, it offers filling an array with record numbers of failing records.

Bye, Olaf.

 

[Olaf Doschke]

Here's a little sample code:

Clear
Cd (Getenv("TEMP"))

Create Table tabStore (iID I, cProduct C(10), iCount I)
Insert into tabStore (iiD, cProduct, iCount) Values (1, 'Nuts',200)
Insert into tabStore (iiD, cProduct, iCount) Values (2, 'Bolts',200)
Use

oUser1 = CreateObject("usersession")
oUser2 = CreateObject("usersession")

oUser1.Removestock('Bolts',150)
oUser2.Removestock('Bolts',100)
? 'This already is the problem: Both users subtract from the same initial stock value 200'

oUser1.Savedata()
oUser2.Savedata()

Define Class usersession as Session
   Procedure Init()
      Set Multilocks On
      Use tabStore
      CursorSetProp('Buffering',5,'tabStore')
      This.Name = This.Name + Transform(This.DataSessionID-1)
   Endproc
   
   Procedure Removestock(tcProduct, tiCount)
      Select tabStore
      Locate For cProduct = tcProduct
      ? This.Name+' changes stock count from',iCount
      Replace Next 1 iCount With iCount-tiCount in tabStore
      ?? ' to',iCount
   EndProc    

   Procedure Savedata()
      ? 'Before transaction '+This.Name+' sees:'
      ? 'direct',tabStore.iCount
      ? 'oldval',Oldval("iCount","tabStore")
      ? 'curval',Curval("iCount","tabStore")

      Begin Transaction 
      Select tabStore
      llSuccess = TableUpdate(2,.f.,'tabStore', laErrorRows)
      ? 'Tableupdate returns:', llSuccess
      If NOT llSuccess 
         ? 'update of record number',laErrorRows[1],' failed.'
      EndIf
      If llSuccess
         End Transaction
      Else
         TableRevert(.T.,"tabStore")
         Rollback
      EndIf
      ? 'After transaction:'
      ? 'direct',tabStore.iCount
      ? 'oldval',Oldval("iCount","tabStore")
      ? 'curval',Curval("iCount","tabStore")
   EndProc    
   
EndDefine

You'll see the problem already arises before starting the transaction, because both usersessions start at the same stock.
Also see doing the update via TableUpdate(2,.f.,alias,laErrorRows) you get conflicting row info.
The way the code is User1 can save, User2 has the conflict. If you let User2 save first, User1 will have the conflict.

One solution to prevent the problem already at the stage of calling Removestock is to set buffermode to pessimistic tablebuffering (4 instead of 5), so locks occur. Then user2 will get an error when trying to removestock.
You can play around with this a little further, ie when user1 buys Nuts and user2 Bolts, there will be no conflict.

Bye, Olaf.

 

[IvySoe]

Hi Olaf,
thanks for sharing.
My problem is happened for single user. Sorry for long post.

1... The grid data is sivd_fil

2....When select grid line and click delete, it gets the record no of selected line of sivd_fil, select ssod_fil and seek sivd_fil.ssod_no and ssod_line, if found() ssod_invoiced_qty =ssod_fil.invoiced_qty- sivd_fil.qty…else show error with messagebox() and return 0
3....Same for pgrd_fil
and other coding to increase and decrease line numbering such as....
in save button-> save buffered tables with above codes

the problem is - when delete 2, 3 ,4 line from sivd_fil-> only line 2 never deduct qty from both ssod_fil and pgrd_fil, but line 3 and 4 is correct.
there is no error shows about "cannot find ssod_no or grnno from ssod_fil,pgrd_fil." as above code not found option.
(the actual data got over 20 lines in sivd, and delete line 15 to 21 , and line 16 and 17 deleted from sivd_fil but did not deduct qty from ssod_fil and pgrd_fil) and there is only one user using the software.

 

[IvySoe]

Hi Olaf,

My understanding of your statement is wssuccess will be .F. if only last TABLEUPDATE() returns .F. for below, am I right?
wsuccess = TABLEUPDATE(.T.,.T.,'pgrh_fil') .AND. TABLEUPDATE(.T.,.T.,'pgrd_view') .AND. TABLEUPDATE(.T.,.T.,'ppoh_fil')
Isn't it like ( .F. and .T. and .T.) will be (.F.) ?
Should I catch return value with individual variable for each TABLEUPDATE() ?

"On error will never occur, also with lForce = .F., if the tableupdate() function returns .F. you have to look into AERROR. If you update many tables as you do the info you get will only be about the last conflict, also without notifying which recno. Take a deeper look into Tableupdate() parameterization, it offers filling an array with record numbers of failing records."

 

[mjcmkrsr]

Hi,

My understanding of your statement is wssuccess will be .F. if only last TABLEUPDATE() returns .F. for below, am I right?
wsuccess = TABLEUPDATE(.T.,.T.,'pgrh_fil') .AND. TABLEUPDATE(.T.,.T.,'pgrd_view') .AND. TABLEUPDATE(.T.,.T.,'ppoh_fil')

No, wSuccess will be .F. if ANY of the TABLEUPDATE() fails.

Should I catch return value with individual variable for each TABLEUPDATE() ?

Have one variable and cascade - it will help you finding out where TABLEUPDATE() = .F. (please see my previous post)

hth

MK

 

[Olaf Doschke]

MK has answered that already, I don't know how you come to your conclusion. The big difference beween your TABLEUPDATES and mine is the parameterization, pay attention to the parameters I used.

a) You use .T. as second parameter, which is lForce, this forces updates to work, even if there are conflicts
b) You try to catch errors with ON ERROR, but no matter how you use TABLEUPDATE, it will never trigger the ON ERROR handler.

MK also is right about splitting up all your TABLEUPDATES. If you do them in one line and AND their results, you get the right wsuccess result, but if that is .F. you have no clue even in which table a conflict occurred. Partly that won't matter, as you choose to revert all changes in that case anyway, also you know you have a problem in the quantitiy fields of some tables, of which records don't reappear via the ROLLBACK. I don't see an error in your transaction start and end points, the observations you have must come from valid successful transactions.

That means your main problem isn't of technical nature (failures in network or file system) but in a logical flaw of multiple users acting on the data. Or it's in your way of acting on the tables within the form, eg havin relations set wrong or replacing when at EOF.

Bye, Olaf.

 

[IvySoe]

I re-enter manually the problem document sales invoice exactly(delete it first and key in again) to generate error again but nothing wrong. so, it is clearly no logic error in coding. and it is single user.
I tested my TABLEREVERT area and AERROR() area by forcing error (eg: force adding duplicate index rows)) and it works , it can get AERROR message.
I wish I can share the whole program and coding.
No clue at all, and I am in trouble. (sob sob sob !_!)

 

[IvySoe]

I get the point getting return value in one line of all TABLEUPDATE() cannot know which TABLEUPDATE() return .F. , I will cascade my TABLEUPDATE(). I will set IFORCE to .F.

But , how comes it didn't add back quantity to ssod_fil and pgrd_fil at the first time, and it is when I key in again second time.
//program code where the quantity is incorrect
select ssod_fil
seek sivd_view.sivd_refcd + sivd_view.sivd_refno + str(sivd_view.sivd_refln,3) &&delete row data
if found()
replace ssod_ivqty with ssod_ivqty - sivd_view.sivd_qty
endif
This part called for every delete row. but , skipped or happened to be "not found" for the problem row.
Now, I added else block for not found condition, updated TABLEUPDATE() with iforce .F., cascade TABLEUPDAET() and have to monitor it happen again or not.

 

[Olaf Doschke]

The only reason something isn't set back is that run of data changes didn't end in a rollback.
Using lForce = .T. (not I, l as logical) is contributing to the fact you never do rollbacks but force data into the dbfs, even wrong and conflicting changes.

Bye, Olaf.

 

[mjcmkrsr]

Hi,

select ssod_fil
seek sivd_view.sivd_refcd + sivd_view.sivd_refno + str(sivd_view.sivd_refln,3) &&delete row data

This piece of code seems wrong to me. You're mixing up workareas. What table are you searching in? SSOD_FIL or SIVD_FIL? You may want to try something like below. Btw please DO have a look at SEEK, SEEK() and INDEXSEEK().

lcStringToSearch = sivd_view.sivd_refcd + sivd_view.sivd_refno + str(sivd_view.sivd_refln,3)
select ssod_fil
If IndexSeek (lcStringToSearch, .T., "SSOD_FIL", YourIndexTag)
[indent]If IndexSeek (lcStringToSearch, .T., "SIVD_FIL", YourIndexTag)[/indent]
[indent][indent]replace ssod_ivqty with ssod_ivqty - sivd_view.sivd_qty[/indent][/indent]
[indent]else[/indent]
[indent]...[/indent]
[indent]endif[/indent]
else
...
endif

hth

MK

 

[Olaf Doschke]

>force adding duplicate index row
That's not happening during Tableupdate, is it?

Bye, Olaf.