Дата публикации: 20.07.2007

Обновлено: 12.11.2017

Использование новых возможностей Firebird 2.1. в ADO.NET

Keywords: Firebird 2.1, Firebird, ADO .Net, c#, .Net провайдер, Firebird 2.

В последние дни июня 2007 года появились первые беты новой версии Firebird 2.1. Среди нововведений необходимо выделить глобальные триггеры базы данных, общие табличные выражения (CTE), временные таблицы, возможность мониторинга базы данных через SQL, отмена зависших запросов, а так же новые SQL инструкции.

Мы решили не отставать с реализацией новых возможностей и рады сообщить о выходе новой версий IBProvider Professional Edition с полноценной поддержкой Firebird 2.1.

RETURNING

Наконец появилась возможность возвращать поля при помощи инструкции RETURNING в запросах UPDATE и DELETE, а так же в новой инструкции UPDATE OR INSERT. Напомню, что в Firebird 2.0 RETURNING поддерживался только для операций вставки. Проиллюстрирую это примером на C#:

using (OleDbConnection con = ConnectionProvider.GetConnection())
  {
     // Описание: UPDATE OR INSERT … RETURNING, UPDATE .. RETURNING, DELETE .. RETURNING, Firebird 2.1 ADO .Net (c#)
     con.Open();
     OleDbTransaction trans = con.BeginTransaction();

  // 1. вставка новой записи при помощи инструкции UPDATE OR INSERT .. RETURNING
     OleDbCommand cmd = new OleDbCommand(
     «UPDATE OR INSERT INTO country (country, currency) VALUES (?, ?) RETURNING country, currency», con, trans);

     // входящие параметры
     cmd.Parameters.AddWithValue(«country», «Russia»);
     cmd.Parameters.AddWithValue(«currency», «Kopec»);

     // выходные параметры для RETURNING
     OleDbParameter outpCountry = new OleDbParameter(«out_country», OleDbType.BSTR);
     outpCountry.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCountry);

     OleDbParameter outpCurrency = new OleDbParameter(«out_currency», OleDbType.BSTR);
     outpCurrency.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCurrency);

     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «inserting record. FAIL»);
     Assert.AreEqual(«Russia», outpCountry.Value, «getting out parameter ‘COUNTRY’ from UPDATE or INSERT command. FAIL»);
     Assert.AreEqual(«Kopec», outpCurrency.Value, «getting out parameter ‘CURRENCY’ from UPDATE or INSERT command. FAIL»);

  // 2. обновление записи и возвращение обновленных полей через инструкцию UPDATE .. RETURNING
     cmd = new OleDbCommand(«UPDATE country SET currency = ? WHERE country = ? RETURNING country, currency», con, trans);

     // входящие параметры
     cmd.Parameters.AddWithValue(«currency», «Rouble»);
     cmd.Parameters.AddWithValue(«country», «Russia»);

     // выходные параметры для RETURNING
     outpCountry = new OleDbParameter(«out_country», OleDbType.BSTR);
     outpCountry.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCountry);

     outpCurrency = new OleDbParameter(«out_currency», OleDbType.BSTR);
     outpCurrency.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCurrency);

     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «updating record. FAIL»);
     Assert.AreEqual(«Russia», outpCountry.Value, «getting out parameter ‘COUNTRY’ from UPDATE command. FAIL»);
     Assert.AreEqual(«Rouble», outpCurrency.Value, «getting out parameter ‘CURRENCY’ from UPDATE command. FAIL»);

  // 3. DELETE .. RETURNING             

     cmd = new OleDbCommand(«DELETE FROM country WHERE country =:country RETURNING country, currency», con, trans);

     // входящие параметры
     cmd.Parameters.AddWithValue(«country», «Russia»);

     // выходные параметры для RETURNING
     outpCountry = new OleDbParameter(«out_country», OleDbType.BSTR);
     outpCountry.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCountry);

     outpCurrency = new OleDbParameter(«out_currency», OleDbType.BSTR);
     outpCurrency.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCurrency);

     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «deleting record. FAIL»);
     Assert.AreEqual(«Russia», outpCountry.Value, «getting out parameter ‘COUNTRY’ from DELETE command. FAIL»);
     Assert.AreEqual(«Rouble», outpCurrency.Value, «getting out parameter ‘CURRENCY’ from DELETE command. FAIL»);


     trans.Commit();
 }

При использовании RETURNING необходимо определять хотя бы один входящий параметр. В примере — это именованный параметр :country:

DELETE FROM country WHERE country =:country RETURNING country, currency

В случае запроса без входящих параметров ADO .Net проигнориует выходные параметры, а сервер вернет ошибку:

DELETE FROM country WHERE country =‘Russia’ RETURNING country, currency

