Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 12 total |
Problems with Generate as Identity and By Default Only. |
Wed, Feb 10 2016 5:33 PM | Permanent Link |
Steve Gill | I have a table that has a column used as a primary key that is set to Generate as Identity and By Default Only.
It appears that this isn't working correctly as I'm getting duplicate primary key errors when I try to add records. It looks like the value is not being incremented. I can't find anything in the documentation but I thought the purpose of By Default Only would increment the value unless it was provided. Or am I completely wrong? Elevate DB 2.21 Build 1. = Steve |
Wed, Feb 10 2016 9:15 PM | Permanent Link |
Raul Team Elevate | On 2/10/2016 5:33 PM, Steve Gill wrote:
> I have a table that has a column used as a primary key that is set to Generate as Identity and By Default Only. I assume it's Integer based ? > It appears that this isn't working correctly as I'm getting duplicate primary key errors when I try to add records. It looks like the value is not being incremented. I can't find anything in the documentation but I thought the purpose of By Default Only would increment the value unless it was provided. Or am I completely wrong? > Elevate DB 2.21 Build 1. yes - "By Default Only" will only generate it if you do not specify it yourself when inserting data. I just did a quick test in EDBManager and it seems to work fine here (2.21 build 1). I have a integer column generated by default and primary key constraint on the same column. When i leave it blank it creates the next logical entry (starting from 1 and incrementing). If i do specify value myself it accepts it and next time auto generates next logical one - i.e. if i have values 1..5 and skip to 20 with my entry then next generated one is 21 etc. Can you provide more info on how this is happening exactly : your table SQL and operations order you're doing when running into key violation ? Any chance your code is populating a duplicate value and getting key violation ? Raul |
Wed, Feb 10 2016 11:12 PM | Permanent Link |
Steve Gill | Hi Raul,
<< Can you provide more info on how this is happening exactly : your table SQL and operations order you're doing when running into key violation ? Any chance your code is populating a duplicate value and getting key violation ? >> Yes, they are integer based. I'm allowing users to migrate data from a DBISAM version of the program into an EDB version. As the primary keys need to be the same for all of the data to match up between tables, I set it to By Default Only in the relevant tables so the existing IDs can be used. That all works fine and there are no duplicate values. What's happening is, if I then try to add records from within the application I get the error. On investigation I saw that in all of the tables the seed value is still at 0 even though there are hundreds of records. This is why I'm getting duplicate key errors. What I've done now to get around it is, once all of the data has been imported, I get the MAX key value for each table, and then alter each table and make the seed value the MAX key value +1. That seems to have fixed the problem. So the issue seems to be that, after adding all of these records and setting the primary key value in code, the actual seed value hasn't changed, ie. it remains at 0. To explain it another way, if you set the key value yourself when adding records, the seed value is not incremented. It's only when the table itself adds the keys itself that it gets incremented. I hope that makes sense. = Steve |
Thu, Feb 11 2016 6:41 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Steve,
<< I'm allowing users to migrate data from a DBISAM version of the program into an EDB version. >> Are you using an EDB migrator to do this, or your own code ? The EDB migrator should handle setting the seed value, as necessary, for you. Tim Young Elevate Software www.elevatesoft.com |
Thu, Feb 11 2016 6:45 AM | Permanent Link |
Steve Gill | Hi Tim,
<< Are you using an EDB migrator to do this, or your own code ? The EDB migrator should handle setting the seed value, as necessary, for you. >> I'm using my own code as the EDB database is quite different from the DBISAM one, ie. there are different tables with different columns, etc. Based on your question I'm assuming that I should be setting a new seed value after adding all of the migrated records? = Steve |
Thu, Feb 11 2016 8:18 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Steve
>Based on your question I'm assuming that I should be setting a new seed value after adding all of the migrated records? Yes When I did my last conversion I chose to handle things in a different way - For the master tables I built a cross reference list in DBISAM - old ID & new ID allowing ElevateDB to create the new ID. Left the foreign keys as was in the detail tables and then after they'd all been transfered went through converting old fk to new fk. Part of the reason for doing it that way was just to clean up a system that had been in use for many years and had duff foreign keys, gaps etc. It ended up adding more work but at least I started clean - its probably back into the crapped up state now though Its so long back that I don't even have the code on a machine any more - may be on an archive somewhere. Roy Lambert |
Thu, Feb 11 2016 8:24 AM | Permanent Link |
Uli Becker | Steve,
> Based on your question I'm assuming that I should be setting a new seed value after adding all of the migrated records? Correct. Here a code snippet from one of my procedures to do that automatically: PREPARE Stmt FROM 'SELECT TableName, Name from information.TableColumns where type = ''integer'' and Identity'; OPEN Result; FETCH FIRST FROM Result('TableName','Name') INTO FTableName,FColumnName; WHILE NOT EOF(Result) DO Execute Immediate 'SELECT coalesce(max(' + FColumnName + '),0) INTO ? from ' + FTableName + ' where ' + FColumnName + ' between ? and ?' using LastAutoIncValue, RangeFrom, RangeTo-1; IF LastAutoIncValue between RangeFrom and RangeTo THEN SET NewStartValue = LastAutoIncValue + 1; ELSE SET NewStartValue = RangeFrom; END IF; Execute Immediate 'ALTER TABLE "' + FTableName + '" ALTER COLUMN "' + FColumnName + '" RESTART with ' + CAST(NewStartValue as VARCHAR); SET LOG MESSAGE TO FTableName + ' - ' + FColumnName + ': ' + CAST(NewStartValue as VARCHAR); FETCH NEXT FROM Result('TableName','Name') INTO FTableName,FColumnName; END WHILE; Regars Uli |
Fri, Feb 12 2016 5:08 AM | Permanent Link |
Steve Gill | Thanks Roy and Uli,
I have already fixed it by setting new seed values after all of the records were imported. It would be nice if there was more documentation on this somewhere. I couldn't find it anywhere in the manuals. = Steve |
Fri, Feb 12 2016 6:11 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Steve,
<< I have already fixed it by setting new seed values after all of the records were imported. It would be nice if there was more documentation on this somewhere. I couldn't find it anywhere in the manuals. >> It's in the FAQ: http://www.elevatesoft.com/supportfaq?action=view&category=edb&question=altering_identity_seed_value Tim Young Elevate Software www.elevatesoft.com |
Sat, Feb 13 2016 8:46 AM | Permanent Link |
Steve Gill | << It's in the FAQ:>>
Thanks Tim. I didn't even know the FAQs were there. = Steve |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, May 23, 2024 at 07:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |