Hi all,

I am porting an app from Paradox to DBISAM 4.22.
Where it is possible, I now use Autoinc fields for integer keys, however
there are some tables where it's not possible.

The app is an accounting one, so it is supposed to keep some series of
numbers that can be audited, like transaction numbers.

Since it's a multi-company, multi-period thing, transaction numbers are
always relative to a company and a period.

The simplyfied data looks like:

Companies table:
---------------
ID   Company
1   TestCo, Ltd
2   AnotherCo, Inc.

Accounting period table:
-----------------------
CompanyID   ID   DateStart
1      1   01/01/2005
1      2   01/01/2006
2      1   01/01/2005
2      2   01/01/2006

Transactions table:
-------------------
CompanyID   PeriodID   Id   Date
1      1      1   01/01/2005
1      1      2   02/01/2005
1      2      1   01/01/2006
1      2      2   02/01/2006
2      1      1   01/01/2005
2      1      2   01/01/2005
2      2      1   01/01/2006
....

This rules out the use of Autoinc fields for Period's or Transaction
Id's, since autoincs are global to the database.
   
So far, with Paradox the process for getting a key was something like
(abreviated and in pseudo code):

Function GetNewKey(TableName: string): integer;

NewKey := 0
ControlTable.FindKey([TableName])
Repeat
  try   
    ControlTable.Edit;
    // Get the last key here depending on table type:
    // Some tables allow key reusing, while with others you just get the
    // higher key and add 1
    ControlTable.Cancel;
  except
  end;
Until NewKey<>0 or TimeOut

Result := integer;

end;


It involves going to a specified record in a control table, putting that
 table in Edit mode, then getting the new key  and cancelling the
editing. Meanwhile, any other access from another user of the network to
do the same will have to wait for the edit lock to be removed.

Not nice, but it worked, just failing if the app or a computer crashed
and left the record in edit state (It happened seldom).

While I can just replicate this with DBISAM, I'd like to come with
something better.

I have seen that with DBISAM I can choose between pessimistic or
optimistic record locking (the latter better suited to C/S, i guess),
and that there are sempahores locks.

Any suggestions?

Best regards,

Gerard.

Gerard


That is sort of what I've done in the past and I'd stick with it - its simple, easy and it works.

Roy Lambert

Gerard Visent wrote:

> Hi all,
>
> I am porting an app from Paradox to DBISAM 4.22.
> Where it is possible, I now use Autoinc fields for integer keys,


Nothing wrong with continuing to use a key generator in Dbisam.  You
should wrap it in a transaction.  Personally, I use GUIDs for our
accounting application keys since it is very easy to do, and works well
in a briefcase model or data merge situation.

Thanks for the feedback.

I just discovered that there is a TDBISAMEngine that wraps the DB engine, and that it has
triggers.
This would allow me to move my key generation stuff there, and clean a little bit
datamodules from event handlers, and use more SQL as well.
I use GUIDs whenever it's possible, but here that would mean too many changes, and I am
not supposed to rewrite the application.
Aside from allowing you to consolidate remote data, it allows you to have keys with no
business meaning, thus more flexibility.
About the transaction, yes of course I will, that was a luxury not available with Paradox.

Best regards,

Gerard.

En/na David Farrell-Garcia ha escrit:
>
> Nothing wrong with continuing to use a key generator in Dbisam.  You
> should wrap it in a transaction.  Personally, I use GUIDs for our
> accounting application keys since it is very easy to do, and works well
> in a briefcase model or data merge situation.

Hi, David,

I just had a look at GUID fields. In DBISAM they are 38 chars long
strings, so it seems that all the formating is saved. In other apps,
with Firebird, I used CHAR(32) fields, and stored only the relevant
info. Is there any reason to use GUID fields over Char(32) in DBISAM 4?

Regards,

Gerard.

David,

<< Nothing wrong with continuing to use a key generator in Dbisam.  You
should wrap it in a transaction. >>

As an addition to this - use a restricted transaction also on only the key
table to keep from running into concurrency issues.

--
Tim Young
Elevate Software
www.elevatesoft.com

En/na Tim Young [Elevate Software] ha escrit:
>
> As an addition to this - use a restricted transaction also on only the key
> table to keep from running into concurrency issues.
>
Hi, Tim.

Sorry if it sounds dumb, what should I do to use a a restricted
transaction if I do the locking of the key table in a BeforeInsert trigger?

TIA,

Gerard.

Tim Young [Elevate Software] wrote:
> As an addition to this - use a restricted transaction also on only
> the key table to keep from running into concurrency issues.

ahh Yes, I should have mentioned that.  Locking down lots of tables
when you don't have to is not a good idea.

Gerard Visent wrote:

>
> Sorry if it sounds dumb, what should I do to use a a restricted
> transaction if I do the locking of the key table in a BeforeInsert
> trigger?

Just pass in as params the list of tables involved in the transaction.
That way it locks only those tables.  See the user guide or help
manual.  It is all there.

Gerard Visent wrote:

> Thanks for the feedback.
>
> I just discovered that there is a TDBISAMEngine that wraps the DB
> engine, and that it has triggers.

Yes, that is a good place for that kind of thing.  Be aware that if
your application will also be used in local mode, you need to add the
DbisamEngine comopoent to your client application and add the triggers
there as well.  It is best to separate the triggers out to a separate
unit so that you can use the same source file for both the server and
local engine.  What I do to make it flexible is use a separate .pas
file as the trigger controller and that way my DbisamEngine has a
single makes a single call in the handler for each of the trigger
events and that will never change.  I then put the controller and
trigger files in a separate package and load it dynamically so that I
can use the same server with versions of my application by simply
intsalling a differnt trigger package.