private static List <object> GetTableValues(string tableName, int indexPosition, string[] columnNames)
{
List <object> columnValues = new List <object>();
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
using (CUBRIDCommand cmd = new CUBRIDCommand("select * from " + tableName, conn))
{
DbDataReader reader = cmd.ExecuteReader();
for (int i = 0; i < indexPosition; i++)
{
reader.Read();
}
for (int i = 0; i < columnNames.Length; i++)
{
columnValues.Add(reader[columnNames[i]]);
}
}
}
return(columnValues);
}
public void CUBRIDCommand_ExecuteReader_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
string sql = "select * from nation order by code asc";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
//verify the first two results
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AFG", reader.GetString(0));
Assert.AreEqual("Afghanistan", reader.GetString(1));
Assert.AreEqual("Asia", reader.GetString(2));
Assert.AreEqual("Kabul", reader.GetString(3));
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AHO", reader.GetString(0));
Assert.AreEqual("Netherlands Antilles", reader.GetString(1));
Assert.AreEqual("Americas", reader.GetString(2));
Assert.AreEqual("Willemstad", reader.GetString(3));
}
}
}
}
public void CUBRIDSchemaProviderConstructorTest()
{
CUBRIDConnection connection = new CUBRIDConnection();; // TODO: Initialize to an appropriate value
connection.ConnectionString = DBHelper.connString;
connection.Open();
CUBRIDSchemaProvider target = new CUBRIDSchemaProvider(connection);
CUBRIDCommand cmd = new CUBRIDCommand();
cmd.Connection = connection;
cmd.CommandText = "select * from nation order by code asc";
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
cmd.Close();
reader.Close();
connection.Close();
}
public void i18n_issue()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"utf-8");
cmd.CommandText = "drop table if exists 测试表;";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table 测试表 (名称 varchar);";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into 测试表 value('小明');";
cmd.ExecuteNonQuery();
cmd.CommandText = "select 名称 from 测试表;";
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
;
conn.Close();
}
public void CUBRIDCommand_Constructor_SQLAndConnAndTran_Test()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = DBHelper.connString;
conn.Open();
conn.SetAutoCommit(false);
string sql = "drop table if exists t";
CUBRIDTransaction transaction = new CUBRIDTransaction(conn, CUBRIDIsolationLevel.TRAN_DEFAULT_ISOLATION);
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn, transaction);
conn.BeginTransaction();
cmd.ExecuteNonQuery();
cmd.CommandText = "create table t (id int, name varchar(50))";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into t values(1, 'Nancy')";
cmd.ExecuteNonQuery();
conn.Commit();
cmd.CommandText = "select * from t";
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(2, reader.FieldCount);
Assert.AreEqual("1", reader.GetString(0));
Assert.AreEqual("Nancy", reader.GetString(1));
cmd.Close();
reader.Close();
conn.Close();
}
public void DataReader_Basic_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
String sql = "select * from nation order by `code` asc";
LogTestStep("retrieve just one row");
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read(); //retrieve just one row
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AFG", reader.GetString(0));
Assert.AreEqual("Afghanistan", reader.GetString(1));
Assert.AreEqual("Asia", reader.GetString(2));
Assert.AreEqual("Kabul", reader.GetString(3));
LogStepPass();
}
}
}
LogTestResult();
}
public void DataReader_MultiQuery_Test2()
{
string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"select s_name from code where s_name='X'; select name from nation where name='Algeria';";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
;
while (reader.NextResult())
{
Console.WriteLine("=============================");
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
;
}
conn.Close();
}
private static void Test_apis_669()
{
String sql = "select s_name from code where f_name = 'Woman';select * from code;";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
;
while (reader.NextResult())
{
Console.WriteLine("=============================");
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
// Console.WriteLine(reader.GetString(1));
}
;
}
}
public void DataReader_GetDate_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
DBHelper.ExecuteSQL("drop table if exists t", conn);
DBHelper.ExecuteSQL("create table t (timeTest time,datetimeTest datetime)", conn);
DBHelper.ExecuteSQL("insert into t values('12:07:39', '2013-03-18 12:07:12')", conn);
using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t", conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual(2, reader.FieldCount);
Assert.AreEqual("12:07:39", reader.GetTime(0));
Assert.AreEqual("2013-03-18", reader.GetDate(1));
DateTime dt = reader.GetDateTime(1);
Assert.AreEqual("2013-03-18 12:07:12", dt.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
//revert test db
DBHelper.ExecuteSQL("drop table if exists t", conn);
}
}
/// <summary>
/// Test DateTime types
/// </summary>
private static void Test_DateTime_Types()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
CleanupTestTable(conn);
TestCases.ExecuteSQL("create table t(dt datetime)", conn);
TestCases.ExecuteSQL("insert into t values('10/31/2008 10:20:30.040')", conn);
using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t", conn))
{
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Debug.Assert(reader.GetDateTime(0) == new DateTime(2008, 10, 31, 10, 20, 30, 040));
Debug.Assert(reader.GetDate(0) == "2008-10-31");
Debug.Assert(reader.GetDate(0, "yy/MM/dd") == "08/10/31");
Debug.Assert(reader.GetTime(0) == "10:20:30");
Debug.Assert(reader.GetTime(0, "HH") == "10");
Debug.Assert(reader.GetTimestamp(0) == "2008-10-31 10:20:30.040");
Debug.Assert(reader.GetTimestamp(0, "yyyy HH") == "2008 10");
}
CleanupTestTable(conn);
}
}
/// <summary>
/// Test 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();
}
}
public List <string> GetSequences(List <Table> tables)
{
var sequences = new List <string>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
CUBRIDCommand seqCommand = conn.CreateCommand();
seqCommand.CommandText = "select [name], class_name from db_serial";
var seqReader = (CUBRIDDataReader)seqCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (seqReader.Read())
{
sequences.AddRange(from table in tables where table.Name.ToUpper() == seqReader.GetString(1).ToUpper() select seqReader.GetString(0));
}
}
}
finally
{
conn.Close();
}
return(sequences);
}
public List <string> GetSequences(string tablename, string column)
{
var sequences = new List <string>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
CUBRIDCommand seqCommand = conn.CreateCommand();
string sqlCmd = String.Format(@"select [name] from db_serial where class_name='{0}' and att_name='{1}'",
tablename,
column);
seqCommand.CommandText = sqlCmd;
var seqReader = (CUBRIDDataReader)seqCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (seqReader.Read())
{
sequences.Add(seqReader.GetString(0));
}
}
}
finally
{
conn.Close();
}
return(sequences);
}
/// <summary>
/// Test CUBRIDConnection BatchExecuteNoQuery() method
/// </summary>
private static void Test_BatchExecuteNoQuery()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string[] sqls = new string[3];
sqls[0] = "create table t(id int)";
sqls[1] = "insert into t values(1)";
sqls[2] = "insert into t values(2)";
conn.BatchExecuteNoQuery(sqls);
string sql = "select count(*) from t";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetInt32(0) == 2);
}
}
TestCases.ExecuteSQL("drop table t", conn);
}
}
public void CUBRIDCommand_Close_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
string sql = "select * from nation order by code asc";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
conn.Open();
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AFG", reader.GetString(0));
Assert.AreEqual("Afghanistan", reader.GetString(1));
Assert.AreEqual("Asia", reader.GetString(2));
Assert.AreEqual("Kabul", reader.GetString(3));
cmd.Close();
try
{
cmd.CommandText = "drop table if exists t";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Assert.AreEqual("Some message about the cmd cannot be used", ex.Message);
}
}
}
public void CUBRIDCommand_Constructor_SQL_Test()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = DBHelper.connString;
string sql = "select * from nation order by code asc";
CUBRIDCommand cmd = new CUBRIDCommand(sql);
cmd.Connection = conn;
conn.Open();
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AFG", reader.GetString(0));
Assert.AreEqual("Afghanistan", reader.GetString(1));
Assert.AreEqual("Asia", reader.GetString(2));
Assert.AreEqual("Kabul", reader.GetString(3));
cmd.Close();
reader.Close();
conn.Close();
}
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 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);
}
}
///<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);
}
}
private static void test()
{
String ip = "192.168.0.1";
String ConnectionString = "server=" + ip + ";database=demodb;port=33000;user=public;password="******"SELECT * FROM record;";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Object[] values = new Object[reader.FieldCount];
int fieldCount = reader.GetValues(values);
foreach (Object obj in values)
{
Console.Out.Write(obj.ToString() + "," + obj.GetType() + "|");
}
Console.Out.WriteLine();
/*
* EXPLAIN athlete; //same as Describe, basic information of table
* SHOW TABLES; //get all table names
* SHOW FULL COLUMNS FROM athlete; //same as Describe but with comments
* SHOW INDEXES FROM athlete; //get table information like fk
* SHOW CREATE TABLE athlete; //get query string of creating the table
*/
}
//(read the values using: reader.Get...() methods)
}
}
Console.Out.WriteLine("CUBRID SQL Connection");
conn.Close();
}
/*using (CUBRIDConnection conn = new CUBRIDConnection(sb.GetConnectionString()))
* {
* conn.Open();
* String sql = "SELECT * FROM athlete;";
* using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
* {
* using (DbDataReader reader = cmd.ExecuteReader())
* {
* reader.Read();
* Console.Out.WriteLine(reader.GetString(3));
* //(read the values using: reader.Get...() methods)
* }
* }
* }*/
}
/// <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);
}
}
public void CUBRIDCommand_ExecuteReader_CloseConnection_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
string sql = "select * from nation order by code asc";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
LogTestStep("Test CommandBehavior.CloseConnection");
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader(CommandBehavior.CloseConnection);
//verify the first two results
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AFG", reader.GetString(0));
Assert.AreEqual("Afghanistan", reader.GetString(1));
Assert.AreEqual("Asia", reader.GetString(2));
Assert.AreEqual("Kabul", reader.GetString(3));
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AHO", reader.GetString(0));
Assert.AreEqual("Netherlands Antilles", reader.GetString(1));
Assert.AreEqual("Americas", reader.GetString(2));
Assert.AreEqual("Willemstad", reader.GetString(3));
reader.Close();
if (reader.IsClosed)
{
try
{
cmd = new CUBRIDCommand("create table t(id int)", conn);
cmd.ExecuteNonQuery();
LogStepFail();
}
catch (Exception ex)
{
Assert.AreEqual("The connection is not open!", ex.Message);
LogStepPass();
}
}
else
{
LogStepFail();
}
//TODO: Test CommandBehavior.Default, CommandBehavior.SchemaOnly, CommandBehavior.KeyInfo, CommandBehavior.SingleRow, CommandBehavior.SequentialAccess
LogTestResult();
}
}
/// <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_CUBRIDClob_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 TestCUBRIDClob", conn);
TestCases.ExecuteSQL("create table TestCUBRIDClob(c_integer int not null auto_increment," +
"c_clob CLOB," +
"primary key (c_integer))", conn);
TestCUBRIDClobType test = new TestCUBRIDClobType
{
c_clob = new CUBRIDClob(conn)
};
StreamReader originalFileReader = new StreamReader("../../BSD License.txt");
string clobStringToInsert = originalFileReader.ReadToEnd();
originalFileReader.Close();
test.c_clob.SetString(1, clobStringToInsert);
//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_clob from TestCUBRIDClob";
CUBRIDCommand cmd2 = new CUBRIDCommand(sql2, conn);
DbDataReader reader = cmd2.ExecuteReader();
while (reader.Read())
{
CUBRIDClob cString = (CUBRIDClob)reader[0];
string clobStringInserted = cString.GetString(1, (int)cString.ClobLength);
Debug.Assert(clobStringToInsert.Length == clobStringInserted.Length);
Debug.Assert(clobStringToInsert == clobStringInserted);
}
//Clean the database schema
TestCases.ExecuteSQL("drop table if exists TestCUBRIDClob", conn);
}
}
/// <summary>
/// Test multiple connections
/// </summary>
private static void Test_MultipleConnections()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
TestCases.ExecuteSQL("drop table if exists t", conn);
TestCases.ExecuteSQL("create table t(idx integer)", conn);
string sql = "select * from nation";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
int count = 0;
while (reader.Read() && count++ < 3)
{
using (CUBRIDConnection conn2 = new CUBRIDConnection())
{
conn2.ConnectionString = conn.ConnectionString;
conn2.Open();
string sqlInsert = "insert into t values(" + count + ")";
using (CUBRIDCommand cmdInsert = new CUBRIDCommand(sqlInsert, conn2))
{
cmdInsert.ExecuteNonQuery();
}
}
}
}
}
using (CUBRIDConnection conn2 = new CUBRIDConnection())
{
conn2.ConnectionString = conn.ConnectionString;
conn2.Open();
string sqlSelect = "select count(*) from t";
using (CUBRIDCommand cmd = new CUBRIDCommand(sqlSelect, conn2))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetInt32(0) == 3);
}
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
}
public void DataReader_GetNumericTypes_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
DBHelper.ExecuteSQL("drop table if exists t", conn);
DBHelper.ExecuteSQL("create table t(c0 short, c1 smallint, c2 integer, c3 int, c4 bigint, c5 numeric, c6 decimal, c7 float, c8 real, c9 double, c10 double precision, c11 monetary)", conn);
DBHelper.ExecuteSQL("insert into t values(-10, 11, -32768, 32769, -2147483650, 2.5, -12.6, 33.5, -123.4567, 23.45, 45.678, 987.65)", conn);
DBHelper.ExecuteSQL("insert into t values(-11, 12, -32769, 32770, -2147483651, 2.6, -12.7, 34.5, -223.4567, 33.45, 46.678, 989.65)", conn);
String sql = "select * from t";
LogTestStep("use exactly the same data type");
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read(); //retrieve just one row
Assert.AreEqual(12, reader.FieldCount);
Assert.AreEqual(-10, reader.GetInt16(0));
Assert.AreEqual(11, reader.GetInt16(1));
Assert.AreEqual(-32768, reader.GetInt32(2));
Assert.AreEqual(32769, reader.GetInt32(3));
Assert.AreEqual(-2147483650, reader.GetInt64(4));
//Assert.AreEqual(2.5, reader.GetDecimal(5)); //Actual 3
//Assert.AreEqual(-12.6, reader.GetDecimal(6)); //Actual -13
Assert.AreEqual(33.5, reader.GetFloat(7));
if (Math.Abs(-123.4567 - reader.GetFloat(8)) > 0.00001)
{
Assert.Fail();
}
Assert.AreEqual(23.45, reader.GetDouble(9));
Assert.AreEqual(45.678, reader.GetDouble(10));
//TODO: ask whether there is GetMONETARY method
Assert.AreEqual(987.65, reader.GetDouble(11));
LogStepPass();
}
}
//TODO change the datatype
//LogTestStep("use exactly the same data type");
}
LogTestResult();
}
private static void Test_apis_514()
{
string sql = "select * from nation order by code asc";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader(CommandBehavior.CloseConnection);
Console.WriteLine(reader.IsClosed);
Console.WriteLine(conn.State);
reader.Close();
Console.WriteLine(reader.IsClosed);
Console.WriteLine(conn.State);
}
/// <summary>
/// Test Encodings support
/// </summary>
private static void Test_Encodings()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = "server=" + ip + ";database=demodb;port=33000;user=public;password=;charset=utf-8";
conn.Open();
TestCases.ExecuteSQL("drop table if exists t", conn);
TestCases.ExecuteSQL("create table t(a int, b varchar(100))", conn);
String sql = "insert into t values(1 ,'¾Æ¹«°³')";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
sql = "select * from t where b = '¾Æ¹«°³'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read(); //retrieve just one row
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetString(1) == "¾Æ¹«°³");
}
}
sql = "update t set b='¾Æ¹°³'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
sql = "select * from t where b = '¾Æ¹°³'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read(); //retrieve just one row
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetString(1) == "¾Æ¹°³");
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
}
/// <summary>
/// Test CUBRIDException class
/// </summary>
private static void Test_CUBRIDException()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string sql = "select count(*) from xyz"; //Table xyz does not exist
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
try
{
using (DbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
}
}
catch (Exception ex)
{
string r = "Syntax: Unknown class \"xyz\". select count(*) from xyz";
Debug.Assert(ex.Message.Substring(0, r.Length) == r);//todo
}
}
}
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string sql = "select count(*) from xyz"; //Table xyz does not exist
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
try
{
using (DbDataReader reader = cmd.ExecuteReader())
{
conn.Close();
reader.Read();
}
}
catch (Exception ex)
{
string r = "Syntax: Unknown class \"xyz\". select count(*) from xyz";
Debug.Assert(ex.Message.Substring(0, r.Length) == r);//todo
}
}
}
}