ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From robertglove...@yahoo.com
Subject iBator generatedKey Element usage with a stored procedure
Date Mon, 06 Apr 2009 16:08:12 GMT
  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