Базовые приемы работы с SQL из Delphi

Источник: Константин Заровный

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

   На данный момент существует достаточно много всяческих описаний самого языка SQL и я больше обращу свое внимание на механизм взаимодействие программы (создаваемой на DELPHI) и сервера базы данных.

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

   Отсюда и появляется определение клиентской программы - это программа, которая составляет запросы на языке SQL, передает их серверу и выдает результаты обработки данного запроса пользователю.

   Как правило база данных создается самим программистом, при помощи специализированного программного обеспечения. А вот работой с данными занимается программа.

  • Первое что должна сделать программа во вновь созданной базе данных - это позволить пользователю наполнить базу данных самими данными.
  • Потом программа должна позволить пользователю просмотреть то, что он ввел в БД
  • И если пользователю чтото не понравилось, то модифицировать данные
  • И на последок сделать хитрый отчет.

Подключение к Базе данных

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

   Предположим, что:

  1. Уже выбран сервер БД и мы знаем его имя.
  2. Тип выбранного сервера поддерживается компонентами доступа, которые вы используете (в примере используется erDA).
  3. База данных уже создана соединение с ней уже проверенно стандартными средствами сервера и все работает.
  4. В Delphi уже создана форма (или DataModule), в которой мы будем создавать это подключение.

   Для описания типа сервера и параметров подключения к нему как правило используетсяобъект в своем названии содержащий DataBase или Connection.(В erDA это TerDataBase, в ADO это TADOConnection, в BDE это TDataBase). Обычно рекомендуется на все приложение создается один такой объект, и вся последующая работа идет через него. В случае BDE и ADO можно обойтись и без этого объекта, но тогда сложно организовать красивый диалог для входа в систему, трудно сделать кнопку отключения от БД. и практически невозможно контролировать количество активных подключений (сессий) - которые на данный момент установила программа с сервером.

   Этот объект создается как правило либо в главной форме (если приложение небольшое), либо в специально созданном DataModule. Для его создания достаточно выбрать нужный компонент в палитре компонентов и указать мышкой - в какое место его положить. Как правило этот объект снабжен редактором свойств, который вызывается двойным нажатием на иконку компонента. Либо, если редактора нет, то свойства необходимо задать при помощи ObjectInspector.

   После создания компонентов желательно присвоить ему читаемое имя. Обзовем его DB.

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

   В связи с тем, что BDE и ADO автоматически создают подключения к БД когда это необходимо дальнейшая настройка не требуется.

   В erDA есть возможность контролировать количество активных сессий, и то, через какое именно подключение будет выполнен запрос. Для этого используется объект TerTransaction там же описаны методы для работы с транзакцией. В связи с тем, что MSSQL не позволяет одновременно держать несколько открытых и не "вытащенных" запросов на одну сессию, в TerTransaction реализован механизм, который при открытии следующего запроса либо останавливает, либо выкачивает весь предыдущий запрос на клиента (вариант поведения можно выбрать при помощи свойства FreeAction компонента TerTransaction).

   Обычно просто достаточно положить TerTransaction на форму вместе с TerDataBase и прописать свойство DataBase. Если вы не хотите добиться какого нибудь другого эффекта. Я например разделяю запросы на 2 части 1-я запросы к небольшим справочникам и 2-я запросы которые могут возвращать большое количество записей. Для 1-й части создаю свою Transaction для второй свою со свойством FreeAction=erStopFetch. В результате - если пользователь откроет 2 "длинных" запроса, то вытаскивание первого просто остановится на том месте, где он был на момент открытия 2-го запроса.

   Дело в том, что если на сервере оставлять не вытащенные данные - как это делает BDE, то на каждый "не вытащенный" запрос создается свое подключение. В случае если пользователь забыл закрыть этот запрос, то он так и остается висеть и занимать ресурсы сервера, которые все-таки не безграничные. В ADO по умолчанию все результаты запроса сразу вытаскиваются на клиента, но это иногда приводит к долгому ожиданию окончания процесса кэширования.

   При этом каждый активный запрос автоматически накладывает ряд блокировок. Хотя при небольшом количестве пользователей это не принципиально, но при увеличении количества пользователей с этим приходится считаться. К сожалению, обычно, когда до этого доходит дело (после написания программы), решать эту проблему очень даже не просто. Гораздо проще, когда задача работы программы с большим количеством клиентов ставилась изначально, и сразу были приняты меры по уменьшению количества блокировок.

   Для того, чтобы было проще разбираться в том, что здесь написано создана следующая демонстрационная програмка. В которой сделано то, о чем здесь расказано. Эта программа работает c серверами при помощи erDA - компоненты, которые вы можете бесплатно скачать по следующему адресу. И для отображения данных использует DBGridEh из ehLib - тоже бесплатная библиоека для xUSSR с очень хорошим dbGrideh - объект позволяющий отображать данные в виде сетки (почти как в Excel).

   В демонстрационной программе используется сервер БД MSSQL имя сервера Test, база данных TestDB.

   Для подключения к нему необходимо прописать следующие свойства TerDataBase:

 

dllName 

er_mssql.dll

Params 

Server Name=Test
Database Name=TestDB

   примечание: для данного драйвера, при данных Params (если не указаны имя пользователя и пароль) будет использоваться Windows авторизация.

   Теперь остается установить свойство (у компонента Trn) DataBase=DB и протестировать настройки установив свойство Active=True.

   Если вы все правильно сделали, то никаких ошибок не произойдет и свойство Active обоих компонентов послушно установится в True и переходим к следующему пункту.

   Если ничего не получилось, то читаем документацию.

Создаем выборку

   Для работы с данными в Delphi используется компонент, который является наследником от TDataSet. Если вы используете BDE то это TTable или TQuery. Если вы используете ADO, то это ADOTable или ADOQuery. Я использую erDA там это TerQuery. TDataset в свою очередь является своеобразной прослойкой между визуальными компонентами отображающими данные пользователю и механизмом реализующим непосредственное взаимодействие с сервером.

   Разница между объектами (TTable,TADOTable) и (TQuery, TADOQuery) состоит в том, что TTable и TADOTable настраиваются на одну таблицу и работают с ней автоматически, в то время как TQuery и TADOQuery могут отображать результаты произвольного SQL запроса. При работе с SQL сервером в любом случае работа с сервером заключается в формировании SQL запросов и отображении результатов выполнения запросов. Поэтому хотя и имеется возможность использовать TTable и TADOTable, при программировании проще пользоваться TQuery и TADOQuery в связи с тем, что они предоставляют больше возможностей для фильтрации и сортировки результатов запроса. В дальнейшем я так же буду называть данный объект DataSet.

   У нас уже:

  1. Есть подключение к серверу.
  2. Мы знакомы с синтаксисом языка SQL и умеем применять его на практике.
  3. Мы ознакомились со структурой БД.

   Добавляем на форму компонент Называем его Master и прописываем свойство подключения. (Так как у меня это TerQuery, Устанавливаем свойство Transaction =Trn.)

   Составив следующий запрос прописываем его в свойство SQL

SELECT
  A_Master.A_Master_ID A_Master_ID,
  A_Master.A_MasterCod A_MasterCod,
  A_Master.A_MasterName_ID A_MasterName_ID,
  A_Master.A_MasterName_ID1 A_MasterName_ID1,
  A_Master.Perc Perc,
  A_MasterName.A_MasterName A_MasterName,
  A_MasterName1.A_MasterName A_MasterName1,
  SUM (A_DetailSM) A_DetailSM,
  SUM (A_DetailSM)* Perc/100 Sm00
FROM
  dbo.A_Master
   LEFT OUTER JOIN dbo.A_MasterName ON A_Master.A_MasterName_ID = A_MasterName.A_MasterName_ID
   LEFT OUTER JOIN A_DETAIL ON A_Detail.A_Master_ID=A_Master.A_Master_ID
   LEFT OUTER JOIN dbo.A_MasterName A_MasterName1 ON A_Master.A_MasterName_ID1 = A_MasterName1.A_MasterName_ID
  GROUP BY
    A_Master.A_Master_ID,
    bsp; A_Master.A_MasterCod,
    bsp; A_Master.A_MasterName_ID,
    bsp; A_Master.A_MasterName_ID1,
    bsp; A_MasterName.A_MasterName,
    bsp; A_MasterName1.A_MasterName,
    bsp; A_Master.Perc

   Теперь можно проверить отсутствие ошибок установив свойство Active=True.

   Для полноты двойным нажатием на Master запустим редактор полей. И нажав правой клавишей мыши на редакторе полей выбрав пункт Add all fields - добавим все поля. Еще отредактируем свойство DisplayLabel так, чтобы в этом свойстве находилось русское описание колонок.

   Взаимодействие DataSet и визуального компонента происходит посредством TDataSource, который тоже надо добавить на форму рядом с DataSet (обозвав его MasterDS). И установить его свойство Dataset=Master

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

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

   Для отображения данных наиболее часто применяется DBGrid, и компонент, который отображают данные в виде Delphi Object Inspector. Так же можно применять целый ряд других компонентов из закладки DataControl (или их аналогов).

   К сожалению в Delphi не включен объект типа инспектора и также в DBGrid отсутствуют ряд необходимых функций, поэтому сразу скажу, что я в своей работе использую DBGridEh, и dxDbInspector. В демонстрационной программе я использую только DBGridEh - потому, что он бесплатный.

   Добавляем компонент DBGridEh (назовем его MasterGrid) на форму Размещаем его и установив свойство DataSource=MasterDs и он готов к использованию. Опять же двойным нажатием можно запустить редактор колонок и отредактировать список отображаемых полей и их ширину и другие настройки, но это надо более детально читать описание тех компонентов - которые вы используете.

   Более подробно об этом можете посмотреть в демонстрашке DBGridEh.

   Следующий пункт, который нам предстоит выполнить - это настройка редактирования БД.

Настройка редактирования БД

   Итак у нас на данный момент уже есть настроенный DataSet и он уже отображается в DBGride. Для того, чтобы данный набор редактировался необходимо настроить то, каким образом будут происходить обновления.

   В случае если вы используете BDE, то необходимо использовать UpdateSQL. Для этого надо добавить данный компонент на форму с DataSet, и прописать свойство Этого Dataset (UpdateObject= этому UpdateSQL). После этого запустить двойным нажатием на иконку UpdateObject редактор - выбрать обновляемую таблицу, обновляемые поля и нажать кнопку Generate SQL. При этом редактор автоматически создаст три SQL запроса. Осталось прописать свойство LiveUpdate=True и почти все.

   Для обновления своего набора данных я использую TerUpdateSQL. Добавляю его на форму и прописываю таблицу, которую мы будем редактировать. Здесь есть дополнительные возможности для генерирования SQL запросов на обновление. В данном случае я выбрал метод umWhereKeyOnly. При этом методе будет автоматически составляться запрос на Update измененных полей при этом в качестве условия будут использоваться поля с установленным флагом pfInKey. Где в условии будут фигурировать только ключевые поля.

   Итого: мы обновляем поля: A_Master_ID, A_MasterCod, A_MasterName_ID1, A_MasterName_ID1 для них соответственно в редакторе полей устнавливаем атрибут pfInUpdate=True, для остальных pfInUpdate=False.

Для однозначной идентефикации записи используется поле A_Master_ID для него устанавливаем атрибут pfInKey=True - для остальных полей False.

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

   Так же очень часто возникает необходимость использовать поля со счетчиками (которые заполняются на сервере). Но их применение в ADO еще возможно, а вот в BDE очень затруднительно. После вставки в БД строчки необходимо получить значение, которое было заполнено серверным счетчиком. В ADO это решается при помощи настройки Resync, а вот в BDE это можно решить только переоткрытием запроса и нахождением текущей записи по Альтернативному ключу. В erDA это решается гораздо проще.

   Достаточно прописать SQL для обновления в случае добавления новой записи в базу (RefreshOnInsSQL)

SELECT
  A_Master.A_Master_ID A_Master_ID,
  A_Master.A_MasterCod A_MasterCod,
  A_Master.A_MasterName_ID A_MasterName_ID,
  A_Master.A_MasterName_ID1 A_MasterName_ID1,
  A_Master.Perc Perc,
  A_MasterName.A_MasterName A_MasterName,
  A_MasterName1.A_MasterName A_MasterName1,
  SUM (A_DetailSM) A_DetailSM,
  SUM (A_DetailSM)* Perc/100 Sm00
FROM
   dbo.A_Master
   LEFT OUTER JOIN dbo.A_MasterName ON A_Master.A_MasterName_ID = A_MasterName.A_MasterName_ID
   LEFT OUTER JOIN A_DETAIL ON A_Detail.A_Master_ID=A_Master.A_Master_ID
   LEFT OUTER JOIN dbo.A_MasterName A_MasterName1 ON A_Master.A_MasterName_ID1 = A_MasterName1.A_MasterName_ID
WHERE
  A_Master.A_MasterCod=:A_MasterCod GROUP BY
  A_Master.A_Master_ID,
  A_Master.A_MasterCod,
  A_Master.A_MasterName_ID,
  A_Master.A_MasterName_ID1,
  A_MasterName.A_MasterName,
  A_MasterName1.A_MasterName,
  A_Master.Perc

   И обновление для строчки (RefreshSQL)

SELECT
  A_Master.A_Master_ID A_Master_ID,
  A_Master.A_MasterCod A_MasterCod,
  A_Master.A_MasterName_ID A_MasterName_ID,
  A_Master.A_MasterName_ID1 A_MasterName_ID1,
  A_Master.Perc Perc,
  A_MasterName.A_MasterName A_MasterName,
  A_MasterName1.A_MasterName A_MasterName1,
  SUM (A_DetailSM) A_DetailSM,
  SUM (A_DetailSM)* Perc/100 Sm00
FROM
   dbo.A_Master
   LEFT OUTER JOIN dbo.A_MasterName ON A_Master.A_MasterName_ID = A_MasterName.A_MasterName_ID
   LEFT OUTER JOIN A_DETAIL ON A_Detail.A_Master_ID=A_Master.A_Master_ID
   LEFT OUTER JOIN dbo.A_MasterName A_MasterName1 ON A_Master.A_MasterName_ID1 = A_MasterName1.A_MasterName_ID
WHERE
  A_Master.A_Master_ID=:A_Master_ID
GROUP BY
  A_Master.A_Master_ID,
  A_Master.A_MasterCod,
  A_Master.A_MasterName_ID,
  A_Master.A_MasterName_ID1,
  A_MasterName.A_MasterName,
  A_MasterName1.A_MasterName,
  A_Master.Perc

   Теперь мы получили обновляемый и редактируемый набор данных. Теперь подключаем справочники

Подключение справочников (Lookup)

   Теперь если у вас есть справочники из которых происходит выбор, то есть как минимум 2 пути:

  1. Воспользоваться стандартным (для Delphi) Lookup полем.
  2. Воспользоваться модальным справочником.

   В случае используемого раньше запроса Lookup полями являются поля MasterName и MasterName1. Сделаем MasterName 1-м способом - а MasterName 2-м способом.

1. Стандартное для Delphi Lookup поле позволяет автоматически подставлять значения из справочника. Для того, чтобы включить эту подстановку - для начала необходимо настроить выборку (DataSet) назовем ее MasterName из справочника. После этого в редакторе полей редактируемого DataSet в свойствах поля A_MasterName прописать:

FieldKind=fkLookup
Key_Fields=A_MasterName_ID
LookupDataset=MasterName
LookupKeyFields=A_MasterName_ID
LookupResultFields=A_MasterName

   При этом поле A_MasterName будет автоматически выбираться из справочника MasterName. Более того если начать редактировать это поле в DBGrid - то автоматически появится выпадающий список. В принципе возможно и создание (в редакторе) Lookup полей, которые отсутствуют в выборке, но тогда сортировка по этим полям будет невозможна. Этот метод наиболее подходит для небольших справочников. Для справочников с большим количеством записей наиболее подходит второй способ с Модальным справочником.

2. Для модального справочника делаем по другому (это опять же в демке).

   Для "модального" справочник создается отдельное окошко, которое показывается при помощи метода ShowModal. При этом в случае если данные выбраны - необходимо еще иметь какой либо механизм возврата выбранных данных.

   Для подключения такого справочника в MasterGrid - в котором отображаются данные делаем поле A_MasterName ReadOnly (чтобы пользователь не мог руками исправить). И добавляем кнопку на которую вешаем приблизительно такое вот событие:

if (Names.ShowModal=mrOk) then
  begin
    Master.Edit;
    MasterA_Mastername_ID1.Value:=MasterNameA_Mastername_ID.Value;
    MasterA_MasterName1.Value:=MasterNameA_Mastername.Value;
  end;

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

Вычисляемые поля

   Вычисляемые поля очень схожи с Lookup полями, только их значение вычисляется в событии onCalcFields (у Dataset).

   В демонстрационной программе показаны два типа вычисляемых полей:
    1-й тип - когда данные считаются на основе данных из другой таблицы (A_DetailSM). Тут ничего не надо делать - кроме как запретить пользователю его редактировать в Гриде установив ReadOnly=True.
    2-й тип - когда данные считаются на основе данных текущей строчки (Sm00). Для этого опять запретим пользователю его редактировать в Гриде. в свойствах данного поля установим FieldKind=fkCalculated. и пропишем метод Master.OncalcField следующим образом:

procedure TMainForm.MasterCalcFields(DataSet: TDataSet);
  begin
    MasterSm00.Value:=MasterA_DetailSM.Value*MasterPerc.Value/100;
  end;

   Следующий пункт Master/Detail

