Hello

I have a database system thats shares data over a network ( ie program  and data on one computer  and program only on another computer - (not a client server setup))

I have an invoice counter that goes up by one each time an invoice is issued.
From time to time it looks as if the counter does not increment correctly.

1. Is this a netwrking issue ? if so any recomendations.

2. Is there a better way to handle database counters so that they work as they are supposed to ?

3. Is the client server approach the way forward?

Thanks in advance.

Kamran

kamran


There's a critical piece of information missing - how does your current counter work? By not increment correctly do you mean it issues duplicates or skips a value?

Roy Lambert [Team Elevate]

Hi

I have a database counter say 1002 which is updated by the system to be the last counter used at the end of a new invoice being sucessfully raised.

Later on when a new invoice is issued I read that saved counter value and add 1 to it to make it say 1003.

The result is sometimes still 1002 when it should be 1003.

Environment is windows Vista for both workstations.

Thanks

Kamran







Roy Lambert wrote:

kamran


There's a critical piece of information missing - how does your current counter work? By not increment correctly do you mean it issues duplicates or skips a value?

Roy Lambert [Team Elevate]

kamran


From what you say so far I'd guess one of two things is happening

1. The program doesn't necessarily update the next invoice record

or

2. You're not handling locking correctly and its possible for the same invoice number to be issued to two users both of whom are raising an invoice at roughly the same time.

Roy Lambert [Team Elevate]

Kamran

You have to use transactions + refresh combination to do this.

Look the code:

database.starttransaction;
try
   table.refresh;
   table.edit;
   table.fieldbyname('counter').asinteger :=
table.fieldbyname('counter').asinteger + 1;
   table.post;
   database.commit
except
  database.rollback;
end;

After the method "starttransaction", other clients were "waiting" and
therefore the code guarantee that counter is updated correctly always. It
works in both file/server or client/server mode.

Eduardo

Eduardo

>You have to use transactions + refresh combination to do this.
>
>Look the code:
>
>database.starttransaction;
>try
> table.refresh;
> table.edit;
> table.fieldbyname('counter').asinteger :=
>table.fieldbyname('counter').asinteger + 1;
> table.post;
> database.commit
>except
> database.rollback;
>end;

That shouldn't be necessary unless locking is set to optimistic. A table.refresh won't hurt even though (from my memory) the edit operation should cause the data to be refetched if its changed.

Roy Lambert [Team Elevate]

Eduardo,

Roy is correct, the transaction is overkill - an Edit automatically
refreshes the current record as long as the locking protocol for the session
is pessimistic (the default).  So, you only need:

Edit;
Increment;
Post;

Tim Young
Elevate Software
www.elevatesoft.com

Ok. I understand.

I have answered this because I always use "Opportunistic Locking".  No
problems.
But a pretty sure 100% that this way works on both situations and with
pessimistic locking it will "cost" a little more.

Eduardo