Запросы

Запросы (TQuery, TADOQuery)

Запросы (TQuery, TADOQuery) - это такие же наборы данных, как и таблицы ( TTable, TADOTable ). Запросы, как и таблицы, происходят от общего предка - TDBDataSet, в связи с этим они имеют схожие свойства, методы и события. Но имеются и существенные различия. Прежде всего, если табличный набор данных TTable ( TADOTable ) получает точную копию данных из таблицы базы данных, то запрос TQuery ( TADOQuery ) получает этот набор, основываясь на запросе, сделанном на специальном языке SQL (Structured Query Language - Язык Структурированных Запросов). С помощью этого языка программист создает запрос, который передается параметру TQuery.SQL ( TADOQuery .SQL ). При открытии набора данных этот запрос обрабатывается используемым механизмом BDE, ADO или др. и в набор данных передаются запрошенные данные. Заметили разницу? Не копия таблицы, а именно запрошенные данные, причем в указанном порядке!

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

Если же пользователю требуется вносить изменения в таблицу (таблицы), то с помощью специальных операторов SQL ( INSERT, UPDATE, DELETE ) формируется запрос, уведомляющий механизм доступа к данным изменить данные БД. В этом случае никаких временных таблиц не создается. Запрос передается механизму доступа, обрабатывается им, выполняются изменения, и механизм доступа уведомляет программу о благополучном (или нет) изменении данных.

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

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

Компонент TADOQuery

Для демонстрации работы компонента TADOQuery создадим совсем маленькое приложение - простейший SQL -монитор (у Delphi имеется встроенный SQL -монитор, но ведь всегда приятно сделать что-то своими руками!).

Итак, создайте папку для нового приложения. В эту папку скопируйте базу данных ok.mdb, с которой мы работали в четвертой лекции. Если вы еще помните, там у нас имеется четыре таблицы, предназначенные для программы отдела кадров. Создайте новый проект в Delphi, форму переименуйте в fMain, сохраните ее модуль под именем Main, а проект в целом как SQLMon. В свойстве Caption формы пропишите "Простой SQL -монитор".

Далее на форму установите панель. В свойстве Align панели выберите alTop, чтобы панель заняла весь верх, а ее высоту растяните примерно на полформы. Очистите свойство Caption. На эту панель установите компонент Memo, именно в нем мы будем писать наши SQL -запросы. Дважды щелкните по свойству Lines этого компонента, чтобы вызвать редактор текста, и очистите весь текст. Также не помешает дважды щелкнуть по свойству Font и изменить размер шрифта на 12 для лучшего восприятия текста. В свойстве Align выберите alLeft, чтобы компонент Memo занял всю левую часть панели.

В правой части панели установите две простые кнопки и компонент TDBNavigator с вкладки Data Controls панели инструментов. Для улучшения внешнего вида интерфейса ширину кнопок сделайте такой же, как у навигатора базы данных. В свойстве Caption первой кнопки напишите "Выполнить SQL -запрос", на второй кнопке напишите "Очистить компонент Memo ". Собственно, мы могли бы очищать Memo сразу при выполнении SQL -запроса, и обойтись без второй кнопки. Но многие запросы похожи, и проще изменить часть текста запроса, чем писать весь запрос заново.

На нижнюю, свободную половину формы установите компонент TDBGrid с вкладки Data Controls для отображения данных. В свойстве Align сетки выберите alClient, чтобы сетка заняла все оставшееся место. У вас должна получиться такая картина:

Еще нам потребуются три компонента: TADOConnection и TADOQuery с вкладки ADO для получения набора данных, и TDataSource с вкладки Data Access для связи сетки DBGrid и навигатора DBNavigator с этим набором данных.

Дважды щелкните по ADOConnection1, чтобы вызвать редактор подключений. Нажмите кнопку " Build ", выберите поставщика Microsoft Jet 4.0 OLE DB Provider, и нажмите "Далее". В поле "Выберите или введите имя базы данных" укажите нашу БД ok.mdb и нажмите "ОК". И еще раз "ОК", чтобы закрыть окно редактора подключений. Сразу же свойство LoginPrompt переводим в False, чтобы при каждом запуске программы у нас не запрашивался логин и пароль, а Connected в True. Подключение к базе данных произошло.

В свойстве Connection компонента TADOQuery выберем ADOConnection1, а в свойстве DataSet компонента DataSource1 выберем наш НД ADOQuery1. Теперь набор данных ADOQuery1 соединен с базой данных, а DataSource1 - с этим набором данных.

