none
Stop to enter null values in Free table RRS feed

  • Question

  • Dear all 

    Please find below two screen-shot and help me to solve out this problem

    when i have run my  voucher entry program some time this type of data is add in database how to stop them to add in database  


    Tuesday, March 12, 2019 4:39 AM

Answers

  • When you buffer, TableUpdate() tells you whether saving succeeded.

    And not using any buffering and directly changing dbf with every control focus change (and thus passing valid event) when binding to a dbf directly unbuffered causes much more lock/unlock actions and induces the problem you want to avoid.

    So what seems the best way to not miss any edit by not buffering, causes the most often DBF corruptions or - less fatal - lock situations (used by another user), as now every focus change writes back changes to the one field to the DBF, locks the files, writes, unlocks the files.

    As writing needs the exclusive file handle able to write, VFPs shared mode works by every vfp session only getting a read handle and when writing, no matter if you yourself locked with flock or rlock, you cause an automatic file lock and VFP swaps to a readwrite filehandle temporarily, then writes, then swaps back to a readonly file handle. Doing that more often by not buffering and deciding when to TableUpdate() you more often have locks and also file corruptions.

    I wonder when that willl finally become general knowledge. It's really complicated, but it all roots in the circumstance of the Windows file systrem, there is no real shared write or shared readwrite access to files. If you use the low level file functions of VFP, FOPEN() pretty much maps quite 1:1 to C++ fopen. Look up the function and try the follolwing:

    1. create a TXT file to start with (with notepad, whatever).
    2. FOPEN() that file in one VFP session so you can write to it, that means nFileAttrtibute parameter has to be 1,2,11 or 12. 
    3. try to FOPEN() that same file in another PC with VFP or at least another VFP session (start VFP once more) again using any mode including write access. You won't get a file handle, you always will get -1.
    You only get secondary filehandles (meaning shared access), if all VFP session only FOPEN in read mode.

    The workarea concept was introduced by the inventors of VFP (Foxpro 1, really, by Fox Software) to work around that problem, it's actually managing a file access handle for you and more, like the concept of RECNO(), BOF(), EOF(), etc. and all the properties CURSORSETPROP() can set.

    RLOCK() is just making an agreement, if you take FOPEN() you still can write into the bytes of a locked record. And no, that's not suggesting to go that route to solve the problem and circumvent the writing problem, you still only get a file handle with write access if no other VFP or other process has a writable file handle.

    What RLOCKs help with is potentially reserving write access, unless you have power outage, network outages, router buffer errors, many more things not letting these "lock agreements" be seen by other VFP processes. Or even let them be seen longer than they actually should last, i.e. the info about the UNLOCK fails to cascade to other network nodes.

    Overall I made the best experiences with optimistic table buffering, meaning no locks but automatic locks and locks only when my code does Tableupdate() and not just with any control focus change or record skipped (as in record buffering).

    And yes, even with buffering you can have the same "in use by another user", because you never can avoid the concurrent write situation nor network problems about the propagation of lock states. So VFP pretty much relies on a stable good network and routers/switches should be configured for write through caching at least, there is no good case for write caching, that is causing the problem of unlocks not becoming known.

    Bye, Olaf.


    Wednesday, March 27, 2019 4:18 PM
  • Finally, extra separate:

    You can't force the save of data, even not with the Tableupdate lForce parameter, if a file is in current write use by another user. You have to deal with that error by retrying or offer a cancelling out of saving.

    Without buffering that means you can't really cancel out, all changes before the one now failing already made it to the DBF/FPT/CDX. Buffering helps to keep a more consistent state, including the buffering of new records until they are saved once all fields are popuplated with data and not empty.

    You don't show your code, but the empty unit field most likely comes from starting with a default empty field after unbuffered APPEND BLANK. Even though free DBFs have no default values, every data type has one, that's the value you store with APPEND BLANK.

    The only other solution without buffering not leading to such single empty fields will be to only INSERT new records after having all the data you want to save. Then you need an extra mode of your data entry form with unbound controls or you bind to a record object variable you can create in VFP9 with SCATTER NAME and then later save with INSERT FROM NAME. Buffering is easier, as you then don't generate the record in the DBF before TableUpdate.

    It'd be easier to judge, if you post code, but if with buffering before even adding rows you won't get an empty field, so it's almost proven you don't buffer and append blank. APPEND isn't your main enemy, but it is unbuffered. And if you want to stick with unbuffered access and not want empty Unit, then you have to store something to begin with, so you'll have to use an INSERT populating the unit field from the start. Then you'll just still get the problem of a modified unit not getting saved with concurrent write access.

    Just also notice control binding doesn't mean any keystroke gets saved, the controls write back values when focus changes and valid event returns true or a number working like the same number of tabs would. It would be even worse network traffic to change just one byte in the dbf or fpt and cdx. Even in unbuffered binding the unit only is saved and changed from its initial empty state when the control for entering or picking the unit loses focus and the automtaic lock mechanism succeeds. Your only chance for a guaranteed never empty unit is to insert the record with a value, but you can still end up in that default value and that might also not be what the user wanted to save.

    So to only get the records initially as the user wnats them is to either first collect your data and INSERT or only APPEND BLANK buffered, which does only generate a record in the local buffer, not in the DBF file.

    All these things are a strong reason to even go one step further and never bind to DBFs directly, neither unbuffered nor buffered, but go through more advanced tier architecture at least using views. All the simpler modes are not good for shared data access.

    Bye, Olaf.

    Thursday, March 28, 2019 10:18 AM

