Note: เหมือนยังมี Error อยู่ ในบางกรณี ไม่แน่ใจว่า ผิดตรงไหน

update blob จากข้อมูลที่เก็บอยู่ใน textfile

• ถ้าใช้ sql ให้ดูที่ LOAD FROM File
• SQL + Informix: How do I add a blob when doing an insert? (Using the .NET (C#) SDK)

สร้างตารางทดสอบ

create table tbtest
(
    id        SERIAL  PRIMARY KEY,
    detail    text,
    timestamp DATETIME YEAR TO FRACTION(5) DEFAULT SYSDATE YEAR TO FRACTION(5) 
);

สร้างฟังก์ชันใน C# ใช้ในการ update

public static string UpdateBlob(string query, string filename)
{
    string connectionString = ConfigurationManager.AppSettings.Get("connectionString");

    query = cleanQuery(query);
    log.Debug(query);

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

    string result = "";
    using (var conn = new IfxConnection(connectionString))
    {
        conn.Open();

        IfxTransaction myTrans;
        // Start a local transaction
        myTrans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

        using (IfxCommand command = new IfxCommand(query, conn))
        {
            command.Transaction = myTrans;
            command.Parameters.Add("@binaryValue", IfxType.Byte).Value = data;

            try
            {
                int res = command.ExecuteNonQuery();
                myTrans.Commit();
                result = "success";
                log.Info(result);
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                result = ex.Message;
                log.Error(ex.Message);
            }
        }
    }

    return result;
}

ทดลองเรียกใช้ฟังก์ชันแบบ INSERT

string filename = "logo.bmp";
string query = string.Format("INSERT into tbtest (detail) values(?);");
UpdateBlob(query, filename);

ทดลองเรียกใช้ฟังก์ชันแบบ UPDATE

string filename = "xxx.txt";
string query = string.Format("UPDATE tbtest SET detail = ? where id = 1;");
UpdateBlob(query, filename);

SELECT blob แล้วบันทึกเป็นรูป

public static void SelectBlob(string query)
{
    try
    {
        // Open a connection
        IfxConnection conn = new IfxConnection(connIIA);
        conn.Open();
        // Create an SQL command
        IfxCommand cmd = new IfxCommand(query, conn);
        IfxDataReader reader = cmd.ExecuteReader();
        // Write the data to the console
        while (reader.Read())
        {
            byte[] img = (byte[])reader["detail"];
            string rowid = reader["id"].ToString();
            string path = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format("x{0}.png", rowid));
            System.IO.File.WriteAllBytes(path, img);
        }
    }
    catch (IfxException e)
    {
        log.Info(e.ToString());
    }
}

SELECT blob แล้วแปลงเป็น string

public static void SelectBlob(string query)
{
    try
    {
        // Open a connection
        IfxConnection conn = new IfxConnection(connIIA);
        conn.Open();
        // Create an SQL command
        IfxCommand cmd = new IfxCommand(query, conn);
        IfxDataReader reader = cmd.ExecuteReader();
        // Write the data to the console
        while (reader.Read())
        {
            byte[] bytes = (byte[])reader["LossDetail"];

            // UTF conversion - String from bytes  
            string utfString = Encoding.UTF8.GetString(bytes, 0, bytes.Length);
            log.Info(utfString);

            // Windows 874
            string win874 = Encoding.GetEncoding(874).GetString(bytes, 0, bytes.Length);
            log.Info(win874);
        }
    }
    catch (IfxException e)
    {
        log.Info(e.ToString());
    }
}