ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeff Butler <jeffgbut...@gmail.com>
Subject Re: iBator generatedKey Element usage with a stored procedure
Date Tue, 07 Apr 2009 03:01:05 GMT
My best guess would be to try something like this:

<table ...>
  <generatedKey column="theKeyColumn" sqlStatement="{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:
>   Hi everybody.  I'm a big iBator fan but haven't used it in a while.  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 procedure
> 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).
>   If this can be done,  then I'll use iBator.  As everyone knows, the
> 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).
>    To tell the truth,  I've never been able to get a stored procedure to
> work with iBatis.  I have a co-worked who inherited my old iBatis/Ibator
> projects, and even though he likes iBatis/ibator,  he gave up after spending
> a day trying to get a stored procedue to work in iBatis.  He couldn't figure
> out how to do it.     That is one reason why I'm a little worried that I'm
> going to end up  not being able to use iBatis/Ibator even though I want to
> use it.  It's a requirement of the project that it get the key for an insert
> from the stored procedure I mentioned.  I am pasting that stored procedure
> after my post, just in case that helps someone in giving me help on this.
> It's for MS SqlServer2005.  If this can actually be done, I sure would
> appreciate a sample of how to invoke that pesky stored procedure work from
> within the generatedKey element
> Thanks in advance (whatever the answer),
> Robert (a big iBator fan).
>
> -- =============================================
> -- Procedure name: GenerateIds
> -- Description:    <generates next primary key for the specified table>
> -- =============================================
> CREATE PROCEDURE [dbo].[GenerateIds]
>     @id varchar(100),
>     @count int =1
> AS
> BEGIN
>
> DECLARE @next_value bigint
>
> BEGIN TRY
>
>     BEGIN TRAN T1
>
>     DECLARE @i int
>     SET @i = 0
>
>     IF (SELECT count(*) FROM SEQUENCE_TABLE) <> 1
>         RAISERROR ('SEQUENCE_TABLE table was not properly initilazed',
>                16, -- Severity.
>                1 -- State.
>                );
>
>     /* Get next value */
>
>         IF @id = 'foo_table_name'
>                 SELECT    @next_value = next_foo_table_name_id
>
>                 FROM    SEQUENCE_TABLE
>         ELSE IF @id = 'bar_table_name'
>                 SELECT    @next_value = next_bar_table_name_id
>
>                 FROM    SEQUENCE_TABLE
>         ELSE
>             RAISERROR ('invalid paramater <table>. valid inputs are
> {foo_table_name, bar_table_name}',
>                16, -- Severity.
>                1 -- State.
>                );
>
>     WHILE @i < @count
>     BEGIN
>
>         /* Increment the sequence*/
>         SET @next_value = @next_value + 1
>
>         SET @i = @i + 1
>     END
>
>     /* Write incremented sequence back to the table */
>
>         IF @id = 'foo_table_name'
>             UPDATE    SEQUENCE_TABLE
>                SET    next_foo_table_name_id = @next_value
>         ELSE IF @id = 'bar_table_name'
>             UPDATE    SEQUENCE_TABLE
>                SET    next_bar_table_name_id = @next_value
>
>     SELECT @next_value - 1
>
>     COMMIT TRAN T1
>
> END TRY
>
> BEGIN CATCH
>     ROLLBACK TRAN T1
>
>    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState
> INT;
>    SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),
> @ErrorState = ERROR_STATE();
>     -- RETHROW ERROR
>     RAISERROR (@ErrorMessage, -- Message text.
>                @ErrorSeverity, -- Severity.
>                @ErrorState -- State.
>                );
> END CATCH;
>
> END
>
>
>
>

Mime
View raw message