Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Managing ID fields / Identities |
Fri, Sep 19 2008 4:46 AM | Permanent Link |
adam | 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 |
Fri, Sep 19 2008 6:51 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Sep 19 2008 8:50 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Sep 21 2008 5:03 PM | Permanent Link |
adam | >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 |
Mon, Sep 22 2008 8:08 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Sep 22 2008 8:11 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Thu, Sep 25 2008 6:07 AM | Permanent Link |
adam | 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 |
Thu, Sep 25 2008 8:49 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |