Crashing with DB2 'SELECT' Parameters

Topics: Data Access Application Block
Jun 12, 2013 at 10:37 PM
When I run this code (minus the SetDatabaseProviderFactory() call) in EntLib 5.0 .NET 4.0 I get output. The query completes.
class Program
{
    const string DB2_QUERY = "SELECT CODE, SYMBOL FROM SCHEMA_ONE.COMPANY WHERE CODE = @code";

    static void Main( string[] args ) {

        DatabaseFactory.SetDatabaseProviderFactory( new DatabaseProviderFactory() );

        var db = DatabaseFactory.CreateDatabase( "CONNECTION" );

        executeQuery( db, DB2_QUERY, "ABC" );
    }

    static void executeQuery( Database db, string query, string code ) {

        using ( var cnn = db.CreateConnection() )
        using ( var cmd = db.GetSqlStringCommand( query ) ) {

            db.AddInParameter( cmd, "code", DbType.String, code );

            var ds = db.ExecuteDataSet( cmd );
        }
    }
}
When I run this in EntLib 6.0 .NET 4.5, I get (in the Output window because the program just crashes when the "var ds = " line executes.):

The program '[764] GenericData.vshost.exe: Managed (v4.0.30319)' has exited with code -1073741811 (0xc000000d).

Has anyone passed parameters to a DB2 "select" query using EntLib 6.0?
Jun 14, 2013 at 7:04 AM
Data Access Application Block is a wrapper on ADO.NET which makes me wonder if the problem is with ADO.NET (4.5) or the DB2 provider with .NET 4.5.

What happens if you don't use a parameter in the query? Does it work?

Another test would be to just use ADO.NET and try to narrow down the issue:
            DataSet dataSet = new DataSet();
            dataSet.Locale = CultureInfo.InvariantCulture;

            DbProviderFactory factory = DbProviderFactories.GetFactory("IBM.Data.DB2.iSeries");
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = "DataSource=DB2DATABASE;UserID=WHINYBABY;Password=WAHWAHWAH;DataCompression=True;";
            connection.Open();

            DbCommand command = factory.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT CODE, SYMBOL FROM SCHEMA_ONE.COMPANY WHERE CODE = @code";

            DbParameter param = dbProviderFactory.CreateParameter();
            param.DbType = DbType.String;
            param.Size = 0;
            param.Value = "ABC";
            param.Direction = ParameterDirection.Input;
            param.IsNullable = true;
            param.SourceColumn = string.Empty;
            param.SourceVersion = DataRowVersion.Default;

            command.Parameters.Add(parameter);
            
            command.Connection = connection;

            DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
            ((IDbDataAdapter)adapter).SelectCommand = command;

            adapter.TableMappings.Add("Table", "Table");
            adapter.Fill(dataSet);
The above is basically what Enterprise Library is doing. Or you could debug the Block.

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Jun 14, 2013 at 6:31 PM
Absolutely. It crashes with straight ADO.NET. However, my actual query is a little more complicated and takes 5 parameters. The first 4 are Int64 but the last one is a string. I discovered that when I set the size of the string it worked perfectly. How can I set the size of the parameter in the DAAB?

I also managed to get some other queries working by using only "?" as parameters (the working ADO uses "@name.")

Would I be better off with DB2 just coding directly to the driver?
Jun 14, 2013 at 6:48 PM
When you call db.AddInParameter() the size gets set to 0. The easiest way to set the size is to use the AddParameter method. It's a bit messier but lets you set all the values:
  static void executeQuery( Database db, string query, string code ) {

        using ( var cnn = db.CreateConnection() )
        using ( var cmd = db.GetSqlStringCommand( query ) ) {

            db.AddParameter(cmd, "code", DbType.String, code.Length, ParameterDirection.Input, true, 0, 0, String.Empty, DataRowVersion.Default, code);

            var ds = db.ExecuteDataSet( cmd );
        }
    }

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Marked as answer by randylevy on 10/7/2013 at 11:15 PM
Jun 14, 2013 at 7:58 PM
That worked! (Any consideration given to allow setting the data length in AddInParameter() calls would be appreciated!)
Thanks so much for the lessons.
Jun 14, 2013 at 8:15 PM
Good to hear. You could create your own extension method to make the code a bit more palatable:
    public static class DatabaseExtensions
    {
        public void AddInParameter(this Database db,
            DbCommand command,
            string name,
            DbType dbType,
            int size,
            bool isNullable,
            object value)
        {
            db.AddParameter(command, name, dbType, size, ParameterDirection.Input,
                isNullable, 0, 0, string.Empty, DataRowVersion.Default, value);
        }
    }

And then you could call it pretty much like the out of the box version:
 db.AddInParameter( cmd, "code", DbType.String, 255, true, code );
~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to