Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (nike.apache.org: domain of jeffgbutler@gmail.com
 designates 74.125.46.157 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        b=sTPgzNZvwLLDx0hZjfO4yBnpthlhZKWk4xS/h8D7zc7kG3bSdSWMZu0oNnK9UP08r+
         lwRaaOTf+oyCwhvWz0GtRFNbjNWmLBprQrZdAMteP3S00afwef19i2GpM7Z1GG7xCxlp
         QHqUMPiC+eBKdDp6dnfy1IutlwDzNNcENy02s=
MIME-Version: 1.0
In-Reply-To: <79080.49480.qm@web30806.mail.mud.yahoo.com>
References: <79080.49480.qm@web30806.mail.mud.yahoo.com>
Date: Mon, 6 Apr 2009 22:01:05 -0500
Message-ID: <c593b8750904062001y562a8cefr121b1a36ea01d3b9@mail.gmail.com>
Subject: Re: iBator generatedKey Element usage with a stored procedure
From: Jeff Butler <jeffgbutler@gmail.com>
To: user-java@ibatis.apache.org, robertgloverjr@yahoo.com
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

My best guess would be to try something like this:

<table ...>
  <generatedKey column=3D"theKeyColumn" sqlStatement=3D"{call
GenerateIds('YourTableName')}" />
</table>

I don't know if this will work or not, but it would be worth trying.

BTW - stored procedures work very well in iBATIS.

Jeff Butler


On Mon, Apr 6, 2009 at 11:08 AM,  <robertgloverjr@yahoo.com> wrote:
> =A0 Hi everybody.=A0 I'm a big iBator fan but haven't used it in a while.=
=A0 I can
> use iBator in my current project, but only if there is a way to use the
> generatedKey element (a sub-element of Table) to invoke a stored procedur=
e
> that takes as input the tablename and the number of unique keys desired
> (that would be always 1), and which returns a result set, each row
> containing exactly one column that is the unique key (there would always =
be
> exactly one row returned).
> =A0 If this can be done,=A0 then I'll use iBator.=A0 As everyone knows, t=
he
> conventional usage of the generatedKey element is with either a sequence =
set
> (which can be specified by a SQL statement, for example with an Oracle
> sequence set), or with an identify field (for example, as supported by
> MySql).
> =A0=A0 To tell the truth,=A0 I've never been able to get a stored procedu=
re to
> work with iBatis.=A0 I have a co-worked who inherited my old iBatis/Ibato=
r
> projects, and even though he likes iBatis/ibator,=A0 he gave up after spe=
nding
> a day trying to get a stored procedue to work in iBatis.=A0 He couldn't f=
igure
> out how to do it.=A0=A0=A0=A0 That is one reason why I'm a little worried=
 that I'm
> going to end up=A0 not being able to use iBatis/Ibator even though I want=
 to
> use it.=A0 It's a requirement of the project that it get the key for an i=
nsert
> from the stored procedure I mentioned.=A0 I am pasting that stored proced=
ure
> after my post, just in case that helps someone in giving me help on this.
> It's for MS SqlServer2005.=A0 If this can actually be done, I sure would
> appreciate a sample of how to invoke that pesky stored procedure work fro=
m
> within the generatedKey element
> Thanks in advance (whatever the answer),
> Robert (a big iBator fan).
>
> -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> -- Procedure name: GenerateIds
> -- Description:=A0=A0=A0 <generates next primary key for the specified ta=
ble>
> -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> CREATE PROCEDURE [dbo].[GenerateIds]
> =A0=A0=A0 @id varchar(100),
> =A0=A0=A0 @count int =3D1
> AS
> BEGIN
>
> DECLARE @next_value bigint
>
> BEGIN TRY
>
> =A0=A0=A0 BEGIN TRAN T1
>
> =A0=A0=A0 DECLARE @i int
> =A0=A0=A0 SET @i =3D 0
>
> =A0=A0=A0 IF (SELECT count(*) FROM SEQUENCE_TABLE) <> 1
> =A0=A0=A0 =A0=A0=A0 RAISERROR ('SEQUENCE_TABLE table was not properly ini=
tilazed',
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0 16, -- Severity.
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0 1 -- State.
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0 );
>
> =A0=A0=A0 /* Get next value */
>
> =A0=A0=A0 =A0=A0=A0 IF @id =3D 'foo_table_name'
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 SELECT=A0=A0=A0 @next_value =3D n=
ext_foo_table_name_id
>
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 FROM=A0=A0=A0 SEQUENCE_TABLE
> =A0=A0=A0 =A0=A0=A0 ELSE IF @id =3D 'bar_table_name'
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 SELECT=A0=A0=A0 @next_value =3D n=
ext_bar_table_name_id
>
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 FROM=A0=A0=A0 SEQUENCE_TABLE
> =A0=A0=A0 =A0=A0=A0 ELSE
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 RAISERROR ('invalid paramater <table>. vali=
d inputs are
> {foo_table_name, bar_table_name}',
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0 16, -- Severity.
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0 1 -- State.
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0 );
>
> =A0=A0=A0 WHILE @i < @count
> =A0=A0=A0 BEGIN
>
> =A0=A0=A0 =A0=A0=A0 /* Increment the sequence*/
> =A0=A0=A0 =A0=A0=A0 SET @next_value =3D @next_value + 1
>
> =A0=A0=A0 =A0=A0=A0 SET @i =3D @i + 1
> =A0=A0=A0 END
>
> =A0=A0=A0 /* Write incremented sequence back to the table */
>
> =A0=A0=A0 =A0=A0=A0 IF @id =3D 'foo_table_name'
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 UPDATE=A0=A0=A0 SEQUENCE_TABLE
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0 SET=A0=A0=A0 next_foo_table_name_id =
=3D @next_value
> =A0=A0=A0 =A0=A0=A0 ELSE IF @id =3D 'bar_table_name'
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 UPDATE=A0=A0=A0 SEQUENCE_TABLE
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0 SET=A0=A0=A0 next_bar_table_name_id =
=3D @next_value
>
> =A0=A0=A0 SELECT @next_value - 1
>
> =A0=A0=A0 COMMIT TRAN T1
>
> END TRY
>
> BEGIN CATCH
> =A0=A0=A0 ROLLBACK TRAN T1
>
> =A0=A0 DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorSt=
ate
> INT;
> =A0=A0 SELECT @ErrorMessage =3D ERROR_MESSAGE(),@ErrorSeverity =3D ERROR_=
SEVERITY(),
> @ErrorState =3D ERROR_STATE();
> =A0=A0=A0 -- RETHROW ERROR
> =A0=A0=A0 RAISERROR (@ErrorMessage, -- Message text.
> =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 @ErrorSeverity, -- Severity.
> =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 @ErrorState -- State.
> =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 );
> END CATCH;
>
> END
>
>
>
>