All replies

  • The last screenshot shows it's not NULL but empty string.

    Whenever you want a field of a record to not be empty your input control shouldn't allow an empty value. That should be clear, so what really is your question? Whenever you save a record first check this value is not empty. Ideally only allow the user to pick a valid value from a listbox or dropdown combobox and if the value is empty, focus that control, play a warning sound or highlicght the control so the user knows this is missing or wrong, Many ways to do that.

    In a table part of a DBC you can define field rules. In a free DBF you have to check those in your forms yourself.

    A record safe routine should call a check routine and only save records fulfilling the rules of a good record.

    Bye, Olaf.


    • Edited by OlafDoschke Thursday, March 28, 2019 12:05 PM
    Tuesday, March 12, 2019 6:52 AM
  • Dear sir 

    user add full data but some time File is use by another user that error occur and exe is exit and data was enter not properly. 
    • Edited by lfumakiya Wednesday, March 13, 2019 4:43 AM
    Wednesday, March 13, 2019 4:41 AM
  • You don' stop it on the database, but on the entry screen validation.

    Other ideas:

    1. Make unit field numeric instead character (you will avoid empty values, but you will still be able to have 0s
    2. add an =RLOCK() before the saving into the table; this is the safest way.
    3. Do a validation !EMPTY(UNIT)
    Wednesday, March 27, 2019 1:06 PM
    Moderator
  • When you buffer, TableUpdate() tells you whether saving succeeded.

    And not using any buffering and directly changing dbf with every control focus change (and thus passing valid event) when binding to a dbf directly unbuffered causes much more lock/unlock actions and induces the problem you want to avoid.

    So what seems the best way to not miss any edit by not buffering, causes the most often DBF corruptions or - less fatal - lock situations (used by another user), as now every focus change writes back changes to the one field to the DBF, locks the files, writes, unlocks the files.

    As writing needs the exclusive file handle able to write, VFPs shared mode works by every vfp session only getting a read handle and when writing, no matter if you yourself locked with flock or rlock, you cause an automatic file lock and VFP swaps to a readwrite filehandle temporarily, then writes, then swaps back to a readonly file handle. Doing that more often by not buffering and deciding when to TableUpdate() you more often have locks and also file corruptions.

    I wonder when that willl finally become general knowledge. It's really complicated, but it all roots in the circumstance of the Windows file systrem, there is no real shared write or shared readwrite access to files. If you use the low level file functions of VFP, FOPEN() pretty much maps quite 1:1 to C++ fopen. Look up the function and try the follolwing:

    1. create a TXT file to start with (with notepad, whatever).
    2. FOPEN() that file in one VFP session so you can write to it, that means nFileAttrtibute parameter has to be 1,2,11 or 12. 
    3. try to FOPEN() that same file in another PC with VFP or at least another VFP session (start VFP once more) again using any mode including write access. You won't get a file handle, you always will get -1.
    You only get secondary filehandles (meaning shared access), if all VFP session only FOPEN in read mode.

    The workarea concept was introduced by the inventors of VFP (Foxpro 1, really, by Fox Software) to work around that problem, it's actually managing a file access handle for you and more, like the concept of RECNO(), BOF(), EOF(), etc. and all the properties CURSORSETPROP() can set.

    RLOCK() is just making an agreement, if you take FOPEN() you still can write into the bytes of a locked record. And no, that's not suggesting to go that route to solve the problem and circumvent the writing problem, you still only get a file handle with write access if no other VFP or other process has a writable file handle.

    What RLOCKs help with is potentially reserving write access, unless you have power outage, network outages, router buffer errors, many more things not letting these "lock agreements" be seen by other VFP processes. Or even let them be seen longer than they actually should last, i.e. the info about the UNLOCK fails to cascade to other network nodes.

    Overall I made the best experiences with optimistic table buffering, meaning no locks but automatic locks and locks only when my code does Tableupdate() and not just with any control focus change or record skipped (as in record buffering).

    And yes, even with buffering you can have the same "in use by another user", because you never can avoid the concurrent write situation nor network problems about the propagation of lock states. So VFP pretty much relies on a stable good network and routers/switches should be configured for write through caching at least, there is no good case for write caching, that is causing the problem of unlocks not becoming known.

    Bye, Olaf.


    Wednesday, March 27, 2019 4:18 PM
  • Finally, extra separate:

    You can't force the save of data, even not with the Tableupdate lForce parameter, if a file is in current write use by another user. You have to deal with that error by retrying or offer a cancelling out of saving.

    Without buffering that means you can't really cancel out, all changes before the one now failing already made it to the DBF/FPT/CDX. Buffering helps to keep a more consistent state, including the buffering of new records until they are saved once all fields are popuplated with data and not empty.

    You don't show your code, but the empty unit field most likely comes from starting with a default empty field after unbuffered APPEND BLANK. Even though free DBFs have no default values, every data type has one, that's the value you store with APPEND BLANK.

    The only other solution without buffering not leading to such single empty fields will be to only INSERT new records after having all the data you want to save. Then you need an extra mode of your data entry form with unbound controls or you bind to a record object variable you can create in VFP9 with SCATTER NAME and then later save with INSERT FROM NAME. Buffering is easier, as you then don't generate the record in the DBF before TableUpdate.

    It'd be easier to judge, if you post code, but if with buffering before even adding rows you won't get an empty field, so it's almost proven you don't buffer and append blank. APPEND isn't your main enemy, but it is unbuffered. And if you want to stick with unbuffered access and not want empty Unit, then you have to store something to begin with, so you'll have to use an INSERT populating the unit field from the start. Then you'll just still get the problem of a modified unit not getting saved with concurrent write access.

    Just also notice control binding doesn't mean any keystroke gets saved, the controls write back values when focus changes and valid event returns true or a number working like the same number of tabs would. It would be even worse network traffic to change just one byte in the dbf or fpt and cdx. Even in unbuffered binding the unit only is saved and changed from its initial empty state when the control for entering or picking the unit loses focus and the automtaic lock mechanism succeeds. Your only chance for a guaranteed never empty unit is to insert the record with a value, but you can still end up in that default value and that might also not be what the user wanted to save.

    So to only get the records initially as the user wnats them is to either first collect your data and INSERT or only APPEND BLANK buffered, which does only generate a record in the local buffer, not in the DBF file.

    All these things are a strong reason to even go one step further and never bind to DBFs directly, neither unbuffered nor buffered, but go through more advanced tier architecture at least using views. All the simpler modes are not good for shared data access.

    Bye, Olaf.

    Thursday, March 28, 2019 10:18 AM