You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by ro...@yahoo.com on 2009/04/06 18:08:12 UTC
iBator generatedKey Element usage with a stored procedure
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
Re: iBator generatedKey Element usage with a stored procedure
Posted by Jeff Butler <je...@gmail.com>.
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, <ro...@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
>
>
>
>