Bob, J, thank you both, I think my brain was just in the wrong place this morning :-) I'm off to work on this strategy now so we'll see how it pans out but I think it'll work nicely!
Cheers all, Rob On Mar 3, 3:37 pm, Bob Silverberg <[email protected]> wrote: > 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 <[email protected]> > 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: [email protected] > > > -----Original Message----- > > From: [email protected] [mailto:[email protected]] > > 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" <[email protected]> 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: [email protected] > > >> -----Original Message----- > >> From: [email protected] [mailto:[email protected]] > > >> 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 Silverbergwww.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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en -~----------~----~----~----~------~----~------~--~---
