Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 22 total |
DBISAM3 to EDB - problem with AUTOINC to GENERATED IDENTITY columns |
Thu, Dec 12 2013 8:32 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi
Struggling with the EDB learning curve still. I have a table migrated from DBISAM3 with the supplied Migrator. My AUTOINC field has been converted to INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 36, INCREMENT BY 1) NOT NULL (see full SQL of the migrated table below. In my code I have the following which works OK with DBISAM3:- // get attachments sl.Text := TableXAttachments.AsString; for i := 0 to sl.Count - 1 do begin Attachments.Insert; AttachmentsPeriod.AsInteger := 0; showmessage(AttachmentsTableAttachmentID.AsString); // <<< displays as blank >>> AttachmentsType.AsString := AnotherTableAddresseeType.AsString; AttachmentsCode.AsString := AnotherTableAddresseeCode.AsString; AttachmentsPath.AsString := sl[i]; AttachmentsMailID.AsString := AnotherTableMailId.AsString; AttachmentsAttachment.LoadFromFile(sl[i]); Attachments.Post; // <<<< error on post >>> end; ------------------------------------------------ BUT in EDB I get this error message:- Project apm.exe raised exception class EDatabaseError with message 'Field 'AttachmentID' must have a value'. --------------------------- My understanding from the manual and newsgroup searches is that the value of AttachmentID is generated at the time of the .Insert. If I'm wrong, then presumably the only time that EDB can generate it is in the .Post - and my ShowMessage suggests I am wrong. Right or wrong, I think my code should post a record with the AttachmentID field generated. Cheers Jeff ---------------------------------------- CREATE TABLE "Attachments" ( "Period" INTEGER NOT NULL, "Type" VARCHAR(1) COLLATE "UNI" NOT NULL, "Code" VARCHAR(4) COLLATE "UNI" NOT NULL, "AttachmentID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 36, INCREMENT BY 1) NOT NULL, "Path" VARCHAR(250) COLLATE "UNI" NOT NULL, "MailID" VARCHAR(6) COLLATE "UNI", "Attachment" BLOB, "Description" VARCHAR(40) COLLATE "UNI", "TransNo" VARCHAR(6) COLLATE "UNI", "OriginalFileDeletedBy" VARCHAR(60) COLLATE "UNI", CONSTRAINT "PrimaryKey" PRIMARY KEY ("Type","Code","Period","AttachmentID") ) READWRITE UNENCRYPTED INDEX PAGE SIZE 8192 BLOB BLOCK SIZE 1024 PUBLISH BLOCK SIZE 1024 PUBLISH COMPRESSION 0 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768 MAX PUBLISH BUFFER SIZE 32768 |
Fri, Dec 13 2013 6:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
I have AUTOINCs splattered around my apps so I suspect a bug. I wonder if the problem is with the primary key definition. One of the changes from DBISAM to ElevateDB is that no part of a primary key can have null so it may be that its failing that test prior to assigning a value to AttachmentID. I don't have any primary keys like that so can't test it out without setting some stuff up. SO it will take me a while. Roy Lambert [Team Elevate] |
Fri, Dec 13 2013 6:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
I realised I could cut'n'paste your table definition - did so, changed UNI to ANSI and tested in EDBManager - it works. Can you try in EDBManager and post back. Roy Lambert [Team Elevate] |
Fri, Dec 13 2013 4:45 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | "Roy Lambert" <roy@lybster.me.uk> wrote in message
news:2520831D-D294-4FDF-B732-8F480F1BE18C@news.elevatesoft.com... > Jeff > > > I realised I could cut'n'paste your table definition - did so, changed UNI > to ANSI and tested in EDBManager - it works. Can you try in EDBManager and > post back. > > Roy Lambert [Team Elevate] > Hi Roy Yes, it works OK in EDBManager - I can insert a new record and the generated AttachmentID populates itself on Post. I only have the problem with the navigational Delphi code. I COULD change the code in this instance to use SQL to insert the record as I don't have any BeforePost or AfterPost events on this particular table, but that is not a general solution as I have other tables that have ID columns and do have those events. (Not to mention the work going through the app making multiple changes - oops, I did mention it!) Before anyone asks - yes I am certain that the same database is being used - I only have one, this being my first EDB project. (EDB 2.14 Build 2, Unicode DB, Delphi XE) Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Sat, Dec 14 2013 4:18 AM | Permanent Link |
Uli Becker | Jeff,
I also tried your table and created a small project: as expected no problems. There must be something special in your project. Can you post the *.pas and *.dfm? Regards Uli |
Sat, Dec 14 2013 4:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
I was going to ask what Uli did only I was going to say the project, and for the non-unicode amongst us (ie me) a reverse engineered copy of the database. Since it works in EDBManager and not your code we have at least reached a point where it has to be something in your code (this is good cos we now know where to look If you don't have MadExcept or EurekaLog its probably worth installing one or the other. Its call stack may give additional clues. Its also worth creating a small project with just that table and try and replicate the problem. If it doesn't then it has to be something in your app. Obvious candidates are any events hooked up to the table (you've already eliminated those), its datasource or any other tables in a master-detail relationship. Next layer are any components used to display the table which may be triggering some sort of action. This is my guess as to where the problem is coming from and the only approach is to start removing components until it goes away. I can't remember how many days of fun I've had doing this! Final option is threads. If there are any. Roy Lambert [Team Elevate] |
Sat, Dec 14 2013 6:45 PM | Permanent Link |
Arthur Williams Zarksoft | I would try getting rid of that "NOT NULL" on the AttachmentID field.
Jeff Cook wrote: CREATE TABLE "Attachments" ( "Period" INTEGER NOT NULL, "Type" VARCHAR(1) COLLATE "UNI" NOT NULL, "Code" VARCHAR(4) COLLATE "UNI" NOT NULL, "AttachmentID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 36, INCREMENT BY 1) NOT NULL, |
Sun, Dec 15 2013 7:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Arthur
>I would try getting rid of that "NOT NULL" on the AttachmentID field. That's not the problem. Since it works in EDBManager its Jeff's code somewhere or other. Roy Lambert [Team Elevate] |
Sun, Dec 15 2013 5:42 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi Roy and Uli
Here is a sample project. When Roy asked, I groaned thinking of how to trim the project down (and how I had done so in the past taking many hours) but then decided to go the whole hog and build a little project from scratch. The little project is attached - this is the first time I have "deployed" any EDB project so I'm not sure if I have given you all that is required. Attached is a reverse engineered table and the source of the project that shows the problem. Cheers Jeff Attachments: AutoInc.zip |
Sun, Dec 15 2013 7:31 PM | Permanent Link |
Arthur Williams Zarksoft | Right, because Jeff just copied his code from EDBManager. Got it.
Roy Lambert wrote: Arthur >I would try getting rid of that "NOT NULL" on the AttachmentID field. That's not the problem. Since it works in EDBManager its Jeff's code somewhere or other. Roy Lambert [Team Elevate] |
Page 1 of 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Thursday, July 4, 2024 at 06:12 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |