/// <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);
}
}
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 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 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 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();
}
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 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();
}
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 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 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);
}
}
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));
}
;
}
}
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");
}
}
}
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();
}
}
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);
}
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();
}
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 CUBRIDCommand GetGeneratedKeys() method
/// </summary>
/*
* private static void Test_GetGeneratedKeys()
* {
* string sqlTablesCount = "select count(*) from db_class";
* int tablesCount, newTableCount;
*
* using (CUBRIDConnection conn = new CUBRIDConnection())
* {
* conn.ConnectionString = TestCases.connString;
* conn.Open();
*
* TestCases.ExecuteSQL("drop table if exists tkeys", conn);
*
* tablesCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
* TestCases.ExecuteSQL("create table tkeys(id int auto_increment, str string)", conn);
* newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
* //Verify table was created
* Debug.Assert(newTableCount == tablesCount + 1);
*
* conn.BeginTransaction();
* CUBRIDCommand cmd = new CUBRIDCommand("insert into tkeys(str) values('xyz')", conn);
* cmd.ExecuteNonQuery();
* DbDataReader keys = cmd.GetGeneratedKeys();
*
* while (keys.Read())
* {
* //only on erow will be returned
* Debug.Assert(keys.GetInt32(0) == 1);
* }
* conn.Commit();
* cmd.Close();
*
* TestCases.ExecuteSQL("drop table if exists tkeys", conn);
* newTableCount = (int)TestCases.GetSingleValue(sqlTablesCount, conn);
* Debug.Assert(newTableCount == tablesCount);
* }
* }
*/
/// <summary>
/// Test ExecuteNonQuery() and ExecuteReader() methods
/// </summary>
private static void Test_ExecuteNonQuery_Query()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string sql = "select count(*) from nation";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
try
{
cmd.ExecuteNonQuery();
}
catch (CUBRIDException ex)
{
Debug.Assert(ex.Message == "Invalid query type!");
}
}
}
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string sql = "insert into nation values('x', 'x', 'x', 'x')";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
try
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read();
}
}
catch (CUBRIDException ex)
{
string r = "Operation would have caused one or more unique constraint violations.";
Debug.Assert(ex.Message.Substring(0, r.Length) == r);
}
ExecuteSQL("delete from nation where code ='x'", 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();
}
}
}
/// <summary>
/// Test CUBRIDDataReader GetOid() method
/// </summary>
private static void Test_OID_Get()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string sql = "select * from nation limit 1";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read();
CUBRIDOid oid = reader.GetOid();
Debug.Assert(oid.ToString() == "OID:@0|0|0");
}
}
}
}
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 CUBRIDDataReader getter methods
/// </summary>
private static void Test_DataReader_Getxxx()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
string sql = "select * from nation;";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetOrdinal("code") == 0);
Debug.Assert(reader.GetName(0) == "code");
Debug.Assert(reader.GetColumnName(0) == "code");
Debug.Assert(reader.GetColumnType(0) == typeof(System.String));
Debug.Assert(reader.GetDataTypeName(0) == "CHAR");
}
}
}
}
public void DataReader_NextResult_Test()
{
string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"select * from nation;";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Console.WriteLine(reader.GetString(0));
if (reader.NextResult())
{
reader.Read();
Console.WriteLine(reader.GetString(0));
}
conn.Close();
}
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)
{
}
}
}
}
private static void TestGetData()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
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(32), ";
sql += "c_varbit bit varying(4096), ";
sql += "c_monetary monetary, ";
sql += "c_string string";
sql += ")";
ExecuteSQL(sql, conn);
sql = "insert into t values(";
sql += "1, ";
sql += "11, ";
sql += "111, ";
sql += "1111, ";
sql += "1.1, ";
sql += "1.11, ";
sql += "1.111, ";
sql += "1.1111, ";
sql += "'a', ";
sql += "'abcdfghijk', ";
sql += "TIME '13:15:45 pm', ";
sql += "DATE '00-10-31', ";
sql += "TIMESTAMP '13:15:45 10/31/2008', ";
sql += "DATETIME '13:15:45 10/31/2008', ";
sql += "B'0110011110101001', ";
sql += "B'010110110111110000', ";
sql += "123456789, ";
sql += "'qwerty'";
sql += ")";
ExecuteSQL(sql, conn);
sql = "select * from t";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");
Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-US");
while (reader.Read()) //only one row will be available
{
int Ordinal = reader.GetOrdinal("c_integer_ai");
Debug.Assert(Ordinal == 0);
try
{
Guid guid = reader.GetGuid(0);
}
catch (Exception e)
{
string error = e.ToString();
Debug.Assert(e.Message == "Value does not fall within the expected range.");
}
try
{
Guid guid = reader.GetGuid(9);
}
catch (Exception e)
{
string error = e.ToString();
Debug.Assert(e.Message == "Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).");
}
int i = reader.GetInt32(0);
Type t = reader.GetColumnType(0);
Debug.Assert(i == 1);
short s = reader.GetInt16(1);
t = reader.GetColumnType(1);
Debug.Assert(s == 11);
i = reader.GetInt32(2);
t = reader.GetColumnType(2);
Debug.Assert(i == 111);
long i64 = reader.GetInt64(3);
t = reader.GetColumnType(3);
Debug.Assert(i64 == 1111);
decimal d = reader.GetDecimal(4);
t = reader.GetColumnType(4);
Debug.Assert(d == (decimal)1.1);
float f = reader.GetFloat(5);
t = reader.GetColumnType(5);
Debug.Assert(f == (float)1.11);
d = reader.GetDecimal(6);
t = reader.GetColumnType(6);
Debug.Assert(d == (decimal)1.111);
double db = reader.GetDouble(7);
t = reader.GetColumnType(7);
Debug.Assert(db == (double)1.1111);
char c = reader.GetChar(8);
t = reader.GetColumnType(8);
Debug.Assert(c == 'a');
string str = reader.GetString(9);
t = reader.GetColumnType(9);
Debug.Assert(str == "abcdfghijk");
DateTime dt = reader.GetDateTime(10);
t = reader.GetColumnType(10);
Debug.Assert(dt.Second == 45);
dt = reader.GetDateTime(11);
t = reader.GetColumnType(11);
string dt_s = reader.GetDate(11);
Debug.Assert(dt == new DateTime(2000, 10, 31));
dt = reader.GetDateTime(12);
t = reader.GetColumnType(12);
dt_s = reader.GetDate(12, "yyyy-MM-dd");
dt_s = reader.GetTimestamp(12);
dt_s = reader.GetTimestamp(12, "yyyy-MM-dd HH:mm:ss.fff");
Debug.Assert(dt == new DateTime(2008, 10, 31, 13, 15, 45));
dt = reader.GetDateTime(13);
t = reader.GetColumnType(13);
dt_s = reader.GetTime(13);
dt_s = reader.GetTime(13, "HH:mm:ss");
Debug.Assert(dt == new DateTime(2008, 10, 31, 13, 15, 45));
byte[] by = new byte[4];
byte[] expected = { 0x67, 0xa9, 0x00, 0x00 };
reader.GetBytes(14, 0, by, 0, 4);
Debug.Assert(by.SequenceEqual(expected));
t = reader.GetColumnType(14);
expected[0] = 0x5b; expected[1] = 0x7c; expected[2] = 0x00;
reader.GetBytes(15, 0, by, 0, 3);
Debug.Assert(by.SequenceEqual(expected));
t = reader.GetColumnType(15);
Debug.Assert(reader.GetString(16) == "123456789.0000000000000000");
t = reader.GetColumnType(16);
char[] buffer = new char[16];
reader.GetChars(16, 0, buffer, 0, 16);
Debug.Assert(reader.GetString(17) == "qwerty");
t = reader.GetColumnType(17);
}
}
ExecuteSQL("drop table t", conn);
}
}
public void DataReader_GetChars_Test()
{
//TODO test date time
using (CUBRIDConnection conn = new CUBRIDConnection())
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-US");
conn.ConnectionString = DBHelper.connString;
conn.Open();
DBHelper.ExecuteSQL("drop table if exists t", conn);
DBHelper.ExecuteSQL("create table t (c varchar(100))", conn);
DBHelper.ExecuteSQL("insert into t values ('abcdefghijklmn')", conn);
DBHelper.ExecuteSQL("insert into t values ('opq')", conn);
long result = 0;
char[] resultArray = { '1', '2', '3', '4', '5', '6', '7', '8', '9' };
string sql = "select * from t order by c";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read();
result = reader.GetChars(0, 2, resultArray, 3, 4);
char[] expected = { '1', '2', '3', 'c', 'd', 'e', 'f', '8', '9' };
Assert.AreEqual(4, result);
for (int i = 0; i < resultArray.Length; i++)
{
Assert.AreEqual(expected[i], resultArray[i]);
}
try
{
result = reader.GetChars(1, 2, resultArray, 3, 4);
}
catch (IndexOutOfRangeException ex)
{
Assert.AreEqual("Index was outside the bounds of the array.", ex.Message);
}
try
{
result = reader.GetChars(0, 20, resultArray, 3, 4);
}
catch (IndexOutOfRangeException ex)
{
Assert.AreEqual("Data offset must be a valid position in the field!", ex.Message);
}
try
{
result = reader.GetChars(0, 2, resultArray, 10, 4);
}
catch (IndexOutOfRangeException ex)
{
Assert.AreEqual("Buffer index must be a valid index in buffer!", ex.Message);
}
try
{
result = reader.GetChars(0, 2, resultArray, 3, 9);
}
catch (ArgumentException ex)
{
Assert.AreEqual("Buffer is not large enough to hold the requested data!", ex.Message);
}
resultArray = new char[] { '1', '2', '3', '4', '5', '6', '7', '8', '9' };
// read opq
reader.Read();
try
{
result = reader.GetChars(0, 1, resultArray, 2, 4);
}
catch (ArgumentOutOfRangeException ex)
{
Assert.AreEqual(@"Index and count must refer to a location within the string.
Parameter name: sourceIndex", ex.Message);
}
}
}
//revert test db
DBHelper.ExecuteSQL("drop table if exists t", conn);
}
}
public void DataType_MultiSet_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
DBHelper.ExecuteSQL("drop table if exists testtable", conn);
DBHelper.ExecuteSQL("CREATE TABLE testtable(clsid multiset_of(INTEGER));", conn);
using (CUBRIDCommand cmd = new CUBRIDCommand("Select * from TestTable", conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual(reader.HasRows, false);
Assert.AreEqual(reader.GetColumnType(0), typeof(Object[]));
Assert.AreEqual(reader.GetColumnTypeName(0), "MULTISET");
try
{
reader.GetColumnName(-1);
}
catch (Exception ex)
{
Assert.IsTrue((ex as IndexOutOfRangeException) != null);
}
try
{
reader.GetColumnTypeName(-1);
}
catch (Exception ex)
{
Assert.IsTrue((ex as IndexOutOfRangeException) != null);
}
try
{
reader.GetColumnTypeName(2);
}
catch (Exception ex)
{
Assert.IsTrue((ex as IndexOutOfRangeException) != null);
}
try
{
reader.GetColumnType(-1);
}
catch (Exception ex)
{
Assert.IsTrue((ex as IndexOutOfRangeException) != null);
}
try
{
reader.GetColumnType(2);
}
catch (Exception ex)
{
Assert.IsTrue((ex as IndexOutOfRangeException) != null);
}
}
}
DBHelper.ExecuteSQL("drop TestTable;", conn);
}
}
public void DataReader_NextResult_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
String sql = "select count(*) from nation; select count(*) from athlete";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual(215, reader.GetInt32(0));
if (reader.NextResult())
{
reader.Read();
Assert.AreEqual(6677, reader.GetInt32(0));
}
}
}
sql = "select * from nation order by code asc; select * from athlete order by name 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));
if (reader.NextResult())
{
//verify the first two results
reader.Read();
Assert.AreEqual(5, reader.FieldCount);
Assert.AreEqual(15718, reader.GetInt32(0));
Assert.AreEqual("Aardenburg Willemien", reader.GetString(1));
Assert.AreEqual("W", reader.GetString(2));
Assert.AreEqual("NED", reader.GetString(3));
Assert.AreEqual("Hockey", reader.GetString(4));
reader.Read();
Assert.AreEqual(5, reader.FieldCount);
Assert.AreEqual(10000, reader.GetInt32(0));
Assert.AreEqual("Aardewijn Pepijn", reader.GetString(1));
Assert.AreEqual("M", reader.GetString(2));
Assert.AreEqual("NED", reader.GetString(3));
Assert.AreEqual("Rowing", reader.GetString(4));
}
}
}
}
}
