public int AddCatalogItem(String title, String uri)
{
FbConnection conn = GetConnection();
try {
using (FbCommand cmd = new FbCommand()) {
using (cmd.Transaction = conn.BeginTransaction(FbTransactionOptions.Autocommit|FbTransactionOptions.Concurrency)) {
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_AddCatalogItem";
cmd.Parameters.Add("@title", FbDbType.VarChar, 4000);
cmd.Parameters.Add("@uri", FbDbType.VarChar, 4000);
cmd.Parameters.Add("@catalog_item_id", FbDbType.Numeric);
cmd.Parameters[0].Value = title;
cmd.Parameters[1].Value = uri;
cmd.Parameters[2].Direction = ParameterDirection.ReturnValue;
//Capture the catalog item ID, and build the list
//of title characters
cmd.ExecuteNonQuery();
//int catId = (int)cmd.ExecuteScalar();
int catId = (int)cmd.Parameters[2].Value;
SetItemTitleChars(cmd.Transaction, catId, title);
cmd.Transaction.Commit();
return catId;
}
}
} finally {
conn.Close();
}
}
public void ClearCatalog()
{
FbConnection conn = GetConnection();
try {
using (FbCommand cmd = new FbCommand("delete from catalog_items", conn)) {
cmd.ExecuteNonQuery();
}
} finally {
conn.Close();
}
}
public void IntergerArrayTest()
{
int id_value = this.GetId();
Console.WriteLine("\r\n");
Console.WriteLine("Integer Array Test");
string selectText = "SELECT iarray_field FROM TEST WHERE int_field = " + id_value.ToString();
string insertText = "INSERT INTO TEST (int_field, iarray_field) values(@int_field, @array_field)";
// Insert new Record
int[] insert_values = new int[4];
insert_values[0] = 10;
insert_values[1] = 20;
insert_values[2] = 30;
insert_values[3] = 40;
Console.WriteLine("Executing insert command");
FbCommand insert = new FbCommand(insertText, Connection, Transaction);
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
insert.ExecuteNonQuery();
insert.Dispose();
Transaction.Commit();
Console.WriteLine("Checking inserted values");
// Check that inserted values are correct
FbCommand select = new FbCommand(selectText, Connection);
FbDataReader reader = select.ExecuteReader();
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
int[] select_values = new int[insert_values.Length];
System.Array.Copy((System.Array)reader.GetValue(0), select_values, select_values.Length);
for (int i = 0; i < insert_values.Length; i++)
{
if (insert_values[i] != select_values[i])
{
throw new Exception("differences at index " + i.ToString());
}
}
}
}
reader.Close();
select.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(@int_field, @blob_field)";
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
FbTransaction transaction = Connection.BeginTransaction();
FbCommand insert = new FbCommand(insertText, Connection, transaction);
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@blob_field", FbDbType.Binary).Value = insert_values;
insert.ExecuteNonQuery();
transaction.Commit();
Console.WriteLine("Checking inserted values");
// Check that inserted values are correct
FbCommand select = new FbCommand(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 FirebirdLikeTest00()
{
FbCommand command = new FbCommand("EXECUTE PROCEDURE GETVARCHARFIELD(?)", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input;
command.Parameters.Add("@VARCHAR_FIELD", FbDbType.VarChar).Direction = ParameterDirection.Output;
command.Parameters[0].Value = 1;
// This will fill output parameters values
command.ExecuteNonQuery();
// Check the value
Assert.AreEqual("IRow Number 1", command.Parameters[1].Value);
// Dispose command - this will do a transaction commit
command.Dispose();
}
public void UnicodeTest()
{
string createTable = "CREATE TABLE VARCHARTEST (VARCHAR_FIELD VARCHAR(10));";
FbCommand ct = new FbCommand(createTable, this.Connection);
ct.ExecuteNonQuery();
ct.Dispose();
ArrayList l = new ArrayList();
l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('1');");
l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('11');");
l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('111');");
l.Add("INSERT INTO VARCHARTEST (VARCHAR_FIELD) VALUES ('1111');");
foreach (string statement in l)
{
FbCommand insert = new FbCommand(statement, this.Connection);
insert.ExecuteNonQuery();
insert.Dispose();
}
string sql = "select * from varchartest";
FbCommand cmd = new FbCommand(sql, this.Connection);
FbDataReader r = cmd.ExecuteReader();
while (r.Read())
{
Console.WriteLine("{0} :: {1}", r[0], r[0].ToString().Length);
}
r.Close();
}
public void InvalidParameterFormat()
{
string sql = "update test set timestamp_field = @timestamp where int_field = @integer";
FbTransaction transaction = this.Connection.BeginTransaction();
try
{
FbCommand command = new FbCommand(sql, this.Connection, transaction);
command.Parameters.Add("@timestamp", FbDbType.TimeStamp).Value = 1;
command.Parameters.Add("@integer", FbDbType.Integer).Value = 1;
command.ExecuteNonQuery();
command.Dispose();
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
public void UpdatedClobFieldTest()
{
Console.WriteLine("\r\nUpdate CLOB field with implicit transaction.");
FbCommand command = new FbCommand("update TEST set clob_field = @clob_field where int_field = @int_field", Connection);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
command.Parameters.Add("@clob_field", FbDbType.Text).Value = "Clob field update with implicit transaction";
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 VarCharArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("VarChar Array Test");
Console.WriteLine("------- ----- ----");
string updateText = "update TEST set varray_field = @array_field " +
"WHERE int_field = 1";
string[] new_values = new string[2];
new_values[0] = "abc";
new_values[1] = "abcdef";
FbCommand update = new FbCommand(updateText, Connection, Transaction);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
public void UpdatedArrayFieldTest()
{
Console.WriteLine("\r\nUpdate CLOB field with implicit transaction.");
int[] values = new int[4];
values[0] = 10;
values[1] = 20;
values[2] = 30;
values[3] = 40;
FbCommand command = new FbCommand("update TEST set iarray_field = @iarray_field where int_field = @int_field", Connection);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
command.Parameters.Add("@iarray_field", FbDbType.Array).Value = values;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Array field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
/// <include file='Doc/en_EN/FbTransaction.xml' path='doc/class[@name="FbTransaction"]/method[@name="Rollback(System.String)"]/*'/>
public void Rollback(string savePointName)
{
lock (this)
{
if (savePointName == null)
{
throw new ArgumentException("No transaction name was be specified.");
}
else
{
if (savePointName.Length == 0)
{
throw new ArgumentException("No transaction name was be specified.");
}
}
if (this.isUpdated)
{
throw new InvalidOperationException("This Transaction has completed; it is no longer usable.");
}
try
{
FbCommand command = new FbCommand(
"ROLLBACK WORK TO SAVEPOINT " + savePointName,
this.connection,
this);
command.ExecuteNonQuery();
command.Dispose();
}
catch (IscException ex)
{
throw new FbException(ex.Message, ex);
}
}
}
public void InsertNullTest()
{
string sql = "insert into TEST (int_field) values (@value)";
FbCommand command = new FbCommand(sql, this.Connection);
command.Parameters.Add("@value", FbDbType.Integer).Value = null;
try
{
command.ExecuteNonQuery();
throw new Exception("The command was executed without throw an exception");
}
catch
{
}
}
public void SqlServerLikeTest00()
{
FbCommand command = new FbCommand("GETVARCHARFIELD", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input;
command.Parameters.Add("@VARCHAR_FIELD", FbDbType.VarChar).Direction = ParameterDirection.Output;
command.Parameters[0].Value = 1;
// This will fill output parameters values
command.ExecuteNonQuery();
// Print output value
Console.WriteLine("Output Parameters");
Console.WriteLine(command.Parameters[1].Value);
// Dispose command - this will do a transaction commit
command.Dispose();
}
public void SqlServerLikeTest01()
{
FbCommand command = new FbCommand("GETRECORDCOUNT", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@RECORDCOUNT", FbDbType.Integer).Direction = ParameterDirection.Output;
// This will fill output parameters values
command.ExecuteNonQuery();
// Print output value
Console.WriteLine("Output Parameters - Record Count");
Console.WriteLine(command.Parameters[0].Value);
// Dispose command - this will do a transaction commit
command.Dispose();
}
public void SavePointTest()
{
FbCommand command = new FbCommand();
Console.WriteLine("Iniciada nueva transaccion");
Transaction = Connection.BeginTransaction("InitialSavePoint");
command.Connection = Connection;
command.Transaction = Transaction;
command.CommandText = "insert into TEST (INT_FIELD) values (200) ";
command.ExecuteNonQuery();
Transaction.Save("FirstSavePoint");
command.CommandText = "insert into TEST (INT_FIELD) values (201) ";
command.ExecuteNonQuery();
Transaction.Save("SecondSavePoint");
command.CommandText = "insert into TEST (INT_FIELD) values (202) ";
command.ExecuteNonQuery();
Transaction.Rollback("InitialSavePoint");
Transaction.Commit();
command.Dispose();
}
public void AbortTransaction()
{
StringBuilder b1 = new StringBuilder();
b1.AppendFormat("ALTER TABLE \"{0}\" drop \"INT_FIELD\"", "TEST");
FbTransaction transaction = null;
FbCommand command = null;
try
{
transaction = this.Connection.BeginTransaction();
command = new FbCommand(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 SimplifiedChineseTest()
{
string createTable = "CREATE TABLE TABLE1 (FIELD1 varchar(20))";
FbCommand create = new FbCommand(createTable, this.Connection);
create.ExecuteNonQuery();
create.Dispose();
// insert using parametrized SQL
string sql = "INSERT INTO Table1 VALUES (@value)";
FbCommand command = new FbCommand(sql, this.Connection);
command.Parameters.Add("@value", FbDbType.VarChar).Value = "中文";
command.ExecuteNonQuery();
command.Dispose();
sql = "SELECT * FROM TABLE1";
FbCommand select = new FbCommand(sql, this.Connection);
string result = select.ExecuteScalar().ToString();
select.Dispose();
Assert.AreEqual("中文", result, "Incorrect results in parametrized insert");
sql = "DELETE FROM TABLE1";
FbCommand delete = new FbCommand(sql, this.Connection);
delete.ExecuteNonQuery();
delete.Dispose();
// insert using plain SQL
sql = "INSERT INTO Table1 VALUES ('中文')";
FbCommand plainCommand = new FbCommand(sql, this.Connection);
plainCommand.ExecuteNonQuery();
plainCommand.Dispose();
sql = "SELECT * FROM TABLE1";
select = new FbCommand(sql, this.Connection);
result = select.ExecuteScalar().ToString();
select.Dispose();
Assert.AreEqual("中文", result, "Incorrect results in plain insert");
}
public void PrepareTest()
{
// Create a new test table
FbCommand create = new FbCommand("create table PrepareTest(test_field varchar(20));", Connection);
create.ExecuteNonQuery();
create.Dispose();
// Insert data using a prepared statement
FbCommand command = new FbCommand(
"insert into PrepareTest(test_field) values(@test_field);",
Connection);
command.Parameters.Add("@test_field", FbDbType.VarChar).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.Dispose();
try
{
// Check that data is correct
FbCommand select = new FbCommand("select * from PrepareTest", Connection);
FbDataReader reader = select.ExecuteReader();
int count = 0;
while (reader.Read())
{
if (count == 0)
{
Assert.AreEqual(DBNull.Value, reader[0], "Invalid value.");
}
else
{
Assert.AreEqual(count, reader.GetInt32(0), "Invalid value.");
}
count++;
}
reader.Close();
select.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
// Drop table
FbCommand drop = new FbCommand("drop table PrepareTest", Connection);
drop.ExecuteNonQuery();
drop.Dispose();
}
}
public void InsertDateTest()
{
string sql = "insert into TEST (int_field, date_field) values (1002, @date)";
FbCommand command = new FbCommand(sql, this.Connection);
command.Parameters.Add("@date", FbDbType.Date).Value = DateTime.Now.ToString();
int ra = command.ExecuteNonQuery();
Assert.AreEqual(ra, 1);
}
public void BigIntArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("BigInt Array Test");
Console.WriteLine("------ ----- ----");
string updateText = "update TEST set larray_field = @array_field " +
"WHERE int_field = 1";
long[] new_values = new long[4];
new_values[0] = 900;
new_values[1] = 1000;
new_values[2] = 1100;
new_values[3] = 1200;
FbCommand update = new FbCommand(updateText, Connection, Transaction);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
public void ParameterDescribeTest()
{
string sql = "insert into TEST (int_field) values (@value)";
FbCommand command = new FbCommand(sql, this.Connection);
command.Prepare();
command.Parameters.Add("@value", FbDbType.Integer).Value = 100000;
command.ExecuteNonQuery();
command.Dispose();
}
public void NumericArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("Numeric/Decimal Array Test");
Console.WriteLine("--------------- ----- ----");
string updateText = "update TEST set narray_field = @array_field " +
"WHERE int_field = 1";
decimal[] new_values = new decimal[2];
new_values[0] = 2100.10M;
new_values[1] = 2200.20M;
FbCommand update = new FbCommand(updateText, Connection, Transaction);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
public void NamedParametersAndLiterals()
{
string sql = "update test set char_field = '*****@*****.**', bigint_field = @bigint, varchar_field = '*****@*****.**' where int_field = @integer";
FbCommand command = new FbCommand(sql, this.Connection);
command.Parameters.Add("@bigint", FbDbType.BigInt).Value = 200;
command.Parameters.Add("@integer", FbDbType.Integer).Value = 1;
int recordsAffected = command.ExecuteNonQuery();
command.Dispose();
Assert.AreEqual(recordsAffected, 1, "Invalid number of records affected.");
}
public void UpdatedBlobFieldTest()
{
Console.WriteLine("\r\nUpdate BLOB field with implicit transaction.");
FbCommand command = new FbCommand("update TEST set blob_field = @blob_field where int_field = @int_field", Connection);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
command.Parameters.Add("@blob_field", FbDbType.Binary).Value =
Encoding.Default.GetBytes("Blob field update with implicit transaction");
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 DoubleArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("Double Array Test");
Console.WriteLine("------ ----- ----");
string updateText = "update TEST set barray_field = @array_field " +
"WHERE int_field = 1";
double[] new_values = new double[2];
new_values[0] = 1700.10;
new_values[1] = 1800.20;
FbCommand update = new FbCommand(updateText, Connection, Transaction);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
public void ExecuteStoredProcTest()
{
FbCommand command = new FbCommand("EXECUTE PROCEDURE GETVARCHARFIELD(?)", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input;
command.Parameters.Add("@VARCHAR_FIELD", FbDbType.VarChar).Direction = ParameterDirection.Output;
command.Parameters[0].Value = 1;
// This will fill output parameters values
command.ExecuteNonQuery();
Console.WriteLine("Output Parameters");
Console.WriteLine(command.Parameters[1].Value);
}
public void TimeStampArrayPartialUpdateTest()
{
Console.WriteLine("\r\n");
Console.WriteLine("TimeStamp Array Test");
Console.WriteLine("--------- ----- ----");
string updateText = "update TEST set tsarray_field = @array_field " +
"WHERE int_field = 1";
DateTime[] new_values = new DateTime[2];
new_values[0] = DateTime.Now.AddSeconds(100);
new_values[1] = DateTime.Now.AddSeconds(200);
FbCommand update = new FbCommand(updateText, Connection, Transaction);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
public void RecordsAffectedTest()
{
FbCommand selectCommand = new FbCommand("SELECT * FROM TEST WHERE INT_FIELD = -1", Connection);
int recordsAffected = selectCommand.ExecuteNonQuery();
Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected);
Assert.IsTrue(recordsAffected == -1);
selectCommand.Dispose();
FbCommand deleteCommand = new FbCommand("DELETE FROM TEST WHERE INT_FIELD = -1", Connection);
recordsAffected = deleteCommand.ExecuteNonQuery();
Console.WriteLine("\r\nRecords Affected: {0}", recordsAffected);
Assert.IsTrue(recordsAffected == 0);
deleteCommand.Dispose();
}
public void PartialUpdatesTest()
{
int id_value = this.GetId();
int elements = 16384;
string selectText = "SELECT big_array FROM TEST WHERE int_field = " + id_value.ToString();
string insertText = "INSERT INTO TEST (int_field, big_array) values(@int_field, @array_field)";
Console.WriteLine("\r\n\r\nPartialUpdatesTest");
Console.WriteLine("Generating an array of temp data");
// Generate an array of temp data
byte[] bytes = new byte[elements*4];
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
rng.GetBytes(bytes);
int[] insert_values = new int[elements];
Buffer.BlockCopy(bytes, 0, insert_values, 0, bytes.Length);
Console.WriteLine("Executing insert command");
// Execute insert command
FbCommand insert = new FbCommand(insertText, Connection, Transaction);
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
insert.ExecuteNonQuery();
insert.Dispose();
Transaction.Commit();
Console.WriteLine("Checking inserted values");
// Check that inserted values are correct
FbCommand select = new FbCommand(selectText, Connection);
FbDataReader reader = select.ExecuteReader();
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
int[] select_values = new int[insert_values.Length];
System.Array.Copy((System.Array)reader.GetValue(0), select_values, select_values.Length);
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");
reader.Close();
select.Dispose();
// Start a new Transaction
Transaction = Connection.BeginTransaction();
}
public void ExecuteNonQueryWithOutputParameters()
{
FbCommand command = new FbCommand("EXECUTE PROCEDURE GETASCIIBLOB(?)", Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ID", FbDbType.VarChar).Direction = ParameterDirection.Input;
command.Parameters.Add("@CLOB_FIELD", FbDbType.Text).Direction = ParameterDirection.Output;
command.Parameters[0].Value = 1;
// This will fill output parameters values
command.ExecuteNonQuery();
// Check that the output parameter has a correct value
Assert.AreEqual("IRow Number 1", command.Parameters[1].Value, "Output parameter value is not valid");
// Dispose command - this will do a transaction commit
command.Dispose();
}