What did I say!! I knew Bob would have the answer and that it would be supported in Transfer some how..
Superb.. Thanks for the tip Bob. --- James Allen E: slingsho...@googlemail.com -----Original Message----- From: transfer-dev@googlegroups.com [mailto:transfer-...@googlegroups.com] On Behalf Of Bob Silverberg Sent: 03 March 2009 15:37 To: transfer-dev@googlegroups.com Subject: [transfer-dev] Re: Autoincredmening a non-primary key field. It sounds like you've figured out that using an auto-increment field for this purpose isn't going to work, as you cannot update the value. For future reference, if you did want to have an auto-increment field in a table and you want to tell Transfer to NOT attempt to insert or update the field, you could do it like this: <property name="Rank" type="numeric" column="Rank" ignore-insert="true" refresh-insert="true" ignore-update="true" /> Cheers, Bob On Tue, Mar 3, 2009 at 9:51 AM, James Allen <slingsho...@googlemail.com> wrote: > > Hey there, > > I totally know where your coming from and I'd prefer some kind of option on > the property to state it's an autonumber so that Transfer won't try to > modify the value but will let you read it. > > Thinking about it though, if you took control of the rank allocation it > might be more flexible for future expansion. What if you want to change the > ranking system at some point or re-rank the articles (ah I just re-read your > reply ;) ). With an autoincrementer field you can't update the value as each > one has to be unique. Therefore by using a normal integer field you have > much more control over how it all works. > > Yeah, I'd just throw a method in your gateway whose sole purpose is to lock > the D/B and grab the biggest rank. You can then increment it in your beans > save() method. > > James. > > --- > James Allen > E: slingsho...@googlemail.com > > > -----Original Message----- > From: transfer-dev@googlegroups.com [mailto:transfer-...@googlegroups.com] > On Behalf Of Sir Rawlins > Sent: 03 March 2009 14:00 > To: transfer-dev > Subject: [transfer-dev] Re: Autoincredmening a non-primary key field. > > > J! <high five> Good to see you mate. > > That certainly sounds like a good option! it is a little long winded > but not impossible, I just wonder if there is a better option, perhaps > a setting in my SQL Server that'll do it for me? I like your approach > though, its not THAT bad if that's the route I've got to go, I'd just > rather avoid a code work around if its possible y'know? > > Hmmm, with these auto-incrementing columns am I able to 'edit' or > 'update' the value at a later stage? I'm not am I? that will cause > problems when trying to re-rank the article anyway so perhaps your > approach is the best option, just have it pull the highest value in > the DB and stick another 1 on it? > > Rob > > On Mar 3, 1:26 pm, "James Allen" <slingsho...@googlemail.com> wrote: >> Sir Rawlins! >> >> <high five> >> >> :) >> >> Your problem is an interesting one. The error is like you say is caused by >> Rank being an auto incrementer field. When saving the object, Transfer is >> using a NULL value in the INSERT statement and this of course is illegal > in >> SQL server due to the field type. >> >> My suggestion is not ideal but would be to change the field to a normal >> numeric type and then handle the auto-increment functionality in an >> overridden setRank() function within the decorator. >> >> I would probably inject my ArticleService (if your using one) into the >> decorator and have a function in there called getNextRank() which would > call >> a method in the gateway to get the highest rank so far from the D/B using >> normal SQL. You could then increment this and store it in the article > bean. >> >> Just thinking about this, and you may be better to have this code in the >> save() method (if you are using one) in the bean. >> >> Long winded but I don't know if Transfer has a way around this - but > knowing >> me there probably is one which Bob (or Mark) will probably point out >> shortly.. ;) >> >> Cheers, >> James. >> >> --- >> James Allen >> E: slingsho...@googlemail.com >> >> -----Original Message----- >> From: transfer-dev@googlegroups.com [mailto:transfer-...@googlegroups.com] >> >> On Behalf Of Sir Rawlins >> Sent: 03 March 2009 12:49 >> To: transfer-dev >> Subject: [transfer-dev] Autoincredmening a non-primary key field. >> >> Morning guys, >> >> It's been a while :-) got myself a new challenge today. I've got a >> table which stores data on articles, I've got an auto-incrementing >> field on the table which denotes the articles 'rank' which I use for >> ordering them and allowing users to increase or decrease an articles >> rank but we keep it auto incrementing to avoid duplicates, however, >> this is NOT the primary key of the table. >> >> The Transfer XML for this table looks as such: >> >> <object name="Article" table="Article" >> decorator="Model.Article.Article"> >> <id name="Article_ID" type="numeric" /> >> <property name="Title" type="string" >> column="Title" /> >> <property name="ShortDescription" >> type="string" >> column="ShortDescription" /> >> <property name="Heading" type="string" >> column="Heading" /> >> <property name="SubHeading" type="string" >> column="SubHeading" /> >> <property name="ArticleContent" >> type="string" >> column="ArticleContent" /> >> <property name="DateCreated" type="date" >> column="DateCreated" /> >> <property name="DateModified" type="date" >> column="DateModified" /> >> <property name="Rank" type="numeric" >> column="Rank" /> >> <manytomany name="ArticleKeyword" >> table="ArticleKeywordArticle"> >> <link to="Article.Article" >> column="Article_ID" /> >> <link to="Article.ArticleKeyword" >> column="ArticleKeyword_ID" /> >> <collection type="array"> >> <order property="Keyword" >> order="asc" /> >> </collection> >> </manytomany> >> </object> >> >> Note the 'rank' field. Now, when trying to have Transfer save a new >> instance of an article I get the following spewed back at me. >> >> essage Error Executing Database Query. >> Detail [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert >> explicit value for identity column in table 'Article' when >> IDENTITY_INSERT is set to OFF >> >> Can you guys offer any suggestions as to what is causing this and how >> it might be solved? Presumably this is caused by Transfer trying to >> inset a value into the auto-incrementing field? is that correct? >> >> Cheers, >> >> Rob > > > > > -- Bob Silverberg www.silverwareconsulting.com --~--~---------~--~----~------------~-------~--~----~ Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to transfer-dev@googlegroups.com To unsubscribe from this group, send email to transfer-dev-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en -~----------~----~----~----~------~----~------~--~---