public void NamedParametersReuseTest()
{
string sql = "select * from test where int_field >= @lang and int_field <= @lang";
BdpCommand command = new BdpCommand(sql, this.Connection);
command.Parameters.Add("@lang", BdpType.Int32).Value = 10;
BdpDataReader reader = command.ExecuteReader();
int count = 0;
int intValue = 0;
while (reader.Read())
{
if (count == 0)
{
intValue = reader.GetInt32(0);
}
count++;
}
Assert.AreEqual(1, count, "Invalid number of records fetched.");
Assert.AreEqual(10, intValue, "Invalid record fetched.");
reader.Close();
command.Close();
}
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 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 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 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 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 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 ExecuteReaderTest()
{
BdpCommand command = Connection.CreateCommand();
command.CommandText = "select * from TEST";
int rows = 0; // Holds number of rows;
object rowNum; // Holds row number fetch from DB
BdpDataReader reader = command.ExecuteReader();
while (reader.Read())
{ // Check INT field for proper value.
rowNum = reader["INT_FIELD"];
Assert.IsTrue((int)rowNum == rows, "Invalid row number");
rows++;
}
reader.Close();
Assert.IsTrue(Connection.State == ConnectionState.Open,
"Connection is not open");
command.Close();
}
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 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 UpdatedArrayFieldTest()
{
Console.WriteLine("\r\nUpdate IARRAY field with implicit transaction.");
int[] values = new int[4];
values[0] = 10;
values[1] = 20;
values[2] = 30;
values[3] = 40;
// Add IARRAY_FIELD column
BdpCommand command = new BdpCommand("alter table TEST add IARRAY_FIELD INTEGER[4]", Connection);
command.ExecuteNonQuery();
command.Close();
// Now test the update of an array
command = new BdpCommand("update TEST set iarray_field = ? where int_field = ?", Connection);
command.Parameters.Add("@iarray_field", BdpType.Array).Value = values;
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Array field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
public void ExecuteScalarTest()
{
BdpCommand command = new BdpCommand("select sum(int_field) from TEST", Connection);
object actual = command.ExecuteScalar();
Console.WriteLine("\r\nExecuteScalar with implicit transaction: {0}", actual);
Assert.AreEqual(4950, actual, "Wrong sum returned.");
command.Dispose();
}
private static void CreateTables(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("DROP TABLE TEST");
BdpCommand command = null;
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
// Table for general purpouse tests
commandText.Append("CREATE TABLE TEST (");
commandText.Append("INT_FIELD INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,");
commandText.Append("CHAR_FIELD CHAR(30),");
commandText.Append("VARCHAR_FIELD VARCHAR(100),");
commandText.Append("BIGINT_FIELD BIGINT,");
commandText.Append("SMALLINT_FIELD SMALLINT,");
commandText.Append("DOUBLE_FIELD DOUBLE PRECISION,");
commandText.Append("FLOAT_FIELD FLOAT,");
commandText.Append("NUMERIC_FIELD NUMERIC(15,2),");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2),");
commandText.Append("DATE_FIELD DATE,");
commandText.Append("TIME_FIELD TIME,");
commandText.Append("TIMESTAMP_FIELD TIMESTAMP,");
commandText.Append("CLOB_FIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,");
commandText.Append("BLOB_FIELD BLOB SUB_TYPE 0 SEGMENT SIZE 80,");
commandText.Append("EXPR_FIELD COMPUTED BY (smallint_field * 1000));");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
connection.Close();
}
public void ExecuteReaderWithBehaviorTest()
{
BdpCommand command = new BdpCommand("select * from TEST", Connection);
BdpDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
reader.Close();
Assert.IsTrue(Connection.State == ConnectionState.Closed,
"Connection was not closed");
command.Close(); // Check to make sure no exceptions are raised
}
public void RecordAffectedTest()
{
string sql = "insert into test (int_field) values (100000);";
BdpCommand command = new BdpCommand(sql, this.Connection);
BdpDataReader reader = command.ExecuteReader();
reader.Close();
Assert.AreEqual(1, reader.RecordsAffected, "RecordsAffected value is incorrect");
}
public void ExecuteReaderWithBehaviorTest()
{
BdpCommand command = new BdpCommand("select * from TEST", Connection);
BdpDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
reader.Close();
Assert.IsTrue(Connection.State == ConnectionState.Closed,
"Connection was not closed");
command.Close(); // Check to make sure no exceptions are raised
}
public void ExecuteScalarTest()
{
BdpCommand command = Connection.CreateCommand();
command.CommandText = "select CHAR_FIELD from TEST where INT_FIELD = ?";
command.Parameters.Add("@INT_FIELD", 2);
string charFieldValue = command.ExecuteScalar().ToString();
Console.WriteLine("Scalar value: {0}", charFieldValue);
Assert.AreEqual(charFieldValue, "IRow 2", "Wrong value.");
command.Close();
}
public void DataAdapterFillTest()
{
BdpCommand command = new BdpCommand("select * from TEST where DATE_FIELD = ?", Connection);
BdpDataAdapter adapter = new BdpDataAdapter(command);
// adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = new DateTime(2003, 1, 5);
adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = DateTime.Today;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Console.WriteLine();
Console.WriteLine("Implicit transactions - DataAdapter Fill Method - Test");
int tables = 0;
int rows = 0;
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
tables++;
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
rows++;
}
}
Assert.AreEqual(1, tables, "Wrong number of tables.");
Assert.AreEqual(100, rows, "Wrong number of rows.");
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
public void DataAdapterFillTest()
{
BdpCommand command = new BdpCommand("select * from TEST where DATE_FIELD = ?", Connection);
BdpDataAdapter adapter = new BdpDataAdapter(command);
// adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = new DateTime(2003, 1, 5);
adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = DateTime.Today;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Console.WriteLine();
Console.WriteLine("Implicit transactions - DataAdapter Fill Method - Test");
int tables = 0;
int rows = 0;
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
tables++;
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
rows++;
}
}
Assert.AreEqual(1, tables, "Wrong number of tables.");
Assert.AreEqual(100, rows, "Wrong number of rows.");
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
// RPH - Changed parameter names to "?". Parameters are substituted in order.
// [Ignore("Named parameters are not support in the Borland Data Provider")]
public void NamedParametersAndLiterals()
{
// string sql = "update test set char_field = '*****@*****.**', bigint_field = @bigint, varchar_field = '*****@*****.**' where int_field = @integer";
string sql = "update test set char_field = '*****@*****.**', bigint_field = ?, varchar_field = '*****@*****.**' where int_field = ?";
BdpCommand command = new BdpCommand(sql, this.Connection);
command.Parameters.Add("@bigint", BdpType.Int64).Value = 200;
command.Parameters.Add("@integer", BdpType.Int32).Value = 1;
int recordsAffected = command.ExecuteNonQuery();
command.Close();
Assert.AreEqual(recordsAffected, 1, "Invalid number of records affected.");
}
public void UpdatedClobFieldTest()
{
Console.WriteLine("\r\nUpdate CLOB field with implicit transaction.");
BdpCommand command = new BdpCommand("update TEST set clob_field = ? where int_field = ?", Connection);
command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stMemo).Value = "Clob field update with implicit transaction";
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Clob field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
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 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 UpdatedBlobFieldTest()
{
Console.WriteLine("\r\nUpdate BLOB field with implicit transaction.");
BdpCommand command = new BdpCommand("update TEST set blob_field = ? where int_field = ?", Connection);
command.Parameters.Add("@blob_field", BdpType.Blob).Value =
Encoding.Default.GetBytes("Blob field update with implicit transaction");
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Blob field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
public void GetCharsLengthTest()
{
string sql = "select clob_field from TEST where int_field = ?";
BdpCommand command = new BdpCommand(sql, this.Connection);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 50;
BdpDataReader reader = command.ExecuteReader();
reader.Read();
long length = reader.GetChars(0, 0, null, 0, 0);
reader.Close();
Assert.AreEqual(14, length, "Incorrect clob length");
}
public void ExecuteNonQueryTest()
{
Transaction = Connection.BeginTransaction();
BdpCommand command = Connection.CreateCommand();
command.Transaction = Transaction;
command.CommandText = "insert into TEST (INT_FIELD) values (?) ";
command.Parameters.Add("@INT_FIELD", 100);
int affectedRows = command.ExecuteNonQuery();
Assert.AreEqual(affectedRows, 1);
Transaction.Rollback();
command.Close();
}
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 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 FillMultipleWithImplicitTransactionTest()
{
BdpCommand command = new BdpCommand("select * from TEST", Connection);
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();
}
public void FillTest()
{
BdpCommand command = new BdpCommand("SELECT * FROM TEST", Connection);
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");
Console.WriteLine();
Console.WriteLine("DataAdapter - Fill Method - Test");
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
}
}
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
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 FillTest()
{
BdpCommand command = new BdpCommand("SELECT * FROM TEST", Connection);
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");
Console.WriteLine();
Console.WriteLine("DataAdapter - Fill Method - Test");
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
}
}
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
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 RecordsAffectedTest()
{
BdpCommand selectCommand = new BdpCommand("SELECT * FROM TEST WHERE INT_FIELD = -1", Connection);
int recordsAffected = selectCommand.ExecuteNonQuery();
Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected);
Assert.IsTrue(recordsAffected == -1);
selectCommand.Close();
BdpCommand deleteCommand = new BdpCommand("DELETE FROM TEST WHERE INT_FIELD = -1", Connection);
recordsAffected = deleteCommand.ExecuteNonQuery();
Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected);
Assert.IsTrue(recordsAffected == 0);
deleteCommand.Close();
deleteCommand = new BdpCommand("DELETE FROM TEST WHERE INT_FIELD = 3", Connection);
recordsAffected = deleteCommand.ExecuteNonQuery();
Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected);
Assert.IsTrue(recordsAffected == 1);
deleteCommand.Close();
}
public void ReadTest()
{
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.GetValue(i) + "\t");
}
Console.WriteLine();
}
reader.Close();
command.Dispose();
transaction.Rollback();
}
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();
}
}
private static void CreateTriggers(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
BdpCommand command = null;
// new_row
commandText = new StringBuilder();
commandText.Append("DROP TRIGGER new_row");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER new_row FOR test ACTIVE\r\n");
commandText.Append("AFTER INSERT POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'new row';\r\n");
commandText.Append("END");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
// update_row
commandText = new StringBuilder();
commandText.Append("DROP TRIGGER update_row");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER update_row FOR test ACTIVE\r\n");
commandText.Append("AFTER UPDATE POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'updated row';\r\n");
commandText.Append("END");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
connection.Close();
}
public void RecordsAffectedTest()
{
BdpCommand selectCommand = new BdpCommand("SELECT * FROM TEST WHERE INT_FIELD = -1", Connection);
int recordsAffected = selectCommand.ExecuteNonQuery();
Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected);
Assert.IsTrue(recordsAffected == -1);
selectCommand.Close();
BdpCommand deleteCommand = new BdpCommand("DELETE FROM TEST WHERE INT_FIELD = -1", Connection);
recordsAffected = deleteCommand.ExecuteNonQuery();
Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected);
Assert.IsTrue(recordsAffected == 0);
deleteCommand.Close();
deleteCommand = new BdpCommand("DELETE FROM TEST WHERE INT_FIELD = 3", Connection);
recordsAffected = deleteCommand.ExecuteNonQuery();
Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected);
Assert.IsTrue(recordsAffected == 1);
deleteCommand.Close();
}
public void UpdatedBlobFieldTest()
{
Console.WriteLine("\r\nUpdate BLOB field with implicit transaction.");
BdpCommand command = new BdpCommand("update TEST set blob_field = ? where int_field = ?", Connection);
command.Parameters.Add("@blob_field", BdpType.Blob).Value =
Encoding.Default.GetBytes("Blob field update with implicit transaction");
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Blob field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
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 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 ExecuteScalarTest()
{
BdpCommand command = new BdpCommand("select sum(int_field) from TEST", Connection);
object actual = command.ExecuteScalar();
Console.WriteLine("\r\nExecuteScalar with implicit transaction: {0}", actual);
Assert.AreEqual(4950, actual, "Wrong sum returned.");
command.Dispose();
}
public void NamedParametersReuseTest()
{
string sql = "select * from test where int_field >= @lang and int_field <= @lang";
BdpCommand command = new BdpCommand(sql, this.Connection);
command.Parameters.Add("@lang", BdpType.Int32).Value = 10;
BdpDataReader reader = command.ExecuteReader();
int count = 0;
int intValue = 0;
while (reader.Read())
{
if (count == 0)
{
intValue = reader.GetInt32(0);
}
count++;
}
Assert.AreEqual(1, count, "Invalid number of records fetched.");
Assert.AreEqual(10, intValue, "Invalid record fetched.");
reader.Close();
command.Close();
}
// RPH - Changed parameter names to "?". Parameters are substituted in order.
// [Ignore("Named parameters are not support in the Borland Data Provider")]
public void NamedParametersAndLiterals()
{
// string sql = "update test set char_field = '*****@*****.**', bigint_field = @bigint, varchar_field = '*****@*****.**' where int_field = @integer";
string sql = "update test set char_field = '*****@*****.**', bigint_field = ?, varchar_field = '*****@*****.**' where int_field = ?";
BdpCommand command = new BdpCommand(sql, this.Connection);
command.Parameters.Add("@bigint", BdpType.Int64).Value = 200;
command.Parameters.Add("@integer",BdpType.Int32).Value = 1;
int recordsAffected = command.ExecuteNonQuery();
command.Close();
Assert.AreEqual(recordsAffected, 1, "Invalid number of records affected.");
}
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 ExecuteNonQueryWithOutputParameters()
{
BdpCommand command = new BdpCommand("EXECUTE PROCEDURE GETASCIIBLOB(?)", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ID", BdpType.String).Direction = ParameterDirection.Input;
command.Parameters.Add("@CLOB_FIELD", BdpType.Blob, BdpType.stMemo).Direction = ParameterDirection.Output;
command.Parameters[0].Value = 3;
// This will fill output parameters values
command.ExecuteNonQuery();
Console.WriteLine("Output Parameters");
Console.WriteLine(command.Parameters[1].Value);
// Check that the output parameter has a correct value
Assert.AreEqual("IRow Number 3", command.Parameters[1].Value, "Output parameter value is not valid");
// Close command - this will do a transaction commit
command.Close();
}
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 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 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 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 UpdatedArrayFieldTest()
{
Console.WriteLine("\r\nUpdate IARRAY field with implicit transaction.");
int[] values = new int[4];
values[0] = 10;
values[1] = 20;
values[2] = 30;
values[3] = 40;
// Add IARRAY_FIELD column
BdpCommand command = new BdpCommand("alter table TEST add IARRAY_FIELD INTEGER[4]", Connection);
command.ExecuteNonQuery();
command.Close();
// Now test the update of an array
command = new BdpCommand("update TEST set iarray_field = ? where int_field = ?", Connection);
command.Parameters.Add("@iarray_field", BdpType.Array).Value = values;
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Array field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
public void GetCharsLengthTest()
{
string sql = "select clob_field from TEST where int_field = ?";
BdpCommand command = new BdpCommand(sql, this.Connection);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 50;
BdpDataReader reader = command.ExecuteReader();
reader.Read();
long length = reader.GetChars(0, 0, null, 0, 0);
reader.Close();
Assert.AreEqual(14, length, "Incorrect clob length");
}
private static void CreateTables(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("DROP TABLE TEST");
BdpCommand command = null;
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
// Table for general purpouse tests
commandText.Append("CREATE TABLE TEST (");
commandText.Append("INT_FIELD INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,");
commandText.Append("CHAR_FIELD CHAR(30),");
commandText.Append("VARCHAR_FIELD VARCHAR(100),");
commandText.Append("BIGINT_FIELD BIGINT,");
commandText.Append("SMALLINT_FIELD SMALLINT,");
commandText.Append("DOUBLE_FIELD DOUBLE PRECISION,");
commandText.Append("FLOAT_FIELD FLOAT,");
commandText.Append("NUMERIC_FIELD NUMERIC(15,2),");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2),");
commandText.Append("DATE_FIELD DATE,");
commandText.Append("TIME_FIELD TIME,");
commandText.Append("TIMESTAMP_FIELD TIMESTAMP,");
commandText.Append("CLOB_FIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,");
commandText.Append("BLOB_FIELD BLOB SUB_TYPE 0 SEGMENT SIZE 80,");
commandText.Append("EXPR_FIELD COMPUTED BY (smallint_field * 1000));");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
connection.Close();
}
public void UpdatedClobFieldTest()
{
Console.WriteLine("\r\nUpdate CLOB field with implicit transaction.");
BdpCommand command = new BdpCommand("update TEST set clob_field = ? where int_field = ?", Connection);
command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stMemo).Value = "Clob field update with implicit transaction";
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Clob field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
private static void CreateProcedures(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
BdpCommand command = null;
StringBuilder commandText = new StringBuilder();
// SELECT_DATA
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE SELECT_DATA");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE SELECT_DATA \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("INT_FIELD INTEGER, \r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100), \r\n");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2)) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("FOR SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD \r\n");
commandText.Append("DO \r\n");
commandText.Append("SUSPEND; \r\n");
commandText.Append("end;");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
// GETRECORDCOUNT
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE GETRECORDCOUNT");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETRECORDCOUNT \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("RECCOUNT SMALLINT) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("for select count(*) from test into :reccount \r\n");
commandText.Append("do \r\n");
commandText.Append("suspend; \r\n");
commandText.Append("end\r\n");
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETVARCHARFIELD
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE GETVARCHARFIELD");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETVARCHARFIELD (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select varchar_field from test where int_field = :id into :varchar_field\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETASCIIBLOB
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE GETASCIIBLOB");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETASCIIBLOB (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("ASCII_BLOB BLOB SUB_TYPE 1)\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select clob_field from test where int_field = :id into :ascii_blob\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
connection.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 PrepareTest()
{
try
{
// Drop the table
BdpCommand drop = new BdpCommand("drop table PrepareTest", Connection);
drop.ExecuteNonQuery();
drop.Close();
}
catch
{
}
// Create a new test table
BdpCommand create = new BdpCommand("create table PrepareTest(test_field varchar(20));", Connection);
create.ExecuteNonQuery();
create.Close();
// Insert data using a prepared statement
BdpCommand command = new BdpCommand(
"INSERT INTO PrepareTest (test_field) VALUES (?);",
Connection);
command.Parameters.Add("@test_field", BdpType.String).Value = DBNull.Value;
command.Prepare();
for (int i = 0; i < 5; i++)
{
if (i < 1)
{
command.Parameters[0].Value = DBNull.Value;
}
else
{
command.Parameters[0].Value = i.ToString();
}
command.ExecuteNonQuery();
}
command.Close();
try
{
// Check that data is correct
BdpCommand select = new BdpCommand("select * from PrepareTest", Connection);
BdpDataReader reader = select.ExecuteReader();
int count = 0;
while (reader.Read())
{
if (count == 0)
{
Assert.AreEqual(DBNull.Value, reader[0], "Invalid value.");
}
else
{
Assert.AreEqual(count.ToString(), reader.GetString(0).Trim(), "Invalid value.");
}
count++;
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
// Drop table
BdpCommand drop = new BdpCommand("drop table PrepareTest", Connection);
drop.ExecuteNonQuery();
drop.Close();
}
}
