Pass null via ODBC

Question

I have a stored procedure in DB2 which accepts a Date as a paramater. I use this code to assign it a value in C#

cmd.Parameters.Add("myfield", OdbcType.DateTime).Value = MyDate;

This works fine! However sometimes I want to pass it null and I cannot get it to work. I tried

cmd.Parameters.Add("myfield", OdbcType.DateTime);

and

cmd.Parameters.Add("myfield", OdbcType.DateTime).Value =DBNull.Value

update

my cmd.CommandText = "{ CALL foo.MY_PROC_NAME(?) }"

When I run it it just returns no rows. MY SQL in the SP looks like this

(p_myfield is null or LAST_UPDATE_DATE > p_myfield) 

Answer 1

something like this should work for a nullable datetime parameter

DateTime? myfield

if (null==myfield)
{
 var nullableparam = new OdbcParameter("@myfield", DBNull.Value);
 nullableparam.IsNullable = true;
 nullableparam.OdbcType = OdbcType.DateTime;
 nullableparam.Direction = ParameterDirection.Input;
 cm.Parameters.Add(nullableparam);
}
else
{
 cm.Parameters.AddwithValue("@myfield",myfield);
}

Answer 2

I encountered several errors along these lines:

• I couldn't pass a NULL value directly to Odbc
• Named parameters didn't seem to be working.

After several hours of exhausting trouble-shooting, it finally occurred to me how I had seen this done in a Java project a while back. I changed my code to use only "?" for parameters (that was how it was done in the Java code I saw), and then made sure that I was adding parameters to a OdbcCommand object in the order that I wanted them to be added to the query. Like magic, it worked.

Here is an example:

OdbcTransaction lTransaction = MyConnection.BeginTransaction();
object[] my_params = function_to_get_input();
toInsert = new OdbcCommand();
toInsert.CommandText = string.Format("INSERT INTO table_name (`col1`,`col2`,`col3`) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?);");
toInsert.Parameters.AddWithValue("val0", my_params[0]);
toInsert.Parameters.AddWithValue("val1", my_params[1]);
toInsert.Parameters.AddWithValue("val2", my_params[2]);
toInsert.Parameters.AddWithValue("val3", my_params[3]);
toInsert.Parameters.AddWithValue("val4", my_params[4]);
toInsert.Parameters.AddWithValue("val5", my_params[5]);
toInsert.Parameters.AddWithValue("val6", my_params[6]);
toInsert.Parameters.AddWithValue("val7", my_params[7]);
toInsert.Parameters.AddWithValue("val8", my_params[8]);
toInsert.Connection = MyConnection;
toInsert.Transaction = lTransaction;
toInsert.ExecuteNonQuery();
lTransaction.Commit();

This approach can be rewritten so you can insert bulk data if you so choose. If you ARE inserting bulk data, then you will have to limit the number of records that are inserted with one INSERT statement. If the amount of data transmitted to the server exceeds the MAX_ALLOWED_PACKET value, then an error will be generated. See here for more detail.

Please forgive my laziness in not showing proper error handling or anything else here. I've been trying to figure out how to fix this error for going on 6 hours now and was about to go insane with the catch 22 of "I can't use named parameters, but I have to use named parameters!"