Работа с Firebird и InterBase в Delphi. Часть 1.

---

Введение. Доступ к InterBase и Firebird из Delphi

В Delphi cуществует несколько способов работы с InterBase и Firebird. Мне известны, как минимум, 3 способа организации доступа к данным через IBProvider:

• dbGo (ADO Express) компоненты, работающие через библиотеку ADO.
• прямой доступ к COM-интерфейсам ADO, минуя компоненты dbGo.
• прямой доступ к COM-интерфейсам OLE DB при помощи сторонних VCL-компонент (OLE DB Direct/OLE DB Express).

Компоненты dbGo

dbGo — это VCL-компоненты от Borland, позволяющие работать с библиотекой ADO из Delphi и C++ Builder привычным для этих средств разработки способом. До Delphi 6 компоненты назывались ADOExpress.

Внешний вид панели компонентов dbGo в Delphi (RAD Studio 2007):

Состав dbGo-компонентов:

• Компонент TADOConnection — подключение к базе данных.
• Компонент TADODataSet — базовый набор данных. TADODataSet позволяет загружать данные таблиц, хранимых процедур и произвольных SQL запросов и передавать изменения обратно в БД. Важным условием является то, что выражение указанное в CommandText, обязательно должно возвращать набор данных. TADODataSet соответствует объекту ADODB.Recordset.
• Компонент TADOTable является упрощенными вариантом TADODataSet. Он позволяет загружать в память целые таблицы.
• Компоненты TADOQuery и TADOStoredProc предназначены для выполнения произвольных SQL-запросов и хранимых процедур.
• Компонент TADOCommand соответствуют объеку ADODB.Command. Фактически объединяет в себе возможности трех компонентов: TADOTable, TADOQuery, TADOStoredProc.

Создание подключения TADOConnection

Компонент TADOConnection осуществляет соединение с хранилищем данных. TADOConnection похож на компонент TSQLConnection. Разница между ними в том, что при работе с TSQLConnection параметры соединения устанавливались с помощью свойств ConnectionName, DriverName и Params. В TADOConnection все параметры подключения задаются через свойство ConnectionString. Кроме того в качестве строки подключения в TADOConnection может быть указано имя файла с параметрами подключения.

Для хранения параметров подключения в Windows существует специальный тип файлов Microsoft Data Link — это файл с расширением udl. С этим расширением ассоциирован универсальный редактор подключений. IBProvider поддерживает свои собственные табы, которые предоставляют удобный интерфейс для формирования параметров соединения. Для того чтобы использовать udl файл в своем приложении выполните следующие шаги:

• Создайте пустой файл с расширением .udl
• Откройте файл (Enter), появится связанный с данным расширением диалог для настройки подключения
• В списке OleDb провайдеров выберете IBProvider v3:

• Установите параметры подключения к БД и нажмите кнопку «Проверить подключение». На рисунке заданы следующие параметры:
  • Интегрированная аутентификация Firebird 2.1;
  • Источник — тестовая БД localhost:employee.fdb. Подключение определено без пути, т.к. БД прописана в файле aliases.conf;
  • Режим автоматического управления транзакциями: Включен;
  • Кодировка: WIN1251 или другая кодировка, поддерживаемая Firebird или InterBase;

Теперь добавьте на форму компонент TADOConnection. Задайте свойство: Login Promt=false и установите в качестве строки подключения ConnectionString созданный ранеее файл подключения udl:

Подробная информация о свойствах инициализации IBProvider.

Отображение данных через TADOTable

Компонент TADOTable позволяет загружать данные одной таблицы.

• Найстройте объект TADOConnection:
• Connected = true;
• Login Promt = false;
• Добавьте на форму объект TADOTable и настройте его следующим образом:
  • Connection = ADOConnection1;
  • TableName = имя таблицы из employee.fdb (CUSTOMER, EMPLOYEE, PROJECT и т.п.);
  • Active=true;
• Добавьте компонент TDataSource с вкладки Data Access. Настройки:
• DataSet = TADOTable1;
• Добавьте компонент TDBGrid с вкладки Data Controls. Настройки:
• DataSource = DataSource1;

В результате DBGrid отобразит данных той таблицы, которая была выбрана в объекте TADOTable:

Компонент TADODataSet

Компонент TADODataSet — это надстройка над объектом ADODB.Recordset. В отличии от TADOTable, TADODataSet может загружать не только таблицы, но и множества, возвращаемые хранимыми процедурами или SQL-запросами.

Перед началом работы с TADODataSet установите свойство Connection = ADOConnection1. Альтернативный вариант — эта указать строку подключения в свойстве ConnectionString. Но я настоятельно рекомендую для хранения подключения использовать отдельный объект TADOConnection.

