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, <[email protected]> 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
>
>
>
>
