Default value in a look-up table

[AndrewMozley]

Trying to work out the best way of handling this.

I have a table of currencies, which contains currency_identifier (e.g. EUR), a description (e.g. Euros), an exchange rate (1.42) and a few other things, like the code for a nominal account to which gains / losses on that currency are to be posted.

All installations have a default currency, which is most commonly £ - Sterling - but it does not have to be.

Several tables, like the customer and supplier tables, have a reference to this table in each record - so, there may be suppliers who present their invoices in Euros. For the form which maintains such a table, there is a combo box which allows the user to select a currency; this offers all currencies, including the home currency.

I had rather wanted the currency_identifier to be blank for the home currency. So, on several screens I only wish to display the currency code if it is foreign.

Coming back to the currency table, XCURR.DBF, I had tried to ensure that the currency codes are unique by making the currency identifier (EUR &c) a candidate key. But this creates a problem: when the user wants to create a new currency, I would normally append a blank record to XCURR, start a transaction and then display a form with several text boxes bound to control sources in the XCURR table, until the user says Save or Undo.

But (as I have it currently set up) this causes a problem when I try to APPEND the blank record for editing, since a record with a blank currency-identifier already exists.

What I currently plan is to make the currency_identifier key just a regular index - then when the user clicks on ‘Save, check for a duplicate in that field.

Feel I am making rather heavy weather of this – can anyone think of a better approach - thanks, Andrew.

 

[Olaf Doschke]

Buffering is the solution.
If buffering is on for the workarea APPEND BLANK does not yet trigger creation of a record nor index nodes and thus does not violate the candidate key.

Bye, Olaf.

 

[Olaf Doschke]

Besides buffering also solves to easily decide for save (tableupdate) or undo (tablerevert).

Bye, Olaf.

 

[AndrewMozley]

Thank you Olaf. That solves my problem.

I had been relying on BEGIN TRANSACTION : END TRANSACTION / ROLLBACK to handle the business of saving or undoing table updates. I appreciate that this may apply to more than one table (all the tables which get altered during the transaction). Is this too crude a tool to use; that is, should I be using pessimistic buffering and transactions.

There is a lot of material on this subject! I will read it again and try to get my head round it!

Thanks again. Andrew M.

 

[Olaf Doschke]

You can do optimistic buffering, there is no need to lock a new record.
Pessimisitc buffering only makes a difference in editing a currency. When will this ever happen?
Even in case some countries will revert to their own currencies from EUR, there still will be Euro, and it will seldom change it's name or identifier.

You can always combine buffering and transactions, though they only play a role in making changes to multiple records of a single table at least. There is no need for a transaction on a single record of a single table.

Bye, Olaf.

 

[Mike Lewis]

Andrew,

Not only is buffering the correct solution, but transactions is not correct - at least, not the way you have described the scenario.

You wrote:

I would normally append a blank record to XCURR, start a transaction and then display a form with several text boxes

This is bad practice, because you should always aim for a transaction to last the shortest amount of time - and that means there should be no waiting for user interaction while a transaction is in force. The reason is that a transaction applies extensive locks, and other users risk being locked out as a result.

If you follow Olaf's advice re buffering, you shouldn't need to apply a transaction while the form is being edited.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Indeed I overlooked that you start with the transaction. Mike has an important point.

A transaction is really just covering the change of the database. So it covers the saving of changes. The only reason you would do such a transaction is you want the full lockout of others. But with a traqnsaction you lock out any other edits, also of other records. A transaction once started already means no write access to involved table(s). To begin with there is no change, so the transaction like a pessimisitc buffering really just starts with the first editing keypress, but it locks the whole table at that moment. Not so importatn with a few records currency look-up table, but in general transactions should be additional to buffering and start just before saving and you end or rollback after the save, depending on conflicts you want to address or not.

It's quite natural you thought transactions are used the way ouy do, because without being aware of the different.buffering modes just starting editing already is about changes of the database. Here VFP is not normal, the whole other database world is acting different, there is no USE of a table of SQL Server. The closest are ADO recordsets with Cursorlocation adUseServer, where changes directly affect whatever the ADO recordset connects you to. But the normal way is loading data into some local thing like collection, array or even inside html form elements and store them back in a transaction. So the database change happens in a more or less complex save routine, which starts, manages and ends the transaction in a commit or a rollback. All editing is done outside of the database at an applications client tier.

Bye, Olaf.

 

[AndrewMozley]

There is a form to maintain a single table XCURR with just two fields – code and desc. There is an index to XCURR using the KeyField of Code.

The form calls CURSORSETPROP(“Buffering”,2, ALIAS()) - but it could be changed to another form of buffering if that would be better.
There are two existing records in table XCURR, one with a blank code (indicating the home currency), and another with Code = “USD”.

When the user wants to create a new currency, he clicks on a button “Add new record”; the form executes an APPEND blank instruction. Because buffering in in operation, this does not cause an error.

The user then enters a new currency (say “YEN”) onto a text box whose control source is XCURR.code.

I would like to validate that field when the user presses TAB. To that end, txtCode.valid() effectively executes the instruction SEEK This.value: It would then reject a blank value or a value of “USD”.