В свойстве DataSource компонентов DBGrid1 и DBNavigator1 выберем DataSource1, чтобы они могли взаимодействовать с набором данных.

Нам осталось лишь запрограммировать обработчик события onClick для обеих кнопок. Щелкните дважды по кнопке "Выполнить SQL -запрос", чтобы сгенерировать это событие, и пропишите в нем такой код:

//проверим - есть ли текст в Memo. Если нет, выходим:
  if Memo1.Text = '' then begin
    ShowMessage('Вначале введите запрос!');
    Memo1.SetFocus;
    Exit;
  end;
  //текст есть. Очистим предыдущий запрос в наборе данных:
  ADOQuery1.SQL.Clear;
  //добавим новый запрос из Memo:
  ADOQuery1.SQL.Add(Memo1.Text);
  //открываем набор данных, т.е. выполняем запрос:
  ADOQuery1.Open;

Комментарии здесь достаточно подробны, чтобы разобраться в происходящем. Заметим только, что набор данных ADOQuery1 обычно закрыт. После того, как мы изменяем его свойство SQL, прописывая туда новый SQL -запрос, этот набор данных открывается. В результате в БД передается SQL -запрос, получаются запрашиваемые данные, которые формируют набор данных ADOQuery1. Когда этот компонент активен, данные доступны.

Можно также заполнить свойство SQL, дважды щелкнув по нему и открыв редактор запросов, и сделать активным во время проектирования программы. Тогда данные становятся доступны сразу. Такой подход удобен, когда программист не собирается в дальнейшем менять SQL -запрос этого набора данных. Однако чаще бывает наоборот - в зависимости от ситуации, используется то один, то другой запрос в одном и том же наборе данных. Так мы поступаем и в нашем примере - передача SQL -запроса и открытие набора данных мы будем делать программно.

Еще мы можем заметить, что свойство SQL набора данных TADOQuery имеет тип TStrings, так же, как свойство Lines компонента Memo или свойство Items компонента ListBox. То есть, в свойстве SQL мы можем использовать все преимущества, которые нам дает тип TStrings, например, загрузка SQL -запроса из внешнего файла:

ADOQuery1.SQL.LoadFromFile('c:\myfile.sql');

Подобный прием нередко используется программистами, когда нужно сделать программу более гибкой. Формируя файл с SQL -запросами можно получать различные наборы данных, в зависимости от обстоятельств. Но в нашей программе мы будем получать SQL -запрос из поля Memo. Поскольку тип TStrings используется и в Memo, и в ADOQuery, то следующие строки кода аналогичны, они одинаково сформируют SQL -запрос на основе текста в поле Memo:

ADOQuery1.SQL.Add(Memo1.Text);
ADOQuery1.SQL := Memo1.Lines;

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

Memo1.Clear;

Вот и вся программа! Сохраните ее, скомпилируйте и запустите программу на выполнение. В поле Memo впишите следующие строки:

SELECT * 
FROM LichData;

После этого нажмите кнопку "Выполнить SQL -запрос". В сетке DBGrid отобразятся данные, которые представляют собой точную копию таблицы LichData из базы данных ok.mdb.

Строки в примере написаны по правилам и рекомендациям языка SQL, то есть, операторы пишутся заглавными буквами, каждый оператор на отдельной строке, а в конце ставится точка с запятой. Однако рекомендации можно нарушать, а правила в Delphi более мягкие. Так, мы можем написать весь текст маленькими буквами, в одну строку, не ставить точку с запятой и не обращать внимания на регистр букв:

select * from lichdata

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

Что же написано у нас в этом запросе? Оператор SELECT означает "выделить", звездочка означает "все поля", оператор FROM означает "из…". Таким образом, запрос означает:

ВЫДЕЛИТЬ все поля ИЗ таблицы LichData

Но такой запрос ничем не отличается от применения табличных компонентов, а ведь мы можем создавать и гораздо более сложные запросы! Предположим, нам нужно получить фамилию, имя и отчество сотрудника, а также город его проживания. Основные данные находятся в таблице LichData, а вот город находится в таблице Adres, связанной с таблицей LichData релятивной связью один-к-одному по полю "Ключ" таблицы LichData, и по полю "Сотрудник" таблицы Adres. В этом случае запрос будет выглядеть так:

SELECT Фамилия, Имя, Отчество, Город 
FROM LichData, Adres
WHERE Ключ = Сотрудник;

