/// <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);
}
}
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 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 static void Test_CUBRIDBlob_Insert()
{
Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof(TestCUBRIDBlobType).Assembly);
//Create the database schema
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);
TestCUBRIDBlobType test = new TestCUBRIDBlobType
{
c_blob = new CUBRIDBlob(conn)
};
BinaryReader origianlFileReader = new BinaryReader(File.Open("../../CUBRID.ico", FileMode.Open));
byte[] bytesOriginalData = origianlFileReader.ReadBytes((int)origianlFileReader.BaseStream.Length);
origianlFileReader.Close();
test.c_blob.SetBytes(1, bytesOriginalData);
//Insert
ISessionFactory sessionFactory = cfg.BuildSessionFactory();
using (var session = sessionFactory.OpenSession())
{
using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
{
session.Save(test);
trans.Commit();
}
}
const string sql2 = "SELECT c_blob from TestCUBRIDBlob";
CUBRIDCommand cmd2 = new CUBRIDCommand(sql2, conn);
DbDataReader reader = cmd2.ExecuteReader();
while (reader.Read())
{
CUBRIDBlob bImage = (CUBRIDBlob)reader[0];
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);
}
}
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 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 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 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);
}
}
public static void Test_CUBRIDBlob_Update()
{
Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof(TestCUBRIDBlobType).Assembly);
//Create the database schema
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);
TestCUBRIDBlobType test = new TestCUBRIDBlobType
{
c_blob = new CUBRIDBlob(conn)
};
BinaryReader originalFileReader = new BinaryReader(File.Open("../../CUBRIDOld.ico", FileMode.Open));
byte[] bytesOriginalData = originalFileReader.ReadBytes((int)originalFileReader.BaseStream.Length);
originalFileReader.Close();
test.c_blob.SetBytes(1, bytesOriginalData);
//Insert
ISessionFactory sessionFactory = cfg.BuildSessionFactory();
using (var session = sessionFactory.OpenSession())
{
using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
{
session.Save(test);
trans.Commit();
}
TestCUBRIDBlobType pGet = session.Get <TestCUBRIDBlobType>(test.c_integer);
pGet.c_blob = new CUBRIDBlob(conn);
BinaryReader updateFileReader = new BinaryReader(File.Open("../../CUBRID.ico", FileMode.Open));
byte[] bytesUpdatedData = updateFileReader.ReadBytes((int)updateFileReader.BaseStream.Length);
updateFileReader.Close();
test.c_blob.SetBytes(1, bytesUpdatedData);
using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
{
session.Update(pGet);
trans.Commit();
}
//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(bytesUpdatedData.Length == bytesRetrievedData.Length);
Debug.Assert(bytesUpdatedData[0] == bytesRetrievedData[0]);
Debug.Assert(bytesUpdatedData[bytesUpdatedData.Length - 1] == bytesRetrievedData[bytesRetrievedData.Length - 1]);
}
//Clean the database schema
TestCases.ExecuteSQL("drop table if exists TestCUBRIDBlob", conn);
}
}
