Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 30770 invoked from network); 24 Mar 2010 18:12:22 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 24 Mar 2010 18:12:22 -0000 Received: (qmail 12585 invoked by uid 500); 24 Mar 2010 18:12:21 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 12553 invoked by uid 500); 24 Mar 2010 18:12:21 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 12546 invoked by uid 99); 24 Mar 2010 18:12:21 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 24 Mar 2010 18:12:21 +0000 X-ASF-Spam-Status: No, hits=-1.2 required=10.0 tests=AWL,HTML_MESSAGE,RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of Brian_Morearty@intuit.com designates 12.149.175.11 as permitted sender) Received: from [12.149.175.11] (HELO mail1.intuit.com) (12.149.175.11) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 24 Mar 2010 18:12:15 +0000 DomainKey-Signature: s=default; d=intuit.com; c=nofws; q=dns; h=X-SBRS:X-IronPort-AV:Received:Received:X-MimeOLE: x-cr-hashedpuzzle:MIME-Version:Content-Type:x-cr-puzzleid: Content-class:Subject:Date:Message-ID:X-MS-Has-Attach: X-MS-TNEF-Correlator:Thread-Topic:Thread-Index:From:To: Return-Path:X-OriginalArrivalTime:ETIME=[7C212D20; b=eSrVihVjIeCSpmc1+KQO9ypPLX40jgXLtplAaoYukV+B3RwrQTNLu5L6 4YbJXqHvW9Kpl2Yer13zib2W4SjaPWGv4lcxaZWChz+nyTEVb2OMJYqXl EVzNrocVRha6MLb; DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=intuit.com; i=Brian_Morearty@intuit.com; q=dns/txt; s=default; t=1269454335; x=1300990335; h=from:sender:reply-to:subject:date:message-id:to:cc: mime-version:content-transfer-encoding:content-id: content-description:resent-date:resent-from:resent-sender: resent-to:resent-cc:resent-message-id:in-reply-to: references:list-id:list-help:list-unsubscribe: list-subscribe:list-post:list-owner:list-archive; z=From:=20"Morearty,=20Brian"=20|Subject:=20newbie=20Q:=20get=20back=20generated=20key =20with=20Oracle|Date:=20Wed,=2024=20Mar=202010=2011:11:3 2=20-0700|Message-ID:=20|To:=20|MIME-Version:=201.0; bh=xyzVarGx28wveU5vvhJ0NPQE0pL5z5uvKwO45aTThLI=; b=KbYUWCmX5OsNACVWayfznBm0WcXAF6WnP+HA1j6jcj32wSjn5P08z3k2 dhOFmKSCLrit1Bzi2iw/EnCUTKq0JcCxmpe7jnsJyweKwlSyZ8U5hk3MB 5+sVNlOQGF+7e94; X-SBRS: None X-IronPort-AV: E=Sophos;i="4.51,302,1267430400"; d="scan'208,217";a="203744738" Received: from sdgexbh03.corp.intuit.net ([172.17.135.77]) by mail1.sdg.ie.intuit.com with ESMTP; 24 Mar 2010 11:11:54 -0700 Received: from SDGEXEVS06.corp.intuit.net ([172.17.135.181]) by SDGEXBH03.corp.intuit.net with Microsoft SMTPSVC(6.0.3790.3959); Wed, 24 Mar 2010 11:11:54 -0700 X-MimeOLE: Produced By Microsoft Exchange V6.5 x-cr-hashedpuzzle: Ah4D A0en A7YT BETO Bx8W DR47 Dd5F EGbR EMYZ FBu9 F0Ce F0xQ HSK0 HoJ/ IqPU I4oU;1;dQBzAGUAcgAtAGoAYQB2AGEAQABpAGIAYQB0AGkAcwAuAGEAcABhAGMAaABlAC4AbwByAGcA;Sosha1_v1;7;{5311DA6E-2B8C-4F30-8617-60889797D916};YgByAGkAYQBuAF8AbQBvAHIAZQBhAHIAdAB5AEAAaQBuAHQAdQBpAHQALgBjAG8AbQA=;Wed, 24 Mar 2010 18:11:33 GMT;bgBlAHcAYgBpAGUAIABRADoAIABnAGUAdAAgAGIAYQBjAGsAIABnAGUAbgBlAHIAYQB0AGUAZAAgAGsAZQB5ACAAdwBpAHQAaAAgAE8AcgBhAGMAbABlAA== MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CACB7D.70CA6C48" x-cr-puzzleid: {5311DA6E-2B8C-4F30-8617-60889797D916} Content-class: urn:content-classes:message Subject: newbie Q: get back generated key with Oracle Date: Wed, 24 Mar 2010 11:11:32 -0700 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: newbie Q: get back generated key with Oracle Thread-Index: AcrLfW8I5zBj5tWKT4qtQVqtVq7bjQ== From: "Morearty, Brian" To: X-OriginalArrivalTime: 24 Mar 2010 18:11:54.0738 (UTC) FILETIME=[7C212D20:01CACB7D] ------_=_NextPart_001_01CACB7D.70CA6C48 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, here's a newbie question:=20 =20 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.) =20 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.) =20 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: =20
=20 This generates the following mapping: =20 SELECT businesses_sequence.nextval FROM dual insert into WEBSITE_USER.BUSINESSES (ID, NAME, PHONE,=20 WEBSITE, CREATED_AT, UPDATED_AT ) values (#{id,jdbcType=3DDECIMAL}, #{name,jdbcType=3DVARCHAR}, #{phone,jdbcType=3DVARCHAR},=20 #{website,jdbcType=3DVARCHAR}, #{createdAt,jdbcType=3DTIMESTAMP}, #{updatedAt,jdbcType=3DTIMESTAMP} ) =20 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. =20 What else should I try? Or am I thinking about this the wrong way? =20 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. =20 =20 Brian Morearty | Grow Your Business Division, Intuit | Staff Software Engineer | direct 650-944-6852=20 =20 ------_=_NextPart_001_01CACB7D.70CA6C48 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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=3D"website_user" tableName=3D"businesses" domainObjectName=3D"Business">

      = <generatedKey column=3D"id" sqlStatement=3D"SELECT businesses_sequence.nextval FROM = dual" type=3D"pre"/>

</table>

 

This generates the following = mapping:

 

  <insert id=3D"insert" parameterType=3D"com.intuit.websites.models.generated.Busine= ss">

    <!--

      = 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=3D"id" order=3D"BEFORE" resultType=3D"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=3DDECIMAL}, #{name,jdbcType=3DVARCHAR}, = #{phone,jdbcType=3DVARCHAR},

      = #{website,jdbcType=3DVARCHAR}, #{createdAt,jdbcType=3DTIMESTAMP}, = #{updatedAt,jdbcType=3DTIMESTAMP}

      = )

  </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 =

 

------_=_NextPart_001_01CACB7D.70CA6C48--