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