However this SEEK instruction causes an error “Uniqueness of Index X1Curr violated”. I imagine that this is because the SEEK instruction is causing the XCURR buffer to be flushed with its original (blank) value.

I realise that I have not helped matters by allowing (one) blank value for Code in the XCURR table. But is there a way that I can check that the new value of ‘YEN’ does not already exist in this table, without causing this index validation error?

Thanks.

 

[Olaf Doschke]

You have to use table buffering and not row buffering (5 instead of 2). Otherwise you save in the moment you leave a row.

Also a SEEK of a value finds it in the new record. That logic of finding a violation before saving has to be changed. You can use the INDEXSEEK() function, allowing to seek a value in the index without moving to the corresponding record. You also need to make sure you find another record already having the new currency identifier/code. You also don'T say when exactly you apply the buffering to the cursor. This setting can get lost after closing and reopening a DBF, also it must be set before APPEND BLANK.

Besides, doing any command expecting an cAlias parameter with ALIAS() is indicating you don't understand the nature of that parameter and the nature of ALIAS(). ALIAS() gives the currently selected or active workarea, all such functions also work on the current workarea, when you don't pass in that parameter, so you don't gain anything from passing in ALIAS(), as that is the alias of the current workarea only. It only makes sense if you store ALIAS() of the current workarea into a variable for later use, even after switching workareas you still can make settings to the workarea you initially memorized with ALIAS().

Bye, Olaf.

 

[Mike Lewis]

Andrew,

I suspect this error is occurring because the Seek is moving the record pointer, which causes the buffer to be committed. It is like doing an implicit TABLEUPDATE(). This in turn is happening because you are using row buffering. The solution is to use one of the table buffering modes, that is, 4 or 5.

In other words, change 2 to 4 or 5 in your CURSORSETPROP(), and that should get you past this error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Just one thing: You have to SET MULTILOCKS ON to be able to use table buffering modes, as table buffering causes multiple locks of all the edited rows. Using INDEXSEEK is an alternative not moving the record pointer, but I'd prefer table buffering, too.

This demos how to check for candidate index violation before saving:

Cd GetEnv("TEMP")

*Init
Create Database buffertest2016
Create Table currencies (cCode C(3), cDescription C(50))
Index On cCode Tag xCode candidate

Insert Into currencies Values ("", "Pound Sterling")
Insert Into currencies Values ("USD", "US Dollar")
Insert Into currencies Values ("EUR", "Euro")
Use
* End of Init

Set Multilocks On
Use currencies In Select("currencies")
CursorSetProp("Buffering",5,"currencies")

Local lcNewCurrency, lcDescription && for demo useing a variable insteadd of a contrrol THIS.VALUE

lcNewCurrency = "YEN"
lcDescription = "Japanese Yen"

Append Blank in currencies && no index violation
Replace cCode with lcNewCurrency, cDescription With lcDescription in currencies

If Indexseek(lcNewCurrency,.F.,"currencies","xCode")
   MessageBox(lcNewCurrency+" already exists")
   TableRevert(.F.,"currencies") && deletes the new row(!)
   *so this goes back to before APPEND BLANK, not to the blank state!
Else
   If TableUpdate(0,.F.,"currencies")
      MessageBox(lcNewCurrency+" was stored")
   Else
      MessageBox("Storing "+lcNewCurrency+" caused some conflict.")
   Endif
EndIf

lcNewCurrency = "USD"
lcDescription = "US Dollar"

* Repeated code (could also do with YEN of course, to demonstrate a fail).
Append Blank in currencies && no index violation
Replace cCode with lcNewCurrency, cDescription With lcDescription in currencies

If Indexseek(lcNewCurrency,.F.,"currencies","xCode")
   MessageBox(lcNewCurrency+" already exists")
   TableRevert(.F.,"currencies") && deletes the new row(!)
   *so this goes back to before APPEND BLANK, not to the blank state!
Else
   If TableUpdate(0,.F.,"currencies")
      MessageBox(lcNewCurrency+" was stored")
   Else
      MessageBox("Storing "+lcNewCurrency+" caused some conflict.")
   Endif
EndIf

If you comment out the Init part you can repeat that and all tries to repeatedly insert YEN and USD are handled. On the other hand instead of INDEXSEEK before TABLEUPDATE you can also handle the index violation save conflict. Eg comment out the indexseek an only take the ELSE part trying to save with Tableupdate. It'll say "Storing ... caused some conflict" and you can use AERROR to find out what. At that stage you still can do TABLEREVERT, ie after TABLEUPDATE returns .F. you may or may not display the cause of the save error to the user and offer to edit or revert the new currency record, still no harm done, as the record is rejected in any case.

Bye, Olaf.

 

[Olaf Doschke]

To see what I mean with preferring table buffering over indexseek, you can let the tabeupdate do the check of validity without first making sure the value isn't already in the candidate index:

...
lcNewCurrency = "YEN"
lcDescription = "Japanese Yen"

Append Blank in currencies && no index violation
Replace cCode with lcNewCurrency, cDescription With lcDescription in currencies

