Как настроить взаимодействие пользовательских приложений и СУБД

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

Немного теории

Появление клиент-серверного подхода обработки данных вызвало спрос на эффективные технологии доступа к данным. Первыми были ODBC, ADO, BDE ... Но их разработчики стремились предоставить максимум сервиса при работе с данными на стороне клиента. Это тянуло за собой громоздкие, сложные компоненты, привязку ПО к рабочей станции клиента, сложные протоколы взаимодействия клиент-сервер. Но вот в начале 2000-х Borland предлагает интерфейс dbExpress. Максимально просто, максимально быстро. Клиентское приложение просто обрабатывает данные выбранные из БД сервера согласно логике приложения и обработав отправляет на сервер. В этом случае интерфейс dbExpress является действительно интерфейсом, а не помесью интерфейса, отображения и управления данными.

При разработке архитектуры dbExpress были решены следующие задачи:

• уменьшение затрачиваемых ресурсов;
• получение наибольшей скорости работы;
• кросс-платформенность;
• обеспечение простоты разработки драйверов;
• предоставление разработчику расширенных возможностей управления памятью и трафиком.

Доступ к данным БД сервера осуществляется при помощи небольших драйверов, реализованных в виде динамических библиотек. На нынешний момент для среды разработки RAD Studio 10.4 создано большое количество драйверов серверов баз данных, основные из них:

• DB2
• Firebird
• Informix
• InterBase
• MSSQL
• MySQL
• Oracle
• Sqlite
• ...

В статье Билла Тодда [1] в сжатой форме, но довольно просто и понятно объясняется архитектура provider/resolver. Используется четыре компонента для предоставления данных и их редактирования. Первый компонент — SQLConnection — предназначен для установления соединения между драйвером dbExpress и используемым сервером БД. О деталях применения и настройки компонента немного ниже, а сейчас хотелось бы обратить ваше внимание на очень нужное свойство SQLConnection — управление транзакциями. В dbExpress это реализуется несложно, необходимо добавить вызовы ApplyUpdates перед каждым Commit и CancelUpdates при Rollback. Механизм транзакций обеспечивает железобетонную уверенность в том, что ни один бит данных не будет потерян.

Дальше идут компоненты, которые предоставляют доступ к данным, получаемым оператором SELECT или вызовом хранимой процедуры. Третий компонент — DataSetProvider, и четвертый — ClientDataSet. Когда вы открываете ClientDataSet, он запрашивает данные у DataSetProvider. DataSetProvider открывает компонент, выполняющий запрос или хранимую процедуру, выбирает данные, закрывает этот компонент, и поставляет данные (и необходимые метаданные) компоненту ClientDataSet. ClientDataSet хранит данные в памяти, пока они просматриваются и модифицируются. При добавлении, удалении или обновлении записи, в коде или через пользовательский интерфейс, компонент ClientDataSet запоминает эти операции в памяти. Для обновления базы данных нужно вызвать метод ClientDataSet.ApplyUpdates. ApplyUpdates передает изменения компоненту DataSetProvider. Провайдер стартует транзакцию, затем создает и выполняет операторы SQL, соответствующие произведенным операциям над данными ClientDataSet. Если все операторы SQL были выполнены успешно, провайдер завершает транзакцию по commit; если нет — отменяет транзакцию по rollback. Изменения в базе данных могут не пройти, например, если изменения нарушают правила контроля данных, или если другой пользователь уже модифицировал эти данные определенным образом. При возникновении ошибки транзакция отменяется по rollback, и вызывается событие ClientDataSet OnReconcileError, предоставляя вам возможность обработки ошибок. Пятый компонент, который, по моему мнению, необходим для комфортной работы с интерфейсом dbExpress — DataSource. Компонент DataSource осуществляет связь между компонентом ClientDataSet и компонентами пользовательского отображения данных.

Долгие транзакции, которые присущи более ранним разработкам (ODBC, BDE и т.д.) заставляют сервер БД удерживать блокировки, которые снижают возможности многопользовательской обработки данных и отнимают ресурсы сервера. При архитектуре provider/resolver, транзакция существует только в тот момент, когда применяются обновления. Это существенно снижает требования к ресурсам и уменьшает вероятность блокировок, особенно при большом количестве пользователей сервера БД. Следует также отметить возможность «клонировать» курсор ClientDataSet позволяет просматривать одни и те же данные различными способами, одновременно. Например, можно просматривать одни и те же данные, отсортированные по разным столбцам.

Обычная схема клиент-сервер — приложения разрабатываются таким образом, чтобы выбирать небольшой объем данных для минимизации сетевого трафика и загрузки сервера БД. Даже если нужно работать с необычно большим количеством записей, то 10 тысяч записей, каждая по 100 байт, занимают 1 мегабайт памяти. В случаях, когда объем данных действительно большой, компоненты ClientDataSet и DataSetProvider имеют свойства и события, которые позволяют выбирать часть записей, редактировать их, удалять из памяти и затем получать новую порцию записей.

Несложный пример

Начинаем новый проект с одной формой. На форму необходимо установить SQLConnector из вкладки компонентов dbExpress. Это первый элемент «цепочки» подключения. Основная задача этого компонента подключится к СУБД. Необходимые установки в инспекторе объектов: Driver — MySQL, далее идут строки в окне Params.

• HostName — имя хоста или его IP,для тестовой задачи на своём компьютере — localhost.
• Database — имя имеющейся БД в среде MySQL.
• User_Name — пользователь MySQL.
• Password — его пароль.

Наиболее удобный вариант — это хранить параметры коннекта в ini-файле. При этом нужно соответствующим образом запрограммировать чтение параметров из ini-файла в SQLConnection.Params при старте приложения.

