I am migrating from DBISAM.

All my DBISAM tables have an AutoInc field called ID as field 1 and my code uses this
uniform structure to allow it to manage edits / deletes etc.

Whether the ID field is in sequence is not important.

I could just adopt this practice for EDB, (using Identity & GENERATED) but I would also
like to build replication into my application so things can't just be as simple as they
used to be (

I can see from the TABLECOLUMNs table we have an "Identity", "Generated" and
"GenerateExpr" column.

Can I write code to change the value of these fields at runtime (say at application
start-up), and if I do does this update the operation of the underlying tables immediately
and reliably?
(I think the answer to all these questions is yes, but I am asking anyway!)

--

Is it possible to create a single trigger (or function?) for a DB so that all ID fields
just increment by asking for "next trigger value" regardless of the table, and if so what
would the "GenerateExpr" column look like?



I can see that the "Triggers" table contains a "TableName" field, which implies that
Triggers operate at a Table rather than DB-wide level.

Adam

Adam,

<< I can see from the TABLECOLUMNs table we have an "Identity", "Generated"
and "GenerateExpr" column.

Can I write code to change the value of these fields at runtime (say at
application start-up), and if I do does this update the operation of the
underlying tables immediately and reliably? (I think the answer to all these
questions is yes, but I am asking anyway!) >>

You can't modify GENERATED or COMPUTED columns.  Only IDENTITY columns can
be modified, and only if they are created with the BY DEFAULT option instead
of the ALWAYS option.

<< Is it possible to create a single trigger (or function?) for a DB so that
all ID fields just increment by asking for "next trigger value" regardless
of the table, and if so what would the "GenerateExpr" column look like? >>

No, any trigger has to be created for each table that will use it.

Did you look over the replication technical article that we have posted ?
The best solution for IDENTITY columns is to use ranges of values with
different starting numbers.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>No, any trigger has to be created for each table that will use it.

What about "Procedures and functions at the session level for use with all databases" when it gets a scheduled assigned and then makes it through to completed?

Roy Lambert

>Did you look over the replication technical article that we have posted ?
>The best solution for IDENTITY columns is to use ranges of values with
>different starting numbers.

I have looked through it & it is very useful. I am actually just thinking of giving each
user a unique starting ID (i.e. 1,000,000 for the first 2,000,000 for the second etc) and
incrementing _every_ record they create from these ID seeds, rather than incrementing each
table separately.

So long as I can maintain the User's last-used-ID I _think_ can then quite easily manage
all the data-relationships I need reasonably effectively.

... I am just having a bit of a learning curve grappling with all the new SQL features in
EDB, there is just such a vast amount of new stuff!

A repository of working SQL scripts would be a really useful resource (?

Adam

Roy,

<< What about "Procedures and functions at the session level for use with
all databases" when it gets a scheduled assigned and then makes it through
to completed? >>

Yes, that would help a bit, but it will also require the support ROW types
so that you could pass the NEWROW or OLDROW values directly to the procedure
or function.

--
Tim Young
Elevate Software
www.elevatesoft.com

Adam,

<< I have looked through it & it is very useful. I am actually just thinking
of giving each user a unique starting ID (i.e. 1,000,000 for the first
2,000,000 for the second etc) and incrementing _every_ record they create
from these ID seeds, rather than incrementing each table separately.

So long as I can maintain the User's last-used-ID I _think_ can then quite
easily manage all the data-relationships I need reasonably effectively. >>

Yep, that will work also.

<< I am just having a bit of a learning curve grappling with all the new SQL
features in EDB, there is just such a vast amount of new stuff! >>

Yes, it's a lot of stuff.  The good thing, however, is that most of it is
much simpler in the sense that it is all done in a very similar fashion,
such as the system information tables.

<< A repository of working SQL scripts would be a really useful resource
(? >>

You should be able to find a lot here on the newsgroups, but yes, I know
what you mean.  It would be nice to have them all in one place.

--
Tim Young
Elevate Software
www.elevatesoft.com

A last thing on this "ID" subject (probably)

I am considering having each laptop hold its "current-max-ID" in the registry. I can also
have a table on the database consisting of

UserName    LastUsedID    DateCreated

in case laptops are lost, registry is corrupted etc.

I have a dedicated DataObject in my code which manages all Inserts / Updates / Deletes. I
can add a FetchNewID procedure in the application for this which does the following:

NewID := Registry.ReadInteger(.....);
Registry.WriteInteger(...);

Result := NewID;

If I test that the Result is roughly correct (i.e. not 0 or -1) then I should have a
fairly solid, but disconnected way of guaranteeing unique ID field values. It should also
be fast, and not require a connection to the database.

Looking at this do other people think it is crazy or likely to fail??

As I have not worked on replication / disconnected users in the past this is a new area
for me, so I am looking for advise.

Adam

Adam,

<< Looking at this do other people think it is crazy or likely to fail?? >>

No, that's a reasonable approach, and the registry should be about as safe
as any area of the OS, especially since the OS has a vested interest in
ensuring that the registry is not corrupted.

--
Tim Young
Elevate Software
www.elevatesoft.com