If TableUpdate(0,.F.,"currencies")
   MessageBox(lcNewCurrency+" was stored")
Else
   MessageBox("Storing "+lcNewCurrency+" caused conflict: "+laError[AERROR(laError),2])
   TableRevert(.F.,"currencies")
Endif

You see no INEXSEEK and you still find out you can't store the YEN as already existing currency (or any other conflict, that may occur at Tableupdate, like a lost connection).

The difference is about the same as with exclusive access: You rather try exclusive access and catch the error instead of trying to find out, whether someone and who has a DBF opened. The approach simply using Tableupdate() to see whether saving works or not also has the advantage to taking every conflict into account, that you even can't foresee by making the INDEXSEEK. Like a check of current use of a file can be invalid in the next moment - no matter how improbable - the currency can become present in the DBF between the INDEXSEEK and the TABLEUPDATE. So while your case is not about a frequently growing table thousands of users add to all the time, it's in general of no value to make all validity checks the database does before saving. You define a candidate index in the database table to let it react and prevent such wrong data in the dbf.

Bye, Olaf.

 

[AndrewMozley]

Thank you Olaf and indeed Mike. Happy to change to Buffering = 4 or 5, (although that did not appear to eliminate the error when I did a SEEK).

What did work, and allowed me to check for a duplicate in the Valid() method, was the IndexSeek() function : it did not cause a flushing of the buffer and the consequent violation error. So that was successful

I take your point that TableUpdate() can be used to do a test for errors (including the duplicate key); also that it will prepare an error message to be passed to the user. However I am inclined still to include the IndexSeek at the time of data entry, to alert the user as soon as possible.

Andrew

 

[Olaf Doschke]

>although that did not appear to eliminate the error when I did a SEEK
Then you still haven't successfully set up table buffering.

If a table is buffered with multilocks a SEEK away from the current row will not cause an implicit save, as it does with row buffering. You may have the form and DE settings for buffering in conflict with the CURSROSETPROP.

Bye, Olaf.

 

[AndrewMozley]

Multilocks are ON. Table buffering is 4 (pessimistic table ). Data Environment Buffer mode over-ride is 4 (pessimistic table). The table has been packed and re-indexed. Buffer mode is 1 for the form (pessimistic). However, a SEEK on that table causes an Index violation.

Don't worry about it; your IndexKey() solution has worked and I am grateful for that.

 

[Olaf Doschke]

I know you say you don't care, but simply out of curiosity simply right before the SEEK do a [tt]MESSAGEBOX(CursorGetProp("Buffering","XCURR"))[/tt], does it show 4?

Weren't you talking about two forms? Do they share the datasession? A buffer is bound to the workarea, not to the dbf, so having the table open in two datasessions each has its own setting and its own buffer, too.

Besides, if you use the DE buffermodeoverride, the form setting has no effect per definition of the meaning "override" and also what you do with CursorSetProp then is unnecessary. You might use neither DE nor form settings but only CursorSetProp, I never found the form and DE settings to be useful, as they are acting too global, though the override is per DE table. The form setting rather misguides you in thinking you know what buffering is set and setting the buffering in code you can put all the settings you do in one method, giving the perfect overview about which table is buffered in what way. The visual objects don't give this overview, as you need to inspect each of them and then are still not sure what other code does after the initial phase of AutoOpen is done.

Bye, Olaf.

 

[mjcmkrsr]

Hi Olaf,

If a table is buffered with multilocks a SEEK away from the current row will not cause an implicit save, as it does with row buffering.

May be, but your code also throws the index violation error if you SEEK() or SEEK

hth

MK

 

[Olaf Doschke]

No, not before you save. I just used Indexseek to not seek away from the new record I want to save.
Would you dare and show what you mean by modifying my code?

Bye, Olaf.

 

[Olaf Doschke]

OK,

now you got me, MK. I simply changed the Indexseek lMovepointer parameter from .F. to .T. and there it is, also SEEK or SEEK() create an immediate index violation error message. That means the CDX is updated even before the DBF is (or is not) updated from the buffer. It makes no sense, that would mean the index candidate rule is checked before a final decision on saving the record or not is done, before the record gets its record number.

If you do all this in a browse window, add a buffered record and want to move to another record, the index violation is detected at that very moment, but the RECNO() still is negative, Or it reverts to its previous negative number, as the record is rejected. But why would that happen too soon? If an index node is added to the index, the recno must exist, so any index entry should only happen after the dbf has the new row in it and so the recno is determined. Beforehand only a rule violation check could be done, that's quite stupid. Or is the CDX also updated "buffered"? Then an index leaf node must be able to store a negative record number.

What you can do to fix this premature check is defining the candidate index [tt]For Recno()>0[/tt]
I'll sleep a night over judging, if this makes sense at all, as filtered indexes are not used for query optimization, this index would be degraded to only be valuable for the candidate check.

Bye, Olaf.

 

[mjcmkrsr]

Hi Olaf,

Sorry

Maybe Tamar could share her knowledge on this behavior. I read the topics on INDEXSEEK() and GETNEXTMODIFIED() in VFP HG. No clue.

MK