Снимите галочку LoginPrompt, что бы исключить ввод логина/пароля за каждым коннектом

Компилируем. В папку <Мой проект>\ Win32\ Debug \ необходимо скопировать libmySQL.dll из \bin СУБД MySQL в каталог с exe-модулем, либо libmySQL.dll должен лежать в \system32
Далее потребуются компоненты:

• SQLQuery — вкладка dbExpress. TSQLQuery предназначен для выполнения команды SQL на сервере баз данных. SQLQuery— это однонаправленный набор данных. В отличие от других наборов данных, однонаправленные наборы данных не буферизируют несколько записей в памяти. Из-за этого необходимо ориентироваться только на методы First и Next. В инспекторе объектов выставляем SQLConnection в SQLConnection1.
• DataSetProvider — вкладка DataAccess. В инспекторе объектов выставляем DataSet — SQLQuery1.
• ClientDataSet— вкладка DataAccess. В инспекторе объектов выставляем ProviderName — DataSetProvider1.
• DataSource— вкладка DataAccess. Ещё несколько слов о DataSource. Он задает набор данных, для которого компонент источника данных служит в качестве канала для элементов управления поддержки различных наборов данных. Для DataSet задаётся имя существующего компонента набора данных во время разработки или выполнения. Изменяя значение DataSet, приложение может эффективно использовать одни и те же элементы управления с учетом данных для отображения и редактирования в разных наборах данных. В тестовом примере две цепочки подключённых к одному SQLConnection. Для отображения результатов тестирования интерфейса на форме разместим DBGrid.

Создаём базу данных в среде MySQL. Это табличка из 5 записей с полями id и name. Source-файл есть на GitHub, как и исходники всего теста. Заполняем Params для SQLConnection1.

• HostName — localhost.
• Database — testconnect.
• User_Name — пользователь MySQL.
• Password — пароль.
• Снимаем галочку LoginPrompt.

Обработчик кнопки «Get Data» выгляядит следующим образом:

void __fastcall TForm1::Button1Click(TObject *Sender){
// Кнопка "Получить данные"
UnicodeString S;
int i;
//
	S = "select * from component";
	DBGrid1->DataSource = DataSource1;
   DBGrid1->DataSource->DataSet = ClientDataSet1;
	//
	ClientDataSet1->Close();
	SQLQuery1->SQL->Clear();
	SQLQuery1->SQL->Add(S);
	ClientDataSet1->Open();
	//
}
И несколько советов по эксплуатации. Для получения данных (SELECT) и обновления данных (INSERT, UPDETE, DELETE) целесообразно написать такие методы:
Для SELECT (т. е. выборка, не изменяя данных в БД)
int QuerySQL(UnicodeString S, int NumChain) {  
// Возвращает количество прочитанных записей
// S – строка SQL-запроса
// NumChain – № цепочки, по которой идёт выборка данных, их 
// желательно иметь несколько, т.к. данные могут выбираться из разных
// таблиц или представлений.
…
switch (NumChain) {
		case 1:
			DataModule1->ClientDataSet1->Close();
			DataModule1->SQLQuery1->SQL->Clear();
			DataModule1->SQLQuery1->SQL->Add(S);
			break; 
…

Метод обновления данных:

void ExecSQL(UnicodeString S, int NumChain) {
//	S – строка SQL-запроса 
// NumChain – № цепочки. Цепочки целесообразно использовать
// в методе, если есть несколько SQLConnection подключённых к 
// разным БД (и/или не только MySQL)  
…
switch (NumChain) {
		case 1:
DataModule1->SQLQuery1->SQL->Clear();
			DataModule1->SQLQuery1->SQL->Add(S);
			DataModule1->SQLQuery1->ExecSQL();
break;
…

Описание такого подхода к обработке данных имеет много преимуществ и, с моей точки зрения, одно из главных — обработка их на сервере. Пускай сервер работает, он в разы производительнее, клиенту просто выгружается результат, даже если у вас 200-2000 рабочих станций. И не нужно знать SQL в совершенстве, достаточно для, например, фильтра

S = "select * from component where id=4";

или сортировки

S = "select * from component order by name";

Волшебный TField

Объекты класса TField являются свойством объекта TDataSet. Свойство Fields объекта типа TDataSet позволяет обращаться к отдельным полям набора данных. Свойство Fields является массивом или набором объектов TField, динамически создающимся во время выполнения приложения. Элементы массива соответствуют колонкам таблицы. Объект TField не делает никаких предположений относительно типов данных, с которыми он связан. Он имеет несколько свойств, позволяющих установить или вернуть обратно значения поля, например, AsString, AsBoolean, AsFloat, AsInteger.

Для примера:

UnicodeString S;
TField *f;
int NumID;
// Здесь необходимо значение id в виде строки для компоновки 
// запроса S
f=DataModule1->ClientDataSet1->FieldByName("id");
S = "select * from component where id="+f->AsString;
// А здесь в виде обычного инта
NumID = f->AsInteger;

И напоследок. Описанная технология имеет многолетний период эксплуатации и ещё ни разу не подводила, отрабатывала на 100%. В мультике «80 дней вокруг света» Филеас Фогг всё время повторял гениальную фразу «Используй то, что под рукою и не ищи себе другое». Используйте dbExpress и будет вам счастье.

Использованные источники

Статья Bill Todd «Migrating Borland^® Database Engine Applications to dbExpress».

Послед Б.С. Borland^® C++ Builder 6. «Разработка приложений баз данных».

Ссылка на GitHub.

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті