Informix的（C＃）：我如何正确设置警戒/解除警戒BLOB字段？ [英] Informix (C#): How do I properly set/unset a blob field?

问题描述

IBM Informix的SDK：

IBM Informix SDK:

声明：更新mytable的设置myblobcolumn =？其中，身份识别码= 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.
}

如果我用另一个 IfxBlob 对象，它工作正常更新，但如果我用的DBNull.Value 更新我得到一个错误。任何人都知道如何使用参数化的更新未设置BLOB列？

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?

更新：

好吧，我做了一些研究，并降低下来了一点。

Ok, I did some research and have reduced things down a bit.

首先，我发现，如果我显式声明 IfxType 生成的参数时，在.NET司机有麻烦转换的DBNull.Value甚至一个byte []数组时，切换从数据库中一个空值来回和具有在数据库中的实际IfxBlob。基本上是：

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:

• 如果该列包含的值，我希望存储空它，而不是那么我必须强制转换我的参数如下：<？code>更新mytable的设置myblobcolumn = ::个字节，其中身份识别码= 1; 。这使我能够存储的DBNull.Value 的值没有任何错误。
• 如果列包含空值，我希望在其中存储的实际值，而不是那么我必须强制转换我的参数如下：<？code>更新mytable的设置myblobcolumn = ::斑点WHERE身份识别码= 1; 。这使我能够存储IfxBlob值。

• 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 of DBNull.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.

现在，避免一切，我的参数初始化降低到只设置ParameterName属性： VAR参数=新IfxParameter {参数名称=myblobcolumn} （甚至这是只是为了能够从集合引用它，仅此而已）。这让我保持我的发言，而不必强制转换我的参数。

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:

声明：更新mytable的设置myblobcolumn =？其中，身份识别码= 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;

    //...
}

这工作正常，但我想我发现了（3.50.xC7）类似<一个在Informix .NET驱动程序中的错误href="https://www-304.ibm.com/support/docview.wss?lang=en&cc=us&rs=0&wv=1&q2=blob&loc=en_US&cs=utf-8&uid=swg1IC59626&q1=informix%20.net&dc=DB500"相对=nofollow>这个。基本上，所提供的链接状态，有发现了一个错误，这错误并没有让.NET驱动程序正确使用byte []数组创建一个blob做插入时。这已得到修复，我可以从字面上做 cmd.Parameters [myblob]值=新的字节[] {为0x1，0X2}; 做当 INSERT 的声明。但是，驱动程序使用byte []数组为更新的语句时，还是给出了一个例外。因此，我不得不实际创建IfxBlob实例，并使用这些而不是实际的byte []数组。

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.

如果这不是在驱动程序中的错误，然后这个问题需要保持开放的，看看有什么要设置/取消BLOB字段使用一个byte []数组用于更新的正确方法。

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.

推荐答案

我想，既然没人说什么，我经历是不是一个错误，我会后我发现一个答案：

I figured that since no one stated that what I experienced isn't a bug, I'll post my findings as an answer:

1. 有关在Informix .NET提供程序（版本：3.5.xC7），不要明确地设置 IfxType 或的DbType 生成当 IfxParameter （或 DbParameter ）。相反，刚刚生成的参数，并允许在Informix .NET提供映射正确的类型为您服务。这可以让你不必担心你的类型转换参数（即避免？::斑点和？::字节）。

1. For the Informix .NET provider (ver: 3.5.xC7), do NOT, explicitly set the IfxType or DbType when generating an IfxParameter (or DbParameter). Instead, just generate the parameter and allow the Informix .NET provider to map the correct types for you. This allows you to not have to worry about typecasting your parameters (i.e. avoid, "?::blob" and "?::byte").

由于一个错误（版本：3.5.xC7），插入一个byte []数组Blob字段工作正常，但用一个byte []数组将导致609错误更新的Blob字段。相反，一个 IfxBlob 将被创建并设置作为值。这也适用于执行插入时。

Due to a bug (ver: 3.5.xC7), inserting a byte[] array into a Blob field works fine, but updating that Blob field using a byte[] array will result in a 609 error. Instead, an IfxBlob will have to be created and set as the value. This also works when doing inserts.

这篇关于Informix的（C＃）：我如何正确设置警戒/解除警戒BLOB字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！