how to loop for condition in a function?

Classic Servoy
1

I would like to set a unique serial number value for a record and validate it by a sql-query.
How can I put this in a loop to get the result I need, because I need no loop through records?
Example:

var sn = forms.serialno.order_no
forms.serialno.order_no += 1
var sql = "select uid from orders where orders.order_no = " + sn
var dataset = databaseManager.getDataSetByQuery("db",sql,null,-1)
var rows = dataset.getMaxRowIndex()
if(rows == 0){
 order_no = sn
 return true
}
2

Why would you validate it?
You already seem to have a table which holds the first serial to be issued.

I think it’s better to request a recordlock on the record which holds the serial to be issued.
Once you succesfully locked this record, get the serialnr., increase the number, save the data and release the lock.
This way you’re sure only 1 user will issue the serialnr set in the record…

Hope this helps

3

mboegem:
This way you’re sure only 1 user will issue the serialnr set in the record…

Hi Marc,

I don’t understand how this should save that a serialno is set only once?
The record, in my example on the order table, is locked while getting the serialno.
What is, if I lock the record which holds the serialno and an other user is creating a new order and has issued an increased serialno at the same time?

4

tgs:
The record, in my example on the order table, is locked while getting the serialno.

I think you shouldn’t consider the order record to be the troublemaker at all.
First of all it’s brand new, so there’s very little chance other users will take over.
Second: records are being updated by field, not as a record. So even if a 2nd user would take over this new record, he/she probably won’t set the serialno.

My solution is based on locking the record holding the serialnr. to be issued.
Please read the steps carefully:

  1. Try to get a lock on the record (when this fails you can wait 100mS and try again)
  2. Once you succesfully locked the record: get the serialnr to be issued
  3. increase the serial nr
  4. save data
  5. release locks

The whole trick is captured in step 1.
As long as you’re not able to succesfully do step 1, you won’t be able to get a serialno.

Hope this clarified my earlier post.

5

Ok Marc,

but my question now is how do you try again if you get “false” as return in step 1?
In my thought I need a loop for waiting the 100ms and trying again, or? I can’t know how many users are creating records and setting the same kind of serlialno. at the same time. Because what is if they don’t get a serialno. at the first try and also not at the second after waiting the 100ms?
My actually questions is how I can loop for the return “true” of the step 1?

6

Hi Thomas,

We call the code below (subroutine) to do what you want.
What will happen if - after all - you don’t get a serialno. is up to you:

  • Cancelling the whole orderrec could be an option (don’t know if it applies to your situation, but it’s still an option ;-) )
  • You can also think about setting the serial with a text like ‘click to get serial’ and build a routine that will run if a user clicks on this serial that wasn’t set.
  • Implement the ‘busy’ plugin and keep waiting for the lock to solve… At some point there’s must be a gap to get the requested lock.

Keep in mind that the time this lock is active is only very short.
We’ve seen a failing lock only 2 times in about 2 years that we’ve users working with our solution.
But agreed: it’s all about the number of people creating records at the same time in 1 specific module of your solution.

Hope this helps.

var $foundset = arguments[0];
	var $newSerial;
	
	//Obtain record lock
	var $counter = 1;
	
	while(!databaseManager.acquireLock($foundset,1))
	{
		if($counter >= 50)
		{
			return false;
		}
		application.sleep(20);
		$counter++; 	
	}

	try
	{
		if($foundset.serial == null || $foundset.serial <= 0) $foundset.serial = 0;
		$foundset.serial += 1;
		
		$newSerial = $foundset.serial;
	}
	catch (e)
	{
		return false;
	}
	finally
	{
		databaseManager.saveData($foundset.getSelectedRecord());
		databaseManager.releaseAllLocks();
	}
	
	return $newSerial.toString();
7

Hi Marc,

thank you very much! You helped me a lot.

Thomas