CUBRID學習筆記 15 Lobs類型數據

• BLOB: Binary large object
• CLOB: Character large object 
• 一個二進位 一個字元類型

  二進位的讀取

CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    CUBRIDBlob bImage = (CUBRIDBlob)reader[0];
    byte[] bytes = new byte[(int)bImage.BlobLength];
    bytes = bImage.getBytes(1, (int)bImage.BlobLength);
    //...
}


更新  clob類型

string sql = "UPDATE t SET c = ?";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);

CUBRIDClob Clob = new CUBRIDClob(conn);

//Use the ConnectionString for testing
str = "server=localhost;database=demodb;port=33000;user=public;password="

Clob.setString(1, str);
CUBRIDParameter param = new CUBRIDParameter();
param.ParameterName = "?";
param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_CLOB;
param.Value = Clob;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();

下麵來看個完整的例子

using CUBRID.Data.CUBRIDClient;
using System.Diagnostics;
using System;
using System.IO;
using System.Data.Common;

namespace LobExample
{
    class Program
    {
        private static void ExecuteSQL(string sql, CUBRIDConnection conn)
        {
            using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        private static void CreateTestTableLOB(CUBRIDConnection conn)
        {
            ExecuteSQL("drop table if exists t", conn);
            ExecuteSQL("create table t(b BLOB, c CLOB)", conn);
        }

        private static void CleanupTestTableLOB(CUBRIDConnection conn)
        {
            ExecuteSQL("drop table if exists t", conn);
        }

        static void Main(string[] args)
        {
            CUBRIDConnectionStringBuilder sb = new CUBRIDConnectionStringBuilder("localhost", "demodb", "public", "", "33000");
            using (CUBRIDConnection conn = new CUBRIDConnection(sb.GetConnectionString()))
            {
                conn.Open();

                CreateTestTableLOB(conn);

                string sql = "insert into t (c) values(?)";
                CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);

                CUBRIDClob Clob = new CUBRIDClob(conn);

                String str = conn.ConnectionString;

                StreamReader r = new StreamReader("test.txt");
                string writestring = r.ReadToEnd();
                r.Close();

                Clob.setString(1, writestring);

                CUBRIDParameter param = new CUBRIDParameter();
                param.ParameterName = "?";
                param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_CLOB;
                param.Value = Clob;
                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
                cmd.Close();

                string sql2 = "SELECT c from t";
                using (CUBRIDCommand cmd2 = new CUBRIDCommand(sql2, conn))
                {
                    DbDataReader reader = cmd2.ExecuteReader();

                    while (reader.Read())
                    {
                        CUBRIDClob cImage = (CUBRIDClob)reader[0];
                        string str2 = cImage.getString(1, (int)cImage.ClobLength);

                        StreamWriter w = new StreamWriter("testout.txt");
                        w.Write(str2);
                        w.Close();

                        StreamReader r2 = new StreamReader("testout.txt");
                        string readstring = r2.ReadToEnd();
                        r2.Close();

                        Debug.Assert(writestring.Length == readstring.Length, "The inserted CLOB length is not valid!");
                        Debug.Assert(writestring.Equals(readstring), "The CLOB was not inserted correctly!");
                    }
                }

                CleanupTestTableLOB(conn);

                conn.Close();
            }
        }
    }
}