Nice one Sir R and good to see you back on the list and stimulating our brains.. ;)
Hope everything is kicking ass with the business.. --- James Allen E: [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Sir Rawlins Sent: 04 March 2009 17:35 To: transfer-dev Subject: [transfer-dev] Re: Autoincredmening a non-primary key field. James you sexy boy, I implemented your solution to this and it works a charm, good thinking. Rob On Mar 3, 3:42 pm, Sir Rawlins <[email protected]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
