Hi!

A few years ago I asked for a method to create a semaphore that let my
application lock one table while making some neccessary calculations.

This is what my app does:
1. There is a common table available for all users/machines
2. All machines stores records (one by one) in that table, with an ascending
number field that must be unique for each record.
3. So, before editing data, I have to know the LAST used number saved in the
table, just to assign the following one.

Somebody (don't remember, sorry) answered me with a custom function to check
if tabla can be edited, or wait to be edited.
This is the function I use:

function sum(fil : string): string;
const
 ntMaxTries = 100;
var
 Tries : Integer;
 nextnumber : integer;
 cTable : TDBISAMtable;
begin
 Result := '0';
 Tries := 0;
 with form1.table4 do  // table used as semaphore
 begin
   {Try to put the semaphore table into edit mode. If calling Edit raises
    an exception wait a random period and try again.}
   while Tries < ntMaxTries do
     try
       Inc(Tries);
       Edit;
       Break;
     except
       sleep(1000);  //waits 1 second and continue checking...
       Continue;
     end; {try}

   if State = dsEdit then
   begin
       //Here I try to get the latest stored number in the common table

       cTable:=TDBISAMTable.create(application);
       cTable.tablename := form1.table6.tablename;
       cTable.DatabaseName := form1.table6.databasename;
       cTable.sessionname := form1.table6.sessionname;
       cTable.filter := fil;
       cTable.filtered:=true;
       cTable.indexname:='movfac';  //orders by num, ascending
       cTable.open;
       cTable.last;
       nextnumber := cTable.fieldbyname('NUM').asinteger + 1;
       cTable.free;

       Result := inttostr(nextnumber);
   end;
 end;
end;


After I get the next number (suppously available), I delete the semaphore
with:


function FreeSemaphore(LastNumberTbl: TDBISAMTable): boolean;
begin
 with LastNumberTbl do
 begin
   result:=true;
   if State = dsEdit then
   begin
       FieldByName('FIELD1').AsInteger := 0;
       Post;
   end
   else
       result:=false;
 end;
end;


Ok, now that everything is explained...
I sometimes get duplicates numbers on the common table. Computer A and
Computer B gets the same number.
It doesn't happen many times, but about 2 or 3 times per 100 records, but
even that is not acceptable.

I suppouse the problem is due to the time needed to execute the number
calculation (open, filter, index...)
Maybe the table is locked but I'm not getting the latest available data from
the common table, and I need to ensure it with a Refresh or something...

Is there anything wrong in my code, any better function or method to
calculate the next number, or to ensure the table result is the latest
available?

Thanks, and please forgive these large posts and my bad english.

--
Santy C

Santy,

<< Is there anything wrong in my code, any better function or method to
calculate the next number, or to ensure the table result is the latest
available? >>

Throw out the whole semaphore bit.  All you need is this:

function GetNextNumber: Integer;
var
   NumberTable: TDBISAMTable;
begin
   NumberTable:=TDBISAMTable.Create(application);
   try
       NumberTable.SessionName := 'MySession';
       NumberTable.DatabaseName := 'MyDatabase';
       NumberTable.TableName := 'Numbers';
       NumberTable.Open;
       while True do
           begin
           try
               NumberTable.Edit;
               Result:=NumberTable.FieldByName('LastOrderNum').AsInteger+1;
               NumberTable.FieldByName('LastOrderNum').AsInteger:=Result;
               NumberTable.Post;
               Break;
           except
               on E: Exception do
                   begin
                   if (ErrorCode=DBISAM_RECLOCKFAILED) then
                       Continue
                   else if (ErrorCode=DBISAM_KEYORRECDELETED) then
                       begin
                       NumberTable.Refresh;
                       Continue;
                       end
                   else
                       raise;
                   end;
           end;
           end;
   finally
       NumberTable.Free;
   end;
end;

That will guarantee that you always get the next unique order number from
the Numbers table.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks, Tim...

Please notice that numbers are no saved in a simple field. There will be a
new record for each number. For example:

   NUM        DATE            HOUR
     1        2008-01-01       20:34
     2        2008-01-01       20:41
     3        2008-01-02       10:21
     ...        ...                            ...

So I think your code will not work as desired.

I have thought about adding a Refresh line after opening the table to ensure
the data is refreshed again, but this will take more time (often
unnecessary)  in C/S Internet access or big tables in shared folder mode.

This is the complete operation circle:

1. Check latest number/record in numbers table (using my code)
2. When obtained, Edit and Post the new record
3. Delete the semaphore (using my code)

Thanks!

--
Santy C.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> escribió en el
mensaje de
noticias:50F193AD-427B-4E35-B0C6-90D223A0F4AA@news.elevatesoft.com...
> Santy,
>
> << Is there anything wrong in my code, any better function or method to
> calculate the next number, or to ensure the table result is the latest
> available? >>
>
> Throw out the whole semaphore bit.  All you need is this:
>
> function GetNextNumber: Integer;
> var
>    NumberTable: TDBISAMTable;
> begin
>    NumberTable:=TDBISAMTable.Create(application);
>    try
>        NumberTable.SessionName := 'MySession';
>        NumberTable.DatabaseName := 'MyDatabase';
>        NumberTable.TableName := 'Numbers';
>        NumberTable.Open;
>        while True do
>            begin
>            try
>                NumberTable.Edit;
>
> Result:=NumberTable.FieldByName('LastOrderNum').AsInteger+1;
>                NumberTable.FieldByName('LastOrderNum').AsInteger:=Result;
>                NumberTable.Post;
>                Break;
>            except
>                on E: Exception do
>                    begin
>                    if (ErrorCode=DBISAM_RECLOCKFAILED) then
>                        Continue
>                    else if (ErrorCode=DBISAM_KEYORRECDELETED) then
>                        begin
>                        NumberTable.Refresh;
>                        Continue;
>                        end
>                    else
>                        raise;
>                    end;
>            end;
>            end;
>    finally
>        NumberTable.Free;
>    end;
> end;
>
> That will guarantee that you always get the next unique order number from
> the Numbers table.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

"Santy Concepción" <santyweb@hotmail.com> wrote in message
news:35435C12-64D9-4B49-8D87-15695E9A9CA1@news.elevatesoft.com...
> Hi!
>
> A few years ago I asked for a method to create a semaphore that let my
> application lock one table while making some neccessary calculations.
>
> This is what my app does:
> 1. There is a common table available for all users/machines
> 2. All machines stores records (one by one) in that table, with an
> ascending number field that must be unique for each record.
> 3. So, before editing data, I have to know the LAST used number saved in
> the table, just to assign the following one.
>

Do you have something against autoinc? It does exactly what you want. Assign
a new number to each new record. With zero code on your part.

Robert

> Somebody (don't remember, sorry) answered me with a custom function to
> check if tabla can be edited, or wait to be edited.
> This is the function I use:
>
> function sum(fil : string): string;
> const
>  ntMaxTries = 100;
> var
>  Tries : Integer;
>  nextnumber : integer;
>  cTable : TDBISAMtable;
> begin
>  Result := '0';
>  Tries := 0;
>  with form1.table4 do  // table used as semaphore
>  begin
>    {Try to put the semaphore table into edit mode. If calling Edit raises
>     an exception wait a random period and try again.}
>    while Tries < ntMaxTries do
>      try
>        Inc(Tries);
>        Edit;
>        Break;
>      except
>        sleep(1000);  //waits 1 second and continue checking...
>        Continue;
>      end; {try}
>
>    if State = dsEdit then
>    begin
>        //Here I try to get the latest stored number in the common table
>
>        cTable:=TDBISAMTable.create(application);
>        cTable.tablename := form1.table6.tablename;
>        cTable.DatabaseName := form1.table6.databasename;
>        cTable.sessionname := form1.table6.sessionname;
>        cTable.filter := fil;
>        cTable.filtered:=true;
>        cTable.indexname:='movfac';  //orders by num, ascending
>        cTable.open;
>        cTable.last;
>        nextnumber := cTable.fieldbyname('NUM').asinteger + 1;
>        cTable.free;
>
>        Result := inttostr(nextnumber);
>    end;
>  end;
> end;
>
>
> After I get the next number (suppously available), I delete the semaphore
> with:
>
>
> function FreeSemaphore(LastNumberTbl: TDBISAMTable): boolean;
> begin
>  with LastNumberTbl do
>  begin
>    result:=true;
>    if State = dsEdit then
>    begin
>        FieldByName('FIELD1').AsInteger := 0;
>        Post;
>    end
>    else
>        result:=false;
>  end;
> end;
>
>
> Ok, now that everything is explained...
> I sometimes get duplicates numbers on the common table. Computer A and
> Computer B gets the same number.
> It doesn't happen many times, but about 2 or 3 times per 100 records, but
> even that is not acceptable.
>
> I suppouse the problem is due to the time needed to execute the number
> calculation (open, filter, index...)
> Maybe the table is locked but I'm not getting the latest available data
> from the common table, and I need to ensure it with a Refresh or
> something...
>
> Is there anything wrong in my code, any better function or method to
> calculate the next number, or to ensure the table result is the latest
> available?
>
> Thanks, and please forgive these large posts and my bad english.
>
> --
> Santy C
>
>

Hi, Robert...

> Do you have something against autoinc? It does exactly what you want.
> Assign a new number to each new record. With zero code on your part.

Yes...
I can't use an autoinc field because some records can be duplicated, but not
all.
And I have to filter the table to get the latest available number for each
'category':

   TYPE    NUMBER        DATE
   SALE        1             2008-01-02
   SALE        2             2008-01-03
   BUY           1             2008-01-02
   SALE        3             2008-01-03
   SBUY        2             2008-01-04

Thanks

--
Santy Concepción
Dpto. Desarrollo
e-Mail: desarrollo@simplygest.es
Web: www.SimplyGest.es
Blog: www.SimplyGest.es\blog

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> escribió en el mensaje de
noticias:F9B4D108-8AED-461E-9AD6-432D62091E72@news.elevatesoft.com...
>
> "Santy Concepción" <santyweb@hotmail.com> wrote in message
> news:35435C12-64D9-4B49-8D87-15695E9A9CA1@news.elevatesoft.com...
>> Hi!
>>
>> A few years ago I asked for a method to create a semaphore that let my
>> application lock one table while making some neccessary calculations.
>>
>> This is what my app does:
>> 1. There is a common table available for all users/machines
>> 2. All machines stores records (one by one) in that table, with an
>> ascending number field that must be unique for each record.
>> 3. So, before editing data, I have to know the LAST used number saved in
>> the table, just to assign the following one.
>>
>
> Do you have something against autoinc? It does exactly what you want.
> Assign a new number to each new record. With zero code on your part.
>
> Robert
>
>> Somebody (don't remember, sorry) answered me with a custom function to
>> check if tabla can be edited, or wait to be edited.
>> This is the function I use:
>>
>> function sum(fil : string): string;
>> const
>>  ntMaxTries = 100;
>> var
>>  Tries : Integer;
>>  nextnumber : integer;
>>  cTable : TDBISAMtable;
>> begin
>>  Result := '0';
>>  Tries := 0;
>>  with form1.table4 do  // table used as semaphore
>>  begin
>>    {Try to put the semaphore table into edit mode. If calling Edit raises
>>     an exception wait a random period and try again.}
>>    while Tries < ntMaxTries do
>>      try
>>        Inc(Tries);
>>        Edit;
>>        Break;
>>      except
>>        sleep(1000);  //waits 1 second and continue checking...
>>        Continue;
>>      end; {try}
>>
>>    if State = dsEdit then
>>    begin
>>        //Here I try to get the latest stored number in the common table
>>
>>        cTable:=TDBISAMTable.create(application);
>>        cTable.tablename := form1.table6.tablename;
>>        cTable.DatabaseName := form1.table6.databasename;
>>        cTable.sessionname := form1.table6.sessionname;
>>        cTable.filter := fil;
>>        cTable.filtered:=true;
>>        cTable.indexname:='movfac';  //orders by num, ascending
>>        cTable.open;
>>        cTable.last;
>>        nextnumber := cTable.fieldbyname('NUM').asinteger + 1;
>>        cTable.free;
>>
>>        Result := inttostr(nextnumber);
>>    end;
>>  end;
>> end;
>>
>>
>> After I get the next number (suppously available), I delete the semaphore
>> with:
>>
>>
>> function FreeSemaphore(LastNumberTbl: TDBISAMTable): boolean;
>> begin
>>  with LastNumberTbl do
>>  begin
>>    result:=true;
>>    if State = dsEdit then
>>    begin
>>        FieldByName('FIELD1').AsInteger := 0;
>>        Post;
>>    end
>>    else
>>        result:=false;
>>  end;
>> end;
>>
>>
>> Ok, now that everything is explained...
>> I sometimes get duplicates numbers on the common table. Computer A and
>> Computer B gets the same number.
>> It doesn't happen many times, but about 2 or 3 times per 100 records, but
>> even that is not acceptable.
>>
>> I suppouse the problem is due to the time needed to execute the number
>> calculation (open, filter, index...)
>> Maybe the table is locked but I'm not getting the latest available data
>> from the common table, and I need to ensure it with a Refresh or
>> something...
>>
>> Is there anything wrong in my code, any better function or method to
>> calculate the next number, or to ensure the table result is the latest
>> available?
>>
>> Thanks, and please forgive these large posts and my bad english.
>>
>> --
>> Santy C
>>
>>
>
>
>

"Santy Concepción" <santyweb@hotmail.com> wrote in message
news:DD648EAB-3476-4B59-9EA9-966E1C4E7611@news.elevatesoft.com...
> Thanks, Tim...
>
> Please notice that numbers are no saved in a simple field. There will be a
> new record for each number. For example:
>
>    NUM        DATE            HOUR
>      1        2008-01-01       20:34
>      2        2008-01-01       20:41
>      3        2008-01-02       10:21
>      ...        ...                            ...
>
> So I think your code will not work as desired.
>
Why not just add you log record code after the post of the unique number?

You then use two tables instead of one but it will work

Santy


On the face of it your approach whilst not the way I'd do it looks as though it should work. Since it isn't there are only a few possibilities:

1. your code is just plain wrong
2. DBISAM isn't rejecting other Edits when it should
3. Windows is giving you grief
4. Something unexpected is happening.

I'm placing my bet on 4. I can't see 2 being the case otherwise we'd have done nasty things to Tim years ago. Much as I hate Windows I don't think it would allow scewups like this otherwise we'd probably have attacked Tim (cos we'd have thought it was his fault). There isn't a lot of code there to go wrong so I'm discounting 1.

Looking at your code you set an edit on a record on form1.table4. Something I can't see is how you select the record to edit for the "semaphore" lock. Unless its a single record table my guess would be that the pointer is occasionally being moved so a different record is being edited.

Two suggestions:

1. use a proper semaphore lock (check out LockSemaphore)
2. write a file to disk

I use both these in my DBISAM apps. LockSemaphore for controlling the number of logged on users and a disk file to control email send/receive to make sure two users aren't trying to download emails simultaneously.

Roy Lambert [Team Elevate]

Thanks, Roy...

Yes, form1.table4 is a single record table. It has only one record, used for
the semaphore.

As you say, code must work (indeed it works fine 99% of the time).

Maybe under some circunstances, when my code opens the numbers table and go
to the last record, Computer B hasn't saved the last number yet, and I'm not
getting the last number.
With a new Refresh call I will solve this (I hope), but it is innecessary in
99% of times, and application could run slower.

Now I'm testing with a single form app I made runing twice, using my code,
and doing the calculation every two seconds automatically. It has calculated
the following number correctly with no problem at all, so I don't know
why/when the problem ocurrs.

WIll test LockSemaphore to see if it helps.

Thanks!

--
Santy C

"Roy Lambert" <roy.lambert@skynet.co.uk> escribió en el mensaje de
noticias:208A05F0-F8FF-4360-B5E7-EF539B317668@news.elevatesoft.com...
> Santy
>
>
> On the face of it your approach whilst not the way I'd do it looks as
> though it should work. Since it isn't there are only a few possibilities:
>
> 1. your code is just plain wrong
> 2. DBISAM isn't rejecting other Edits when it should
> 3. Windows is giving you grief
> 4. Something unexpected is happening.
>
> I'm placing my bet on 4. I can't see 2 being the case otherwise we'd have
> done nasty things to Tim years ago. Much as I hate Windows I don't think
> it would allow scewups like this otherwise we'd probably have attacked Tim
> (cos we'd have thought it was his fault). There isn't a lot of code there
> to go wrong so I'm discounting 1.
>
> Looking at your code you set an edit on a record on form1.table4.
> Something I can't see is how you select the record to edit for the
> "semaphore" lock. Unless its a single record table my guess would be that
> the pointer is occasionally being moved so a different record is being
> edited.
>
> Two suggestions:
>
> 1. use a proper semaphore lock (check out LockSemaphore)
> 2. write a file to disk
>
> I use both these in my DBISAM apps. LockSemaphore for controlling the
> number of logged on users and a disk file to control email send/receive to
> make sure two users aren't trying to download emails simultaneously.
>
> Roy Lambert [Team Elevate]
>

Hi!

I think the problem is here:

I use a try..except..end when Posting numbers table:

try
   table.post;
   table.flushbuffers;
except
   showmessage('error occurred when trying to save data. Please, try
again.');
end;

As you see, there is no "Table.Cancel" to try again from the begining after
the error, so there still is an active record in edit/insert state, WHILE
ComputerB is calculating a new number.

If ComputerA was trying to post number 73, but an error ocurred, record 73
is not saved to the table.
ComputerB now calculates the latest available number (73, because it hasn't
been saved from ComputerA).
BUT Computer A is still in Edit or Inserting mode, so a new Insert, or a
scroll change, a refresh, etc... will save it (with old 73).
There will be two 73 saved in the database.

The possible solution? Add a Table.Cancel if couldn't post due to an
exception

--
Santy C


"Santy Concepción" <santyweb@hotmail.com> escribió en el mensaje de
noticias:35435C12-64D9-4B49-8D87-15695E9A9CA1@news.elevatesoft.com...
> Hi!
>
> A few years ago I asked for a method to create a semaphore that let my
> application lock one table while making some neccessary calculations.
>
> This is what my app does:
> 1. There is a common table available for all users/machines
> 2. All machines stores records (one by one) in that table, with an
> ascending number field that must be unique for each record.
> 3. So, before editing data, I have to know the LAST used number saved in
> the table, just to assign the following one.
>
> Somebody (don't remember, sorry) answered me with a custom function to
> check if tabla can be edited, or wait to be edited.
> This is the function I use:
>
> function sum(fil : string): string;
> const
>  ntMaxTries = 100;
> var
>  Tries : Integer;
>  nextnumber : integer;
>  cTable : TDBISAMtable;
> begin
>  Result := '0';
>  Tries := 0;
>  with form1.table4 do  // table used as semaphore
>  begin
>    {Try to put the semaphore table into edit mode. If calling Edit raises
>     an exception wait a random period and try again.}
>    while Tries < ntMaxTries do
>      try
>        Inc(Tries);
>        Edit;
>        Break;
>      except
>        sleep(1000);  //waits 1 second and continue checking...
>        Continue;
>      end; {try}
>
>    if State = dsEdit then
>    begin
>        //Here I try to get the latest stored number in the common table
>
>        cTable:=TDBISAMTable.create(application);
>        cTable.tablename := form1.table6.tablename;
>        cTable.DatabaseName := form1.table6.databasename;
>        cTable.sessionname := form1.table6.sessionname;
>        cTable.filter := fil;
>        cTable.filtered:=true;
>        cTable.indexname:='movfac';  //orders by num, ascending
>        cTable.open;
>        cTable.last;
>        nextnumber := cTable.fieldbyname('NUM').asinteger + 1;
>        cTable.free;
>
>        Result := inttostr(nextnumber);
>    end;
>  end;
> end;
>
>
> After I get the next number (suppously available), I delete the semaphore
> with:
>
>
> function FreeSemaphore(LastNumberTbl: TDBISAMTable): boolean;
> begin
>  with LastNumberTbl do
>  begin
>    result:=true;
>    if State = dsEdit then
>    begin
>        FieldByName('FIELD1').AsInteger := 0;
>        Post;
>    end
>    else
>        result:=false;
>  end;
> end;
>
>
> Ok, now that everything is explained...
> I sometimes get duplicates numbers on the common table. Computer A and
> Computer B gets the same number.
> It doesn't happen many times, but about 2 or 3 times per 100 records, but
> even that is not acceptable.
>
> I suppouse the problem is due to the time needed to execute the number
> calculation (open, filter, index...)
> Maybe the table is locked but I'm not getting the latest available data
> from the common table, and I need to ensure it with a Refresh or
> something...
>
> Is there anything wrong in my code, any better function or method to
> calculate the next number, or to ensure the table result is the latest
> available?
>
> Thanks, and please forgive these large posts and my bad english.
>
> --
> Santy C
>
>
>

Santy

If I am reading what you are doing correctly the I suspect your problem
might be

> After I get the next number (suppously available), I delete the semaphore
file

You will need to keep the semaphore file locked until you have posted the
record(s) using the latest number.

As I read it the duplicates occur because sometimes

User A locks file
User A gets latest number
User A unlocks file
User B locks file
User B gets latest number (same as User A!)
User B unlocks file
User A posts records
User B posts records

If you are not unlocking the semaphore file before posting the records(s)
ignore this message.

I don't think the refresh is a problem as you are calling table.open before
getting the last number.

If you want to avoid using a semaphore file you could wrap the "get latest
number" and "post records" in a transaction.

John