Как видите, в операторе SELECT поля перечисляются через запятую. Также через запятую перечисляются используемые таблицы в операторе FROM. А вот оператор WHERE указывает, что нужны только те записи, в которых значения поля "Ключ" и "Сотрудник" равны. Если бы мы не использовали оператор WHERE, то получили бы кучу недостоверных записей, где к каждой записи одной таблицы добавлялись бы все записи другой. Оператор WHERE позволил нам получить связные данные, в которых к одной записи первой таблицы добавляется соответствующая запись из другой таблицы. С этими и другими операторами мы подробней познакомимся на следующей лекции.

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

SELECT Фамилия, Имя, Телефон
FROM LichData, Telephones
WHERE Ключ = Сотрудник;

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

Компонент-запрос может формировать набор данных двух типов: изменяемый, в котором пользователь может менять (редактировать, удалять или добавлять) записи, и не изменяемый, предназначенный только для просмотра данных или для составления отчетности. Возможность получения "живого" набора данных зависит от разных факторов - от применяемого оператора, от механизма доступа к данным, от используемой клиент-серверной СУБД. В данном примере мы используем оператор SELECT, работаем с локальной БД посредством механизма ADO. Если вы воспользуетесь навигатором, то убедитесь, что записи можно добавлять и удалять, а в сетке DBGrid их можно редактировать. Однако при редактировании данных, полученных более чем из одной таблицы, могут возникнуть трудности. Зато набор данных из одной таблицы можно спокойно изменять. Подробней с "живыми" и неизменяемыми наборами данных мы познакомимся позднее.

Компонент TQuery/ TADOQuery может выполнять запросы двумя разными способами. Вначале в свойство SQL компонента помещается необходимый запрос. Это можно сделать программно, как в нашем SQL -мониторе, так и на этапе проектирования приложения. Дальнейшие действия зависят от того, какой запрос нам нужно выполнить. Если это запрос на получение набора данных, то есть, оператор SELECT, то достаточно просто открыть TQuery/ TADOQuery методом Open, или присвоив True свойству Active. Если же запрос должен модифицировать данные, то есть, используются такие операторы, как INSERT, UPDATE, DELETE, то тогда запрос выполняется методом ExecSQL.

С работой компонента-запроса TQuery ( TADOQuery ) мы поработали на практике. Как и табличные компоненты, компонент-запрос произошел от родительского класса TDBDataSet. Унаследовав его свойства, методы и события, он имеет и собственные, отличительные черты. Так, например, запрос может быть изменяемым (живым), при котором пользователь может модифицировать записи набора данных, и не изменяемым, при котором данные доступны только для просмотра и составления отчетности. Наиболее важные свойства, методы и события, отличные от TDBDataSet, рассматриваются ниже.

Свойства компонента-запроса

Constrained - Свойство логического типа. Если свойство имеет значение True, то в изменяемом наборе данных на модифицируемые записи накладываются ограничения блока WHERE оператора SELECT (с операторами SQL -запросов вплотную познакомимся на следующей лекции).

DataSource - Указывает тот компонент TDataSource, который используется для формирования параметрического запроса.

Local - Свойство логического типа. Если свойство имеет значение True, это означает, что компонент-запрос работает с локальной или файл-серверной базой данных.

ParamCheck - Логическое свойство. При значении True список параметров автоматически обновляется при каждом программном изменении SQL -запроса.

Params - Свойство имеет тип TParams и содержит массив объектов-параметров этого типа. На этом типе данных следует остановиться подробнее:

Prepared - Свойство логического типа. Содержит значение True, если SQL -запрос был подготовлен методом Prepare.

RequestLive - Логическое свойство. Если компонент-запрос содержит изменяемый (живой) набор данных, то RequestLive содержит True.

RowsAffected - Свойство содержит количество записей, которые были удалены или отредактированы в наборе данных в результате выполнения SQL -запроса.

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

UniDirectional - Свойство логического типа. Содержит True, если курсор набора данных может перемещаться только вперед (типы курсоров см. в лекции №4). Это свойство используется, в основном, при работе с клиент-серверными СУБД, не поддерживающими курсоры, которые могут двигаться как вперед, так и назад.

Методы компонента-запроса

ExecSQL() - Выполняет модифицирующие запросы, то есть запросы на изменение, добавление или удаление записей, а также создание или удаление таблиц. В случае обычных запросов, выполненных с помощью оператора SELECT, используется метод Open, или присвоение значения True свойству Active набора данных.

ParamByName() - Метод дает доступ к значению параметра по его имени, указанному в скобках.

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

UnPrepare() - Этот метод отменяет результаты действия метода Prepare(), и освобождает буфер от хранения компилированного запроса.