public void TextBlobTest()
{
int id_value = this.GetId();
string text = "Borland Data Provider Clob Field test";
string selectText = "SELECT clob_field FROM TEST WHERE int_field = " + id_value.ToString();
string insertText = "INSERT INTO TEST (int_field, clob_field) values(?, ?)";
Console.WriteLine("\r\n\r\nBinary Blob Test");
Console.WriteLine("Executing insert command");
// Execute insert command
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand insert = new BdpCommand(insertText, Connection, transaction);
insert.Parameters.Add("@int_field", BdpType.Int32).Value = id_value;
insert.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stHMemo).Value = text;
insert.ExecuteNonQuery();
transaction.Commit();
Console.WriteLine("Checking inserted values");
// Check that inserted values are correct
BdpCommand select = new BdpCommand(selectText, Connection);
string result = new String((char[])select.ExecuteScalar());
Assert.AreEqual(text, result);
Console.WriteLine("Finishing test");
}
public void AbortTransaction()
{
StringBuilder b1 = new StringBuilder();
b1.AppendFormat("ALTER TABLE \"{0}\" drop \"INT_FIELD\"", "TEST");
BdpTransaction transaction = null;
BdpCommand command = null;
try
{
transaction = this.Connection.BeginTransaction();
command = new BdpCommand(b1.ToString(), this.Connection, transaction);
command.ExecuteNonQuery();
transaction.Commit();
transaction = null;
}
catch (Exception)
{
transaction.Rollback();
transaction = null;
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
public void IndexerByIndexTest()
{
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
Console.WriteLine();
Console.WriteLine("DataReader - Read Method - Test");
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader[i] + "\t");
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void GetValuesTest()
{
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
Console.WriteLine();
Console.WriteLine("DataReader - Read Method - Test");
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
for (int i = 0; i < values.Length; i++)
{
Console.Write(values[i] + "\t");
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void DeleteTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 10;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0].Delete();
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void InvalidParameterFormat()
{
string sql = "update test set timestamp_field = ? where int_field = ?";
bool failed = false;
BdpTransaction transaction = this.Connection.BeginTransaction();
try
{
BdpCommand command = new BdpCommand(sql, this.Connection, transaction);
command.Parameters.Add("@timestamp", BdpType.DateTime).Value = 1;
command.Parameters.Add("@integer", BdpType.Int32).Value = 1;
command.ExecuteNonQuery();
command.Close();
transaction.Commit();
}
catch
{
failed = true;
transaction.Rollback();
}
Assert.IsTrue(failed, "Bad parameter not detected");
}
public void GetSchemaTableWithExpressionFieldTest()
{
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select TEST.*, 0 AS VALOR from TEST", Connection, transaction);
BdpDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schema = reader.GetSchemaTable();
Console.WriteLine();
Console.WriteLine("DataReader - GetSchemaTable Method- Test");
DataRow[] currRows = schema.Select(null, null, DataViewRowState.CurrentRows);
foreach (DataColumn myCol in schema.Columns)
{
Console.Write("{0}\t\t", myCol.ColumnName);
}
Console.WriteLine();
foreach (DataRow myRow in currRows)
{
foreach (DataColumn myCol in schema.Columns)
{
Console.Write("{0}\t\t", myRow[myCol]);
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void GetTables()
{
BdpTransaction t = this.Connection.BeginTransaction();
DataTable tables = this.Connection.GetMetaData().GetTables("TEST", TableType.Table);
Assert.AreEqual(1, tables.Rows.Count);
t.Commit();
}
public virtual void SetUp()
{
// Build the connection string
StringBuilder connectionString = new StringBuilder();
connectionString.AppendFormat(
"Assembly={0};UserName={1};Password={2};Database={3}",
ConfigurationSettings.AppSettings["Assembly"],
ConfigurationSettings.AppSettings["UserName"],
ConfigurationSettings.AppSettings["Password"],
ConfigurationSettings.AppSettings["Database"]);
StringBuilder connOptions = new StringBuilder();
connOptions.AppendFormat(
"Charset={0};Dialect={1};Server Type={2};Pooling=false",
ConfigurationSettings.AppSettings["Charset"],
ConfigurationSettings.AppSettings["Dialect"],
ConfigurationSettings.AppSettings["ServerType"]);
try
{
try
{
System.IO.File.Delete(ConfigurationSettings.AppSettings["DatabasePath"]);
}
catch
{
}
System.IO.File.Copy(
ConfigurationSettings.AppSettings["BaseDatabase"],
ConfigurationSettings.AppSettings["DatabasePath"]);
}
catch
{
}
this.connection = new BdpConnection();
this.connection.ConnectionString = connectionString.ToString();
this.connection.ConnectionOptions = connOptions.ToString();
this.connection.Open();
CreateTables(connectionString.ToString());
CreateProcedures(connectionString.ToString());
CreateTriggers(connectionString.ToString());
InsertTestData(connectionString.ToString());
if (this.withTransaction)
{
this.transaction = this.connection.BeginTransaction();
}
}
public void BinaryBlobTest()
{
int id_value = this.GetId();
string selectText = "SELECT blob_field FROM TEST WHERE int_field = " + id_value.ToString();
string insertText = "INSERT INTO TEST (int_field, blob_field) values(?, ?)";
Console.WriteLine("\r\n\r\nBinary Blob Test");
Console.WriteLine("Generating an array of temp data");
// Generate an array of temp data
byte[] insert_values = new byte[100000 * 4];
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
rng.GetBytes(insert_values);
Console.WriteLine("Executing insert command");
// Execute insert command
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand insert = new BdpCommand(insertText, Connection, transaction);
insert.Parameters.Add("@int_field", BdpType.Int32).Value = id_value;
insert.Parameters.Add("@blob_field", BdpType.Blob, BdpType.stHBinary).Value = insert_values;
insert.ExecuteNonQuery();
transaction.Commit();
Console.WriteLine("Checking inserted values");
// Check that inserted values are correct
BdpCommand select = new BdpCommand(selectText, Connection);
byte[] select_values = (byte[])select.ExecuteScalar();
for (int i = 0; i < insert_values.Length; i++)
{
if (insert_values[i] != select_values[i])
{
throw new Exception("differences at index " + i.ToString());
}
}
Console.WriteLine("Finishing test");
}
public void UpdateTimeStampTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
DateTime dtValue = DateTime.Now;
ds.Tables["TEST"].Rows[0]["TIMESTAMP_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT timestamp_field FROM TEST WHERE int_field = ?";
command = new BdpCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
DateTime val = (DateTime)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(dtValue.Day, val.Day, "timestamp_field has not correct day");
Assert.AreEqual(dtValue.Month, val.Month, "timestamp_field has not correct month");
Assert.AreEqual(dtValue.Year, val.Year, "timestamp_field has not correct year");
Assert.AreEqual(dtValue.Hour, val.Hour, "timestamp_field has not correct hour");
Assert.AreEqual(dtValue.Minute, val.Minute, "timestamp_field has not correct minute");
Assert.AreEqual(dtValue.Second, val.Second, "timestamp_field has not correct second");
}
public void NextResultTest()
{
string querys = "select * from TEST order by INT_FIELD asc;" +
"select * from TEST order by INT_FIELD desc;";
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand(querys, Connection, transaction);
BdpDataReader reader = command.ExecuteReader();
Console.WriteLine();
Console.WriteLine("DataReader - NextResult Method - Test ( First Result )");
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
if (reader.NextResult())
{
Console.WriteLine("DataReader - NextResult Method - Test ( Second Result )");
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void BeginTrasactionTest()
{
BdpConnection conn01 = new BdpConnection(Connection.ConnectionString);
conn01.Open();
BdpTransaction txn01 = conn01.BeginTransaction(IsolationLevel.Unspecified);
txn01.Commit();
conn01.Close();
BdpConnection conn02 = new BdpConnection(Connection.ConnectionString);
conn02.Open();
BdpTransaction txn02 = conn02.BeginTransaction(IsolationLevel.ReadCommitted);
txn02.Commit();
conn02.Close();
BdpConnection conn03 = new BdpConnection(Connection.ConnectionString);
conn03.Open();
BdpTransaction txn03 = conn03.BeginTransaction(IsolationLevel.ReadUncommitted);
txn03.Commit();
conn03.Close();
BdpConnection conn04 = new BdpConnection(Connection.ConnectionString);
conn04.Open();
BdpTransaction txn04 = conn04.BeginTransaction(IsolationLevel.RepeatableRead);
txn04.Commit();
conn04.Close();
BdpConnection conn05 = new BdpConnection(Connection.ConnectionString);
conn05.Open();
BdpTransaction txn05 = conn05.BeginTransaction(IsolationLevel.Serializable);
txn05.Commit();
conn05.Close();
}
public void DeriveParameters2()
{
BdpTransaction transaction = Connection.BeginTransaction();
// BdpCommandBuilder builder = new BdpCommandBuilder();
BdpCommand command = new BdpCommand("GETVARCHARFIELD", Connection, transaction);
command.CommandType = CommandType.StoredProcedure;
// RPH - DeriveParameters consistently fails with an Invalid Cast exception.
// The code below bypasses DeriveParameters and instead calls
// GetProcedureParams.
// BdpCommandBuilder.DeriveParameters(command);
DataTable parameters = Connection.GetMetaData().GetProcedureParams(command.CommandText, "");
Console.WriteLine("Derived Parameters");
/*
* for (int i = 0; i < command.Parameters.Count; i++)
* {
* Console.WriteLine("Parameter name: {0}\tParameter Source Column:{1}\tDirection:{2}",
* command.Parameters[i].ParameterName,
* command.Parameters[i].SourceColumn,
* command.Parameters[i].Direction);
* }
*/
for (int i = 0; i < parameters.Rows.Count; i++)
{
DataRow row = parameters.Rows[i];
ParameterDirection direction =
(ParameterDirection)Convert.ToInt16(row["ParamType"]);
Console.WriteLine("Parameter name: {0}\tParameter Source Column: {1}\tDirection: {2}",
row["ParamName"],
row["ParamPosition"],
direction);
}
transaction.Commit();
}
public void InsertTest()
{
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
DataRow newRow = ds.Tables["TEST"].NewRow();
newRow["INT_FIELD"] = 101;
newRow["CHAR_FIELD"] = "ONE THOUSAND";
newRow["VARCHAR_FIELD"] = ":;,.{}`+^*[]\\!|@#$%&/()?_-<>";
newRow["BIGINT_field"] = 100000;
newRow["SMALLINT_field"] = 100;
newRow["DOUBLE_FIELD"] = 100.01;
newRow["NUMERIC_FIELD"] = 100.01;
newRow["DECIMAL_FIELD"] = 100.01;
newRow["DATE_FIELD"] = new DateTime(100, 10, 10);
newRow["TIME_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10);
newRow["TIMESTAMP_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10);
newRow["CLOB_FIELD"] = "ONE THOUSAND";
ds.Tables["TEST"].Rows.Add(newRow);
adapter.Update(ds, "TEST");
Assert.AreEqual(101, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void UpdateDecimalTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["DECIMAL_FIELD"] = System.Int32.MaxValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT decimal_field FROM TEST WHERE int_field = ?";
command = new BdpCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
decimal val = (decimal)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int32.MaxValue, val, "decimal_field has an incorrect value");
}
public void FillMultipleTest()
{
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
adapter.Fill(ds1, "TEST");
adapter.Fill(ds2, "TEST");
Assert.AreEqual(100, ds1.Tables["TEST"].Rows.Count, "Incorrect row count (ds1)");
Assert.AreEqual(100, ds2.Tables["TEST"].Rows.Count, "Incorrect row count (ds2)");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void UpdateVarCharTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["VARCHAR_FIELD"] = "ONE VAR THOUSAND";
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT varchar_field FROM TEST WHERE int_field = ?";
command = new BdpCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
string val = (string)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual("ONE VAR THOUSAND", val.Trim(), "varchar_field has an incorrect value");
}
public virtual void SetUp()
{
// Build the connection string
StringBuilder connectionString = new StringBuilder();
connectionString.AppendFormat(
"Assembly={0};UserName={1};Password={2};Database={3}",
ConfigurationSettings.AppSettings["Assembly"],
ConfigurationSettings.AppSettings["UserName"],
ConfigurationSettings.AppSettings["Password"],
ConfigurationSettings.AppSettings["Database"]);
StringBuilder connOptions = new StringBuilder();
connOptions.AppendFormat(
"Charset={0};Dialect={1};Server Type={2};Pooling=false",
ConfigurationSettings.AppSettings["Charset"],
ConfigurationSettings.AppSettings["Dialect"],
ConfigurationSettings.AppSettings["ServerType"]);
try
{
try
{
System.IO.File.Delete(ConfigurationSettings.AppSettings["DatabasePath"]);
}
catch
{
}
System.IO.File.Copy(
ConfigurationSettings.AppSettings["BaseDatabase"],
ConfigurationSettings.AppSettings["DatabasePath"]);
}
catch
{
}
this.connection = new BdpConnection();
this.connection.ConnectionString = connectionString.ToString();
this.connection.ConnectionOptions = connOptions.ToString();
this.connection.Open();
CreateTables(connectionString.ToString());
CreateProcedures(connectionString.ToString());
CreateTriggers(connectionString.ToString());
InsertTestData(connectionString.ToString());
if (this.withTransaction)
{
this.transaction = this.connection.BeginTransaction();
}
}
private static void InsertTestData(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("DELETE FROM TEST");
BdpCommand command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
BdpTransaction transaction = connection.BeginTransaction();
command = new BdpCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field", BdpType.Int32);
command.Parameters.Add("@char_field", BdpType.String, BdpType.stFixed);
command.Parameters.Add("@varchar_field", BdpType.String);
command.Parameters.Add("@bigint_field", BdpType.Int64);
command.Parameters.Add("@smallint_field", BdpType.Int16);
command.Parameters.Add("@float_field", BdpType.Float);
command.Parameters.Add("@double_field", BdpType.Double);
command.Parameters.Add("@numeric_field", BdpType.Decimal);
command.Parameters.Add("@date_field", BdpType.Date);
command.Parameters.Add("@time_Field", BdpType.Time);
command.Parameters.Add("@timestamp_field", BdpType.DateTime);
command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stHMemo);
command.Parameters.Add("@blob_field", BdpType.Blob, BdpType.stHBinary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10) / 5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10) / 5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value = DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
command.Dispose();
connection.Close();
}
}
