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

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

Отображение данных через 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.

СУБД FireBird является одной из самых популярных в мире бесплатных, кросплатформенных систем управления базами данных с открытым исходным кодом. Она была разработана на основе исходного кода СУБД Interbase и развивается сегодня независимым международным сообществом. По надёжности, производительности и функциональным возможностям эта система мало в чём уступает признанным лидерам своего класса - Oracle и Microsoft SQL Server.

Firebird полностью поддерживает стандартны ANSI в синтаксисе языка SQL и может работать под управлением многих операционных систем - Windows, Linux, MacOS, Solaris и различных Unix-платформах. Среди достоинств этой системы использование очень развитого языка для хранимых процедур и триггеров. Предшественник Firebird, СУБД Interbase использовалась в информационных системах начиная с 1981 года.

Firebird это свободный проект, поддерживаемый многими программистами и специалистами из других областей по всему миру. Его начало было положено 25 июля 2000 года, когда корпорация Inprise Corp (ныне известная как Borland Software Corp) открыла исходные коды своей СУБД Interbase, которая использовалась в различных информационных системах начиная с 1981 года.

Firebird полностью бесплатна, она не требует ни регистрации, ни оплаты за поддержку. Исходный код этой системы открыт и любой желающий может разрабатывать на его базе собственные некоммерческие проекты, при условии соблюдения требований лицензии IDPL, по которой распространяется Firebird.

Firebird (FirebirdSQL) — компактная, кроссплатформенная, свободная система управления базами данных (СУБД), работающая на Linux, Microsoft Windows и разнообразных Unix платформах.

Firebird основан на исходном коде InterBase 6.0 который был выпущен как Open Source компанией Borland в августе 2000 года. История Interbase начинается в 1984 году, таким образом, продукт является наследником более чем 20-летнего опыта работы с реляционными базами данных

В качестве преимуществ Firebird можно отметить многоверсионную архитектуру, обеспечивающую параллельную обработку оперативных и аналитических запросов (это возможно потому, что читающие пользователи не блокируют пишущих), компактность (дистрибутив 5Mb), высокую эффективность и мощную языковую поддержку для хранимых процедур и триггеров.

Firebird используется в различных промышленных системах (складские и хозяйственные, финансовый и государственный сектора) с 2001 г. Это коммерчески независимый проект C и C++ программистов, технических советников и разработчиков мультиплатформенных систем управления базами данных, основанный на исходном коде, выпущенном корпорацией Borland 25 июля 2000 года в виде свободной версии Interbase 6.0.

Firebird является сервером баз данных. Один сервер Firebird может обрабатывать несколько сотен независимых баз данных, каждую с множеством пользовательских соединений. Он является полностью свободным от лицензионных отчислений даже для коммерческого использования.

Среди недостатков: отсутствие кэша результатов запросов, полнотекстовых индексов.

Архитектуры сервера

Существует четыре различных взаимозаменяемых архитектуры сервера.

Классик сервер (англ. ClassicServer) — один процесс на одно соединение; поддержка многопроцессорных машин.

Суперсервер (англ. SuperServer) — все соединения используют один процесс, меньшие требования к памяти при большем быстродействии; для однопроцессорных машин.

СуперКлассик (англ. SuperClassic Server) — один процесс, но свой поток на каждое соединение. Данная архитектура введена в версии 2.5.

Встраиваемая (англ. Embedded) версия — весь движок содержится в одной библиотеке с именем клиентской библиотеки сервера, идеально подходит для однопользовательских систем, не требует инсталляции.

Все архитектуры используют одинаковый формат файла базы данных, таким образом в любой момент можно переключиться на другую архитектуру.

Соответствие стандарту SQL

Firebird полностью поддерживает SQL-92 Entry Level 1 и реализует большую часть стандарта SQL-99 c некоторыми очень полезными дополнениями. Это включает выражения DML/DDL, синтаксис объединений FULL/LEFT/RIGHT [OUTER] JOIN, выражения UNION, DISTINCT, подзапросы (IN, EXISTS), встроенные функции (AVG, SUM, MIN, MAX, COALESCE, CASE, ..), ограничения целостности (PRIMARY KEY, UNIQUE, FOREIGN KEY), и все общие типы данных SQL.

Firebird также реализует ограничения проверки (check constraints) на уровне доменов и полей, отображения (views), исключения, роли и управление правами доступа. Для более подробной информации см. Firebird Reference Guide и Release Notes.

Требования к аппаратному обеспечению

Firebird работает на 32- и 64-разрядных версиях Windows, Linux, также на MacOS X, HP-UX, FreeBSD, и др., на аппаратных платформах x86, x64 и PowerPC, Sparc и многих других, и поддерживает легкий переход между этими платформами. Может использоваться даже не очень мощное оборудование, особенно под Linux. И как в любой СУБД, на производительность влияют: количество памяти, скорость работы дисковой подсистемы, и т. д. Рекомендации для выбора аппаратного обеспечения зависят от требования к системе, прогнозируемого размера базы данных, количества пользователей, и т. д. Допустимо начинать с минимальной конфигурации, расширяя её по мере надобности.

Средства доступа к серверу

Firebird поддерживает множество способов доступа, включая: собственные наборы компонент для C/C++, Delphi, классы для ADO, ODBC, JDBC (Jaybird), драйверы для Python, PHP, драйвер OLE DB, dbExpress, провайдер данных .NET и прямой доступ с использованием клиентской библиотеки сервера (fbclient.dll или GDS32.dll)

Физические ограничения

Firebird поддерживает большие базы данных. Базы данных могут быть расположены в нескольких файлах, предельный размер которых зависит от операционной системы. Теоретический предел в настоящее время составляет 64TB для одного файла базы данных, таким образом, главные ограничения накладываются файловой системой и местом на жёстком диске.

Практически база данных объемом 1 ТБ стабильно работает, что доказывает проведенный тест.

Максимальная длина одной записи (суммарно все поля за исключением полей с типом BLOB) равна 64 КБ.

Версии сервера

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