В свойстве CommandText укажите текст команды для загрузки данных. Текстом команды могут являться:

• SQL — запрос. (Примеры: «SELECT * FROM EMPLOYEE», «EXEC ALL_LANGS» и т.д.). При этом свойство CommandType=cmdText.
• Имя таблицы. (Примеры: «EMPLOYEE», «CUSTOMER», «PROJECT»). CommandType=cmdTableDirect.
• Имя хранимой процедуры. (пример: «ALL_LANGS», «SHOW_LANGS»). CommandType=cmdStoredProc.

Команда в CommandText должна возвращать набор данных. К примеру, в тексте нельзя использовать INSERT, DELETE или UPDATE, а так же указывать хранимые процедуры, которые не возвращают набор данных (Recordset).

Перейдем к практическому примеру демонстрирующему возможности Delphi при работе c Firebird/InterBase:

• Создадим простейший редактор служащих на основе TADODataSet.
• Перемещаться по записям будем при помощи компонента TDBNavigator.
• Список служащих отобразим в TDBGrid.
• Для редактирования деталей воспользуемся контейнером TDBCtrlGrid в который поместим TDBLabel, TDBText, TDBComboBox.

Законченный пример показан на картинке:

Далее я приведу последовательность действий для воспроизведения примера саммостоятельно:

• Добавьте на форму и настройте компонент TADOConnection.
• Добавьте компонент TADODataSet и настройте его следующим образом:
  • CommandText = «select EMP_NO, FIRST_NAME, LAST_NAME, FULL_NAME, JOB_COUNTRY, DEPT_NO from EMPLOYEE»;
  • CommandType = cmdText;
  • Active = true;
• Добавьте компонент TDataSource с вкладки Data Access:
  • Установите свойство DataSet = ADODataSet1;
• Добавьте на форму 2 компонента c вкладки Data Controls: TDBNavigator и TDBCtrlGrid:
  • Каждому установите свойство DataSource = DataSource1;
  • У TDBCtrlGrid установите RowСount = 1;

TDBCtrlGrid — это компонент-контейнер. Он может отображать как одну так и несколько записей в зависимости от значения свойства RowCount. Внутри него расположены компоненты для редактирования данных.

TDBLabel — нередактируемое поле. При помощи этого компонента будем отображать колонку FULL_NAME.

Для редактирования полей FIRST_NAME, LAST_NAME добавим на форму 2 компонента TDBText, у которых установим свойство DataField.

Поле JOB_COUNTRY связано внешним ключом с таблицей COUNTRY. Для редактивания этого поля используем выпадающий список TDBComboBox содержащий коды стран. После установки поля DataField = JOB_COUNTRY, необходимо заполнить список данными из таблицы COUNTRY.

Кроме описанных в примере, на вкладке Data Controls расположены компоненты для редактирования изображений (TDBImage), многострочного текста (TDBMemo), отображения списков и т.д.

Передача изменений в БД — технология обновляемых множеств

IBProvider поддерживает замечательную возможность OLE DB — технологию обновляемых множеств. Благодаря этой технологии, в приведенном примере не пришлось прописывать в коде логику передачи изменений обратно в БД. Все изменения передавались автоматически через обновляемые множества. На данный момент технологию обновляемых множеств поддерживает только IBProvider v2. Пожалуйста учитывайте это при выборе провайдера для подключения.

Клиентские и серверные курсоры

Для перемещения по записям IBProvider использует курсоры. Они бывают серверные и клиентские.

Клиентский курсор — хранится на стороне клиента. После выполнения запроса всё результирующее множество загружается в оперативную память или swap-файл драйвера. Это может вызывать задержки при передаче больших объемов данных, особенно по сети. По умолчанию в dbGo используется именно клиентский тип курсора. Это дает преимущество в быстродействии на небольших объемах данных. С клиентскими курсорами могут работать все три провайдера из состава IBPRovider Professional Edition.

Серверный курсор используется при работе с большими наборами данных, которые невыгодно пересылать клиенту целиком. В результате сразу после выполнения запроса клиенту возвращается курсор, а данные считываются по мере необходимости. Кроме того реализация IBProvider, позволяет считывать BLOB-поля по мере обращения к ним, а не при получении всей записи. Это так же повышает производительность, в случае если в одной записи содержится несколько BLOB-колонок с большими данными. В текущей реализации серверный курсор можно использовать только с IBProvider v1, т.к. только он поддерживает 4-х байтные закладки, которые используются в dbGo для работы в режиме серверных курсоров. Более новые версии IBProvider используют уже 8-байтные закладки.

За тип курсора отвечает свойство CursorLocation. Оно может принимать значения clUseServer и clUseClient.

Какой провайдер выбрать?

В состав IBProvider Professional входят три OLE DB провайдера, которые предоставляют разработчику различные возможности.

IBProvider v1 — на данный момент единственный провайдер, позволяющий работать с dbGo компонентами в режиме серверных курсоров. Это связанно с ограниченным размером закладок в dbGo = 4 байта. Мы уже запланировали сделать IBProvider v3 совместимым с серверными курсорами dbGo в Delphi и C++ Builder. Как только это будет реализовано, IBProvider v1 станет историей. Для использования провайдера, укажите в строке подключения Provider=LCPI.IBProvider.1

IBProvider v2 поддерживает технологию обновляемых множеств. Благодаря им появляется возможность передавать изменения обратно в БД без явного указания текстов команд на вставку/удаление/обновление. Провайдер самостоятельно генерирует SQL-команды на основании select-выражений. Пример использования этой технологии я привел выше. Для использования провайдера, укажите в строке подключения Provider=LCPI.IBProvider.2. В наших ближайших планах реализовать поддержку обновляемых множеств в IBProvider v3.

IBProvider v3 самый современный и производительный из всех провайдеров. Он обладает уникальным набором технологий, поддерживает все кодовые страницы, специальные возможности последних версий серверов Firebird и InterBase, 64-битные операционные системы и множество других полезных функций. Если вы не планируете использовать обновляемые множества или серверные курсоры однозначно выбирайте IBProvider v3. Для этого укажите в строке подключения Provider=LCPI.IBProvider.3 или Provider=LCPI.IBProvider.

Если вам необходимо использовать возможности всех трех драйверов в одном Delphi-приложении, то в качестве временного решения, рекомендую использовать три одинаковых подключения к БД с разными драйверами. К примеру, все основные операции пропускать через IBProvider v3, обновляемые множества через IBProvider v2, а кешировать данные через серверные курсоры с IBProvider v1. После того как обновляемые множества и поддержка серверных курсоров dbGo будет доступна в IBProvider v3 можно будет оставить только одно подключение.

Компонент TADOQuery

Компонент TADOQuery предназначен для выполнения SQL-команд. Его можно рассматривать, как аналог компонента TSQLQuery из dbExpress. Связь с базой данных устанавливается через свойства Connection или ConnectionString. Текст запроса записывается в свойство SQL. Если запрос возвращает набор данных, следует использовать метод Open() или свойство Active=true. Если запрос не должен возвращать набор данных, то его необходимо выполнять с помощью метода ExecSQL. ExecSQL возвращает число записей, которые были обработаны во время выполнения запроса. Это же значение содержится в свойстве RowsAffected.

SQL-запросы с параметрами

Запросы могут быть параметризованными. При помощи параметров команды можно задавать условия и передавать данные серверу БД. Существуют два вида параметров: Именованные и позиционные. По умолчанию именованные параметры в IBProvider задаются через двоеточие (:), a позиционные через знак вопроса (?).

Для демонстрации работы компонента TADOQuery напишем пример, в котором рассмотрим три варианта его применения:

• Выполнение запросов с именованными параметрами. ExecSQL и RowsAffected.
• Выполнение запросов с позиционными параметрами. ExecSQL и RowsAffected.
• Выполнение запросов возвращающих множество. Метод Open() или свойство Active.

Законченный пример показан на картинке. Для выборки множества, а так же для выпонлнения операций вставки/удаления используется компонент TADOQuery:

Компоненты на форме связаны следующим образом: TADOQuery запрашивает данные у БД, которая указана в TADOConnection и передает их в компонент-посредник TDataSource. TDBGrid умеет отображать данные, которые загружены в TDataSource. Получается следующая схема взаимодействия: ADOConnection->ADOQuery->DataSource (вкладка Data Access)->DBGrid.

Рассмотрим первый вариант использования TADOQuery — выполнение запросов, которые не возвращают результирующее множество:

