Open Source RDBMS - Seamless, Scalable, Stable and Free

English | Login |Register

CUBRID Collections


Collections are a specific CUBRID data type(s). If you are not familiar with them, you can read more in the Collection Data Types manual page.

Because collections are not common to any database, but only to CUBRID, the support for them is implemented in some specific CUBRID Collection methods:

public void AddElementToSet(CUBRIDOid oid, String attributeName, Object value)
public void DropElementInSet(CUBRIDOid oid, String attributeName, Object value)
public void UpdateElementInSequence(CUBRIDOid oid, String attributeName, int index, Object value)
public void InsertElementInSequence(CUBRIDOid oid, String attributeName, int index, Object value)
public void DropElementInSequence(CUBRIDOid oid, String attributeName, int index)
public int GetCollectionSize(CUBRIDOid oid, String attributeName)

Here below are two examples of using these CUBRID extensions.

Reading values from a Collection data type:

using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT * FROM t", conn))
{
    using (DbDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            object[] o = (object[])reader[0];
            for (int i = 0; i < SeqSize; i++)
            {
                //...
            }
        }
    }
}

Updating a Collection data type:

conn.InsertElementInSequence(oid, attributeName, 5, value);
SeqSize = conn.GetCollectionSize(oid, attributeName);
using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT * FROM t", conn))
{
    using (DbDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            int[] expected = { 7, 1, 2, 3, 7, 4, 5, 6 };
            object[] o = (object[])reader[0];
            for (int i = 0; i < SeqSize; i++)
            {
                Debug.Assert(Convert.ToInt32(o[i]) == expected[i]);
            }
        }
    }
}
conn.DropElementInSequence(oid, attributeName, 5);
SeqSize = conn.GetCollectionSize(oid, attributeName);

Here is a complete example:

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

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

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

                ExecuteSQL("DROP TABLE IF EXISTS t", conn);

                //Create a new table with a sequence
                ExecuteSQL("CREATE TABLE t(seq SEQUENCE(int))", conn);
                //Insert some data in the sequence column
                ExecuteSQL("INSERT INTO t(seq) VALUES({0,1,2,3,4,5,6})", conn);

                CUBRIDOid oid = new CUBRIDOid("@0|0|0");
                using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT t FROM t", conn))
                {
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            oid = (CUBRIDOid)reader[0];
                        }
                    }
                }

                String attributeName = "seq";
                int value = 7;

                int SeqSize = conn.GetCollectionSize(oid, attributeName);
                Debug.Assert(SeqSize == 7);

                conn.UpdateElementInSequence(oid, attributeName, 1, value);
                SeqSize = conn.GetCollectionSize(oid, attributeName);
                Debug.Assert(SeqSize == 7);

                using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT * FROM t", conn))
                {
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int[] expected = { 7, 1, 2, 3, 4, 5, 6 };
                            object[] o = (object[])reader[0];
                            for (int i = 0; i < SeqSize; i++)
                            {
                                Debug.Assert(Convert.ToInt32(o[i]) == expected[i]);
                            }
                        }
                    }
                }

                conn.InsertElementInSequence(oid, attributeName, 5, value);
                SeqSize = conn.GetCollectionSize(oid, attributeName);
                Debug.Assert(SeqSize == 8);

                using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT * FROM t", conn))
                {
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int[] expected = { 7, 1, 2, 3, 7, 4, 5, 6 };
                            object[] o = (object[])reader[0];
                            for (int i = 0; i < SeqSize; i++)
                            {
                                Debug.Assert(Convert.ToInt32(o[i]) == expected[i]);
                            }
                        }
                    }
                }

                conn.DropElementInSequence(oid, attributeName, 5);
                SeqSize = conn.GetCollectionSize(oid, attributeName);
                Debug.Assert(SeqSize == 7);

                using (CUBRIDCommand cmd = new CUBRIDCommand("SELECT * FROM t", conn))
                {
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int[] expected = { 7, 1, 2, 3, 4, 5, 6 };
                            object[] o = (object[])reader[0];
                            for (int i = 0; i < SeqSize; i++)
                            {
                                Debug.Assert(Convert.ToInt32(o[i]) == expected[i]);
                            }
                        }
                    }
                }

                ExecuteSQL("DROP t", conn);
                conn.Close();
            }
        }
    }
}

comments powered by Disqus
문서 정보
viewed 1572 times
번역 en
작성자
posted 2년 전
CUBRID
공헌자
마지막 수정시간 2년 전
변경 내역 보기
Share this article