Right! On Fri, Mar 26, 2010 at 3:12 PM, Morearty, Brian <brian_morea...@intuit.com> wrote: > Thanks, Jeff. And now I see the likely fix: when there's a <generatedKey > type="pre"/> in the Ibator config, Ibator should always include the ID in the > insert statement instead of conditionally including it. > > Brian > > > -----Original Message----- > From: Jeff Butler [mailto:jeffgbut...@gmail.com] > Sent: Friday, March 26, 2010 1:01 PM > To: user-java@ibatis.apache.org > Subject: Re: newbie Q: get back generated key with Oracle > > This is an Ibator issue and it's an interesting case that I had not > considered previously. I'll fix it. > > Jeff Butler > > On Wed, Mar 24, 2010 at 5:33 PM, Morearty, Brian > <brian_morea...@intuit.com> wrote: >> Related to this discussion earlier today, I have a feature request. I'm not >> sure if this should be in iBATIS or Ibator. >> >> Problem: if I let Oracle autogenerate a primary key for me, like this: >> >> <insert id="insertSelective" >> parameterType="com.intuit.websites.models.generated.Business"> >> <selectKey keyProperty="id" order="BEFORE" >> resultType="java.math.BigDecimal"> >> SELECT businesses_sequence.nextval FROM dual >> </selectKey> >> insert into WEBSITE_USER.BUSINESSES >> <trim prefix="(" suffix=")" suffixOverrides=","> >> <if test="id != null"> >> ID, >> </if> >> .... >> >> then the Ibator-generated "insertSelective" statement fails with an error: >> >> ORA-01400: cannot insert NULL into ("WEBSITE_USER"."BUSINESSES"."ID") >> >> Why? Because I didn't set the ID (I was planning to let it be generated) and >> apparently the <if test="id != null"> gets evaluated before the value gets >> inserted into the ID. >> >> I do have a workaround: always insert a dummy ID of 0 before calling >> insertSelective. But it would be nice if I didn't have to. >> >> Not sure what the best solution is but it seems like it would be nice to fix. >> >> Brian >> >> >> >> -----Original Message----- >> From: Morearty, Brian [mailto:brian_morea...@intuit.com] >> Sent: Wednesday, March 24, 2010 11:18 AM >> To: user-java@ibatis.apache.org >> Subject: RE: newbie Q: get back generated key with Oracle >> >> Oh yeah, it does! Duh! Woo hoo. >> >> Thanks for the quick reply, Larry. >> >> >> -----Original Message----- >> From: Larry Meadors [mailto:larry.mead...@gmail.com] >> Sent: Wednesday, March 24, 2010 11:15 AM >> To: user-java@ibatis.apache.org >> Subject: Re: newbie Q: get back generated key with Oracle >> >> It should set the id property on the Business bean passed in as a parameter. >> >> Larry >> >> >> On Wed, Mar 24, 2010 at 12:11 PM, Morearty, Brian >> <brian_morea...@intuit.com> wrote: >>> Hi, here's a newbie question: >>> >>> >>> >>> I'm using iBATIS, Ibator, and Oracle. I have a "businesses" table. I also >>> created a "businesses_sequence" sequence so I can autogenerate primary keys. >>> (Apparently Oracle does it that way instead of allowing an autoincrement >>> column.) >>> >>> >>> >>> How do I get back the primary key after an insert statement? (So I can, for >>> example, add reference the thing I just inserted from other tables.) >>> >>> >>> >>> Originally I was using a before-insert trigger to select the next value from >>> the sequence but I don't get the value back from the mapper's insert >>> statement. I realized I probably had to use the 'generatedKey' statement so >>> I tried this: >>> >>> >>> >>> <table schema="website_user" tableName="businesses" >>> domainObjectName="Business"> >>> >>> <generatedKey column="id" sqlStatement="SELECT >>> businesses_sequence.nextval FROM dual" type="pre"/> >>> >>> </table> >>> >>> >>> >>> This generates the following mapping: >>> >>> >>> >>> <insert id="insert" >>> parameterType="com.intuit.websites.models.generated.Business"> >>> >>> <!-- >>> >>> WARNING - @ibatorgenerated >>> >>> This element is automatically generated by Apache iBATIS Ibator, do >>> not modify. >>> >>> This element was generated on Wed Mar 24 10:49:12 PDT 2010. >>> >>> --> >>> >>> <selectKey keyProperty="id" order="BEFORE" >>> resultType="java.math.BigDecimal"> >>> >>> SELECT businesses_sequence.nextval FROM dual >>> >>> </selectKey> >>> >>> insert into WEBSITE_USER.BUSINESSES (ID, NAME, PHONE, >>> >>> WEBSITE, CREATED_AT, UPDATED_AT >>> >>> ) >>> >>> values (#{id,jdbcType=DECIMAL}, #{name,jdbcType=VARCHAR}, >>> #{phone,jdbcType=VARCHAR}, >>> >>> #{website,jdbcType=VARCHAR}, #{createdAt,jdbcType=TIMESTAMP}, >>> #{updatedAt,jdbcType=TIMESTAMP} >>> >>> ) >>> >>> </insert> >>> >>> >>> >>> It successfully selects from the sequence but it does not return the >>> inserted id to me, porbably because the last statement is an insert, not a >>> select. >>> >>> >>> >>> What else should I try? Or am I thinking about this the wrong way? >>> >>> >>> >>> P.S. This is Oracle 10g Express Edition with ojdbc14.jar. Supposedly it >>> supports the JDBC getGeneratedKey() function-I'm not sure if iBATIS under >>> the hood calls that function. >>> >>> >>> >>> >>> >>> Brian Morearty | Grow Your Business Division, Intuit | Staff Software >>> Engineer | direct 650-944-6852 >>> >>> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org >> For additional commands, e-mail: user-java-h...@ibatis.apache.org >> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org >> For additional commands, e-mail: user-java-h...@ibatis.apache.org >> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org >> For additional commands, e-mail: user-java-h...@ibatis.apache.org >> >> > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org > For additional commands, e-mail: user-java-h...@ibatis.apache.org > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org > For additional commands, e-mail: user-java-h...@ibatis.apache.org > >
--------------------------------------------------------------------- To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org For additional commands, e-mail: user-java-h...@ibatis.apache.org
