Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

CUBRID Lobs


Starting from the 8.4.0 release, CUBRID deprecated the GLO data type, and added support for LOB data types:

  • BLOB: Binary large object
  • CLOB: Character large object

These are specific CUBRID data types, and if you need to deal with such data types, you need to learn and use the CUBRID ADO.NET Provider specific extensions.

Here are some basic source code examples to get you start up fast.

Reading BLOB data:

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);
    //...
}

Updating CLOB data:

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();

Here is a complete example:

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();
            }
        }
    }
}

The test.txt file must be created in the directory where the executable is and it can contain any text you want to use for testing. The code will create a testout.txt file that will contain the exact same contents as test.txt file.

comments powered by Disqus
Page info
viewed 2070 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated last year by
View revisions
tagged
Share this article