Foxpro DBF record locking

[TonyK]

Hi,

Apologies if this in the wrong section but I couldn't see a anything for non SQL databases. I'm pretty new to Servoy but am trying to create a web-client to work along side a Foxpro application.
Much of what I'm doing will be reporting but I want users to be able to edit and add records to certain tables. With this in mind, I've tried using the following to lock a record, using the onRecordEditStart event.

Code: Select all

var success = databaseManager.acquireLock(foundset,0,'GrpRec');
if(!success)
{
  plugins.dialogs.showWarningDialog('Alert','Failed to get a lock','OK');
}

I can see the record lock in the Server Administration but my Foxpro app is not and so using the separate interfaces it is possible to edit the same record.
Equally if I lock a record through editing in the Foxpro app, the above snippet still thinks it has successfully locked the record and both users can edit.
That said, if I have a record locked from the Foxpro app, when the onRecordEditStop event triggers the databaseManager.saveData() an error is produced in the console.

Cannot save form data
> com.servoy.j2db.dataprocessing.DataException: Record of 2 in Group has been locked by another process

So I guess I'm going about my record locking the wrong way, can someone advise me please...

Kind regards,

Tony

[Jan Aleman]

Servoy relies on the driver/database engine for the actual locking and the HXTT driver does not implement that currently. We are currently researching on implementing it ourselves and hope to release that as soon as we get it to work.

[nromeou]

Has this problem been solved? Im having the same issue in a similar situation

[rgansevles]

Nromeou,

This has been fixed in the driver that is bundled with Servoy 6.

Rob

[nromeou]

Rob,
Will it work if i use that driver in servoy 5.2.2?

[rgansevles]

Yes, that should work.

Rob

[nromeou]

Ive been running some tests on this, in servoy 5.2.2 and servoy 6 using 2 different drivers, the one bundled in servoy 6 and another version of the same hxtt driver. Im using VFP 7.0

i wanted to see if either servoy and VPF realize the other is locking a table, so i locked the table on servoy (with an acquireLock) and tryied to get a lock in fox and had no problem, i could edit records, add new ones without any trouble. It just ignores servoy's lock, thing that makes me believe servoy lock is at administrator level or something

On the opposite test, i locked the table from VPF and i saw 2 things. If i do an acquireLock() it returs true, it believes he can lock them (wich is not true cause VPF has them locked) and when the commit is done (Insert) just there i get an exception telling that the table is locked by another process and that it failed to lock the data region.
The other thing i saw is that having the tables locked by VFP, if i execute a loadAllRecords() on that table i also get the failed lo lock data region exception, even if i have no edited records to save (considering loadAllRecords try to save), on the server log i get the lock problem when executing a Select... why couldnt it read from the table?

Code: Select all

2011-11-23 13:29    RMI TCP Connection(3)-10.198.101.151    ERROR    com.servoy.j2db.util.Debug    select num_doc, serie_doc from numcomp order by num_doc asc, serie_doc asc parameters: <null>
java.sql.SQLException: Failed to lock the data region[2147483646,2147483647] of numcomp.dbf

Is this the way i should try this? perhaps im doing it wrong i dont know, what can i do in order to solve this? what was solved in the bundled version of the driver?

thanks in advance

[omar]

Hi nromeou,

I just tested this with VFP 9.0 and got the same results. When locking between Servoy SmartClient and/or WebClients it works fine, when locking between VFP versions it also works fine. I am guessing this is due to the fact that the lock is not actually physically placed in the table but is an administrative lock instead.

You can mimic VFP's behaviour by adding a custom column for flagging locks. However this will not produce exactly the same result because if you kill VFP from the task manager the lock is released and a custom flag will stay up.

If you realy, realy need to get this working the way you would expect you could consider creating a vfp com component and let it do the locking instead. But I wouldn't be surprised if it didn't work the same there either.

With an ansi-standard SQL database with natively built-in transaction support you will not have these kinds of problems but that may not be an option for you.

Greetings,
Omar

[nromeou]

omar,
thanks for the answer, i can discard now that my problem was the VPF version. Anyway due to proyect planning i cant move to an sql database right now, it is planned to do so on the future in the final steps of the proyect. For now i should have coexisting a VFP application and my servoy solution over the same DBFs and im having those lock problems.
I want to know what can servoy ppl tell me about this, in case i cant solve this probably we'll have to change our proyect plan.

thanks

[rgansevles]

nromeou,

Do you do the acquireLock in Servoy within a transaction?
And have you set the servoy.record.lock.lockInDB property to true (see the admin page).

Only when these 2 are done, will Servoy lock the data in the db.

Hope this helps

Rob

[nromeou]

Rob,

ive tryied this and still get the same results, neither of them (servoy or VFP) realizes the other has a lock. Servoy only realizes when the commit is done or when a loadAllRecords is executed. But both of them still return true when getting the lock even if the other has the lock.

Ive done a small solution to try this, do u think i should make a case and give you this solution along with the dbf?
i dont know perhaps im doing something wrong

thanks

[rgansevles]

nromeou,

What does your jdbc url look like, do you use the lockType=VFP argument?

Rob

[nromeou]

jdbc:dbf:/c:\labprado\labprado\data?lockType=vfp

thats my url, could the lower case be the problem or it makes no difference?

[rgansevles]

Maybe, try the uppercase variant.

Rob

[nromeou]

i was just about to do it, thanks for the fast reply

i wonder anyway, if this is the problem, shouldnt i get an error or something in the url? is it possible that it passing an invalid argument it still works?