Oracle Record Locking

Classic Servoy
1

Hi,

I have no experience (yet) with Servoy, i am evaluating and comparing
it to Oracle Forms.

Using an Oracle (10g) backend database.

Can anyone give me an example/directions of locking Oracle Database Records from a Servoy form ?

Tia,

Hans

2

I am not 100% sure what you mean by “locking records from a Servoy form”. If you want to lock a record from within Servoy, you can call databaseManager.acquireLock(). See the documentation or sample code of that method for details.

3

Thanks for the reply,

databaseManager.acquireLock() will lock the record in the context of Servoy only. If i look at the record from within Oracle, it is not locked.

I read something about issueing a " select for update …" but i do not know how to do that in Servoy.

Regards,

Hans

4

You can issue a select for update statement using the databasemanagers function getdatasetbyquery. Make sure to start a transaction with startTransaction before doing so.

5

Hello Jan,

First of all : Great presentation of 3.5 last night, like the new features.

I am still testing the locking mechanism in Servoy.
I am testing the servoy locking now and if i understand that, i will try the Oracle locking.

I start a transaction (via a button) and then there is an onRecordEditStart method :

if(databaseManager.hasTransaction())
{

var success = databaseManager.acquireLock( forms.site_pers.foundset, 0 );

if (!success)
{
plugins.dialogs.showErrorDialog( “Failed to get lock”, “Ok” );
}
else
{
plugins.dialogs.showErrorDialog( “Lock set”, “Ok” );
}
}

I then start two smart clients. I start a transaction in the first one and when i click into a record i get the message “Lock set”, so that’s ok.

I start a transaction in the second one and when i click the same record i get the message “Failed to get lock”, but the focus is on the selected field in the record and i can edit it !!
I would expect that when a lock fails, i can not edit any field in the record ?

Hans Nieuwenhuis

6

Hmm. I think this is expected behavior. If the lock acquisition fails, you have no lock. So why (or what) should Servoy lock then? I think this has to be done / taken care of by you.

7

Thanks for the reply.

When you can not acquire a lock, you do not want the user to be able
to edit the record. Is there an way to do this ?
Somehow remove the focus from the selected record or ??

8

You could set the controller to readOnly or something like that…

9

When i do that, the user cannot edit other (non locked) records in the same form.

So what i need is :

  • Records in the form which are locked by another user are not editable
  • other records can be locked, edited and saved

I am used to this behaviour in Oracle Forms.

Thanks for the reponse.

10

Hi Hans,

Whilst I do not have much experience in implementing Locks, I always believed that, having acquired a lock on a record, editing that record from another client was prohibited !

Search the forum using ‘acquirelock’ and you will get around 10 or so threads discussing locking which seem to bear this out !

Cheers
Harry

11

In that case I’d advise to code it exactly that way:

  1. Set your forms non editable by default
  2. When a user wants to edit try to get a lock and if successful unlock and allow the editting
  3. when save is pressed set the controller back to readonly