Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Enhancement Requests and Suggestions » View Thread |
Messages 1 to 9 of 9 total |
Autoincs |
Sat, Oct 18 2008 1:31 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I'd like to request a slight change to autoincs, at least the generated by default ones. The current behaviour is that if you manually enter a value that becomes the start point for the next entry. I'd like it to continue with the existing series. EG
current behaviour auto 1 auto 2 auto 3 manual 78 auto 79 I'd like auto 1 auto 2 auto 3 manual 78 auto 4 ie similar to auto 1 auto 2 auto 3 auto 4 edit 78 auto 5 Roy Lambert |
Mon, Oct 20 2008 10:59 AM | Permanent Link |
"David Cornelius" | > I'd like to request a slight change to autoincs, at least the
> generated by default ones. The current behaviour is that if you > manually enter a value that becomes the start point for the next > entry. I'd like it to continue with the existing series. EG Hmm... I would *not* like this functionality because I might forget the higher number is there and as more records are added, eventually there would be a duplicate key (assuming the AutoInc field is a key field). Instead, you can use ALTER TABLE ... ALTER COLUMN ... RESTART WITH <seed value> to reset the auto-generated ID, albeit it can be a bit of work to find out what that value should be in this case. -- David Cornelius CorneliusConcepts.com |
Mon, Oct 20 2008 11:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | David
>> I'd like to request a slight change to autoincs, at least the >> generated by default ones. The current behaviour is that if you >> manually enter a value that becomes the start point for the next >> entry. I'd like it to continue with the existing series. EG > >Hmm... I would *not* like this functionality because I might forget >the higher number is there and as more records are added, eventually >there would be a duplicate key (assuming the AutoInc field is a key >field). Who cares if you forget - its up to the system to remember, and if there isn't a collision mechanism built in I'll start worrying now. >Instead, you can use > ALTER TABLE ... ALTER COLUMN ... RESTART WITH <seed value> >to reset the auto-generated ID, albeit it can be a bit of work to find >out what that value should be in this case. For what I'm thinking about that just wouldn't be viable. Roy Lambert |
Mon, Oct 20 2008 11:34 AM | Permanent Link |
"David Cornelius" | Roy Lambert wrote:
> David > > >> I'd like to request a slight change to autoincs, at least the > >> generated by default ones. The current behaviour is that if you > >> manually enter a value that becomes the start point for the next > >> entry. I'd like it to continue with the existing series. EG > > > > Hmm... I would not like this functionality because I might forget > > the higher number is there and as more records are added, eventually > > there would be a duplicate key (assuming the AutoInc field is a key > > field). > > Who cares if you forget - its up to the system to remember, and > if there isn't a collision mechanism built in I'll start worrying now. I care! I'm a very caring person! Seriously, if I just let the collision mechanism catch it, then I have to build in a lot of extra error-handling code to prevent the situation. Usually autoincs are for internal IDs with a huge upper limit, so it doesn't matter if a few are missed. Here's an short description of how I handle purposeful gaps in autoincs--I don't know if it directly applies to what you're doing, but it might give you an idea... In my replicated application, most tables are synchronized frequently through the day. Any of the users can add/delete/edit any of the records. The tables all have AutoInc IDs as the primary key, but they don't have collisions because they all are setup in their own autoinc range. There are about 7 users (if you count my 3 test ones) each with a 10-million autoinc range. I have a procedure that selects the MAX(ID) from a table where the ID is within their autoinc range, then it uses that MAX(ID) to restart the seed value for the ID field. That way, if any record ever gets manually entered or imported, their autoinc field always gets reset to the proper next value for them. Hope that helps. Or at least perhaps it was interesting? -- David Cornelius CorneliusConcepts.com |
Mon, Oct 20 2008 1:52 PM | Permanent Link |
Lance Rasmussen Jazzie Software Team Elevate | Sounds like you may need to go the Oracle workaround for autoinc, for which
Oracle doesn't do autoincs natively. You have to create a sequence and a trigger. Change the type from autoinc to longint. create sequence test_seq start with 1 increment by 1 nomaxvalue; create trigger test_trigger before insert on test for each row begin select test_seq.nextval into :new.id from dual; end; or instead of the trigger, just use the value from the test_seq insert into test values(test_seq.nextval, xxx,yyy,zzz'); Those are Oracle examples and I've not tested to see how they would work with EDB. Lance Rasmussen "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:D302A4F9-0A99-453E-87EA-674C6FBFE92C@news.elevatesoft.com... > I'd like to request a slight change to autoincs, at least the generated by > default ones. The current behaviour is that if you manually enter a value > that becomes the start point for the next entry. I'd like it to continue > with the existing series. EG > > current behaviour > > auto 1 > auto 2 > auto 3 > manual 78 > auto 79 > > I'd like > > auto 1 > auto 2 > auto 3 > manual 78 > auto 4 > > ie similar to > > auto 1 > auto 2 > auto 3 > auto 4 edit 78 > auto 5 > > > > Roy Lambert |
Mon, Oct 20 2008 2:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I'd like to request a slight change to autoincs, at least the generated by default ones. The current behaviour is that if you manually enter a value that becomes the start point for the next entry. I'd like it to continue with the existing series. EG >> I think you're going to have to roll your own sequencing with a table in order to do this. I don't think we can just change the existing behavior, although I may be able to look into adding an option for this during the column creation/alteration. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Oct 21 2008 3:50 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
OK, I thought that might be the answer. Roy Lambert |
Tue, Oct 21 2008 3:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Lance
Thanks for the example. What I'll probably end up doing is what I've done before and have a table which I use to manage incrementing the record number. Roy Lambert |
Tue, Oct 21 2008 3:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | David
>Hope that helps. Or at least perhaps it was interesting? How about mind stretching? Roy Lambert |
This web page was last updated on Thursday, March 28, 2024 at 08:36 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |