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 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 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();
}
}
}
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();
}
// 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 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 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 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 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();
}
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();
}
}
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();
}
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 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();
}
}
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 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 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();
}
// 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");
}
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 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 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");
}
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 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 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 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();
}