/// <summary>
/// Test CREATE Database Stored Functions calls
/// </summary>
private static void Test_CreateFunction()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
try
{
TestCases.ExecuteSQL("drop function sp1", conn);
}
catch { }
string sql = "CREATE FUNCTION sp1(a int) RETURN string AS LANGUAGE JAVA NAME 'SpTest.test1(int) return java.lang.String'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetProcedures(null);
Debug.Assert(dt.Rows.Count == 1);
TestCases.ExecuteSQL("drop function sp1", conn);
}
}
/// <summary>
/// Test DateTime types
/// </summary>
private static void Test_DateTime_Types()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
CleanupTestTable(conn);
TestCases.ExecuteSQL("create table t(dt datetime)", conn);
TestCases.ExecuteSQL("insert into t values('10/31/2008 10:20:30.040')", conn);
using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t", conn))
{
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Debug.Assert(reader.GetDateTime(0) == new DateTime(2008, 10, 31, 10, 20, 30, 040));
Debug.Assert(reader.GetDate(0) == "2008-10-31");
Debug.Assert(reader.GetDate(0, "yy/MM/dd") == "08/10/31");
Debug.Assert(reader.GetTime(0) == "10:20:30");
Debug.Assert(reader.GetTime(0, "HH") == "10");
Debug.Assert(reader.GetTimestamp(0) == "2008-10-31 10:20:30.040");
Debug.Assert(reader.GetTimestamp(0, "yyyy HH") == "2008 10");
}
CleanupTestTable(conn);
}
}
/// <summary>
/// Test CREATE Stored Procedures calls
/// </summary>
private static void Test_CreateProcedure()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
try
{
TestCases.ExecuteSQL("drop function sp2", conn);
}
catch { }
string sql = "CREATE PROCEDURE \"sp2\"() AS LANGUAGE JAVA NAME 'SpTest.test2()'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetProcedures(null);
Debug.Assert(dt.Rows.Count == 1);
TestCases.ExecuteSQL("drop procedure sp2", conn);
}
}
/// <summary>
/// Test CUBRIDConnection BatchExecuteNoQuery() method
/// </summary>
private static void Test_BatchExecuteNoQuery()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string[] sqls = new string[3];
sqls[0] = "create table t(id int)";
sqls[1] = "insert into t values(1)";
sqls[2] = "insert into t values(2)";
conn.BatchExecuteNoQuery(sqls);
string sql = "select count(*) from t";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetInt32(0) == 2);
}
}
TestCases.ExecuteSQL("drop table t", conn);
}
}
/// <summary>
/// Test CUBRIDTransaction class
/// </summary>
private static void Test_Transaction()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.ExecuteSQL("drop table if exists t", conn);
conn.BeginTransaction();
string sql = "create table t(idx integer)";
using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
{
command.ExecuteNonQuery();
}
int tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.Rollback();
//Verify the table does not exist
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
conn.BeginTransaction();
sql = "create table t(idx integer)";
using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
{
command.ExecuteNonQuery();
}
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.BeginTransaction();
TestCases.ExecuteSQL("drop table t", conn);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
}
}
/// <summary>
/// Test multiple connections
/// </summary>
private static void Test_MultipleConnections()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.ExecuteSQL("drop table if exists t", conn);
TestCases.ExecuteSQL("create table t(idx integer)", conn);
string sql = "select * from nation";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
int count = 0;
while (reader.Read() && count++ < 3)
{
using (CUBRIDConnection conn2 = new CUBRIDConnection())
{
conn2.ConnectionString = conn.ConnectionString;
conn2.Open();
string sqlInsert = "insert into t values(" + count + ")";
using (CUBRIDCommand cmdInsert = new CUBRIDCommand(sqlInsert, conn2))
{
cmdInsert.ExecuteNonQuery();
}
}
}
}
}
using (CUBRIDConnection conn2 = new CUBRIDConnection())
{
conn2.ConnectionString = conn.ConnectionString;
conn2.Open();
string sqlSelect = "select count(*) from t";
using (CUBRIDCommand cmd = new CUBRIDCommand(sqlSelect, conn2))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetInt32(0) == 3);
}
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
}
/// <summary>
/// Test Encodings support
/// </summary>
private static void Test_Encodings()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = "server=" + ip + ";database=demodb;port=33000;user=public;password=;charset=utf-8";
conn.Open();
TestCases.ExecuteSQL("drop table if exists t", conn);
TestCases.ExecuteSQL("create table t(a int, b varchar(100))", conn);
String sql = "insert into t values(1 ,'¾Æ¹«°³')";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
sql = "select * from t where b = '¾Æ¹«°³'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read(); //retrieve just one row
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetString(1) == "¾Æ¹«°³");
}
}
sql = "update t set b='¾Æ¹°³'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
sql = "select * from t where b = '¾Æ¹°³'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read(); //retrieve just one row
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetString(1) == "¾Æ¹°³");
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
}
/// <summary>
/// Test CUBRIDConnection Auto-Commit property
/// </summary>
private static void Test_AutoCommit()
{
int tablesCount;
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
conn.SetAutoCommit(false);
tablesCount = (int)TestCases.GetSingleValue("select count(*) from db_class", conn);
//Create table
TestCases.ExecuteSQL("drop table if exists xyz", conn);
TestCases.ExecuteSQL("create table xyz(id int)", conn);
}
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
//Verify table was not created
Debug.Assert(tablesCount == (int)TestCases.GetSingleValue("select count(*) from db_class", conn));
//Create table
TestCases.ExecuteSQL("drop table if exists xyz", conn);
TestCases.ExecuteSQL("create table xyz(id int)", conn);
}
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
//Verify table was created
Debug.Assert(tablesCount == ((int)TestCases.GetSingleValue("select count(*) from db_class", conn) - 1));
TestCases.ExecuteSQL("drop table if exists xyz", conn);
}
}
/// <summary>
/// Test Enum data type with wrong data
/// </summary>
public static void Test_WithWrongEnumData()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.ExecuteSQL("drop table if exists table11;", conn);
try
{
/* create new table */
string sql = "create table table11(index enum(1, 2, 3, 4, 5, 6));";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
}
catch (Exception exp)
{
string expected = exp.Message.Substring(0, exp.Message.LastIndexOf("["));
Debug.Assert(expected == "Syntax: In line 1, column 28 before '(1, 2, 3, 4, 5, 6));'\nSyntax error: unexpected 'enum' ");
}
}
}
/// <summary>
/// Test SEQUENCE operations
/// </summary>
private static void Test_SequenceOperations()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.ExecuteSQL("DROP TABLE IF EXISTS t", conn);
//Create a new table with a sequence
TestCases.ExecuteSQL("CREATE TABLE t(seq SEQUENCE(int))", conn);
//Insert some data in the sequence column
TestCases.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]);
}
}
}
}
TestCases.ExecuteSQL("DROP t", conn);
}
}
/// <summary>
/// Test large SQL statement
/// </summary>
public static void Test_Big_Data()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string _create = "create table TBL_RAW_POWER(PDMU_ID int, CHANNEL_NUM int, REG_DATE datetime, AMPERE int," +
"ACTIVE_POWER int, POWER_ACT int, APPARENT_POWER int, REACTIVE_POWER int, POWER_REA int," +
"SYSTEM_STATUS int, FREQUENCY int, POWER_FACTOR int, POWER_STATUS int, VOLTAGE int);";
try
{
TestCases.ExecuteSQL("drop table if exists TBL_RAW_POWER;", conn);
}
catch { }
/* create new table */
using (CUBRIDCommand cmd = new CUBRIDCommand(_create, conn))
{
cmd.ExecuteNonQuery();
}
const string _insert = "INSERT INTO TBL_RAW_POWER(PDMU_ID, CHANNEL_NUM, REG_DATE, AMPERE, ACTIVE_POWER, " +
"POWER_ACT, APPARENT_POWER, REACTIVE_POWER, POWER_REA, SYSTEM_STATUS, FREQUENCY, " +
"POWER_FACTOR, POWER_STATUS, VOLTAGE ) VALUES " +
" (637, 12, '2013-01-18 13:34:19', 1316, 2268, 40729, 2804, 972, 40729, 1000, 596, 94, 0, 1011), " +
"(637, 14, '2013-01-18 13:34:19', 456, 942, 15605, 964, 294, 15605, 1000, 597, 95, 0, 1011), " +
"(637, 15, '2013-01-18 13:34:19', 4316, 2268, 15151, 2804, 972, 15151, 1000, 596, 94, 0, 1011), " +
"(637, 16, '2013-01-18 13:34:19', 1316, 2268, 15279, 2804, 972, 15279, 1000, 596, 94, 0, 1011), " +
"(637, 17, '2013-01-18 13:34:19', 4316, 2268, 15347, 2804, 972, 15347, 1000, 596, 94, 0, 1011), " +
"(637, 13, '2013-01-18 13:34:19', 456, 942, 15408, 964, 294, 15408, 1000, 597, 95, 0, 1011), " +
"(637, 31, '2013-01-18 13:34:19', 456, 942, 15282, 964, 294, 15282, 1000, 597, 95, 0, 1011), " +
"(637, 32, '2013-01-18 13:34:19', 1600, 3318, 15480, 3475, 979, 15480, 1000, 597, 95, 0, 1011), " +
"(637, 33, '2013-01-18 13:34:19', 1600, 3318, 15132, 3475, 979, 15132, 1000, 597, 95, 0, 1011), " +
"(637, 34, '2013-01-18 13:34:19', 4316, 2268, 15363, 2804, 972, 15363, 1000, 596, 94, 0, 1011), " +
"(637, 35, '2013-01-18 13:34:19', 0, 0, 15464, 0, 0, 15464, 1000, 597, 95, 0, 1011), " +
"(637, 36, '2013-01-18 13:34:19', 1600, 3318, 15415, 3475, 979, 15415, 1000, 597, 95, 0, 1011), " +
"(637, 37, '2013-01-18 13:34:19', 1316, 2268, 15251, 2804, 972, 15251, 1000, 596, 94, 0, 1011), " +
"(637, 38, '2013-01-18 13:34:19', 4316, 2268, 15299, 2804, 972, 15299, 1000, 596, 94, 0, 1011), " +
"(637, 39, '2013-01-18 13:34:19', 1600, 3318, 15384, 3475, 979, 15384, 1000, 597, 95, 0, 1011), " +
"(637, 40, '2013-01-18 13:34:19', 1316, 2268, 15305, 2804, 972, 15305, 1000, 596, 94, 0, 1011), " +
"(637, 41, '2013-01-18 13:34:19', 456, 942, 15431, 964, 294, 15431, 1000, 597, 95, 0, 1011), " +
"(637, 42, '2013-01-18 13:34:19', 1600, 3318, 15341, 3475, 979, 15341, 1000, 597, 95, 0, 1011), " +
"(637, 43, '2013-01-18 13:34:19', 1600, 3318, 15202, 3475, 979, 15202, 1000, 597, 95, 0, 1011), " +
"(637, 44, '2013-01-18 13:34:19', 1316, 2268, 15170, 2804, 972, 15170, 1000, 596, 94, 0, 1011), " +
"(637, 45, '2013-01-18 13:34:19', 456, 942, 15020, 964, 294, 15020, 1000, 597, 95, 0, 1011), " +
"(637, 46, '2013-01-18 13:34:19', 1316, 2268, 15268, 2804, 972, 15268, 1000, 596, 94, 0, 1011), " +
"(637, 47, '2013-01-18 13:34:19', 456, 942, 15253, 964, 294, 15253, 1000, 597, 95, 0, 1011), " +
"(637, 48, '2013-01-18 13:34:19', 1600, 3318, 15258, 3475, 979, 15258, 1000, 597, 95, 0, 1011), " +
"(637, 49, '2013-01-18 13:34:19', 1600, 3318, 15159, 3475, 979, 15159, 1000, 597, 95, 0, 1011), " +
"(637, 50, '2013-01-18 13:34:19', 1600, 3318, 15178, 3475, 979, 15178, 1000, 597, 95, 0, 1011), " +
"(637, 70, '2013-01-18 13:34:19', 1316, 2268, 7522, 2804, 972, 7522, 1000, 596, 94, 0, 1011), " +
"(637, 71, '2013-01-18 13:34:19', 1600, 3318, 7532, 3475, 979, 7532, 1000, 597, 95, 0, 1011), " +
"(637, 72, '2013-01-18 13:34:19', 456, 942, 7483, 964, 294, 7483, 1000, 597, 95, 0, 1011), " +
"(637, 18, '2013-01-18 13:34:19', 1600, 3318, 96721, 3475, 979, 96721, 1000, 597, 95, 0, 1011), " +
"(637, 19, '2013-01-18 13:34:19', 456, 942, 96110, 964, 294, 96110, 1000, 597, 95, 0, 1011), " +
"(637, 20, '2013-01-18 13:34:19', 0, 0, 56793, 0, 0, 56793, 1000, 597, 95, 0, 1011), " +
"(637, 1, '2013-01-18 13:34:19', 456, 942, 15250, 964, 294, 15250, 1000, 597, 95, 0, 1011), " +
"(637, 2, '2013-01-18 13:34:19', 456, 942, 15374, 964, 294, 15374, 1000, 597, 95, 0, 1011), " +
"(637, 30, '2013-01-18 13:34:19', 4316, 2268, 7595, 2804, 972, 7595, 1000, 596, 94, 0, 1011), " +
"(637, 28, '2013-01-18 13:34:19', 1600, 3318, 7713, 3475, 979, 7713, 1000, 597, 95, 0, 1011), " +
"(637, 29, '2013-01-18 13:34:19', 456, 942, 7487, 964, 294, 7487, 1000, 597, 95, 0, 1011)";
/* insert multi rows values */
using (CUBRIDCommand cmd = new CUBRIDCommand(_insert, conn))
{
cmd.ExecuteNonQuery();
}
/* verify count */
string sql = "select count(*) from TBL_RAW_POWER";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetInt32(0) == 37);
}
}
TestCases.ExecuteSQL("drop TBL_RAW_POWER;", conn);
}
}
public static void Test_BeginDbTransaction()
{
try
{
conn.SetConnectionTimeout(30);
}
catch (Exception e)
{
Debug.Assert(e.ToString() == "Not allowed to change the 'ConnectionTimeout'");
}
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string source = conn.DataSource;
TestCases.ExecuteSQL("drop table if exists t", conn);
conn.BeginTransaction();
string sql = "create table t(idx integer)";
using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
{
command.ExecuteNonQuery();
}
int tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.Rollback();
//Verify the table does not exist
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
conn.BeginTransaction();
sql = "create table t(idx integer)";
using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
{
command.ExecuteNonQuery();
}
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.BeginTransaction();
TestCases.ExecuteSQL("drop table t", conn);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
//IsolationLevel.Chaos
try
{
conn.BeginTransaction(IsolationLevel.Chaos);
}
catch (Exception e)
{
Debug.Assert(e.Message == "Not supported in CUBRID!");
}
//IsolationLevel.ReadCommitted
conn.BeginTransaction(IsolationLevel.ReadCommitted);
sql = "create table t(idx integer)";
using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
{
command.ExecuteNonQuery();
}
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.BeginTransaction();
TestCases.ExecuteSQL("drop table t", conn);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
//IsolationLevel.RepeatableRead
conn.BeginTransaction(IsolationLevel.RepeatableRead);
sql = "create table t(idx integer)";
using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
{
command.ExecuteNonQuery();
}
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.BeginTransaction();
TestCases.ExecuteSQL("drop table t", conn);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
//IsolationLevel.Serializable
conn.BeginTransaction(IsolationLevel.Serializable);
sql = "create table t(idx integer)";
using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
{
command.ExecuteNonQuery();
}
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 1);
conn.BeginTransaction();
TestCases.ExecuteSQL("drop table t", conn);
conn.Commit();
tablesCount = GetTablesCount("t", conn);
Debug.Assert(tablesCount == 0);
//IsolationLevel.Snapshot
try
{
conn.BeginTransaction(IsolationLevel.Snapshot);
}
catch (Exception e)
{
Debug.Assert(e.Message == "Not supported in CUBRID!");
}
//IsolationLevel.Snapshot
try
{
conn.BeginTransaction(IsolationLevel.Unspecified);
}
catch (Exception e)
{
Debug.Assert(e.Message == "Unknown isolation level is not supported!");
}
try
{
conn.BeginTransaction(0);
}
catch (Exception e)
{
Debug.Assert(e.Message == "Unknown isolation level is not supported!");
}
}
}
private static void CreateTestTable(CUBRIDConnection conn)
{
TestCases.ExecuteSQL("drop table if exists t", conn);
TestCases.ExecuteSQL("create table t(a int, b char(10), c string, d float, e double, f date)", conn);
}
/// <summary>
/// Test CUBRID Isolation Levels
/// </summary>
private static void Test_IsolationLevel()
{
string sqlTablesCount = "select count(*) from db_class";
int tablesCount, newTableCount;
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.ExecuteSQL("drop table if exists isol", conn);
conn.SetIsolationLevel(CUBRIDIsolationLevel.TRAN_REP_READ);
Debug.Assert(conn.GetIsolationLevel() == CUBRIDIsolationLevel.TRAN_REP_READ);
tablesCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
TestCases.ExecuteSQL("create table isol(id int)", conn);
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
//Verify table was created
Debug.Assert(newTableCount == tablesCount + 1);
using (CUBRIDConnection connOut = new CUBRIDConnection())
{
connOut.ConnectionString = TestCases.connString;
connOut.Open();
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, connOut);
//CREATE TABLE is visible from another connection
Debug.Assert(newTableCount == tablesCount + 1);
}
TestCases.ExecuteSQL("drop table if exists isol", conn);
}
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
conn.SetIsolationLevel(CUBRIDIsolationLevel.TRAN_REP_CLASS_COMMIT_INSTANCE);
Debug.Assert(conn.GetIsolationLevel() == CUBRIDIsolationLevel.TRAN_REP_CLASS_COMMIT_INSTANCE);
tablesCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
conn.BeginTransaction();
TestCases.ExecuteSQL("create table isol(id int)", conn);
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
//Verify table was created
Debug.Assert(newTableCount == tablesCount + 1);
using (CUBRIDConnection connOut = new CUBRIDConnection())
{
connOut.ConnectionString = TestCases.connString;
connOut.Open();
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, connOut);
Debug.Assert(newTableCount == tablesCount);
}
conn.Commit();
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
//Verify table was created
Debug.Assert(newTableCount == tablesCount + 1);
TestCases.ExecuteSQL("drop table if exists isol", conn);
newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
Debug.Assert(newTableCount == tablesCount);
}
}
private static void CreateTestTableLOB(CUBRIDConnection conn)
{
TestCases.ExecuteSQL("drop table if exists t", conn);
TestCases.ExecuteSQL("create table t(b BLOB, c CLOB)", conn);
}
private static void CleanupTestTableLOB(CUBRIDConnection conn)
{
TestCases.ExecuteSQL("drop table if exists t", conn);
}
/// <summary>
/// Test CUBRID data types, using parameters
/// </summary>
private static void Test_Various_DataTypes_Parameters()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.ExecuteSQL("drop table if exists t", conn);
string sql = "create table t(";
sql += "c_integer_ai integer AUTO_INCREMENT, ";
sql += "c_smallint smallint, ";
sql += "c_integer integer, ";
sql += "c_bigint bigint, ";
sql += "c_numeric numeric(15,1), ";
sql += "c_float float, ";
sql += "c_decimal decimal(15,3), ";
sql += "c_double double, ";
sql += "c_char char(1), ";
sql += "c_varchar character varying(4096), ";
sql += "c_time time, ";
sql += "c_date date, ";
sql += "c_timestamp timestamp, ";
sql += "c_datetime datetime, ";
sql += "c_bit bit(8), ";
sql += "c_varbit bit varying(4096), ";
sql += "c_monetary monetary, ";
sql += "c_string string, ";
sql += "c_null string ";
sql += ")";
TestCases.ExecuteSQL(sql, conn);
sql = "insert into t values(";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "?, ";
sql += "? ";
sql += ")";
CUBRIDCommand cmd_i = new CUBRIDCommand(sql, conn);
//sql += "c_integer_ai integer AUTO_INCREMENT, ";
//sql += "1, ";
CUBRIDParameter p1 = new CUBRIDParameter("?p1", CUBRIDDataType.CCI_U_TYPE_INT);
p1.Value = 1;
cmd_i.Parameters.Add(p1);
//sql += "c_smallint smallint, ";
//sql += "11, ";
CUBRIDParameter p2 = new CUBRIDParameter("?p2", CUBRIDDataType.CCI_U_TYPE_SHORT);
p2.Value = 11;
cmd_i.Parameters.Add(p2);
//sql += "c_integer integer, ";
//sql += "111, ";
CUBRIDParameter p3 = new CUBRIDParameter("?p3", CUBRIDDataType.CCI_U_TYPE_INT);
p3.Value = 111;
cmd_i.Parameters.Add(p3);
//sql += "c_bigint bigint, ";
//sql += "1111, ";
CUBRIDParameter p4 = new CUBRIDParameter("?p4", CUBRIDDataType.CCI_U_TYPE_BIGINT);
p4.Value = 1111;
cmd_i.Parameters.Add(p4);
//sql += "c_numeric numeric(15,0), ";
//sql += "1.1, ";
CUBRIDParameter p5 = new CUBRIDParameter("?p5", CUBRIDDataType.CCI_U_TYPE_NUMERIC);
p5.Value = 1.1;
cmd_i.Parameters.Add(p5);
//sql += "c_float float, ";
//sql += "1.11, ";
CUBRIDParameter p6 = new CUBRIDParameter("?p6", CUBRIDDataType.CCI_U_TYPE_FLOAT);
p6.Value = 1.11;
cmd_i.Parameters.Add(p6);
//sql += "c_decimal decimal, ";
//sql += "1.111, ";
CUBRIDParameter p7 = new CUBRIDParameter("?p7", CUBRIDDataType.CCI_U_TYPE_NUMERIC);
p7.Value = 1.111;
cmd_i.Parameters.Add(p7);
//sql += "c_double double, ";
//sql += "1.1111, ";
CUBRIDParameter p8 = new CUBRIDParameter("?p8", CUBRIDDataType.CCI_U_TYPE_DOUBLE);
p8.Value = 1.1111;
cmd_i.Parameters.Add(p8);
//sql += "c_char char(1), ";
//sql += "'a', ";
CUBRIDParameter p9 = new CUBRIDParameter("?p9", CUBRIDDataType.CCI_U_TYPE_CHAR);
p9.Size = 1;
p9.Value = 'a';
cmd_i.Parameters.Add(p9);
//sql += "c_varchar varchar(4096), ";
//sql += "'abcdfghijk', ";
CUBRIDParameter p10 = new CUBRIDParameter("?p10", CUBRIDDataType.CCI_U_TYPE_STRING);
p10.Value = "abcdfghijk";//trebuie luat cap coada si vazut dc plm nu se trimite ok. S-ar putea sa fie de la n
cmd_i.Parameters.Add(p10);
//sql += "c_time time, ";
//sql += "TIME '13:15:45 pm', ";
CUBRIDParameter p11 = new CUBRIDParameter("?p11", CUBRIDDataType.CCI_U_TYPE_TIME);
p11.Value = new DateTime(2010, 1, 1, 13, 15, 45); //year/month/date is not relevant, only the time part is used
cmd_i.Parameters.Add(p11);
//sql += "c_date date, ";
//sql += "DATE '00-10-31', ";
CUBRIDParameter p12 = new CUBRIDParameter("?p12", CUBRIDDataType.CCI_U_TYPE_DATE);
p12.Value = new DateTime(2000, 10, 31);
cmd_i.Parameters.Add(p12);
//sql += "c_timestamp timestamp, ";
//sql += "TIMESTAMP '13:15:45 10/31/2008', ";
CUBRIDParameter p13 = new CUBRIDParameter("?p13", CUBRIDDataType.CCI_U_TYPE_TIMESTAMP);
p13.Value = new DateTime(2008, 10, 31, 13, 15, 45);
cmd_i.Parameters.Add(p13);
//sql += "c_datetime datetime, ";
//sql += "DATETIME '13:15:45 10/31/2008', ";
CUBRIDParameter p14 = new CUBRIDParameter("?p14", CUBRIDDataType.CCI_U_TYPE_DATETIME);
p14.Value = new DateTime(2008, 10, 31, 13, 15, 45);
cmd_i.Parameters.Add(p14);
//sql += "c_bit bit(1), ";
//sql += "B'1', ";
CUBRIDParameter p15 = new CUBRIDParameter("?p15", CUBRIDDataType.CCI_U_TYPE_BIT);
p15.Value = (byte)1;
cmd_i.Parameters.Add(p15);
//sql += "c_varbit bit varying(4096), ";
//sql += "B'1', ";
CUBRIDParameter p16 = new CUBRIDParameter("?p16", CUBRIDDataType.CCI_U_TYPE_VARBIT);
p16.Value = (byte)1;
cmd_i.Parameters.Add(p16);
//sql += "c_monetary monetary, ";
//sql += "123456789, ";
CUBRIDParameter p17 = new CUBRIDParameter("?p17", CUBRIDDataType.CCI_U_TYPE_MONETARY);
p17.Value = 123456789;
cmd_i.Parameters.Add(p17);
//sql += "c_string string ";
//sql += "'qwerty'";
CUBRIDParameter p18 = new CUBRIDParameter("?p18", CUBRIDDataType.CCI_U_TYPE_STRING);
p18.Value = "qwerty";
cmd_i.Parameters.Add(p18);
//sql += "c_null string ";
//sql += "null";
CUBRIDParameter p19 = new CUBRIDParameter("?p19", CUBRIDDataType.CCI_U_TYPE_NULL);
p19.Value = null;
cmd_i.Parameters.Add(p19);
cmd_i.ExecuteNonQuery();
cmd_i.Close();
sql = "select * from t ";
sql += "where 1 = 1 ";
sql += "and c_integer_ai = ? ";
sql += "and c_smallint = ? ";
sql += "and c_integer = ? ";
sql += "and c_bigint = ? ";
sql += "and c_numeric = ? ";
sql += "and c_float = ? ";
sql += "and c_decimal = ? ";
sql += "and c_double = ? ";
sql += "and c_char = ? ";
sql += "and c_varchar = ? ";
sql += "and c_time = ? ";
sql += "and c_date = ? ";
sql += "and c_timestamp = ? ";
sql += "and c_datetime = ? ";
sql += "and c_bit = ? ";
sql += "and c_varbit = ? ";
sql += "and c_monetary = ? ";
sql += "and c_string = ? ";
sql += "and c_null IS NULL ";
CUBRIDCommand cmd_s = new CUBRIDCommand(sql, conn);
cmd_s.Parameters.Add(p1);
cmd_s.Parameters.Add(p2);
cmd_s.Parameters.Add(p3);
cmd_s.Parameters.Add(p4);
cmd_s.Parameters.Add(p5);
cmd_s.Parameters.Add(p6);
cmd_s.Parameters.Add(p7);
cmd_s.Parameters.Add(p8);
cmd_s.Parameters.Add(p9);
cmd_s.Parameters.Add(p10);
cmd_s.Parameters.Add(p11);
cmd_s.Parameters.Add(p12);
cmd_s.Parameters.Add(p13);
cmd_s.Parameters.Add(p14);
cmd_s.Parameters.Add(p15);
cmd_s.Parameters.Add(p16);
cmd_s.Parameters.Add(p17);
cmd_s.Parameters.Add(p18);
//cmd_s.Parameters.Add(p19);
DbDataReader reader = cmd_s.ExecuteReader();
while (reader.Read()) //only one row will be available
{
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetInt16(1) == 11);
Debug.Assert(reader.GetInt32(2) == 111);
Debug.Assert(reader.GetInt64(3) == 1111);
Debug.Assert(reader.GetDecimal(4) == (decimal)1.1);
Debug.Assert(reader.GetFloat(5) == (float)1.11);
Debug.Assert(reader.GetDouble(6) == 1.111);
Debug.Assert(reader.GetDouble(7) == 1.1111);
Debug.Assert(reader.GetChar(8) == 'a');
Debug.Assert(reader.GetString(9) == "abcdfghijk");
Debug.Assert(reader.GetDateTime(10) == new DateTime(1, 1, 1, 13, 15, 45));
Debug.Assert(reader.GetDateTime(11) == new DateTime(2000, 10, 31));
Debug.Assert(reader.GetDateTime(12) == new DateTime(2008, 10, 31, 13, 15, 45));
Debug.Assert(reader.GetDateTime(13) == new DateTime(2008, 10, 31, 13, 15, 45, 00));
Debug.Assert(reader.GetByte(14) == 1);
Debug.Assert(reader.GetByte(15) == 1);
Debug.Assert(reader.GetString(16) == "123456789");
Debug.Assert(reader.GetString(17) == "qwerty");
Debug.Assert(reader.GetValue(18) == null);
}
cmd_s.Close();
TestCases.ExecuteSQL("drop table t", conn);
}
}
/// <summary>
/// Test CUBRID data types Get...()
/// </summary>
private static void Test_Various_DataTypes()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.ExecuteSQL("drop table if exists t", conn);
string sql = "create table t(";
sql += "c_integer_ai integer AUTO_INCREMENT, ";
sql += "c_smallint smallint, ";
sql += "c_integer integer, ";
sql += "c_bigint bigint, ";
sql += "c_numeric numeric(15,1), ";
sql += "c_float float, ";
sql += "c_decimal decimal(15,3), ";
sql += "c_double double, ";
sql += "c_char char(1), ";
sql += "c_varchar character varying(4096), ";
sql += "c_time time, ";
sql += "c_date date, ";
sql += "c_timestamp timestamp, ";
sql += "c_datetime datetime, ";
sql += "c_bit bit(1), ";
sql += "c_varbit bit varying(4096), ";
sql += "c_monetary monetary, ";
sql += "c_string string";
sql += ")";
TestCases.ExecuteSQL(sql, conn);
sql = "insert into t values(";
sql += "1, ";
sql += "11, ";
sql += "111, ";
sql += "1111, ";
sql += "1.1, ";
sql += "1.11, ";
sql += "1.111, ";
sql += "1.1111, ";
sql += "'a', ";
sql += "'abcdfghijk', ";
sql += "TIME '13:15:45 pm', ";
sql += "DATE '00-10-31', ";
sql += "TIMESTAMP '13:15:45 10/31/2008', ";
sql += "DATETIME '13:15:45 10/31/2008', ";
sql += "B'0', ";
sql += "B'0', ";
sql += "123456789, ";
sql += "'qwerty'";
sql += ")";
TestCases.ExecuteSQL(sql, conn);
sql = "select * from t";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //only one row will be available
{
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetInt16(1) == 11);
Debug.Assert(reader.GetInt32(2) == 111);
Debug.Assert(reader.GetInt64(3) == 1111);
Debug.Assert(reader.GetDecimal(4) == (decimal)1.1);
Debug.Assert(reader.GetFloat(5) == (float)1.11);
Debug.Assert(reader.GetDecimal(6) == (decimal)1.111);
Debug.Assert(reader.GetDouble(7) == (double)1.1111);
Debug.Assert(reader.GetChar(8) == 'a');
Debug.Assert(reader.GetString(9) == "abcdfghijk");
Debug.Assert(reader.GetDateTime(10).Second == 45);
Debug.Assert(reader.GetDateTime(11) == new DateTime(2000, 10, 31));
Debug.Assert(reader.GetDateTime(12) == new DateTime(2008, 10, 31, 13, 15, 45));
Debug.Assert(reader.GetDateTime(13) == new DateTime(2008, 10, 31, 13, 15, 45));
//Debug.Assert(reader.GetByte(14) == (byte)0);
//Debug.Assert(reader.GetByte(15) == (byte)0);
Debug.Assert(reader.GetString(16) == "123456789.0000000000000000");
Debug.Assert(reader.GetString(17) == "qwerty");
}
}
TestCases.ExecuteSQL("drop table t", conn);
}
}
/// <summary>
/// Test Encodings support with parameters
/// </summary>
private static void Test_EncodingsWithParameters()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = "server=" + ip + ";database=demodb;port=33000;user=public;password=;charset=utf-8";
conn.Open();
TestCases.ExecuteSQL("drop table if exists t", conn);
TestCases.ExecuteSQL("create table t(a int, b varchar(100))", conn);
String sql = "insert into t values(1 ,?)";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
CUBRIDParameter param = new CUBRIDParameter();
param.ParameterName = "?";
param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING;
param.Value = "¾Æ¹«°³";
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
sql = "select * from t where b = ?";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
CUBRIDParameter param = new CUBRIDParameter();
param.ParameterName = "?";
param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING;
param.Value = "¾Æ¹«°³";
cmd.Parameters.Add(param);
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read(); //retrieve just one row
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetString(1) == "¾Æ¹«°³");
}
}
sql = "update t set b=?";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
CUBRIDParameter param = new CUBRIDParameter();
param.ParameterName = "?";
param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING;
param.Value = "¾Æ¹°³";
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
sql = "select * from t where b = ?";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
CUBRIDParameter param = new CUBRIDParameter();
param.ParameterName = "?";
param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING;
param.Value = "¾Æ¹°³";
cmd.Parameters.Add(param);
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read(); //retrieve just one row
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetString(1) == "¾Æ¹°³");
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
}
/// <summary>
/// Test Enum data type
/// </summary>
public static void Test_DataType_Enum()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.ExecuteSQL("drop table if exists table11;", conn);
/* create new table */
string sql = "create table table11(city enum('BeiJing', 'ChengDu', 'QingDao', 'DaLian'), nationality enum('China', 'Korea', 'Japan'));";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
/* insert multi rows values */
sql = "insert into table11 (city, nationality) values ('BeiJing', 'Japan'),('ChengDu','China'),('QingDao', 'Korea');";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
/* verify count */
sql = "select count(*) from table11";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetInt32(0) == 3);
}
}
sql = "select * from table11";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetString(0) == "BeiJing");
}
}
try
{
/*
* Only thrown exception is the correct result
*/
sql = "insert into table11 (city, nationality) values ('Peking', 'China');";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
}
catch (Exception exp)
{
string expected = exp.Message.Substring(0, exp.Message.LastIndexOf("["));
Debug.Assert(expected == "Semantic: before ' , 'China');'\nCannot coerce 'Peking' to type enum. insert into table11 table11 (table11.city, table11.nationali...");
}
TestCases.ExecuteSQL("drop table11;", conn);
}
}
