Execute fails on iDB2 with parameters of different data types

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Execute an insert statement for a table that has column data types of string 
and double.  For example "INSERT INTO F550411(QB$B1, QB$B2) VALUES (@employee, 
@amount)" QB$B1 is a char(8) and QB$B2 is numeric.
2. Pass the parameters in as an anonymous type: var parameters = new {"name", 
1.1}

Instead of inserting the data, the driver throws an exception: 
System.IndexOutOfRangeException: Index was outside the bounds of the array.
   at System.Text.StringBuilder.get_Chars(Int32 index)
   at IBM.Data.DB2.iSeries.iDB2DecNumeric.NormalizeNumericString(String stringVal, String& outStringVal, Int32 maxprecision, Boolean useCurrentCulture, Boolean& isNegative, Boolean removeTrailingZeros)
   at IBM.Data.DB2.iSeries.MPParamConverter.setString(Int32 parameterIndex, String parmname, iDB2Command cmd, String str, MpDcData& ds, Int32 jobccsid, Int32 size, Byte precision, Byte scale, UInt32 lobBlockSize)
   at IBM.Data.DB2.iSeries.iDB2Command.setRowOfParameterData(MpDcData[]& dcDataRow)
   at IBM.Data.DB2.iSeries.iDB2Command.execute()
   at IBM.Data.DB2.iSeries.iDB2Command.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, IDbTransaction transaction, String sql, Action`2 paramReader, Object obj, Nullable`1 commandTimeout, Nullable`1 commandType) in c:\Project\app\Core\SqlMapper.cs:line 2042
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in c:\Project\app\Core\SqlMapper.cs:line 730

I can execute the same SQL using plain ADO.NET and it works properly; even by 
adding parameters in the same fashion Dapper is doing.  e.g. 
var param = command.CreateParameter();
param.ParameterName = "employee";
param.DbType = System.Data.DbType.String;
param.Direction = System.Data.ParameterDirection.Input;
param.Value = "name";
command.Parameters.Add(param);
var param2 = command.CreateParameter();
param2.ParameterName = "amount";
param2.DbType = System.Data.DbType.Double;
param2.Direction = System.Data.ParameterDirection.Input;
param2.Value = 1.1;
command.Parameters.Add(param2);

I can also successfully insert with Dapper when using only columns of type 
string or double; the problem only occurs when using them both in the same 
statement.  It would seem something isn't resetting between adding parameters 
in the CreateParamInfoGenerator method, but that's just my guess based on the 
results I'm seeing.  I've inspected the Parameters on the command object didn't 
see anything different between the way Dapper generates it and plain ADO.NET.

I realize this is a very specific situation and probably nobody uses an iDB2 
driver (connecting to an IBM iSeries aka AS/400).

Original issue reported on code.google.com by jonesc...@gmail.com on 7 Jul 2013 at 9:05

[GoogleCodeExporter]

Did you get a solution, or workaround to this? I'm having a similar issue 
inserting numeric values using the iDB2 driver.

Regards,

Original comment by walter.p...@gmail.com on 25 Sep 2013 at 8:19

[GoogleCodeExporter]

Unfortunately no I never found a solution to this problem.  I just ended up 
doing plain ADO.NET in this case.  We've built other applications that use 
nhibernate with iDB2 and that works great if you don't mind using a full-on ORM.

Original comment by jonesc...@gmail.com on 28 Sep 2013 at 3:11

[GoogleCodeExporter]

hi,
after a bit of I managed to get this working (we runa also against a as400) you 
need to set all parameters as type DbType.Object

I overrode the execute method and then looped over the dynamic parameters added 
the required information. this has its draw backs but you get the point
Here is a code snippet. class 

 public int Execute(string sql, dynamic parameters = null, IDbTransaction transaction = null)
        {
            var objectParameters = OverrideDapperParameterMapping(parameters);

            using (var connection = CreateConnection())
            {
                return SqlMapper.Execute(connection, sql, objectParameters, transaction, _db2Config.CommandTimeoutSeconds, CommandType.Text);
            }
        }

        private DynamicParameters OverrideDapperParameterMapping(dynamic parameters)
        {
            if (parameters == null)
                return null;

            //todo DOES NOT handle a list of a parameters as dapper does need implementing
            return CreateDb2CompliantDynamicParameters(parameters);
        }

        private static DynamicParameters CreateDb2CompliantDynamicParameters(dynamic parameters)
        {
            var dynamicParameters = new DynamicParameters();
            foreach (PropertyDescriptor property in TypeDescriptor.GetProperties(parameters))
            {
                dynamicParameters.Add(property.Name.Replace('_', '-'), property.GetValue(parameters), DbType.Object);
            }
            return dynamicParameters;
        }

 public IDbConnection CreateConnection()
        {
            var connection = new iDB2Connection(_db2Config.ConnectionString);
            connection.Open();

            return connection;
        }

Original comment by chocobsm...@gmail.com on 29 Nov 2013 at 9:31

[GoogleCodeExporter]

another method which suits IoC a bit better 

//call using 
var sql ="select * from something"
var safeParameters = _db2DapperDynamicParameters.ConvertParamters(parameters);
var result = Dapper.SqlMapper.Query(connection, sql, safeParameters);

public interface IDb2DapperDynamicParameters
    {
        dynamic ConvertParamters(dynamic parameters);
    }

    public class Db2DapperDynamicParameters : IDb2DapperDynamicParameters
    {
        public dynamic ConvertParamters(dynamic parameters)
        {
            if (parameters == null)
                return null;

            return CreateDb2CompliantDynamicParameters(parameters);
        }

        private static DynamicParameters CreateDb2CompliantDynamicParameters(dynamic parameters)
        {
            var dynamicParameters = new DynamicParameters();
            foreach (PropertyDescriptor property in TypeDescriptor.GetProperties(parameters))
            {
                dynamicParameters.Add(property.Name.Replace('_', '-'), property.GetValue(parameters), DbType.Object);
            }
            return dynamicParameters;
        }
    }

Original comment by chocobsm...@gmail.com on 29 Nov 2013 at 12:54