// перехватываем все ошибки обращения к БД Firebird/InterBase из Delphi
try

  // открываем подключенеи Delphi Firebird
  ADOConnection1.Open();
  ADOConnection1.BeginTrans();

  // Позиционные параметры
  with ADOQuery1 do
    begin
        // текст запроса
        with SQL do begin
          Clear;
          Add('DELETE FROM COUNTRY WHERE Country=? and Currency=?');
        end;

        with Parameters do begin
             Clear;
             // вариант 1
             AddParameter().Value :='Turkey';
             // вариант 2
             AddParameter();
             Items[1].Value :='Lira';
        end;

        // вставка одной записи
        ExecSQL();
        Memo1.Lines.Add ('ExecSQL. Affected Records: ' + IntToStr(ADOQuery1.RowsAffected));
  end;

  // Именованные параметры
  with ADOQuery1 do
    begin
        with SQL do begin
          Clear;
          Add('INSERT INTO COUNTRY (country, currency) VALUES (:c1, :c2)');
        end;

        // устанавливаем 2 параметра за раз
        Parameters.ParamValues['c1;c2'] := VarArrayOf (['Turkey', 'Lira']);

        ExecSQL();
        Memo1.Lines.Add ('ExecSQL. Affected Records: ' + IntToStr(ADOQuery1.RowsAffected));
    end;

    ADOConnection1.CommitTrans();
  except on E : Exception do
    // откатываем транзакцию в случае ошибки
    begin
      ADOConnection1.RollbackTrans();
      ShowMessage(E.ClassName+' db error: '+E.Message);
    end;
  end;

  // закрываем подключение
  ADOConnection1.Close();

В приведенном примере параметры команды устанавливаются двумя различными способами:

1. Одним выражением за раз:

ADOQuery1.Parameters.ParamValues['c1;c2'] := VarArrayOf (['Turkey', 'Lira']);

2. И каждый параметр отдельно:

with Parameters do begin
     Clear;
     AddParameter().Value :='Turkey';
     AddParameter();
     Items[1].Value :='Lira';
end;

Для выборки данных через TADOQuery используется SQL-выражение, содержащее команду SELECT, и метод Open():

try

// открываем подключение Delphi InterBase
ADOConnection1.Open();
ADOConnection1.BeginTrans();

  with ADOQuery1 do
   begin
     // текст запроса
     with SQL do begin
        Clear;
        Add('SELECT * FROM EMPLOYEE WHERE EMP_NO > ?');
     end;

     with Parameters do begin
        Clear;
        AddParameter().Value :='10';
     end;

     // открываем Recordset через метод Open(),
     // вместо этого так же можно установить свойство Active=true
     Open();
   end;

ADOConnection1.CommitTrans();

except on E : Exception do
 // откат транзакции в случае ошибки
 begin
    ADOConnection1.RollbackTrans();
    ShowMessage(E.ClassName+' db error: '+E.Message);
 end;

ADOConnection1.Close();

TADOStoredProc

Компонент TADOStoredProc позволяет выполнять хранимые процедуры InterBase и Firebird. Принцип работы TADOStoredProc такой же как и у TADOQuery:

• Подключение задается в свойстве Connection или ConnectionString.
• Метод Open() или свойство Active=true позволяют получить результирующее множество.
• Метод ExecSQL позволяет выполнить процедуру и вернуть результат в наборе OUT-параметров.

Единственные отличием является свойство ProcedureName в котором определяется имя хранимой процедуры. Свойство SQL в компоненте TADOStoredProc отсутствует

Хранимая процедура может возвращать результат двумя способами:

• в виде результирующего множества;
• в виде выходных OUT-параметров;

Для получения результирующего множества используется оператор SELECT:

select * from stored_procedure_name(…)

Для выполнения процедуры, которая ничего не возвращает или возвращает OUT-параметры необходимо использовать инструкцию exec:

exec procedure stored_procedure_name

Компонент TADOCommand

TADOCommand — команда, которая передается серверу, для того чтобы считать или изменить данные. Компонент фактически объединяет в себе возможности 3-х рассмотренных компонентов: TADOTable, TADOQuery, TADOStoredProc.

Поведение TADOCommand меняется в зависимости от свойства CommandType. Оно может принимать следующие значения:

• cmdText — текст команды содержит SQL-запрос. Поведение аналогично TADOQuery.
• cmdStoredProc — в свойстве CommandText задано имя хранимой процедуры. Поведение соответствует компоненту TADOStoredProc.
• cmdTable и cmdTableDirect — означают, что в тексте указано имя таблицы, которую необходимо загрузить. Для Firebird и InterBase значения cmdTable и cmdTableDirect идентичны. Соответствует TADOTable.

Работа с транзакциями

Механизмы управления транзакциями в IBProvider одинаковы для всех средств разработки. Ранее я подробно расматривал транзакции в первой части руководства по работе с InterBase и Firebird в ADO .Net. Рекомендую вам обратить внимание на следующие его главы:

• Автоматическое управление транзакциями;
• Уровни изоляции транзакций;
• Именованные точки сохранения;
• Commit Retain и Rollback Retain и Firebird;

Полезные ссылки

• Примеры работы с Firebird из Delphi минуя dbGo, напрямую через COM-объекты ADO.
• Скачать Firebird.

Теги статьи: Firebird delphi, InterBase Delphi, dbGo, ADO Express, Firebird OLE DB Direct/Express.

---