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 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 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 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 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();
}
// 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 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 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 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();
}
// RPH - Changed parameter names to "?". Parameters are substituted in order.
// [Ignore("Named parameters are not support in the Borland Data Provider")]
public void NamedParametersTest()
{
BdpCommand command = Connection.CreateCommand();
// command.CommandText = "select CHAR_FIELD from TEST where INT_FIELD = @int_field or CHAR_FIELD = @char_field";
command.CommandText = "select CHAR_FIELD from TEST where INT_FIELD = ? or CHAR_FIELD = ?";
command.Parameters.Add("@int_field", 2);
command.Parameters.Add("@char_field", "TWO");
BdpDataReader reader = command.ExecuteReader();
int count = 0;
while (reader.Read())
{
count++;
}
Assert.AreEqual(1, count, "Invalid number of records fetched.");
reader.Close();
command.Close();
}
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 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 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 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");
}
// 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 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 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 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();
}
}
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();
}
}
