Lock rows?

[Jokez]

Hi! I have a problem. We are having a simple order/invoice system that we use. And we have an online store hooked up to it. Now that we have alot of buisiness on the web we are geting a problem.

When and order is created inhouse and on the web at the same time it happens that the both orders get the same ordernumber.

Can you prevent this with some kind of row lock?

The highest orderno is stored in a global and internally we acces it by a script and from the web via XML.

Best Regards

Joakim

[Genx]

Use a serial key?

[Jokez]

Serial key?

We have different companys that uses the same system so we can´t just have a number that increases if thats what you mean. The same problem can occure with invoices. We have gathered the highest order and invoice numbers in a settings table. They are stored in a global variable.

[Genx]

... can't help you if you don't clearly state your scenario.

[Jokez]

The Senario is:

Someone makes an order in our inhouse system and at the exact same time someone places an order on our website. This makes them both to get the same ordernumber beacuse it comes from a table in our FM database that has a global that keeps track of the highest orderno. My problem is that I want to lock this table for 1s or so wile either the inhouse or web gets the highest orderno. Is that possible in Filemaker?

[Vaughan]

The highest order number should NOT be stored in a global because globals cannot be synchronised between different users.

It should be in a single-record prefs table in a "normal" stored field.

[Jokez]

Ah sorry my bad. It is stored in a single row table.. but still can I lock that row?

[Genx]

The row would automatically be locked if two users attempted to access it at once.

In any case, before adding your record, retrieve the current id, update the id, then run your record adding in the other table.

[Jokez]

So why can XML and and inhouse order get the same orderno then? You mean that this shouldn´t be possible? Beacuse this has happened twice today.

[Vaughan]

The key is not to just *read* the next serial number from the record because more than one user can do this at once. Instead, you have to open the record, increment the serial number and remember the new number, then close the record. Only one user at a time can do this, because opening the record locks it to other users.

Note that multiple users could still read the locked record, because it's only locked for *editing*! It's the process of only remembering the serial number after it's been incremented that's the key.

The actual steps could go something like this:

Loop

Set Error Capture [ on ]

Open Record

Set Variable [ $error ; Get( LastError ) ]

Set Error Capture [ off ]

Exit Loop if [ $error = 0 ]

End Loop

#Record is open and locked to all other users.

Commit Record/Request [ no dialog ]

#Record can now be accessed by other users.

[Vaughan]

If you're only using FMP 7 then the Set Variable step won't work. Instead, put the error into a global field.

Was the Open Record step introduced in FMP 7? If not, you need to edit the record to open it. The easiest way is to set a field to it's current value and check for an error:

Set Error Capture [ on ]

Set Field [ fieldname ; fieldname ]

Set Field [gError ; Get( LastError ) ]

Set Error Capture [ off ]

If [ gError = 0 ]

etc...

[Genx]

... why do you turn error capture on... and then off...inside a loop?

And why capture the last error into a variable or global and then run a comparison on that instead of directly on get(LastError)?

Set Error Capture [ on ]

Loop

Open Record

Exit Loop if [ not Get( LastError ) ]

End Loop

#Record is open and locked to all other users.

Commit Record/Request [ no dialog ]

#Record can now be accessed by other users.

Edited October 30, 2007 by Guest

[Vaughan]

".. why do you turn error capture on... and then off...inside a loop?"

Because it's bad practice to run with error captue on all the time: if there is an error I want FMP to tell me about it.

I had a *huge* argument with somebody about this once. I created a script that generated related records, part of a new feature I implemented. After its first install it threw up an error at a client's site. I got *blasted* for not suppressing the error, because it looked bad to the client. The real problem was that the other developer had done a data import and forgotten to import the primary keys! WTF!

With error capture constantly on, FMP would not have reported the error at all and the problem would have been massively compounded, possibly not have been noticed for a couple of days and been much harder to track down and fix.

[Genx]

So turn it off after the completion of the loop... you are already capturing for the one error that can possibly occur which is something to do with a locked record... what else could happen if you tried to open a record?

[Vaughan]

"And why capture the last error into a variable or global and then run a comparison on that instead of directly on get(LastError)?"

Because sometimes the error needs to be remembered, or there need to steps between when the error is trapped and when the action is taken.

Let's say we implement a timeout for the loop:

Loop

Set Variable [ $start ; Get ( CurrentTime ) ]

Set Error Capture [ on ]

Open Record

Set Variable [ $error ; Get( LastError ) ]

Set Error Capture [ off ]

#The loop times-out after 10 seconds.

Exit Loop if [ $error = 0 or ( Get( CurrentTime ) - $start ) > 10 ]

End Loop

#How do we know if the record is open or the loop timed out? If $error = 0 the record is open.

If [ $error = 0 ]

#Record is open and locked to all other users.

Commit Record/Request [ no dialog ]

#Record can now be accessed by other users.

Else

#Timeout occurred

#Pass the error code onto the calling script through a script result.

Exit Script [ Result = $error ]

End If

[Vaughan]

"So turn it off after the completion of the loop..."

Oh yeah, I have no problem with that. I just choose to "clean up" immediately after making a mess. :(

[Jokez]

Still thnx for the good explanation! It worked. We will fix this today!

[Vaughan]

It might be worth pointing out that my habit of storing the error in a global or variable was due to the error-checking behaviour of FMP 6 and earlier:

Set Error Capture [ on ]

Set Field [ whatever, whatever ]

#A comment. Nothing special.

Set Field [ gError, Status( CurrentError ) ]

In this case, the current error will be zero because the comment was processed without error!! Whilst it's not strictly a bug, it must have been considered undesirable behaviour because it was changed in FMP 7 and later.

My habit remains because I still support FMP 6 solutions, and it's not a bad enough habit to change. :(

[Genx]

Yeah okay fine, but Set Variable [ $start ; Get ( CurrentTime ) ] goes outside the loop.