Master-detail (Главный / подчиненный)

   Это схема отображения данных при которой в 1-м наборе данных отображается суммарная информация а во 2-м наборе данных отображается детализация выбранной строчки 1-го набора данных. (например Информация о накладной / строчки накладной).

   В демонстрационной программе показано - каким образом пользоваться данным механизмом.

   Для этого на форму нужно положить еще один компоненте DataSet (Detail).

   Со следующим SQL запросом:

SELECT * FROM dbo.A_Detail
WHERE
A_MASTER_ID=:A_MASTER_ID

В данном случае во втором запросе необходимо отобразить только ту информацию, которая относится к данной строчке Master. У нас связь происходит по полю A_MASTER_ID при этом если установить свойство данного DataSet. DataSource=MasterdDS то значение параметра A_MASTER_ID будет автоматически подставляться из Master. Более того в Delphi автоматически происходит обновление информации в подчиненойй таблице - если в мастере перешли на другую строчку либо что-то отредактировали.

В erDA есть одна маленькая особенность если имена полей в Mаster и Detail будут совпадать и в свойствах совпадающего поля в Master будет установлен флаг pfInKey - то при добавлении новых строчек в Detail эти поля будут заполняться автоматически. в случае если вы работаете с BDE либо ADO - то надо убедиться в том, что эти поля заполняются (если не заполняются автоматически, то прописать вручную).

После этого можно настроить обновление информации.

Обновление информации

   Теперь в случае если отредактировали Detail - необходимо обновить текущую строчку Master, чтобы пересчитывалось суммарное значение A_DetailSM и SM00. Тут у в BDE и ADO необходимо запомнить текущее положение в Master и Detail. Произвести синхронизацию данных с сервером и восстановить текущее положение. В случае с erDA достаточно просто прописать следующее событие.

procedure TMainForm.DetailAfterPost(DataSet: TDataSet);
  begin
    Master.RefreshRecord;
  end;

Немного о передаче параметров

Обход проблем блокировок

   Чтобы не блокировать ресурсы на SQL сервере крайне рекомендую выкачивать результаты запроса на клиента (ограничив максимальное количество возвращаемых записей).

   Для фонового выкачивания данных с сервера для erDA пишем такую процедуру:

procedure TMainForm.OnIdle(Sender: TObject; var Done: Boolean);
  begin
    if DB.RunReadAhead then Done:=False;
      if Assigned(OldOnIdle) then
        OldOnIdle(Sender,Done);
  end;

И прописываем ее в Application.OnIdle

примечание: Хоть это и не совсем фоновое выкачивание - но выглядит почти так как нужно.

   В BDE и ADO такого механизма нет, поэтому надо позаботиться о полном кэшировании там, где это возможно. Так же надо позаботиться о своевременном закрытии запросов (тем более "длинных"). В ADO одним из приемов может быть использование не блокирующего серверного курсора (который по моим наблюдениям очень сильно похож на полное кэширование только на стороне сервера). Не стоит позволять пользователю оставлять программу с активным запросом на долгое время. В SQL Enterprise Manager например по таймеру запрос закрывается автоматически - если с ним никто не работает.

Демонстрационная программа

   Данная демонстрационная программа показывает основные приемы работы с SQL серверами баз данных при помощи erDA.

   Скачать ее можно здесь (554K).

    В данном примере используется стандартный SQL без специфических команд сервера - поэтому программа одинаково работает как с MSSQL, так и с Oracle. Достаточно просто поменять настройки подключения.

Для примера взята следующая структура данных

структура данных

Примечание: в данном случае таблица A_Master имеет 2 поля выбираемых из справочника A_MasterName.

   Также для компиляции вам потребуется DBGridEh.

   Скрипты для генерации структуры БД приведены отдельно для MSSQL и Oracle.

Использование параметризированных запросов

   При программировании очень часто возникает необходимость в передаче каких либо параметров в SQL запрос. В большинстве случаев это делают при помощи обычных текстовых операций, но в таком подходе иногда возникают подводные камни. Например при передаче текстового параметра необходимо позаботиться о спец символах (например апострофы). Так же не стоит забывать, что при передаче таким образом даты - может возникнуть серьезная проблема из-за различий в формате даты.

   Самым правильным с точки зрения надежности является передача параметров при помощи параметризированных запросов. Параметризированный запрос выглядит следующим образом:

SELECT * FROM Table1 WHERE Field1=:Param1

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

DataSet.ParamByName('Param1').as_TYPE_=Value; // Где TYPE нужный тип данных

В данном случае вероятность возникновения ошибки минимальная.

   Немного о возможных проблемах


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=20957