Работа с 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 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.