UPDATE OR INSERT

Еще одна, несомненно полезная, инструкция UPDATE OR INSERT позволяет выполнять одну команду для операций вставки и обновления записей. Сервер сам определяет необходимое действие определяя наличие записей в базе данных по совпадению колонок указанных в MATCHING (fields). Если в запросе не определено сопоставление, то по умолчанию используется первичный ключ.

using (OleDbConnection con = ConnectionProvider.GetConnection())
 {
     // Описание: UPDATE OR INSERT, Firebird 2.1 ADO .Net (c#)
     con.Open();
     OleDbTransaction trans = con.BeginTransaction();

     // вставка новой записи
     // используется сопоставление по первичному ключу «country»
     OleDbCommand cmd = new OleDbCommand(
     «UPDATE OR INSERT into COUNTRY (country, currency) values (?, ?)», con, trans);

     cmd.Parameters.AddWithValue(«country», «Russia»);
     cmd.Parameters.AddWithValue(«currency», «Kopec»);

     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «inserting record. FAIL»);

     // обновление записи с использованием все той же командой
     cmd.Parameters[«country»].Value = «Russia»;
     cmd.Parameters[«currency»].Value = «Rouble»; // новое значение
     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «updating record. FAIL»);

     // обновление записи при определении сопоставления по полю ‘Currency’
     cmd = new OleDbCommand(
     «UPDATE OR INSERT into COUNTRY (country, currency) values (?, ?) MATCHING (currency) returning country, currency», con, trans);

     cmd.Parameters.AddWithValue(«country», «RF»);
     cmd.Parameters.AddWithValue(«currency», «Rouble»);
     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «updating record fail.»);

     // удаление записи
     OleDbCommand cmd_delete = new OleDbCommand(string.Format(
         «delete from COUNTRY where country ='{0}'», «Russian Federaion»), con, trans);
     Assert.AreEqual(1, cmd_delete.ExecuteNonQuery(), «deleting record fail»);

     trans.Commit();
 }

The LIST() Function

Данная функция позволяет объединить поля из нескольких записей в одну строку. Она имеет следующий формат:

<list function> ::=
  LIST ‘(‘ [ {ALL | DISTINCT} ] <value expression> [‘,’ <delimiter value>] ‘)’

<delimiter value> ::= { <string literal> | <parameter> | <variable> }

Далее простой пример который формирует строку из списка клиентов:

using (OleDbConnection con = ConnectionProvider.GetConnection())
{
  // Описание: LIST function, Firebird 2.1 c# (ADO .Net)
  con.Open();
  OleDbTransaction trans = con.BeginTransaction();

  // выбираем всех клиентов и формируем строку с разделителями
  OleDbCommand cmd = new OleDbCommand(
  string.Format(«SELECT LIST(CUSTOMER, ‘,{0}’) FROM CUSTOMER», Environment.NewLine) , con, trans);

  // запрос возвращает единственное значение
  string customer_list = cmd.ExecuteScalar().ToString();
  Console.WriteLine(customer_list);

  trans.Commit();
}

Мониторинг базы данных через SQL

Статистика базы данных

В новой версии СУБД Firebird 2.1. появились системные таблицы мониторинга. При их помощи можно прочитать информацию об активных подключениях, запросах, статистику базы данных, а так же отменять зависшие запросы, что может быть тоже весьма полезно.

Просто пример выводящий статистику по базе данных:

using (OleDbConnection con = ConnectionProvider.GetConnection())
 {
     // Описание: MONITORING TABLES Firebird 2.1 c# (ADO .Net)
     con.Open();
     OleDbTransaction trans = con.BeginTransaction();

     OleDbCommand cmd = new OleDbCommand(«select * from MON$DATABASE», con, trans);

  using(OleDbDataReader rdr = cmd.ExecuteReader())
      while (rdr.Read())
      {
          Console.WriteLine(«[DATABASE INFO]»);
          Console.WriteLine(«Database name:       {0}», rdr[«MON$DATABASE_NAME»]);
          Console.WriteLine(«Dialect:             {0}», rdr[«MON$SQL_DIALECT»]);
          Console.WriteLine(«Page size:           {0}», rdr[«MON$PAGE_SIZE»]);
          Console.WriteLine(«Pages:               {0}», rdr[«MON$PAGES»]);
          Console.WriteLine(«Creation date:       {0}», rdr[«MON$CREATION_DATE»]);
          Console.WriteLine(«Read only database:  {0}», Convert.ToBoolean(rdr[«MON$READ_ONLY»]));

          Console.WriteLine(«ODS Version:         {0}.{1}», rdr[«MON$ODS_MAJOR»], rdr[«MON$ODS_MINOR»]);
          Console.WriteLine(«Oldest transaction:  {0}», rdr[«MON$OLDEST_TRANSACTION»]);
          Console.WriteLine(«Oldest active:       {0}», rdr[«MON$OLDEST_ACTIVE»]);
          Console.WriteLine(«Oldest snapshot:     {0}», rdr[«MON$OLDEST_SNAPSHOT»]);

          // enumeration from RDB$TYPES WHERE RDB$FIELD_NAME =’MON$SHUTDOWN_MODE’
          Console.WriteLine(«Shutdown mode:       {0}», rdr[«MON$SHUTDOWN_MODE»]);

                     Console.WriteLine(«Sweep interval:      {0}», rdr[«MON$SWEEP_INTERVAL»]);
                 }

             trans.Commit();
         }

