SQL + Informix: How do I add a blob when doing an insert? (Using the .NET (C#) SDK)

I'm using Informix and the .NET SDK (C#):

Basically, is there any way to insert a blob when doing a standard insert sql statement?

INSERT INTO mytable (nam开发者_JAVA技巧e, theblob) VALUES ('foo', ? what goes here ?);

Oh, and the data I have is in a form of a byte[] array.

---

A couple of notes:

1) You should use parameterized queries

//Assuming you already have a connection somewhere that is opened.

var sql = "INSERT INTO mytable (name, theblob) VALUES (?, ?);";

using (var command = new IfxCommand(sql, connection))
{
    command.Parameters.Add(new IfxParameter()).Value = "foo";
    command.Parameters.Add(new IfxParameter()).Value = ifxBlob;
}

---

A couple of things to note: Informix has a bug when it comes to the Client SDK 3.5.xC7 (so far). You can easily pass in the byte array during inserts, but you will have a 609 error when doing updates if you pass in a byte[] array. Instead, you must use the IfxBlob object.

public IfxBlob CreateIfxBlob(byte[] data)
{
    //Get the connection however you like and make sure it's open...
    //Obviously you should make this method handle exceptions and the such.

    IfxBlob blob = connection.GetIfxBlob();

    blob.Open(IfxSmartLOBOpenMode.ReadWrite);
    blob.Write(data);
    blob.Close();

    return blob;
}

You have to pass in an IfxBlob instead during update, so you might as well also do it during inserts.

Also, keep in mind that if you are trying to set null, you will get an error. Instead, if the value is null pass in DBNull.Value.

public Object AsDBValue(Object value) //Or this Object value if you want it as an extension
{
    if (value == null)
        return DBNull.Value;

    //Other checks
    if (value is Enum)
        return Convert.ChangeType(value, value.GetType().GetEnumUnderlyingType());

    //Is Blob?
    if (value is byte[])
        return GetIfxBlob(value as byte[]);

    return value;
}

---

DO NOT SPECIFY THE TYPE OF PARAMETER

//These will lead to errors unless you typecast the parameters in the query.
new IfxParameter { IfxType = IfxType.Blob };
new IfxParameter { DbType = DbType.Binary };

If you do either of those, you'll have to do the following:

• When the Blob value is not null: "INSERT INTO mytable (name, theblob) VALUES (?, ?::blob);";
• When the Blob value is null: "INSERT INTO mytable (name, theblob) VALUES (?, ?::byte);";

You can see that it will be a pain in the butt to have different queries because of the type and value. Just don't specify a DbType or IfxType and let the Informix .NET provider map the correct types for you (even though it can't map the byte[] array properly on an Update).

Hope that works out for you, because I went through the same pains trying to figure this out and discovering what I think is a bug in the Informix .NET provider (Ver: 3.5.xC7).

---

Your best bet is to use a parametrized query. For example:

using(System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("INSERT INTO mytable (name, theblob) VALUES ('foo', @binaryValue)", conn))
{
  cmd.Parameters.Add("@binaryValue", System.Data.SqlDbType.Text, 8000).Value = arraytoinsert;
  cmd.ExecuteNonQuery();
}

I've made the assumption that your column type is Text. Original credit for the above approach is from this post.

---

I read your both messages and this is solution that was helped me:

 byte[] data = File.ReadAllBytes(PathFile);

 StringBuilder sql = new StringBuilder();

sql.Append(" UPDATE  updater SET report = ?  where path = " + "\'" + Path + "\' and status = 1;");
IfxCommand cmd = new IfxCommand(sql.ToString(), i_connect);
cmd.Parameters.Add("@binaryValue", IfxType.Byte).Value = data;
int res = cmd.ExecuteNonQuery();

PathFile - is a File.txt

my informix table:

   CREATE TABLE updater
 (
    nzp_up SERIAL PRIMARY KEY,
    version VARCHAR(50),
    status INT,
    path VARCHAR(200),
    key VARCHAR(100),
    soup VARCHAR(20),
    report TEXT
 );

---

this post was really usefull to fix my issues, so i would like to share my solution, it may help others. Here is the full code:

        try
        {
            //pFoto is a byte[] loaded in another method.
            if (pFoto != null && pFoto.Length > 0)
            {
                StringBuilder sentenciaSQL = new StringBuilder();
                sentenciaSQL.Append("INSERT INTO bd_imagenes:imagenes ");
                sentenciaSQL.Append("(identificador, cod_imagen, fecha_desde, fecha_hasta, fecha_grabacion, usuario, sec_transaccion, imagen) ");
                sentenciaSQL.Append("VALUES (?, 'FP', current, null, current, ?, 0, ?);");

                using (IfxConnection conIFX = new IfxConnection("Database=bd_imagenes; Server=xxxxxxxx; uid=xxxxxxx; password=xxxxxxxx; Enlist=true; Client_Locale=en_US.CP1252;Db_Locale=en_US.819"))
                {
                    conIFX.Open(); //<- Abro la conexion.
                    //Aqui convierto la foto en un BLOB:                        
                    IfxBlob blob = conIFX.GetIfxBlob();
                    blob.Open(IfxSmartLOBOpenMode.ReadWrite);
                    blob.Write(pFoto);
                    blob.Close(); 

                    //Creo el Comando con la SQL:
                    using (IfxCommand cmd = new IfxCommand(sentenciaSQL.ToString(), conIFX))
                    {
                        //Agrego los parámetros en el mismo orden que la SQL:
                        cmd.Parameters.Add(new IfxParameter()).Value = pCedula;
                        cmd.Parameters.Add(new IfxParameter()).Value = SecurityHandler.Auditoria.NombreUsuario;
                        cmd.Parameters.Add(new IfxParameter()).Value = blob;
                        //Ejecuto la Consulta:
                        Resultado = cmd.ExecuteNonQuery();
                    }
                    conIFX.Close();  
                }
                if (Resultado != 0) { retorno = true; }
            }
        }
        catch (IfxException ae)
        {
            if (exepcionesValidacion == null) { exepcionesValidacion = new ArrayList(); }
            exepcionesValidacion.Add(Util.CrearExcepcion(ae.Message, "ERROR_INESPERADO", ae.StackTrace));
        }
        catch (Exception ex)
        {
            if (exepcionesValidacion == null) { exepcionesValidacion = new ArrayList(); }
            exepcionesValidacion.Add(Util.CrearExcepcion(ex.Message, "ERROR_INESPERADO", ex.StackTrace));
        }
        return retorno;
    }