/// <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 CUBRIDTransaction class, using parameters /// </summary> private static void Test_Transaction_Parameters() { DbTransaction tran = null; using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTable(conn); tran = conn.BeginTransaction(); string sql = "insert into t values(?, ?, ?, ?, ?, ?)"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDParameter p1 = new CUBRIDParameter("?p1", CUBRIDDataType.CCI_U_TYPE_INT); p1.Value = 1; cmd.Parameters.Add(p1); CUBRIDParameter p2 = new CUBRIDParameter("?p2", CUBRIDDataType.CCI_U_TYPE_CHAR); p2.Value = 'A'; cmd.Parameters.Add(p2); CUBRIDParameter p3 = new CUBRIDParameter("?p3", CUBRIDDataType.CCI_U_TYPE_STRING); p3.Value = "cubrid"; cmd.Parameters.Add(p3); CUBRIDParameter p4 = new CUBRIDParameter("?p4", CUBRIDDataType.CCI_U_TYPE_FLOAT); p4.Value = 1.1f; cmd.Parameters.Add(p4); CUBRIDParameter p5 = new CUBRIDParameter("?p5", CUBRIDDataType.CCI_U_TYPE_DOUBLE); p5.Value = 2.2d; cmd.Parameters.Add(p5); CUBRIDParameter p6 = new CUBRIDParameter("?p6", CUBRIDDataType.CCI_U_TYPE_DATE); p6.Value = DateTime.Now; cmd.Parameters.Add(p6); cmd.ExecuteNonQuery(); tran.Commit(); } Debug.Assert(GetTableRowsCount("t", conn) == 1); CleanupTestTable(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 + 1); } 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); } }
/// <summary> /// Test BLOB INSERT in a transaction /// </summary> private static void Test_Blob_InsertTransaction() { DbTransaction tran = null; using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTableLOB(conn); tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted); string sql = "insert into t (b) values(?)"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDBlob Blob = new CUBRIDBlob(conn); byte[] bytes = new byte[256]; bytes[0] = 69; bytes[1] = 98; bytes[2] = 99; bytes[255] = 122; Blob.SetBytes(1, bytes); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_BLOB; param.Value = Blob; cmd.Parameters.Add(param); cmd.Parameters[0].DbType = DbType.Binary; cmd.ExecuteNonQuery(); } tran.Rollback(); } //We have to close and reopen connection. Otherwise we get an invalid buffer position. using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); string sql2 = "SELECT b from t"; using (CUBRIDCommand cmd2 = new CUBRIDCommand(sql2, conn)) { DbDataReader reader = cmd2.ExecuteReader(); Debug.Assert(reader.HasRows == false, "Transaction did not rollback!"); } CleanupTestTableLOB(conn); } }
/// <summary> /// Test BLOB DELETE in a transaction /// </summary> private static void Test_Blob_DeleteTransaction() { DbTransaction tran = null; byte[] bytes1 = new byte[256]; using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTableLOB(conn); string sql1 = "insert into t (b) values(?)"; using (CUBRIDCommand cmd1 = new CUBRIDCommand(sql1, conn)) { CUBRIDBlob Blob = new CUBRIDBlob(conn); bytes1[0] = 69; bytes1[1] = 98; bytes1[2] = 99; bytes1[255] = 122; Blob.SetBytes(1, bytes1); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_BLOB; param.Value = Blob; cmd1.Parameters.Add(param); cmd1.Parameters[0].DbType = DbType.Binary; cmd1.ExecuteNonQuery(); cmd1.Close(); tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted); string sql2 = "DELETE from t"; CUBRIDCommand cmd2 = new CUBRIDCommand(sql2, conn); cmd2.ExecuteNonQuery(); } tran.Rollback(); } //We have to close and reopen connection. Otherwise we get an invalid buffer position. using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); string sql = "SELECT b from t"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Debug.Assert(reader.HasRows == true); CUBRIDBlob bImage = (CUBRIDBlob)reader[0]; byte[] bytes = new byte[(int)bImage.BlobLength]; bytes = bImage.GetBytes(1, (int)bImage.BlobLength); Debug.Assert(bytes1.Length == bytes.Length); bool ok = true; for (int i = 0; i < bytes.Length; i++) { if (bytes1[i] != bytes[i]) ok = false; } Debug.Assert(ok == true, "The BLOB DELETE command was not rolled-back correctly!"); } } CleanupTestTableLOB(conn); } }
public void Transaction_Test() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = DBHelper.connString; conn.Open(); DBHelper.ExecuteSQL("drop table if exists t", conn); LogTestStep("Begin a transaction, then rollback"); conn.BeginTransaction(); DBHelper.ExecuteSQL("create table t(idx integer)", conn); int tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(1, tablesCount); conn.Rollback(); //Verify the table does not exist tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(0, tablesCount); LogStepPass(); LogTestStep("Begin a transaction, then commit, then rollback"); conn.BeginTransaction(); DBHelper.ExecuteSQL("create table t(idx integer)", conn); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(1, tablesCount); conn.Commit(); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(1, tablesCount); conn.Rollback(); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(1, tablesCount); LogStepPass(); Console.WriteLine(); //revert the test db DBHelper.ExecuteSQL("drop table t", conn); conn.Commit(); LogTestResult(); } }
public void APIS_485() { LogTestStep("Test the lockTimeout is set successfully"); CUBRIDConnection conn = new CUBRIDConnection(); conn.ConnectionString = DBHelper.connString; Assert.AreEqual(-1, conn.LockTimeout); conn.Open(); int timeout = 20; conn.SetLockTimeout(timeout); Assert.AreEqual(timeout, conn.LockTimeout); DBHelper.ExecuteSQL("drop table if exists t", conn); DBHelper.ExecuteSQL("create table t(id integer)", conn); DBHelper.ExecuteSQL("insert into t values (1)", conn); conn.SetAutoCommit(false); CUBRIDConnection conn2 = null; double elapseTime = 0; var stopwatch = new Stopwatch(); try { Thread thread2 = new Thread(delegate() { conn2 = new CUBRIDConnection(); conn2.ConnectionString = DBHelper.connString; conn2.Open(); conn2.SetAutoCommit(false); conn2.BeginTransaction(); DBHelper.ExecuteSQL("update t set id=2", conn2); }); conn.BeginTransaction(); thread2.Start(); Thread.Sleep(5000); stopwatch.Start(); DBHelper.ExecuteSQL("update t set id=3", conn); thread2.Join(); } catch (Exception ex) { stopwatch.Stop(); elapseTime = (double)stopwatch.ElapsedMilliseconds / 1000; double diffTime = elapseTime - (double)(timeout / 1000); Console.WriteLine("different time = " + stopwatch.ElapsedMilliseconds); Console.WriteLine("different time = " + diffTime); Log(ex.Message); Assert.AreEqual(timeout, conn.LockTimeout); if (diffTime >= 0 && diffTime < 10) { LogStepPass(); } else { LogStepFail(); } } finally { LogTestResult(); //DBHelper.ExecuteSQL("drop table t", conn); conn.Close(); conn2.Close(); } }
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!"); } } }
public void Close_Test() { using (CUBRIDConnection conn = new CUBRIDConnection()) { LogTestStep("Close DB before connected"); try { conn.ConnectionString = DBHelper.connString; conn.Close(); } catch (Exception ex) { Assert.AreEqual("The connection is not open!", ex.Message); LogStepPass(); } LogTestStep("Close a valid DB Connection, and check: (1)the connection is closed (2)the transaction which is not committed is rolled back"); Log("Connect to a DB"); conn.Open(); Log("Verify the Connection is OK by feching some data"); Assert.IsTrue(DBHelper.GetTableRowsCount("db_class", conn) > 0); Log("Update the DB in a transaction, and close the connection before commit"); DBHelper.ExecuteSQL("drop table if exists t", conn); conn.BeginTransaction(); DBHelper.ExecuteSQL("create table t(idx integer)", conn); int tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(1, tablesCount); conn.Close(); Log("Verify the Connection is closed by feching some data"); try { int count = DBHelper.GetTableRowsCount("db_class", conn); } catch (Exception ex) { Assert.AreEqual("The connection is not open!", ex.Message); } Log("Verify the transaction is rolled back"); conn.Open(); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(0, tablesCount); LogStepPass(); conn.Close(); LogTestStep("Close a valid DB Connection twice, check no exception is generated"); conn.Close(); LogStepPass(); LogTestResult(); } }
public void CUBRIDTransaction_CUBRIDIsolationLevel_Test() { LogTestStep("Test CUBRIDTransaction.CUBRIDConnection"); CUBRIDConnection conn = new CUBRIDConnection(DBHelper.connString); conn.Open(); CUBRIDTransaction transaction = conn.BeginTransaction(CUBRIDIsolationLevel.TRAN_DEFAULT_ISOLATION); Console.WriteLine(transaction.CUBRIDIsolationLevel); Assert.AreEqual(CUBRIDIsolationLevel.TRAN_DEFAULT_ISOLATION, transaction.CUBRIDIsolationLevel); LogStepPass(); LogTestResult(); }
public void CUBRIDTransaction_CUBRIDConnection_Test() { LogTestStep("Test CUBRIDTransaction.CUBRIDConnection"); CUBRIDConnection conn = new CUBRIDConnection(DBHelper.connString); conn.Open(); CUBRIDTransaction transaction = conn.BeginTransaction(); transaction.Connection.Close(); try { DBHelper.ExecuteSQL("drop table if exists t", conn); LogStepFail(); } catch(Exception ex) { Assert.AreEqual("The connection is not open!", ex.Message); LogStepPass(); } LogTestResult(); }
public void CUBRIDTransaction_Commit_Rollback_Negtive2_Test() { using (CUBRIDConnection conn = new CUBRIDConnection(DBHelper.connString)) { conn.Open(); DBHelper.ExecuteSQL("drop table if exists t", conn); LogTestStep("Begin a transaction, commit after close the connection"); CUBRIDTransaction transaction = conn.BeginTransaction(); DBHelper.ExecuteSQL("create table t(idx integer)", conn); try { conn.Close(); transaction.Commit(); LogStepFail(); } catch (Exception ex) { Assert.AreEqual("Connection must be valid and open to commit transaction!", ex.Message); LogStepPass(); } conn.Open(); LogTestStep("Begin a transaction, rollback after close the connection"); CUBRIDTransaction transaction2 = conn.BeginTransaction(); DBHelper.ExecuteSQL("drop table if exists t", conn); try { conn.Close(); transaction2.Rollback(); LogStepFail(); } catch (Exception ex) { Assert.AreEqual("Connection must be valid and open to rollback transaction!", ex.Message); LogStepPass(); } LogTestResult(); } }
public void CUBRIDTransaction_Commit_Rollback_Negtive1_Test() { using (CUBRIDConnection conn = new CUBRIDConnection(DBHelper.connString)) { conn.Open(); DBHelper.ExecuteSQL("drop table if exists t", conn); LogTestStep("Begin a transaction, commit twice"); CUBRIDTransaction transaction = conn.BeginTransaction(); DBHelper.ExecuteSQL("create table t(idx integer)", conn); transaction.Commit(); int tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(1, tablesCount); try { transaction.Commit(); LogStepFail(); } catch (Exception ex) { Assert.AreEqual("Transaction has already been committed or is not pending!", ex.Message); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(1, tablesCount); LogStepPass(); } LogTestStep("Begin a transaction, rollback twice"); CUBRIDTransaction transaction2 = conn.BeginTransaction(); DBHelper.ExecuteSQL("drop table if exists t", conn); transaction2.Rollback(); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(1, tablesCount); try { transaction.Rollback(); LogStepFail(); } catch (Exception ex) { Assert.AreEqual("Transaction has already been committed or is not pending!", ex.Message); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(1, tablesCount); LogStepPass(); } LogTestStep("Begin a transaction, commit, then rollback"); CUBRIDTransaction transaction3 = conn.BeginTransaction(); DBHelper.ExecuteSQL("drop table if exists t", conn); transaction3.Commit(); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(0, tablesCount); try { transaction3.Rollback(); LogStepFail(); } catch (Exception ex) { Assert.AreEqual("Transaction has already been committed or is not pending!", ex.Message); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(0, tablesCount); LogStepPass(); } LogTestStep("Begin a transaction, rollback, then commit"); CUBRIDTransaction transaction4 = conn.BeginTransaction(); DBHelper.ExecuteSQL("create table t(idx integer)", conn); transaction4.Rollback(); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(0, tablesCount); try { transaction4.Commit(); LogStepFail(); } catch (Exception ex) { Assert.AreEqual("Transaction has already been committed or is not pending!", ex.Message); tablesCount = DBHelper.GetTablesCount("t", conn); Assert.AreEqual(0, tablesCount); LogStepPass(); } LogTestResult(); } }
public void CUBRIDCommand_Constructor_SQLAndConnAndTran_Test() { CUBRIDConnection conn = new CUBRIDConnection(); conn.ConnectionString = DBHelper.connString; conn.Open(); conn.SetAutoCommit(false); string sql = "drop table if exists t"; CUBRIDTransaction transaction = new CUBRIDTransaction(conn, CUBRIDIsolationLevel.TRAN_DEFAULT_ISOLATION); CUBRIDCommand cmd = new CUBRIDCommand(sql, conn, transaction); conn.BeginTransaction(); cmd.ExecuteNonQuery(); cmd.CommandText = "create table t (id int, name varchar(50))"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into t values(1, 'Nancy')"; cmd.ExecuteNonQuery(); conn.Commit(); cmd.CommandText = "select * from t"; CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader(); reader.Read(); Assert.AreEqual(2, reader.FieldCount); Assert.AreEqual("1", reader.GetString(0)); Assert.AreEqual("Nancy", reader.GetString(1)); cmd.Close(); reader.Close(); conn.Close(); }