You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "Oddur Magnusson (JIRA)" <ib...@incubator.apache.org> on 2006/06/26 18:59:29 UTC

[jira] Created: (IBATISNET-165) Getting return value generatedKeys from stored procedures in inserts

Getting return value generatedKeys from stored procedures in inserts
--------------------------------------------------------------------

         Key: IBATISNET-165
         URL: http://issues.apache.org/jira/browse/IBATISNET-165
     Project: iBatis for .NET
        Type: Improvement

  Components: DataMapper  
    Versions: DataAccess 1.5    
 Environment: .net framework 2.0
    Reporter: Oddur Magnusson


When calling a stored procedure that does a insert and returns the row idendity in a return statement, like

CREATE PROCEDURE [dbo].[DoFoo]  @foo int AS
INSERT INTO Foos (foo) VALUES (@foo)
RETURN @@IDENTITY

there is no way of accessing the return value and returning it as the generatedKey

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (IBATISNET-165) Getting return value generatedKeys from stored procedures in inserts

Posted by "Gilles Bayon (JIRA)" <ib...@incubator.apache.org>.
    [ http://issues.apache.org/jira/browse/IBATISNET-165?page=comments#action_12417843 ] 

Gilles Bayon commented on IBATISNET-165:
----------------------------------------

You can retrieve it with an output parameter

CREATE PROCEDURE dbo.[ps_InsertCategorie]
@Category_Id [int] output,
@Category_Name [varchar] (32),
@Category_Guid [uniqueidentifier] 
AS
insert into Categories  
			(Category_Name, Category_Guid ) 
values 
			(@Category_Name, @Category_Guid)
SELECT @Category_Id = SCOPE_IDENTITY()

> Getting return value generatedKeys from stored procedures in inserts
> --------------------------------------------------------------------
>
>          Key: IBATISNET-165
>          URL: http://issues.apache.org/jira/browse/IBATISNET-165
>      Project: iBatis for .NET
>         Type: Improvement

>   Components: DataMapper
>     Versions: DataAccess 1.5
>  Environment: .net framework 2.0
>     Reporter: Oddur Magnusson

>
> When calling a stored procedure that does a insert and returns the row idendity in a return statement, like
> CREATE PROCEDURE [dbo].[DoFoo]  @foo int AS
> INSERT INTO Foos (foo) VALUES (@foo)
> RETURN @@IDENTITY
> there is no way of accessing the return value and returning it as the generatedKey

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Closed: (IBATISNET-165) Getting return value generatedKeys from stored procedures in inserts

Posted by "Gilles Bayon (JIRA)" <ib...@incubator.apache.org>.
     [ http://issues.apache.org/jira/browse/IBATISNET-165?page=all ]
     
Gilles Bayon closed IBATISNET-165:
----------------------------------

    Fix Version: DataMapper 1.5
     Resolution: Fixed

In SVN

> Getting return value generatedKeys from stored procedures in inserts
> --------------------------------------------------------------------
>
>          Key: IBATISNET-165
>          URL: http://issues.apache.org/jira/browse/IBATISNET-165
>      Project: iBatis for .NET
>         Type: Improvement

>   Components: DataMapper
>     Versions: DataAccess 1.5
>  Environment: .net framework 2.0
>     Reporter: Oddur Magnusson
>      Fix For: DataMapper 1.5
>  Attachments: MappedStatement.cs.patch, category-procedureWithReturn.sql, complete.patch, procedure.cs.patch
>
> When calling a stored procedure that does a insert and returns the row idendity in a return statement, like
> CREATE PROCEDURE [dbo].[DoFoo]  @foo int AS
> INSERT INTO Foos (foo) VALUES (@foo)
> RETURN @@IDENTITY
> there is no way of accessing the return value and returning it as the generatedKey

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (IBATISNET-165) Getting return value generatedKeys from stored procedures in inserts

Posted by "Oddur Magnusson (JIRA)" <ib...@incubator.apache.org>.
    [ http://issues.apache.org/jira/browse/IBATISNET-165?page=comments#action_12417850 ] 

Oddur Magnusson commented on IBATISNET-165:
-------------------------------------------

But when you don't have the option of changing your stored procedures, that's not a viable option. 

I suggest adding a useReturnValue boolean attribute to the <procedure> tag that tells ExecuteInsert if it should get the generatedKey from the return value:

<procedure id="CreateStuff" parameterMap="Stuff" resultClass="int" useReturnValue="true">CreateStuff</procedure>

and in ExecuteInsert in MappedStatement.cs check if we want to use the return value:

if (_statement is Insert)
				{
					command.ExecuteNonQuery();
				}
	
				else if (_statement is Procedure && ((Procedure) _statement).UseReturnValue)
                {
                    IDataParameter rvp = command.CreateParameter ( );
                    rvp.Direction = ParameterDirection.ReturnValue;
                    command.Parameters.Add ( rvp );
                    command.ExecuteNonQuery ( );
                    generatedKey = rvp.Value ;
                    if ( ( _statement.ResultClass != null ) &&
                        _sqlMap.TypeHandlerFactory.IsSimpleType ( _statement.ResultClass ) )
                    {
                        ITypeHandler typeHandler = _sqlMap.TypeHandlerFactory.GetTypeHandler ( _statement.ResultClass );
                        generatedKey = typeHandler.GetDataBaseValue ( generatedKey, _statement.ResultClass );
                    }
				}
			    else
				{

I've implemented this for our current usage of ibatis. I'm attaching the pathes we did to this issue.

> Getting return value generatedKeys from stored procedures in inserts
> --------------------------------------------------------------------
>
>          Key: IBATISNET-165
>          URL: http://issues.apache.org/jira/browse/IBATISNET-165
>      Project: iBatis for .NET
>         Type: Improvement

>   Components: DataMapper
>     Versions: DataAccess 1.5
>  Environment: .net framework 2.0
>     Reporter: Oddur Magnusson
>  Attachments: MappedStatement.cs.patch, procedure.cs.patch
>
> When calling a stored procedure that does a insert and returns the row idendity in a return statement, like
> CREATE PROCEDURE [dbo].[DoFoo]  @foo int AS
> INSERT INTO Foos (foo) VALUES (@foo)
> RETURN @@IDENTITY
> there is no way of accessing the return value and returning it as the generatedKey

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (IBATISNET-165) Getting return value generatedKeys from stored procedures in inserts

Posted by "Gilles Bayon (JIRA)" <ib...@incubator.apache.org>.
    [ http://issues.apache.org/jira/browse/IBATISNET-165?page=comments#action_12418077 ] 

Gilles Bayon commented on IBATISNET-165:
----------------------------------------

I don't think we need the attribute UseReturnValue whcih can be replace by
 _statement.ResultClass != null ) && 
                        _sqlMap.TypeHandlerFactory.IsSimpleType ( _statement.ResultClass ) 
and so we have the code

else if (_statement is Procedure && (_statement.ResultClass != null) &&
                        _sqlMap.TypeHandlerFactory.IsSimpleType(_statement.ResultClass) )
                {
                    IDataParameter returnValueParameter = command.CreateParameter();
                    returnValueParameter.Direction = ParameterDirection.ReturnValue;
                    command.Parameters.Add(returnValueParameter);
                    
                    command.ExecuteNonQuery ( );
                    generatedKey = returnValueParameter.Value;

                    ITypeHandler typeHandler = _sqlMap.TypeHandlerFactory.GetTypeHandler ( _statement.ResultClass );
                    generatedKey = typeHandler.GetDataBaseValue ( generatedKey, _statement.ResultClass );
}

				}

> Getting return value generatedKeys from stored procedures in inserts
> --------------------------------------------------------------------
>
>          Key: IBATISNET-165
>          URL: http://issues.apache.org/jira/browse/IBATISNET-165
>      Project: iBatis for .NET
>         Type: Improvement

>   Components: DataMapper
>     Versions: DataAccess 1.5
>  Environment: .net framework 2.0
>     Reporter: Oddur Magnusson
>  Attachments: MappedStatement.cs.patch, category-procedureWithReturn.sql, complete.patch, procedure.cs.patch
>
> When calling a stored procedure that does a insert and returns the row idendity in a return statement, like
> CREATE PROCEDURE [dbo].[DoFoo]  @foo int AS
> INSERT INTO Foos (foo) VALUES (@foo)
> RETURN @@IDENTITY
> there is no way of accessing the return value and returning it as the generatedKey

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira