RE: newbie Q: get back generated key with Oracle
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
Re: newbie Q: get back generated key with Oracle
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
newbie Q: get back generated key with Oracle
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
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
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
RE: newbie Q: get back generated key with Oracle
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
