public override void SetUp()
{
base.SetUp(); // Run inherited method first
// Get connection to database
fConnection = this.Connection;
// Create IDbCommand object
iCommand = fConnection.CreateCommand();
// Create a resolver object
fFbResolver = (FbResolver)fConnection.GetResolver();
// Define table name for tests
fTableName = "TEST";
fColumnCount = 15; // Number of columns in the TEST table
// Define name for Primary Key field in TEST
fKeyField = "INT_FIELD";
// Define default BdpUpdateMode
fUpdateMode = BdpUpdateMode.Key;
// Get list of columns for table
fSelectCmd = new StringBuilder();
fSelectCmd.AppendFormat("SELECT * FROM {0}", fTableName);
iCommand.CommandText = fSelectCmd.ToString();
fMetaData = (FbMetaData)fConnection.GetMetaData();
fTestTable = fMetaData.GetSchemaTable(null, iCommand, null);
fColumns = fTestTable.Rows;
// Create expectedValue object
fExpectedValue = new StringBuilder();
}
// RPH - Connection pooling is now supported
// [Ignore("Borland Data Provider doesn't support Connection Pooling yet.")]
public void ConnectionPoolingTest()
{
BdpConnection myConnection1 = new BdpConnection(Connection.ConnectionString);
BdpConnection myConnection2 = new BdpConnection(Connection.ConnectionString);
BdpConnection myConnection3 = new BdpConnection(Connection.ConnectionString);
// Open two connections.
Console.WriteLine("Open two connections.");
myConnection1.Open();
myConnection2.Open();
// Now there are two connections in the pool that matches the connection string.
// Return the both connections to the pool.
Console.WriteLine("Return both of the connections to the pool.");
myConnection1.Close();
myConnection2.Close();
// Get a connection out of the pool.
Console.WriteLine("Open a connection from the pool.");
myConnection1.Open();
// Get a second connection out of the pool.
Console.WriteLine("Open a second connection from the pool.");
myConnection2.Open();
// Open a third connection.
Console.WriteLine("Open a third connection.");
myConnection3.Open();
// Return the all connections to the pool.
Console.WriteLine("Return all three connections to the pool.");
myConnection1.Close();
myConnection2.Close();
myConnection3.Close();
}
public void BeginTrasactionTest()
{
BdpConnection conn01 = new BdpConnection(Connection.ConnectionString);
conn01.Open();
BdpTransaction txn01 = conn01.BeginTransaction(IsolationLevel.Unspecified);
txn01.Commit();
conn01.Close();
BdpConnection conn02 = new BdpConnection(Connection.ConnectionString);
conn02.Open();
BdpTransaction txn02 = conn02.BeginTransaction(IsolationLevel.ReadCommitted);
txn02.Commit();
conn02.Close();
BdpConnection conn03 = new BdpConnection(Connection.ConnectionString);
conn03.Open();
BdpTransaction txn03 = conn03.BeginTransaction(IsolationLevel.ReadUncommitted);
txn03.Commit();
conn03.Close();
BdpConnection conn04 = new BdpConnection(Connection.ConnectionString);
conn04.Open();
BdpTransaction txn04 = conn04.BeginTransaction(IsolationLevel.RepeatableRead);
txn04.Commit();
conn04.Close();
BdpConnection conn05 = new BdpConnection(Connection.ConnectionString);
conn05.Open();
BdpTransaction txn05 = conn05.BeginTransaction(IsolationLevel.Serializable);
txn05.Commit();
conn05.Close();
}
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 virtual void SetUp()
{
// Build the connection string
StringBuilder connectionString = new StringBuilder();
connectionString.AppendFormat(
"Assembly={0};UserName={1};Password={2};Database={3}",
ConfigurationSettings.AppSettings["Assembly"],
ConfigurationSettings.AppSettings["UserName"],
ConfigurationSettings.AppSettings["Password"],
ConfigurationSettings.AppSettings["Database"]);
StringBuilder connOptions = new StringBuilder();
connOptions.AppendFormat(
"Charset={0};Dialect={1};Server Type={2};Pooling=false",
ConfigurationSettings.AppSettings["Charset"],
ConfigurationSettings.AppSettings["Dialect"],
ConfigurationSettings.AppSettings["ServerType"]);
try
{
try
{
System.IO.File.Delete(ConfigurationSettings.AppSettings["DatabasePath"]);
}
catch
{
}
System.IO.File.Copy(
ConfigurationSettings.AppSettings["BaseDatabase"],
ConfigurationSettings.AppSettings["DatabasePath"]);
}
catch
{
}
this.connection = new BdpConnection();
this.connection.ConnectionString = connectionString.ToString();
this.connection.ConnectionOptions = connOptions.ToString();
this.connection.Open();
CreateTables(connectionString.ToString());
CreateProcedures(connectionString.ToString());
CreateTriggers(connectionString.ToString());
InsertTestData(connectionString.ToString());
if (this.withTransaction)
{
this.transaction = this.connection.BeginTransaction();
}
}
public void BeginTrasactionTest()
{
BdpConnection conn01 = new BdpConnection(Connection.ConnectionString);
conn01.Open();
BdpTransaction txn01 = conn01.BeginTransaction(IsolationLevel.Unspecified);
txn01.Commit();
conn01.Close();
BdpConnection conn02 = new BdpConnection(Connection.ConnectionString);
conn02.Open();
BdpTransaction txn02 = conn02.BeginTransaction(IsolationLevel.ReadCommitted);
txn02.Commit();
conn02.Close();
BdpConnection conn03 = new BdpConnection(Connection.ConnectionString);
conn03.Open();
BdpTransaction txn03 = conn03.BeginTransaction(IsolationLevel.ReadUncommitted);
txn03.Commit();
conn03.Close();
BdpConnection conn04 = new BdpConnection(Connection.ConnectionString);
conn04.Open();
BdpTransaction txn04 = conn04.BeginTransaction(IsolationLevel.RepeatableRead);
txn04.Commit();
conn04.Close();
BdpConnection conn05 = new BdpConnection(Connection.ConnectionString);
conn05.Open();
BdpTransaction txn05 = conn05.BeginTransaction(IsolationLevel.Serializable);
txn05.Commit();
conn05.Close();
}
public virtual void SetUp()
{
// Build the connection string
StringBuilder connectionString = new StringBuilder();
connectionString.AppendFormat(
"Assembly={0};UserName={1};Password={2};Database={3}",
ConfigurationSettings.AppSettings["Assembly"],
ConfigurationSettings.AppSettings["UserName"],
ConfigurationSettings.AppSettings["Password"],
ConfigurationSettings.AppSettings["Database"]);
StringBuilder connOptions = new StringBuilder();
connOptions.AppendFormat(
"Charset={0};Dialect={1};Server Type={2};Pooling=false",
ConfigurationSettings.AppSettings["Charset"],
ConfigurationSettings.AppSettings["Dialect"],
ConfigurationSettings.AppSettings["ServerType"]);
try
{
try
{
System.IO.File.Delete(ConfigurationSettings.AppSettings["DatabasePath"]);
}
catch
{
}
System.IO.File.Copy(
ConfigurationSettings.AppSettings["BaseDatabase"],
ConfigurationSettings.AppSettings["DatabasePath"]);
}
catch
{
}
this.connection = new BdpConnection();
this.connection.ConnectionString = connectionString.ToString();
this.connection.ConnectionOptions = connOptions.ToString();
this.connection.Open();
CreateTables(connectionString.ToString());
CreateProcedures(connectionString.ToString());
CreateTriggers(connectionString.ToString());
InsertTestData(connectionString.ToString());
if (this.withTransaction)
{
this.transaction = this.connection.BeginTransaction();
}
}
private static void InsertTestData(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("DELETE FROM TEST");
BdpCommand command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
BdpTransaction transaction = connection.BeginTransaction();
command = new BdpCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field", BdpType.Int32);
command.Parameters.Add("@char_field", BdpType.String, BdpType.stFixed);
command.Parameters.Add("@varchar_field", BdpType.String);
command.Parameters.Add("@bigint_field", BdpType.Int64);
command.Parameters.Add("@smallint_field", BdpType.Int16);
command.Parameters.Add("@float_field", BdpType.Float);
command.Parameters.Add("@double_field", BdpType.Double);
command.Parameters.Add("@numeric_field", BdpType.Decimal);
command.Parameters.Add("@date_field", BdpType.Date);
command.Parameters.Add("@time_Field", BdpType.Time);
command.Parameters.Add("@timestamp_field", BdpType.DateTime);
command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stHMemo);
command.Parameters.Add("@blob_field", BdpType.Blob, BdpType.stHBinary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10) / 5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10) / 5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value = DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
command.Dispose();
connection.Close();
}
}
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();
}
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();
}
// RPH - Connection pooling is now supported
// [Ignore("Borland Data Provider doesn't support Connection Pooling yet.")]
public void ConnectionPoolingTest()
{
BdpConnection myConnection1 = new BdpConnection(Connection.ConnectionString);
BdpConnection myConnection2 = new BdpConnection(Connection.ConnectionString);
BdpConnection myConnection3 = new BdpConnection(Connection.ConnectionString);
// Open two connections.
Console.WriteLine ("Open two connections.");
myConnection1.Open();
myConnection2.Open();
// Now there are two connections in the pool that matches the connection string.
// Return the both connections to the pool.
Console.WriteLine ("Return both of the connections to the pool.");
myConnection1.Close();
myConnection2.Close();
// Get a connection out of the pool.
Console.WriteLine ("Open a connection from the pool.");
myConnection1.Open();
// Get a second connection out of the pool.
Console.WriteLine ("Open a second connection from the pool.");
myConnection2.Open();
// Open a third connection.
Console.WriteLine ("Open a third connection.");
myConnection3.Open();
// Return the all connections to the pool.
Console.WriteLine ("Return all three connections to the pool.");
myConnection1.Close();
myConnection2.Close();
myConnection3.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();
}