Уровень изоляции текущей транзакции

Для определения соответствия уровней изоляции транзации в ADO .Net и Firebird был написан тестовый пример, использующий системную таблицу MON$TRANSACTIONS. Следующий запрос выбирает номер текущей транзакции и её уровень изоляции:

select CURRENT_TRANSACTION, trans.MON$ISOLATION_MODE, types.RDB$TYPE_NAME
from MON$TRANSACTIONS trans
join RDB$TYPES types
on types.RDB$FIELD_NAME = ‘MON$ISOLATION_MODE’ and types.RDB$TYPE = trans.MON$ISOLATION_MODE
where MON$TRANSACTION_ID = CURRENT_TRANSACTION

Результы тестов для всех уровней изоляции перечисленных в ADO .Net сведены в таблицу:

ADO .Net Isolation level	Firebird Isolation level
Chaos	READ_COMMITTED_NO_VERSION
ReadUncommitted	Не поддерживается
ReadCommitted	READ_COMMITTED_NO_VERSION
RepeatableRead	CONCURRENCY (тоже что и SNAPHOT)
Serializable	CONSISTENCY
Snapshot	Не поддерживается
Unspecified	READ_COMMITTED_NO_VERSION

Уровень изоляции READ_COMMITTED_NO_VERSION устанавливается по умолчанию. Кроме того в Firebird есть еще один уровень изоляции, который нельзя установить через перечисление ADO .Net — это READ_COMMITTED_VERSION. Для использования этого режима необходимо стартовать транзакцию через инструкцию SET TRANSACTION.

Отмена зависшего запроса

Все выполняющиеся на сервере запросы помещаются в таблице MON$STATEMENTS. Для отмены любого запроса необходимо удалить соотвествующую запись из этой таблицы например так:

delete from mon$statements where mon$sql_text = :sql_text

Если текст sql-запроса неопределен для выявления зависшего запроса можно воспользоваться другими полями, например MON$STATE (определяет активность запроса [1 — выполняется, 0 — нет]) или MON$TIMESTAMP — время поступления запроса серверу.

Заключение

СУБД Firebird непрерывно развивается, вместе с ней развивается IBProvider. Мы считаем что применение бесплатной СУБД и современных компонентов для Firebird и InterBase позволяет создавать экономически выгодные и качественные решения.

Для комфортной работы с Firebird в .Net (c#, vb .Net, и т.д.) и на других платформах воспользуйтесь драйвером IBProvider Professional Edition. Список всех изменений в новой версии Firebird 2.1 можно найти в официальном документе Release Notes

Для быстрого старта необходимо:

Скачать Firebird 2.1 и установить его.
Установить IBProvider Professional Edition.
Воспользоваться средством разработки, поддерживающим COM или OLE DB: Visual Studio, Borland C++ Builder, Delphi, WSH, .Net, VBA, Visual Basic, и т.д.

Дополнительная информация:

Быстрый старт Firebird Delphi и InterBase Delphi.
Быстрый старт с Visual Studio .Net: Работа с Firebird и InterBase в ADO.Net (c#).
Управляющие последовательности ODBC.
ODBC Firebird driver, ODBC InterBase или OLE DB?

Автор материала: Меркулов Андрей Александрович

Дата публикации: 20.07.2007. Права на материал принадлежат: IBProvider. При перепечатке ссылка на сайт https://www.ibprovider.com/rus обязательна.

Вся документация

Использование новых возможностей Firebird 2.1. в ADO.NET

RETURNING

UPDATE OR INSERT

The LIST() Function

Мониторинг базы данных через SQL

Статистика базы данных

Уровень изоляции текущей транзакции

Отмена зависшего запроса

Заключение

Дополнительная информация:

Действия

InterBase быстрый старт

Firebird быстрый старт

Общие вопросы

ADO.Net

MS SQL Linked Server и BI

Контактная информация

Действия