/// <summary> /// Test SQL statements execution, using DataReader and parameters /// </summary> private static void Test_DataReader_Parameters() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CUBRIDCommand cmd = new CUBRIDCommand("select `code` from nation where capital = ?", conn); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING; param.Value = "Kabul"; cmd.Parameters.Add(param); DbDataReader reader = cmd.ExecuteReader(); Debug.Assert(reader.FieldCount == 1); while (reader.Read()) //only one row is available { Debug.Assert(reader.GetString(0) == "AFG"); } cmd.Close(); } }
/// <summary> /// Test BLOB SELECT, using CUBRIDDataAdapter and DataTable /// </summary> private static void Test_Blob_SelectDataAdapter() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTableLOB(conn); string sql1 = "insert into t (b) values(?)"; CUBRIDCommand cmd1 = new CUBRIDCommand(sql1, conn); CUBRIDBlob Blob1 = new CUBRIDBlob(conn); CUBRIDBlob Blob2 = new CUBRIDBlob(conn); byte[] bytes1 = new byte[256]; bytes1[0] = 69; bytes1[1] = 98; bytes1[2] = 99; bytes1[255] = 122; Blob1.SetBytes(1, bytes1); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_BLOB; param.Value = Blob1; cmd1.Parameters.Add(param); cmd1.Parameters[0].DbType = DbType.Binary; cmd1.ExecuteNonQuery(); cmd1.Close(); string sql = "SELECT b from t"; DataTable dt = new DataTable("t"); CUBRIDDataAdapter da = new CUBRIDDataAdapter(); da.SelectCommand = new CUBRIDCommand(sql, conn); da.Fill(dt); for (int j = 0; j < dt.Rows.Count; j++) { CUBRIDBlob bImage = (CUBRIDBlob)dt.Rows[j]["b"]; byte[] bytes = new byte[(int)bImage.BlobLength]; bytes = bImage.GetBytes(1, (int)bImage.BlobLength); Debug.Assert(bytes1.Length == bytes.Length, "The selected length is not valid!"); bool ok = true; for (int i = 0; i < bytes.Length; i++) { if (bytes1[i] != bytes[i]) { ok = false; } } Debug.Assert(ok == true, "The BLOB was not selected correctly!"); } CleanupTestTableLOB(conn); } }
/// <summary> /// Test CLOB UPDATE /// </summary> private static void Test_Clob_Update() { String str; using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTableLOB(conn); string sql1 = "insert into t (c) values(?)"; using (CUBRIDCommand cmd1 = new CUBRIDCommand(sql1, conn)) { CUBRIDClob Clob1 = new CUBRIDClob(conn); Clob1.SetString(1, "test string to be inserted"); CUBRIDParameter param1 = new CUBRIDParameter(); param1.ParameterName = "?"; param1.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_CLOB; param1.Value = Clob1; cmd1.Parameters.Add(param1); cmd1.ExecuteNonQuery(); cmd1.Close(); string sql = "UPDATE t SET c = ?"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); CUBRIDClob Clob = new CUBRIDClob(conn); str = conn.ConnectionString; //Use the ConnectionString for testing 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(); } 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); Debug.Assert(str.Length == str2.Length, "The selected CLOB length is not valid!"); Debug.Assert(str.Equals(str2), "The CLOB was not selected correctly!"); } } CleanupTestTableLOB(conn); } }
private static void TestParameterCollection() { string sql = "drop table if exists TestTable;"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } sql = "CREATE TABLE TestTable (clsid BLOB);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } byte[] bytes = new byte[36] { 55, 56, 50, 69, 55, 57, 67, 69, 45, 50, 70, 68, 68, 45, 52, 68, 50, 55, 45, 65, 51, 48, 48, 45, 69, 48, 56, 56, 70, 56, 68, 68, 55, 54, 66, 69 }; sql = "INSERT INTO TestTable VALUES(?);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDBlob Blob = new CUBRIDBlob(conn); Blob.SetBytes(1, bytes); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = Blob; cmd.Parameters.Add(param); cmd.Parameters[0].DbType = DbType.Binary; try { cmd.Parameters.Insert(0, param); } catch (Exception e) { Debug.Assert(e.Message == "Parameter already added to the collection!"); } try { cmd.Parameters.Insert(0, null); } catch (Exception e) { string es = e.ToString(); Debug.Assert(e.Message == "Only CUBRIDParameter objects are valid!"); } cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("Select * from TestTable", conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { reader.Read(); byte[] buffer = new byte[36]; long len = reader.GetBytes(0, 0, buffer, 0, 36); ASCIIEncoding encoding = new ASCIIEncoding(); string clsid = encoding.GetString(buffer); Debug.Assert(clsid == "782E79CE-2FDD-4D27-A300-E088F8DD76BE"); } } }
///<summary> /// Test BLOB SELECT /// </summary> private static void Test_Blob_Select() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTableLOB(conn); string sql1 = "insert into t (b) values(?)"; CUBRIDCommand cmd1 = new CUBRIDCommand(sql1, conn); CUBRIDBlob Blob = new CUBRIDBlob(conn); byte[] bytes1 = new byte[256]; 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(); string sql = "SELECT b from t"; 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); Debug.Assert(bytes1.Length == bytes.Length, "The selected BLOB length is not valid!"); bool ok = true; for (int i = 0; i < bytes.Length; i++) { if (bytes1[i] != bytes[i]) { ok = false; } } Debug.Assert(ok == true, "The BLOB was not selected correctly!"); } cmd.Close(); CleanupTestTableLOB(conn); } }
/// <summary> /// Test CLOB INSERT, using a txt input file /// </summary> private static void Test_Clob_FromFile() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTableLOB(conn); string sql = "insert into t (c) values(?)"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); CUBRIDClob Clob = new CUBRIDClob(conn); StreamReader r = new StreamReader("../../../BSD License.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); } }
/// <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); } }
public void CUBRIDCommand_Prepare_Basic_Test() { using (CUBRIDConnection conn = new CUBRIDConnection(DBHelper.connString)) { conn.Open(); DBHelper.ExecuteSQL("drop table if exists t", conn); DBHelper.ExecuteSQL("create table t (id int, name varchar(50))", conn); CUBRIDCommand cmd = new CUBRIDCommand(null, conn); LogTestStep("Test Prepare"); cmd.CommandText = "insert into t (id, name) values (?, ?)"; CUBRIDParameter idParam = new CUBRIDParameter("?", CUBRIDDataType.CCI_U_TYPE_INT, 8); CUBRIDParameter nameParam = new CUBRIDParameter("?", CUBRIDDataType.CCI_U_TYPE_STRING, 20); idParam.Value = 2; nameParam.Value = "Rachel Green"; cmd.Parameters.Add(idParam); cmd.Parameters.Add(nameParam); Log("Call Prepare after setting the Commandtext and Parameters."); cmd.Prepare(); cmd.ExecuteNonQuery(); Log("Change parameter values and call ExecuteNonQuery."); cmd.Parameters[0].Value = 5; cmd.Parameters[1].Value = "Bill Gates"; cmd.ExecuteNonQuery(); Log("Verify the date are inserted by querying them from db"); cmd = new CUBRIDCommand("select * from t", conn); CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); Assert.AreEqual(2, dt.Rows.Count); Assert.AreEqual(2, (int)dt.Rows[0][0]); Assert.AreEqual("Rachel Green", dt.Rows[0][1].ToString()); Assert.AreEqual(5, (int)dt.Rows[1][0]); Assert.AreEqual("Bill Gates", dt.Rows[1][1].ToString()); LogStepPass(); cmd.Close(); Log("delete test table"); DBHelper.ExecuteSQL("drop table if exists t", conn); LogTestResult(); } }
/// <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); } }
public static void Test_CUBRIDBlob_Select() { Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof(TestCUBRIDBlobType).Assembly); using (CUBRIDConnection conn = new CUBRIDConnection(cfg.GetProperty(NHibernate.Cfg.Environment.ConnectionString))) { conn.Open(); TestCases.ExecuteSQL("drop table if exists TestCUBRIDBlob", conn); TestCases.ExecuteSQL("create table TestCUBRIDBlob(c_integer int not null auto_increment," + "c_blob BLOB," + "primary key (c_integer))", conn); const string sql = "insert into TestCUBRIDBlob values(1, ?)"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); CUBRIDBlob Blob = new CUBRIDBlob(conn); BinaryReader originalFileReader = new BinaryReader(File.Open("../../CUBRID.ico", FileMode.Open)); byte[] bytesOriginalData = originalFileReader.ReadBytes((int)originalFileReader.BaseStream.Length); originalFileReader.Close(); Blob.SetBytes(1, bytesOriginalData); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = Blob; cmd.Parameters.Add(param); cmd.Parameters[0].DbType = DbType.Binary; cmd.ExecuteNonQuery(); cmd.Close(); ISessionFactory sessionFactory = cfg.BuildSessionFactory(); using (var session = sessionFactory.OpenSession()) { //Retrieve the inserted information IQuery query = session.CreateQuery("FROM TestCUBRIDBlobType"); IList <TestCUBRIDBlobType> testQuery = query.List <TestCUBRIDBlobType>(); Debug.Assert(testQuery[0].c_integer == 1); CUBRIDBlob bImage = testQuery[0].c_blob; byte[] bytesRetrievedData = bImage.GetBytes(1, (int)bImage.BlobLength); Debug.Assert(bytesOriginalData.Length == bytesRetrievedData.Length); Debug.Assert(bytesOriginalData[0] == bytesRetrievedData[0]); Debug.Assert(bytesOriginalData[bytesOriginalData.Length - 1] == bytesRetrievedData[bytesRetrievedData.Length - 1]); } //Clean the database schema TestCases.ExecuteSQL("drop table if exists TestCUBRIDBlob", conn); } }
private static void Test_Sequence_Lob() { ExecuteSQL("DROP TABLE IF EXISTS t", conn); //Create a new table with a collection ExecuteSQL("CREATE TABLE t(s SET(object))", conn); //Insert some data in the sequence column string[] sArray = new string[3] { "1", "0", null }; string sql = "INSERT INTO t(s) VALUES( ?);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = sArray; param.InnerCUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_BLOB; cmd.Parameters.Add(param); cmd.Parameters[0].CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_SET; try { cmd.ExecuteNonQuery(); } catch (Exception e) { Debug.Assert(e.Message == "Not implemented"); } } using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = sArray; param.InnerCUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_CLOB; cmd.Parameters.Add(param); cmd.Parameters[0].CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_SET; try { cmd.ExecuteNonQuery(); } catch (Exception e) { Debug.Assert(e.Message == "Not implemented"); } } }
public static void Test_CUBRIDClob_Select() { Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof(TestCUBRIDClobType).Assembly); using (CUBRIDConnection conn = new CUBRIDConnection(cfg.GetProperty(NHibernate.Cfg.Environment.ConnectionString))) { conn.Open(); TestCases.ExecuteSQL("drop table if exists TestCUBRIDClob", conn); TestCases.ExecuteSQL("create table TestCUBRIDClob(c_integer int not null auto_increment," + "c_clob CLOB," + "primary key (c_integer))", conn); const string sql = "insert into TestCUBRIDClob values(1, ?)"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); CUBRIDClob Clob = new CUBRIDClob(conn); StreamReader originalFileReader = new StreamReader("../../BSD License.txt"); string clobStringToInsert = originalFileReader.ReadToEnd(); originalFileReader.Close(); Clob.SetString(1, clobStringToInsert); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = Clob; cmd.Parameters.Add(param); cmd.Parameters[0].DbType = DbType.AnsiString; cmd.ExecuteNonQuery(); cmd.Close(); ISessionFactory sessionFactory = cfg.BuildSessionFactory(); using (var session = sessionFactory.OpenSession()) { //Retrieve the inserted information IQuery query = session.CreateQuery("FROM TestCUBRIDClobType"); IList <TestCUBRIDClobType> testQuery = query.List <TestCUBRIDClobType>(); Debug.Assert(testQuery[0].c_integer == 1); CUBRIDClob bImage = testQuery[0].c_clob; string clobInserted = bImage.GetString(1, (int)testQuery[0].c_clob.ClobLength); Debug.Assert(clobStringToInsert.Length == clobInserted.Length); Debug.Assert(clobStringToInsert == clobInserted); } //Clean the database schema TestCases.ExecuteSQL("drop table if exists TestCUBRIDClob", conn); } }
private static void Test_InitParameter() { Object v = new Object(); DbType type = DbType.Byte; ParameterDirection dir = ParameterDirection.Input; DataRowVersion ver = DataRowVersion.Default; CUBRIDDataType cdt = CUBRIDDataType.CCI_U_TYPE_BIGINT; string sourceColumn = "driver"; int size = 10; bool isNullable = false; byte precision = 1; byte scale = 0; CUBRIDParameter p1 = new CUBRIDParameter(v); Debug.Assert(p1.Value == v); CUBRIDParameter p2 = new CUBRIDParameter(cdt); Debug.Assert(p2.CUBRIDDataType == cdt); CUBRIDParameter p3 = new CUBRIDParameter("cubrid", type, dir, "col", ver, v); Debug.Assert(p3.ParameterName == "cubrid"); CUBRIDParameter p4 = new CUBRIDParameter("cubrid", cdt, size); Debug.Assert(p4.Size == size); CUBRIDParameter p5 = new CUBRIDParameter("cubrid", cdt, size, sourceColumn); Debug.Assert(p5.SourceColumn == sourceColumn); CUBRIDParameter p6 = new CUBRIDParameter("cubrid", cdt, size, dir, isNullable, precision, scale, sourceColumn, ver, v); Debug.Assert(p6.SourceColumn == sourceColumn); CUBRIDParameter p7 = new CUBRIDParameter("cubrid", cdt, dir, "col", ver, v); Debug.Assert(p7.CUBRIDDataType == cdt); CUBRIDParameter p8 = p7.Clone(); Debug.Assert(p8.ToString() == p7.ToString()); }
public void CUBRIDCommand_CreateParameter_Test() { CUBRIDConnection conn = new CUBRIDConnection(); conn.ConnectionString = DBHelper.connString; conn.Open(); DBHelper.ExecuteSQL("drop table if exists t", conn); DBHelper.ExecuteSQL("create table t (clsid bit(288))", conn); byte[] bytes = new byte[36] { 55, 56, 50, 69, 55, 57, 67, 69, 45, 50, 70, 68, 68, 45, 52, 68, 50, 55, 45, 65, 51, 48, 48, 45, 69, 48, 56, 56, 70, 56, 68, 68, 55, 54, 66, 69 }; string sql = "INSERT INTO t VALUES (?);"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); CUBRIDParameter param = (CUBRIDParameter)cmd.CreateParameter(); param.ParameterName = "?p"; param.Value = bytes; cmd.Parameters.Add(param); cmd.Parameters[0].CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_BIT; cmd.ExecuteNonQuery(); cmd.Close(); sql = "select * from t"; cmd = new CUBRIDCommand(sql, conn); CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader(); reader.Read(); byte[] buffer = (byte[])reader.GetValue(0); string clsid = conn.GetEncoding().GetString(buffer); Assert.AreEqual(clsid, "782E79CE-2FDD-4D27-A300-E088F8DD76BE"); reader.Close(); cmd.Close(); //Revert the test db DBHelper.ExecuteSQL("drop table if exists t", conn); conn.Close(); }
/// <summary> /// Test the CUBRIDDataType.CCI_U_TYPE_NULL data type /// </summary> private static void Test_Null_WithParameters() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); using (CUBRIDCommand cmd = new CUBRIDCommand("drop table if exists t", conn)) { cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("create table t(id int, str string)", conn)) { cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("insert into t values(?, ?)", 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_NULL); p2.Value = null; cmd.Parameters.Add(p2); cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t where id = 1", conn)) { using (DbDataReader reader = cmd.ExecuteReader()) { reader.Read(); Debug.Assert(reader.GetValue(1) == null); } } using (CUBRIDCommand cmd = new CUBRIDCommand("drop table t", conn)) { cmd.ExecuteNonQuery(); } } }
/// <summary> /// Test CLOB SELECT, using CUBRIDDataAdapter and DataSet /// </summary> private static void Test_Clob_SelectDataAdapter2() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTableLOB(conn); string sql1 = "insert into t (c) values(?)"; CUBRIDCommand cmd1 = new CUBRIDCommand(sql1, conn); CUBRIDClob Clob1 = new CUBRIDClob(conn); String str1 = conn.ConnectionString; //Use ConnectionString content for testing Clob1.SetString(1, str1); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_CLOB; param.Value = Clob1; cmd1.Parameters.Add(param); cmd1.ExecuteNonQuery(); cmd1.Close(); string sql = "SELECT c from t"; DataSet ds = new DataSet(); CUBRIDDataAdapter da = new CUBRIDDataAdapter(); da.SelectCommand = new CUBRIDCommand(sql, conn); da.Fill(ds); DataTable dt = ds.Tables[0]; for (int j = 0; j < dt.Rows.Count; j++) { CUBRIDClob cImage = (CUBRIDClob)dt.Rows[j]["c"]; string str = cImage.GetString(1, (int)cImage.ClobLength); Debug.Assert(str.Length == str1.Length, "The selected CLOB length is not valid!"); Debug.Assert(str.Equals(str1), "The CLOB was not selected correctly!"); } CleanupTestTableLOB(conn); } }
private static void Test_Sequence_String() { ExecuteSQL("DROP TABLE IF EXISTS t", conn); //Create a new table with a collection ExecuteSQL("CREATE TABLE t(s SET(string))", conn); //Insert some data in the sequence column string[] sArray = new string[8] { "P", "Types of fragmentation", "File fragmentation", "Free space fragmentation", "File scattering", "File system fragmentation", "Preventing fragmentation", null }; string sql = "INSERT INTO t(s) VALUES( ?);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = sArray; param.InnerCUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING; cmd.Parameters.Add(param); cmd.Parameters[0].CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_SET; cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t", conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { reader.Read(); object objValue = reader.GetValue(0); Array oArray = objValue as Array; if (oArray != null) { Console.WriteLine(oArray.Length); } cmd.ColumnInfos[0].ToString(); } } }
static void Main(string[] args) { string ConnectionString = "server=localhost;database=demodb;port=30000;user=dba;password=123456"; DataTable dt = new DataTable(); DataSet ds = new DataSet(); CUBRIDConnection con = new CUBRIDConnection(ConnectionString); CUBRIDCommand com = new CUBRIDCommand(); com.CommandType = CommandType.Text; //Important ADO.NET driver crash using call convention com.Connection = con; com.CommandText = "select rset();"; CUBRIDParameter pan = new CUBRIDParameter(); con.Open(); DbDataReader reader = com.ExecuteReader(); CustomAdapter da = new CustomAdapter(); da.FillFromReader(dt, reader); con.Close(); DataRow fila = dt.Rows[0]; Console.WriteLine(fila[0].ToString()); Console.ReadKey(); }
private static void Test_Sequence_Object() { ExecuteSQL("DROP TABLE IF EXISTS t", conn); //Create a new table with a collection ExecuteSQL("CREATE TABLE t(s SET(object))", conn); //Insert some data in the sequence column object[] sArray = new object[1] { null }; string sql = "INSERT INTO t(s) VALUES( ?);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = sArray; param.InnerCUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_OBJECT; cmd.Parameters.Add(param); cmd.Parameters[0].CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_SET; cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t", conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { reader.Read(); object objValue = reader.GetValue(0); Array oArray = objValue as Array; if (oArray != null) { Console.WriteLine(oArray.Length); } } } }
/// <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); } }
/// <summary> /// Test CUBRIDParameterCollection class /// </summary> private static void Test_Parameters_Collection() { string errMsg; Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-us"); using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CUBRIDParameterCollection pcoll = new CUBRIDParameterCollection(); CUBRIDParameter p1 = new CUBRIDParameter("?p1", CUBRIDDataType.CCI_U_TYPE_INT); p1.Value = 1; pcoll.Add(p1); CUBRIDParameter p2 = new CUBRIDParameter("?p2", CUBRIDDataType.CCI_U_TYPE_CHAR); p2.Value = 'A'; pcoll.Add(p2); CUBRIDParameter p3 = new CUBRIDParameter("?p3", CUBRIDDataType.CCI_U_TYPE_NULL); p3.Value = null; pcoll.Add(p3); //Try to add again p1 errMsg = ""; try { pcoll.Add(p1); throw new Exception(); } catch (Exception ex) { errMsg = ex.Message; } Debug.Assert(errMsg == "Parameter already added to the collection!"); Debug.Assert(pcoll.Count == 3); Debug.Assert(pcoll["?p1"].ParameterName == "?p1"); Debug.Assert(pcoll[1].ParameterName == "?p2"); Debug.Assert(pcoll["?p1"].DbType == DbType.Int32); Debug.Assert(pcoll[1].DbType == DbType.StringFixedLength); Debug.Assert(pcoll[2].DbType == DbType.Object); Debug.Assert((int)pcoll[0].Value == 1); Debug.Assert((char)pcoll[1].Value == 'A'); Debug.Assert(pcoll[2].Value == null); //Try get non-existing parameter errMsg = ""; try { string str = pcoll["?p11"].ParameterName; } catch (Exception ex) { errMsg = ex.Message; } Debug.Assert(errMsg == "?p11: Parameter not found in the collection!"); //Try get non-existing parameter errMsg = ""; try { string str = pcoll[99].ParameterName; } catch (Exception ex) { errMsg = ex.Message; } Debug.Assert(errMsg == "Index was outside the bounds of the array."); pcoll.RemoveAt(1); pcoll.Remove(p1); Debug.Assert(pcoll.Count == 1); pcoll.Clear(); Debug.Assert(pcoll.Count == 0); } }
/// <summary> /// Test BLOB INSERT, using a jpg image input file /// </summary> private static void Test_Blob_FromFile() { BinaryReader b; using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTableLOB(conn); string sql = "insert into t (b) values(?)"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDBlob Blob = new CUBRIDBlob(conn); byte[] bytes; b = new BinaryReader(File.Open("../../../CUBRID.ico", FileMode.Open)); int length = (int)b.BaseStream.Length; bytes = b.ReadBytes(length); 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(); } string sql2 = "SELECT b from t"; using (CUBRIDCommand cmd2 = new CUBRIDCommand(sql2, conn)) { DbDataReader reader = cmd2.ExecuteReader(); while (reader.Read()) { CUBRIDBlob bImage = (CUBRIDBlob)reader[0]; byte[] bytes2 = new byte[(int)bImage.BlobLength]; bytes2 = bImage.GetBytes(1, (int)bImage.BlobLength); FileStream stream = new FileStream("1out.jpg", FileMode.Create); BinaryWriter writer = new BinaryWriter(stream); writer.Write(bytes2); writer.Close(); BinaryReader b2 = new BinaryReader(File.Open("1out.jpg", FileMode.Open)); Debug.Assert(b2.BaseStream.Length == b.BaseStream.Length, "The inserted BLOB length is not valid!"); bool ok = true; int file1byte, file2byte; b.BaseStream.Position = 0; do { file1byte = b.BaseStream.ReadByte(); file2byte = b2.BaseStream.ReadByte(); if (file1byte != file2byte) { ok = false; } }while (file1byte != -1); Debug.Assert(ok == true, "The BLOB was not inserted correctly!"); b.Close(); b2.Close(); } } CleanupTestTableLOB(conn); } }
private static void Test_SetDataTypesFromValue() { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; Boolean b = true; param.Value = b; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_SHORT); SByte sb = new SByte(); param.Value = sb; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_SHORT); Byte by = new Byte(); param.Value = by; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_SHORT); Int16 i16 = 0; param.Value = i16; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_SHORT); UInt16 ui16 = 0; param.Value = ui16; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_SHORT); UInt32 ui32 = 0; param.Value = ui32; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_INT); Int64 i64 = 0; param.Value = i64; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_BIGINT); UInt64 ui64 = 0; param.Value = ui64; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_BIGINT); param.Value = DateTime.Now; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_DATETIME); string str = "cubrid"; param.Value = str; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_STRING); Single sin = 0; param.Value = sin; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_FLOAT); Double dou = 0; param.Value = dou; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_DOUBLE); Decimal dec = 0; param.Value = dec; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_NUMERIC); DBNull n = null; param.Value = n; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_NULL); }
private static void Test_SetCUBRIDDataTypeFromDbType() { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; DbType db = DbType.Int16; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_SHORT); db = DbType.Int32; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_INT); db = DbType.Int64; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_BIGINT); db = DbType.Single; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_FLOAT); db = DbType.Double; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_DOUBLE); db = DbType.Decimal; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_NUMERIC); db = DbType.Date; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_DATE); db = DbType.Time; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_TIME); db = DbType.DateTime; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_TIMESTAMP); db = DbType.Boolean; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_BIT); db = DbType.Currency; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_MONETARY); db = DbType.StringFixedLength; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_CHAR); db = DbType.AnsiString; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_CLOB); db = DbType.Byte; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_VARBIT); db = DbType.Object; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_OBJECT); db = DbType.VarNumeric; param.DbType = db; Debug.Assert(param.CUBRIDDataType == CUBRIDDataType.CCI_U_TYPE_SET); }
/// <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 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); } }
public void CUBRIDCommand_Clone_Test() { using (CUBRIDConnection conn = new CUBRIDConnection(DBHelper.connString)) { conn.Open(); DBHelper.ExecuteSQL("drop table if exists t", conn); DBHelper.ExecuteSQL("create table t (id int primary key, name varchar(50))", conn); DBHelper.ExecuteSQL("insert into t (id, name) values (2, 'Rachel Green')", conn); DBHelper.ExecuteSQL("insert into t (id, name) values (3, 'Rachel Green')", conn); DBHelper.ExecuteSQL("insert into t (id, name) values (5, 'Bill Gates')", conn); LogTestStep("Clone a CUBRIDCommand which has parameters"); CUBRIDCommand cmd = new CUBRIDCommand(null, conn); cmd.CommandText = "select * from t where id = ?myId and name = ?myName"; CUBRIDParameter idParam = new CUBRIDParameter("?myId", CUBRIDDataType.CCI_U_TYPE_INT, 8); CUBRIDParameter nameParam = new CUBRIDParameter("?myName", CUBRIDDataType.CCI_U_TYPE_STRING, 20); idParam.Value = 2; nameParam.Value = "Rachel Green"; cmd.Parameters.Add(idParam); cmd.Parameters.Add(nameParam); CUBRIDCommand cmdClone = cmd.Clone(); CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(); adapter.SelectCommand = cmdClone; Log("Verify the cloned command"); DataTable dt = new DataTable(""); adapter.Fill(dt); Assert.AreEqual(1, dt.Rows.Count); Assert.AreEqual(2, (int)dt.Rows[0][0]); Assert.AreEqual("Rachel Green", dt.Rows[0][1].ToString()); adapter.FillSchema(dt, SchemaType.Source);//To retrieve all the column properties you have to use the FillSchema() method Assert.AreEqual(cmdClone.ColumnInfos[0].Name, "id"); Assert.AreEqual(cmdClone.ColumnInfos[0].IsPrimaryKey, true); Assert.AreEqual(cmdClone.ColumnInfos[0].IsForeignKey, false); Assert.AreEqual(cmdClone.ColumnInfos[0].IsNullable, false); Assert.AreEqual(cmdClone.ColumnInfos[0].RealName, "t"); Assert.AreEqual(cmdClone.ColumnInfos[0].Precision, 10); Assert.AreEqual(cmdClone.ColumnInfos[0].Scale, 0); Assert.AreEqual(cmdClone.ColumnInfos[0].IsAutoIncrement, false); Assert.AreEqual(cmdClone.ColumnInfos[0].IsReverseIndex, false); Assert.AreEqual(cmdClone.ColumnInfos[0].IsReverseUnique, false); Assert.AreEqual(cmdClone.ColumnInfos[0].IsShared, false); Assert.AreEqual(cmdClone.ColumnInfos[0].Type, CUBRIDDataType.CCI_U_TYPE_INT); Assert.AreEqual(cmdClone.ColumnInfos[0].Table, "t"); LogStepPass(); adapter.Dispose(); cmd.Close(); Log("delete test db"); //DBHelper.ExecuteSQL("drop table if exists t", conn); } LogTestResult(); }
private static void TestGetBytes() { string sql = "drop table if exists TestTable;"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } sql = "CREATE TABLE TestTable (clsid BLOB);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDDataAdapter ad1 = new CUBRIDDataAdapter(cmd); CUBRIDDataAdapter ad2 = new CUBRIDDataAdapter("Select * from TestTable", connString); cmd.ExecuteNonQuery(); } byte[] bytes = new byte[36] { 55, 56, 50, 69, 55, 57, 67, 69, 45, 50, 70, 68, 68, 45, 52, 68, 50, 55, 45, 65, 51, 48, 48, 45, 69, 48, 56, 56, 70, 56, 68, 68, 55, 54, 66, 69 }; sql = "INSERT INTO TestTable VALUES(?);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDBlob Blob = new CUBRIDBlob(conn); Blob.SetBytes(1, bytes); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = Blob; cmd.Parameters.Add(param); cmd.Parameters[0].DbType = DbType.Binary; cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("Select * from TestTable", conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { reader.Read(); byte[] buffer = new byte[36]; long len = reader.GetBytes(0, 0, buffer, 0, 36); ASCIIEncoding encoding = new ASCIIEncoding(); string clsid = encoding.GetString(buffer); Debug.Assert(clsid == "782E79CE-2FDD-4D27-A300-E088F8DD76BE"); } } sql = "drop table if exists TestTable;"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } sql = "CREATE TABLE TestTable (clsid CLOB);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } sql = "INSERT INTO TestTable VALUES('1234567890');"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("Select * from TestTable", conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { reader.Read(); byte[] buffer = new byte[36]; long len = reader.GetBytes(0, 0, buffer, 0, 8); try { len = reader.GetBytes(0, 0, buffer, 0, 36); } catch (Exception e) { } } } }
/// <summary> /// Test basic SQL statements execution, using parameters /// </summary> private static void Test_Demo_Basic_WithParameters() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); using (CUBRIDCommand cmd = new CUBRIDCommand("drop table if exists t", conn)) { cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("create table t(id int, str string)", conn)) { cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("insert into t values(?, ?)", 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_STRING); p2.Value = "abc"; cmd.Parameters.Add(p2); cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t where id = ?", conn)) { CUBRIDParameter p1 = new CUBRIDParameter("?p1", CUBRIDDataType.CCI_U_TYPE_INT); p1.Value = 1; cmd.Parameters.Add(p1); using (DbDataReader reader = cmd.ExecuteReader()) { reader.Read(); Debug.Assert(reader.GetInt16(0) == 1); Debug.Assert(reader.GetString(1) == "abc"); } } using (CUBRIDCommand cmd = new CUBRIDCommand("update t set str = ? where id = ?", conn)) { CUBRIDParameter p2 = new CUBRIDParameter("?p2", CUBRIDDataType.CCI_U_TYPE_STRING); p2.Value = "xyz"; cmd.Parameters.Add(p2); CUBRIDParameter p1 = new CUBRIDParameter("?p1", CUBRIDDataType.CCI_U_TYPE_INT); p1.Value = 1; cmd.Parameters.Add(p1); cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t where str = ?", conn)) { CUBRIDParameter p2 = new CUBRIDParameter("?p2", CUBRIDDataType.CCI_U_TYPE_STRING); p2.Value = "xyz"; cmd.Parameters.Add(p2); using (DbDataReader reader = cmd.ExecuteReader()) { reader.Read(); Debug.Assert(reader.GetInt16(0) == 1); Debug.Assert(reader.GetString(1) == "xyz"); } } using (CUBRIDCommand cmd = new CUBRIDCommand("delete from t where id = ? and str = ?", 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_STRING); p2.Value = "xyz"; cmd.Parameters.Add(p2); cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t where str = ? and id = ?", conn)) { CUBRIDParameter p2 = new CUBRIDParameter("?p2", CUBRIDDataType.CCI_U_TYPE_STRING); p2.Value = "xyz"; cmd.Parameters.Add(p2); CUBRIDParameter p1 = new CUBRIDParameter("?p1", CUBRIDDataType.CCI_U_TYPE_INT); p1.Value = 1; cmd.Parameters.Add(p1); using (DbDataReader reader = cmd.ExecuteReader()) { Debug.Assert(reader.HasRows == false); } } using (CUBRIDCommand cmd = new CUBRIDCommand("drop table t", conn)) { cmd.ExecuteNonQuery(); } } }