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