IBATIS.NET: Stored procedure ReturnValue

[Oddur Snær Magnússon]

Hi All, 

I'm currently evaluating ibatis.net to use with a database which only allows 
access through a fixed set of stored procedures. 
The insert procedures return the identity value with the sql return statement 
rather than an out parameter, like so:

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

And I need to get access to the return value.

So far I've got the statement like so:

<procedure id="CreateFoo" parameterMap="FooClass" 
resultClass="int">DoFoo</procedure>

The procedure executes as planned ( new foo inserted in the DB), but this code:

int id = ( int ) Mapper.Instance ( ).Insert ( "CreateFoo", fooInstance ) ;

Always returns a value of 0.

I know ibatis supports the use of out parameters to sprocs, but in this case 
I'd rather avoid modifying the sproc code.

So when looking at the ExecuteInsert method on MappedStatement, I realize that 
I can never get a hold of the return value since there is never added a 
parameter to the command with a direction of ReturnValue. And if the identity 
is returned using a return rather than a select, either one will be null. So I 
can safely assume that is the return value from ExecuteScalar is null and the 
value of the return value parameters is not null, I can assume that the 
idendity value is being returned througth the return statement. 

So I'd suggest modifying ExecuteInsert:

1. Add a default return value parameter to the command before execution. 

2. Check the scalar value if it is null, if not, use the scalar value, then 
check if the return parameter value is null, if not, then use the return value.

If no resultClass is defined for that statement, then a null will be returned.

Refined ExecuteInsert:

                public virtual object ExecuteInsert(IDalSession session, object 
parameterObject )
                {
                        object generatedKey = null;
                        SelectKey selectKeyStatement = null;
                        RequestScope request = 
_statement.Sql.GetRequestScope(parameterObject, session);;

                        if (_statement is Insert)
                        {
                                selectKeyStatement = 
((Insert)_statement).SelectKey;
                        }

                        if (selectKeyStatement != null && 
!selectKeyStatement.isAfter)
                        {
                                MappedStatement mappedStatement = 
_sqlMap.GetMappedStatement( selectKeyStatement.Id );
                                generatedKey = 
mappedStatement.ExecuteQueryForObject(session, parameterObject);

                                ObjectProbe.SetPropertyValue(parameterObject, 
selectKeyStatement.PropertyName, generatedKey);
                        }

                        //using (IDbCommand command = 
CreatePreparedCommand(request, session, parameterObject ))
                        using ( IDbCommand command = _preparedCommand.Create( 
request, session, this.Statement, parameterObject ) )
                        {
                                if (_statement is Insert)
                                {
                                        command.ExecuteNonQuery();
                                }
                                else
                                {
        
                                IDataParameter returnValueParameter  = 
command.CreateParameter();
                                    returnValueParameter.ParameterName = 
"@return_value";
                                    returnValueParameter.Direction = 
ParameterDirection.ReturnValue;
                                command.Parameters.Add(returnValueParameter);

                                    
                                        generatedKey = command.ExecuteScalar();
                                        
                                    if ( (generatedKey != null) && 
(_statement.ResultClass!=null) && 
                                                
_sqlMap.TypeHandlerFactory.IsSimpleType(_statement.ResultClass) )
                                        {
                                                ITypeHandler typeHandler = 
_sqlMap.TypeHandlerFactory.GetTypeHandler(_statement.ResultClass);
                                                generatedKey = 
typeHandler.GetDataBaseValue(generatedKey, _statement.ResultClass);
                                        }else if ( (generatedKey == null) && 
(returnValueParameter.Value != null) 
                                        &&(_statement.ResultClass!=null) && 
_sqlMap.TypeHandlerFactory.IsSimpleType(_statement.ResultClass) )
                                        {
                        ITypeHandler typeHandler = 
_sqlMap.TypeHandlerFactory.GetTypeHandler(_statement.ResultClass);
                        generatedKey = 
typeHandler.GetDataBaseValue(returnValueParameter.Value, 
_statement.ResultClass);
                                        }
                                }
                        
                                if (selectKeyStatement != null && 
selectKeyStatement.isAfter)
                                {
                                        MappedStatement mappedStatement = 
_sqlMap.GetMappedStatement( selectKeyStatement.Id );
                                        generatedKey = 
mappedStatement.ExecuteQueryForObject(session, parameterObject);

                                        
ObjectProbe.SetPropertyValue(parameterObject, selectKeyStatement.PropertyName, 
generatedKey);
                                }

                                ExecutePostSelect( session, request);

                                RetrieveOutputParameters(request, session, 
command, parameterObject);
                        }

                        RaiseExecuteEvent();

                        return generatedKey;
                }





- Oddur Magnusson