IBM Informix SDK:
Statement: Update mytable set myblobcolumn = ? where myid = 1;
using (IfxConnection conn = GetIfxConnection())
using (IfxCommand cmd = new IfxCommand(updateSql, conn))
{
var param = new IfxParameter("myblobcolumn", IfxType.Blob) { IsNullable = true };
cmd.Parameters.Add(param).Value = DBNull.Value
cmd.ExecuteNonQuery(); //ERROR [HY000] [Informix .NET provider][Informix]Illegal attempt to use Text/Byte host variable.
}
If I update it with another IfxBlob
object it works fine, but if I update it with DBNull.Value
I get an error. Anyone know how to "unset" the blob column using a parameterized update?
Update:
Ok, I did some research and have reduced things down a bit.
Firstly, I found that if I explicitly declare the IfxType
when generating the parameter, the .NET driver has trouble converting DBNull.Value or even a Byte[] array when switching back and forth from a null value in the database and having an actual IfxBlob in the database. Basically:
- If the column contains a value and I wish to store null in it instead then I must typecast my parameter as follows:
UPDATE mytable SET myblobcolumn = ?::byte WHERE myid = 1;
. This allows me to store the value ofDBNull.Value
without any errors. - If the column contains a null value and I wish to store an actual value in it instead then I must typecast my parameter as follows:
UPDATE mytable SET myblobcolumn = ?::blob WHERE myid = 1;
. This allows me to store the IfxBlob value.
Now, to avoid all that I reduced down the Parameter initialization to only setting the ParameterName property: var param = new IfxParameter { ParameterName = "myblobcolumn" }
(and even that is just to be able to reference it from the collection, nothing more). This let me keep my statement without having to typecast my parameter.
so... I'm left with the following:
Statement: UPDATE mytable SET myblobcolumn = ? where myid = 1;
using (IfxConnection conn = GetIfxConnection())
using (IfxCommand cmd = new IfxCommand(updateSql, conn))
{
var param = new IfxParameter { ParameterName = "myblob" }
cmd.Parameters.Add(param);
var value = GetSomeValue();
if (value is Byte[])
cmd.Paremeters["myblob"].Value = GetIfxBlob(value);
else
cmd.Parameters["myblob"].Value = DBNull.Value;
//...
}
This works fine, except I think I discovered a bug in the Informix .NET Driver (3.50.xC7) similar to the this one. Basically, the link provided states that there was a bug discovered which did not allow the .NET driver to properly use a byte[] array to create a blob when doing an insert. That has been fixed in that I can literally do cmd.Parameters["myblob"].Value = new Byte[] { 0x1, 0x2 };
when doing an INSERT statement. However, the driver still gives an exception when using the byte[] array for an UPDATE statement. Hence, I had to actually create IfxBlob instances and use those instead of the actual byte[] array.
If this isn't a bug in the driver then this question needs to stay open to see what the proper way to set/unset a blob field is using a byte[] array for UPDATES.