9. Работа с компонентом TQuery

 

9.1. Общие сведения *

9.2. Соединение компонента TQuery с базой данных *

9.3. Соединение компонента TQuery и визуальных компонентов для работы с данными *

9.4. Выполнение статических запросов *

9.5. Методы открытия и закрытия компонента TQuery *

9.6. Изменяемые TQuery *

9.7. Выполнение динамических запросов *

9.7.1. Понятие динамического запроса *

9.7.2. Формирование динамического запроса *

9.7.3. Установка значений параметров динамического запроса во время выполнения *

9.7.4. Методы Prepare и Unprepare *

9.7.5. Указание значения NULL для параметров *

9.7.6. Передача параметров через свойство DataSource *

9.8. Формируемые запросы *

9.9. Динамический редактор SQL *

9.10. Использование TQuery для получения агрегированных значений *

9.11. Использование компонента TQuery для локальных и удаленных БД *

9. Работа с компонентом TQuery

9.1. Общие сведения

Компонент TQuery предназначен для: работы с НД, источником данных для которого могут служить записи как одной, так и нескольких ТБД (TQuery, возвращающий набор данных); выполнения запросов к БД, не возвращающих наборов данных (добавление, изменение, удаление записей в таблицах БД и др ) Основные отличия компонента данных TQuery, возвращающего набор данных, от выполняющего сходные функции компонента TTable:

НД, возвращаемый TQuery, может быть составлен из записей нескольких таблиц (над которыми выполнена операция объединения. Join);

• в общем случае НД, возвращаемый TQuery, даже если источником этого НД служит одна таблица БД, предполагает обращение к подмножеству записей и столбцов (полей), в то время как TTable ориентирован на работу со всеми записями и полями и для того, чтобы работать в нем с подмножеством строк и полей, необходимо предпринять дополнительные действия (фильтрацию записей, ограничение состава полей в редакторе полей).

Результирующий НД компонента TQuery формируется путем выполнения запроса к БД на языке SQL (Structured Query Languague, язык структурированных запросов). Такой запрос использует SQL-оператор SELECT. Текст любого запроса хранится в свойстве SQL компонента TQuery.

Запросы, выполняемые компонентом TQuery - независимо от того, возвращают они набор данных или просто производят какие-либо действия в БД, - могут быть статическими и динамическими

Статический запрос характерен тем, что описывающий его SQL-оператор не изменяется в процессе выполнения приложения.

SQL-оператор динамического запроса может частично изменяться в процессе выполнения приложения. В этом случае изменяемые части SQL-запроса оформляют в качестве параметров, значения которых могут многократно изменяться в процессе выполнения приложения. Таким образом можно использовать один компонент TQuery для выполнения множества разнесенных во времени запросов к БД, различающихся по значению параметров. Заметим, что состав параметров также может меняться во время выполнения. Это более характерно для формируемых запросов - разновидности динамических запросов.

Формируемые запросы - такие запросы, текст SQL-оператора которых формируется программно в процессе выполнения приложения. Действия по формированию такого запроса состоят в очистке предыдущего содержимого свойства SQL и программного занесения в это свойство нового текста SQL-запроса (вид которого зависит от текущей ситуации и определяется рядом условий), а также в последующем его выполнении. Такая задача является тривиальной, поскольку свойство SQL имеет тип TString, то есть являет собой экземпляр динамического строкового списка Таким образом, один компонент TQuery используется для выполнения таких различных запросов, как, например, SELECT и INSERT

SQL-операторы (SELECT, INSERT, UPDATE, DELETE), применяемые в компоненте TQuery, достаточно подробно описаны в части книги, посвященной использованию SQL-сервера InterBase и архитектуры "клиент-сервер".

9.2. Соединение компонента TQuery с базой данных

Для случаев работы с локальными и удаленными БД имеются некоторые различия в способе соединения компонента TQuery с базой данных, для которой и будет выполняться SQL-оператор из свойства SQL этого компонента Свойствос property Database: TDatabase; возвращает указатель на компонент TDatabase, выполняющий соединение данного TQuery с базой данных. Если компонент TDatabase явно в приложении не создан (что характерно при работе с локальными БД), на период сеанса автоматически создается временный компонент TDatabase.

Свойство property DatabaseName: TFileName; позволяет указать, с какой БД будет работать компонент TQuery.

При работе с локальными БД в свойстве DatabaseName указывается:

• псевдоним БД, ранее определенный при помощи утилиты BDE Administrator,

переопределенный псевдоним из свойства DatabaseName явно определенного компонента TDatabase, если он используется;

• путь на диске к конкретному каталогу (если свойство DatabaseName хранит пустое значение, подразумевается, что указан текущий каталог). Если свойство DatabaseName хранит пустое значение (подразумевается путь • текущий каталог) или явно указан путь на диске к вполне конкретному каталогу, БД будут искаться именно там (для случая работы с локальными СУБД).

Например, при запросе "выбрать все записи из таблицы RASHOD" имя таблицы в операторе SELECT можно указать следующим образом:

• если свойство DatabaseName хранит пустое значение (подразумевается путь к текущему каталогу)

SELECT *

FROM "RASHOD.DB"

или, если в установках BDE указано, что в случае отсутствия расширения для файла локальной таблицы по умолчанию берутся таблицы Paradox, просто

SELECT *

FROM RASHOD

• если свойство DaгabaseName хранит пустое значение (подразумевается путь к конкретному каталогу), этот каталог можно указать в составе имени файла ТБД:

SELECT *

FROM "С:\BOOK\LOC_SKLD\RASHOD.DB"

где "C:\BOOK\LOC_SKLD" указывает конкретный каталог, в котором следует искать файл "RASHOD.DB";

если свойство DaгabaseName хранит псевдоним БД, переустановленный псевдоним БД или путь к конкретному каталогу

SELECT *

FROM RASHOD

При работе с удаленными БД (архитектура "клиент-сервер") в свойстве DatabaseNawe указывается:

• псевдоним БД, ранее определенный при помощи BDE Administrator;

• переопределенный псевдоним из свойства DatabaseName явно определенного компонента TDatabase, если он используется.

9.3. Соединение компонента TQuery и визуальных компонентов для работы с данными

Соединение компонента TQuery с визуальными компонентами происходит через промежуточный компонент TDataSource. Для этого в компоненте TDataSource в свойстве DataSet необходимо указать имя компонента TQuery. В визуальных компонентах (TDBGrid, TDBEdit и т.д.) в свойстве DaгaSource указывается имя компонента TDataSource и, если необходимо, в свойстве DataField выбирается имя интересующего поля.

9.4. Выполнение статических запросов

Для формирования статического запроса необходимо:

1. Выбрать для существующего компонента TQuery в инспекторе объектов свойство SQL и нажать кнопку в правой части строки;

2. В появившемся окне текстового редактора набрать текст SQL-запроса (рис. 9.1);

3. Установить свойство Active компонента TQuery в True, если НД должен быть открыт в момент начала работы приложения или оставить свойство Active в состоянии False, если открытие НД будет производиться в программе в некоторый момент работы приложения.

Условия выборки записей, единожды реализованные по статическому запросу, изменить нельзя, поскольку текст SQL-оператора данного запроса в программе не изменяется. Например, в компоненте TQuery, использующем оператор SELECT, показанный выше, закрытие и повторное открытие компонента приведет к выдаче результирующего НД, в который будут включены все записи из таблицы RASHOD, присутствующие в данной таблице на момент повторного открытия компонента TQuery.

Формирование текста SQL-оператора SELECT может осуществляться не вручную, а при помощи встроенного в Delphi средства Visual Query Builder, действующего по принципу QBE (Query By Example, запрос по образцу). Для его запуска нужно сделать компонент TQuery текущим, нажать правую кнопку мыши и выбрать режим Query Builder. Заметим, что в TQuery на момент запуска Visual Query Builder должно быть установлено значение свойства DaгabaseName.

Visual Query Builder запрашивает имена таблиц, которые будут участвовать в запросе (рис. 9.2). Выбрав нужную таблицу, нажмите кнопку Add и так до тех пор, пока не будут выбраны все нужные таблицы. После этого нажмите кнопку Cancel.

Далее в появившемся окне из верхней его части берется название соответствующего поля и "перетаскивается" в нижнюю часть; для каждого такого поля создается столбец условий . По информации в ячейках таких столбцов и строится текст SQL-запроса. Например, для того, чтобы реализовать внутреннее соединение таблиц RASHOD и ТО VARY по условию TOVARY.TOVAR = RASHOD.TOVAR, следует ввести TOVARY.TOVAR в графе Criteria для столбца, соответствующего таблице RASHOD (рис.9.3) или RASHOD.TOVAR для столбца, соответствующего таблице ТОVARY.

Сформированный запрос может быть просмотрен (кнопка с изображением очков) и выполнен (кнопка с изображением зеленого треугольника). Кнопка с изображением галочки приводит к закрытию Visual Query Builder и выходу в приложение. При этом текст сформированного запроса помещается в свойство SQL данного компонента TQuery (рис. 9.4).

Visual Query Builder обычно используется для создания черновых вариантов оператора SELECT. Впоследствии текст запроса трансформируется разработчиком к нужному виду.

Для тех, кто только начинает осваивать язык SQL, Visual Query Builder может послужить средством обучения синтаксису оператора SELECT.

9.5. Методы открытия и закрытия компонента TQuery

Компонент TQuery может возвращать НД (если компонент использует оператор SELECT, то есть осуществляет выборку из одной или более таблиц БД) и выполнять действие над одной или более таблицей БД (SQL-операторы INSERT, UPDATE, DELETE).

В случае использования оператора SELECT после открытия компонента TQuery возвращается НД, в котором указатель текущей записи всегда установлен на первую запись (если она имеется). Такой компонент TQuery следует открывать:

• установкой свойства Active в значение True, или

• выполнением метода

procedure Open;

Например,

RashodQuery.Active := True;

TovaryQuery.Open;

В случае использования операторов INSERT, UPDATE, DELETE набор данных не возвращается. Такой компонент TQuery следует открывать, выполняя метод

procedure ExecSQL;

Например,

InsertQuery.ExecSQL;

Метод ExecSQL посылает серверу для выполнения SQL-оператор из свойства SQL данного компонента TQuery.

Закрытие компонента TQuery осуществляется методом procedure Close; или установкой в False свойства Active, например:

RashodQuery.Active := False;

TovaryQuery.Open;

При этом следует помнить, что для компонента TQuery, не возвращающего набор данных, выполнение метода Close не имеет последствий, поскольку с данным компонентом не связан открытый НД. Для динамических запросов, особенно для отсылаемых к удаленной БД, полезно использовать методы, осуществляющие "связывание" параметров с их фактическими значениями (Prepare) и отменяюще такое "связывание" (UnPrepare) Более подробно о них будет рассказано далее в подразделах, посвященных выполнению динамических запросов.

9.6. Изменяемые TQuery

Записи НД, возвращаемые компонентом TQuery, могут изменяться, подобно тому, как это происходит в компоненте TTable. Записи можно редактировать в компоненте TDBGrid, связанном с TQuery (метод автоматического перевода НД в состояния dsInsert, dsEdit, автоматического выполнения методов Insert, Edit, Delete, Post и Cancel). Также может применяться метод программного формирования значения полей записи, ввода таких значений с использованием компонента TDBEdit, TDBCheckBox и других. В этом случае методы Insert, Edit, Delete, Post и Cancel вызываются в программе явно .

Возможность изменения НД, возвращаемого после выполнения оператора SELECT, определяется свойством property CanModify: Boolean; Значение этого свойства устанавливается автоматически, исходя из определенных факторов. Если в процессе выполнения свойство CanModify установлено в True, набор данных доступен для изменения. При значении False записи НД не могут быть добавлены, изменены или удалены.

Свойство CanModify всегда устанавливается в False, если в False установлено свойство RequestLive компонента TQuery: property RequestLive: Boolean; Значение данного свойства может быть установлено как во время проектирования, так и во время разработки приложения. По умолчанию всегда устанавливается False (НД доступен только для чтения). Однако установка данного свойства в значение True (НД может быть изменен) вовсе не означает, что НД действительно будет позволено изменяться и что свойство CanModify будет установлено в True. Это произойдет только в том случае, если синтаксис оператора SELECT при выполнении запроса будет признан "верным".

Синтаксис оператора SELECT будет признан "неверным", если:

• НД формируется более чем из одной ТБД;

• присутствует предложение принудительной сортировки результирующего набора данных ORDER BY;

• значения хотя бы одного столбца результирующего НД сформировано с использованием агрегатных функций (SUM, COUNT, AVG, MIN, MAX);

• при доступе к СУБД Sybase в таблице отсутствует уникальный индекс

В том случае, если свойство RequestLive установлено в True, а синтаксис оператора SELECT признан "неверным":

• возвращается НД, доступный только для чтения - при доступе к таблицам локальных СУБД (Paradox, dBase);

• выдается ошибка - при доступе к серверным СУБД (InterBase, Oracle) и т.д.

Если изменения, внесенные в НД методами Post, Delete, не отображаются в НД, его содержимое можно обновить методом procedure Refresh; Однако в этом случае оператор SELECT должен быть выполнен для таблицы локальной СУБД и эта таблица должна иметь уникальный индекс. Для НД, возвращенных в результате выполнения запроса к удаленной СУБД, выполнение метода Refresh не влечет за собой никаких последствий.

Для случая работы с удаленными БД следует избегать выполнения изменений при помощи методов Insert, Edit, Delete записей в НД, полученного при помощи TQuery. В этом случае вся нагрузка на модификацию удаленных Таблиц ложится на клиентское приложение, в то время как в соответствии с одним из краеугольных камней идеологии "клиент-сервер" - вся тяжесть операций по физическому доступу к БД должна ложиться на сервер БД.

Предотвратить ввод записей, не удовлетворяющих условиям, перечисленным в предложении WHERE оператора SELECT, можно путем установки в True значения свойства

property Constrained: Boolean;

Например, для НД, полученного по запросу

SELECT *

FROM RASHOD

WHERE KOLVO > 1000

при Constrained, установленном в True, будут блокироваться попытки запоминания записей со значением поля KOLVO, меньшим 1000.

В том случае, если НД доступен только для чтения, его записи могут быть изменены при помощи SQL-операторов INSERT, UPDATE, DELETE. При этом изменения в НД не отображаются Для отображения изменений НД следует переоткрыть, выполнив метод Close и повторно Open. Повторное открытие НД устанавливает указатель текущей записи на первую строку, что удобно далеко не всегда. В этом случае можно

1) вносить изменения в НД пакетом, визуализируя их путем его повторного открытия после равных порций изменений - например, после каждых 10 изменений, этот метод больше подходит для случаев значительного количества изменений в НД;

2) реализовать в форме локатор - механизм поиска записи, удовлетворяющей некоторому условию (значение условия можно вводить, например, в компонент TEdit или группу компонентов TEdit или автоматически помещать туда значения полей, однозначно идентифицирующих запись, для последней добавленной или измененной записи). Поиск реализуется при нажатии экранной кнопки. Для поиска используется метод Locate, например:

procedure TForm1.LocatorButtonClick(Sender: TObject);

begin

RashodQuery.Locate('N_RASH',Editl.Text, [loPartialKey]);

end;

3) перед повторным открытием НД запоминать значение поля (полей), однозначно идентифицирующего запись, и после открытия восстанавливать местоположение указателя текущей записи при помощи метода Locate.

Например, пусть в набор данных (компонент RashodQuery) добавляются записи при помощи SQL-оператора INSERT (компонент InsertQuery). Однозначно идентифицирует запись в НД RashodQuery поле 'М'. Тогда восстановление указателя текущей записи после повторного открытия НД RashodQuery может быть реализован так:

TmpN_Rash := FieldByName('М').Aslnteger;

InsertQuery.ExecSQL;

RashodQuery.Close;

RashodQuery.Open;

RashodQuery.Locate('М',TmpN_Rash, []);

Заметим, что такой подход не годится для случая удаления записей при помощи SQL-оператора DELETE, поскольку после удаления в НД не существует записи с запомненным значением уникального поля (полей).

9.7. Выполнение динамических запросов

9.7.1. Понятие динамического запроса

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

Например, пусть в процессе выполнения приложения может быть выдан запрос 'выдать все записи из таблицы RASHOD, относящиеся к расходу товара "Сахар" со склада 10 января 1997 г.'

SELECT *

FROM RASHOD

WHERE (TOVAR = "Сахар") AND

(DAT_RASH = "10.01.97")

и запрос выдать все записи из таблицы RASHOD, относящиеся к расходу товара "Кока-кола" со склада 20 января 1997 г.'

SELECT *

FROM RASHOD

WHERE (TOVAR = "Кока-кола") AND

(DAT_RASH = "20.01.97")

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

SELECT *

FROM RASHOD

WHERE (TOVAR = :TOVAR) AND

(DAT_RASH = :DAT_RASH)

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

SELECT *

FROM RASHOD

WHERE (TOVAR = :PARAMETR1) AND

(DAT_RASH = : ZNACHENIE)

9.7.2. Формирование динамического запроса

Для формирования динамического запроса необходимо:

1. выбрать для существующего компонента TQuery в инспекторе объектов свойство SQL и нажать кнопку текстового редактора;

2. в появившемся окне текстового редактора набрать текст SQL-запроса с параметрами;

3. выбрать в инспекторе объектов свойство Params и нажать кнопку в строке данного свойства; в появившемся окне будут показаны имена всех параметров, введенных в текст динамического SQL-оператора на шаге 2; список параметров отслеживается автоматически всякий раз при изменении содержимого свойства SQL;

4. каждому параметру из списка необходимо поставить в соответствие определенный тип и, если нужно, стартовое значение в поле Value. Переключатель Null Value позволяет указать в качестве стартового значения NULL (рис. 9.5). Стартовые значения присваивать необязательно, однако каждому параметру необходимо поставить в соответствие определенный тип данных, иначе попытка открытия компонента TQuery приведет к возбуждению исключения;

5. компонент TQuery можно сделать активным (установить свойство Active = True) на стадии разработки приложения только в том случае, если каждому из параметров присвоено стартовое значение.

Компонент TQuery, содержащий динамический запрос, если его свойство Active установлено на этапе разработки в значение True, открывается при

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

Если компонент TQuery, содержащий динамический запрос, не открыт в момент создания формы, его можно открыть в некоторый момент времени, программно установив значения параметров и выполнив метод Open или установив свойство Active в True.

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

 

9.7.3. Установка значений параметров динамического запроса во время выполнения

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

Параметры компонента TQuery доступны через его свойство property Params[Index: Word]:TParams;

Это свойство является набором параметров, где каждый параметр определяется индексом в диапазоне (0...ParamCount-l, где ParamCount есть число параметров, которое можно получить с помощью свойства ParamCount компонента TQuery: property ParamCount: Word;

Обратиться к конкретному параметру можно:

1) указав индекс параметра в свойстве Params компонента TQuery, например, Params[0]. Порядок следования параметров аналогичен показываемому в окне редактора параметров (активизирующегося после нажатия кнопки в строке свойства Params инспектора объектов);

2) через метод компонента Tquery function ParamByName(const Value: string): TParam;

где Value определяет имя параметра.

Для установки значения конкретного параметра используется одно из свойств компонента TParam A \NNN (AsString, Aslnteger и т.д.) или более общее свойство property Value: Variant;

Например,

RashodQuery.Params[0].AsDate := StrToDate(Edit1.Text) ;

RashodQuery.ParamByName('DAT_RASH').Value := TmpDat_Rash;

Пример. Для динамического запроса "выдать записи по расходу товара, определяемого параметром :TOVAR, за дату, определяемую параметром :DAT RASH":

SELECT *

FROM RASHOD

WHERE (TOVAR = :TOVAR) AND

(DAT_RASH = :DAT_RASH)

будем вводить текущие значения параметров в компоненты типа TEdit с именами Dat_Rash_Edit и Tovar_Edit. Открытие НД (компонент TQuery с именем RashodQuery) будем производить после нажатия кнопки GoButton (рис. 9.6).

Обработчик события OnClick кнопки GoButton:

procedure TForm1.GoButtonClick(Sender: TObject);

var TmpDat_Rash : TDateTime;

begin

TRY

TmpDat_Rash := StrToDate(Dat_Rash_Edit.Text);

EXCEPT

ShowMessage('Неверная дата');

Dat_Rash_Edit.SetFocus ;

Exit;

END;//try

WITH RashodQuery do begin

Close;

ParamByName('DAT_RASH').Value := TmpDat_Rash;

ParamByName('TOVAR').Value := Tovar_Edit.Text; Open;

END;//with

end;

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

ЗАМЕЧАНИЕ 1. По разным причинам попытка открыть НД может быть неуспешной. Поэтому рекомендуется помещать программный код, реализующий открытие НД, внутрь оператора TRY, например:

TRY

RashodQuery.Open;

EXCEPT

END;//try

ЗАМЕЧАНИЕ 2. Параметры более чувствительны к значениям даты и времени, чем поля (компонент TField). Известно, что переменные типа TDateTime хранят дату и время, и, если при преобразовании AsDateTime время или дата отсутствуют, они устанавливаются в значении поля, равными нулю:

SomeField.AsDateTime := StrToDate(Edit1.Text);

Подобное преобразование для параметра может стать источником ошибки на этапе выполнения. Поэтому для отдельных значений даты параметрам в инспекторе объектов может быть поставлен в соответствие тип Date или Time, а не только тип DateTime. В первом случае преобразовывать дату следует, используя свойство компонента TParam AsDate, а во втором - используя свойство AsTime компонента TParam:

SomeQuery.ParamByName('DateParam').AsDate := ...

SomeQuery.ParamByName('TimeParam').AsTime := ...

9.7.4. Методы Prepare и Unprepare

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

Для того чтобы "подготовить" запрос к многократному использованию, следует хотя бы один раз выполнить метод Prepare компонента TQuery: procedure Prepare; Выполнение этого метода для динамических запросов с неизменным синтаксисом лучше делать в обработчике события формы OnCreate. В дальнейшем можно многократно присваивать параметрам различные значения и выполнять запрос методами Open и ExecSQL.

Выполнение метода Prepare необязательно в том смысле, что и без этого запрос будет выполнен. Однако выполнение метода Prepare для динамических запросов строго рекомендуется. В противном случае "подготовка" будет производиться всякий раз при выполнении запроса.

Свойство компонента Tquery property Prepared: Boolean; возвращает True, если НД был "подготовлен" методом Prepare.

Метод procedure UnPrepare; позволяет освободить ресурсы, выделенные для "подготовленного" запроса. Этот метод неявно вызывается всякий раз, когда изменяется текст SQL-оператора запроса, что также ведет к немедленному закрытию НД.

9.7.5. Указание значения NULL для параметров

Значение NULL показывает отсутствие значения в поле или присутствие неопределенного значения. Для числовых полей значение 0 и пустая строка " для строковых полей - это вполне определенные значения, отличные от NULL (значение неопределенное).

Часто при добавлении или корректировки записей (SQL-операторы INSERT, UPDATE) необходимо указать значение NULL для какого-либо параметра. Для этого следует выполнить метод Clear компонента ТРаrат:

procedure Clear;

Например, пусть для корректировки записи в таблице RASHOD используется компонент TQuery с именем UpdateQuery, в свойстве SQL которого указан оператор

UPDATE RASHOD

SET DAT_RASH = :new_DAT_RASH,

KOLVO = :new_KOLVO,

TOVAR = :new_TOVAR,

POKUP = :new_POKUP

WHERE

(N_RASH = :old_N_RASH)

Пусть, в частности, значение параметра :new_POKUP (название покупателя) вводится пользователем в компонент TEdit с именем Pokup_Edit. Пусть на значение поля POKUP в таблице RASHOD наложено ограничение, согласно которому значение поля должно либо быть равным NULL, либо совпадать с одним из значений поля POKUP в таблице POKUPATELI.

Если пользователь ввел в поле ввода компонента Pokup_Edit пустую строку ("), присваивание

UpdateQuery.ParamByName('new_POKUP').Value := Pokup_Edit.Text;

и последующее выполнение метода UpdateQuery.ExecSQL приведет к ошибке, поскольку в таблице POKUPATELI нет записи со значением " в поле POKUP. Поэтому в случае, если в Pokup_Edit введена пустая строка, параметру :new_POKUP присваивается значение NULL:

IF Pokup_Edit.Text = '' THEN

ParamByName('new_POKUP').Clear

ELSE

ParamByName('new_POKUP').Value := Pokup_Edit.Text;

9.7.6. Передача параметров через свойство DataSource

Для передачи параметров может служить свойство DataSource компонента TQuery: property DataSource: TDataSource; В случае использования этого свойства явные присваивания значений параметрам динамического запроса не производятся, то есть не кодируются операторы присваивания типа

Query1.ParamByName('ИмяПараметра').Value := Значение;

Как же поступает приложение, когда нужно открыть набор данных (выполнить метод Open) и когда у динамического запроса есть параметры, а значения им не присвоены?

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

Пример. Пусть в свойстве SQL компонента RashodQuery содержится динамический запрос

SELECT *

FROM RASHOD

WHERE TOVAR = :TOVAR

ORDER BY DAT_RASH, KOLVO

Компонент RashodQuery открыт при старте приложения и значение параметру :TOVAR перед этим не назначалось. Свойство RashodQuery.DataSet = DS_TovarQuery. DS_TovarQuery - компонент типа TDataSource; он связан с набором данных TovaryQuery, у которого есть поле Tovar. Поэтому в качестве значения параметра :TOVAR берется текущее значение поля Tovar из НД TovaryQuery, как если бы это было назначено при помощи оператора

RashodQuery.ParamByName('Tovar').Value :=

TovaryQuery.FieldByName('Tovar').Value;

Отметим, что при смене текущей записи в наборе данных TovaryQuery автоматически переоткрывается и в нем показываются записи, соответствующие текущему значению поля Tovar набора данных TovaryQuery.

Вид окна программы, содержащей оба названных компонента, приводится на рис.9.7.

9.8. Формируемые запросы

Часто один компонент TQuery используют для выполнения различных отстоящих друг от друга во времени запросов. Такой подход уменьшает число используемых компонентов, но может привести к возрастанию программного кода.

Свойство SQL компонента TQuery имеет тип TStrings: property SQL: TStrings; и потому содержимое свойства SQL может формироваться программно методами Add (добавить элемент). Delete (удалить элемент), Clear (очистить список) и прочими.

Пример. Пусть компонент RashodQuery используется для выполнения динамического запроса

SELECT *

FROM RASHOD

WHERE POKUP = :POKUP

то есть запрос "выдать все записи из таблицы расхода товаров RASHOD, у которых имя покупателя POKUP совпадает со значением, указанным в параметре :POKUP". Пусть значение в параметр :POKUP берется из поля POKUP текущей на данный момент записи в таблице POKUPATELI (компонент PokupQuery):

WITH RashodQuery do begin Close;

ParamByName('POKUP').Value :=PokupQuery.FieldByName('POKUP').Value;

Open;

END;//with

Тогда и динамический оператор, и использование параметров можно заменить на следующий код:

WITH RashodQuery do begin

Close; SQL.Add ('SELECT *') ;

SQL.Add('FROM RASHOD')

SQL.Add('WHERE POKUP = ' + PokupQuery.FieldByName('POKUP').AsString) ;

Open;

END;//with

Заметим, что второй вариант лучше использовать в том случае, когда вид выполняемого запроса не повторяется во времени. В противном случае целесообразнее использовать динамический запрос.

Пример. Пусть для оператора UPDATE, выполняющего корректировку записи в таблице RASHOD, значения полей DAT_RASH, KOLVO, TOVAR, POKUP могут вводиться компонентами TEdit с именами Dat_Rash_Edit, Kolvo_Edit, Tovar_Edit, Pokup_Edit. Требуется сформировать такой SQL-оператор, чтобы в нем указывались в качестве обновляемых только поля, для которых введено значение в соответствующих компонентах. Вид формы показан на рис. 9.8. Для формирования оператора UPDATE используется такой обработчик нажатия экранной кнопки "Изменить":

procedure TForm!.UpdateButtonClick(Sender: TObject) ;

var TmpN_Rash : Integer;

Cnt : Integer; //счетчик непустых TEdit

Separator : String[1]; //запятая или пустая строка

begin

{ проверку правильности соответствия значения в Dat Rash Edit.Text формату даты и значения в Koivo_Edit.Text формату целого числа для простоты не производим }

Cnt := 0;

IF Dat_Rash_Edit.Text 0 " THEN INC(Cnt);

IF Kolvo_Edit.Text 0 " THEN INC(Cnt);

IF Tovar_Edit.Text 0 " THEN INC(Cnt);

IF Pokup_Edit.Text 0 " THEN INC(Cnt);

IF Cnt = 0 THEN begin

ShowMessage('He введено ни одно новое значение');

Dat_Rash_Edit.SetFocus;

Exit;

END;//if

//формирование текста оператора UPDATE

WITH ListBoxl.Items do begin

Clear;

Add ('UPDATE RASHOD') ;

Add('SET') ;

IF Dat_Rash_Edit.Text <> " THEN begin

DEC(Cnt) ;

IF Cnt > 0 THEN

Separator := ','

ELSE Separator := " ;

Add(' DAT_RASH = \" + Dat_Rash_Edit.Text + '"' -Separator);

END;//if

IF Kolvo_Edit.Text 0 "THEN begin

DEC(Cnt) ;

IF Cnt > 0 THEN

Separator := ','

ELSE

Separator := " ;

Add(' KOLVO = ' + Kolvo_Edit.Text + Separator);

END;//if

IF Tovar_Edit.Text <> " THEN begin

DEC(Cnt);

IF Cnt > 0 THEN Separator := ','

ELSE Separator := " ;

Add(' TOVAR = "' + Tovar_Edit.Text + '"' + Separator);

END;//if

IF Pokup_Edit.Text <> " THEN

Add(' POKUP = "' + Pokup_Edit.Text + '"');

TmpN_Rash := RashodQuery.FieldByName('N_RASH').As Integer;

Add('WHERE N_RASH = ' + IntToSTr(TmpN_Rash)) ;

END;//with

//выполнение сформированного SQL-оператора

WITH UpdateQuery do begin

SQL.Clear;

SQL := ListBoxl.Items;

TRY

ExecSQL;

EXCEPT

on EDBEngineError do begin

ShowMessage('Ошибка БД. Проверьте уникальность номера ' + 'расхода!');

Exit;

end;//do

ELSE

SHowMessage('Неклассифицированная ошибка при ExecSQL') ;

END;//try

RashodQuery.Close;

RashodQuery.Open;

END;//with

9.9. Динамический редактор SQL

Разработаем приложение, в котором пользователь сам вводит текст SQL-запроса, а приложение (если текст запроса правильный) этот запрос выполняет и выдает результаты.

Пусть SQL-оператор вводится в некоторое поле ввода (лучше типа TMemo). После ввода пользователь нажимает экранную кнопку "Выполнить", и только что введенный им запрос выполняется для одной или более ТБД. Вид окна программы, реализующей динамический редактор SQL-операторов, показан на рис.9.9. Результат выполнения запроса выводится в отдельной форме после нажатия в основной форме кнопки "Выполнить SQL-запрос" (рис. 9.10).

Техника реализации такого режима работы проста: текст запроса вводится в компонент Memo1 и перед выполнением переписывается в свойство SQL компонента TQuery (Form2. Query I):

procedure TForm1.GoQueryButtonClick(Sender: TObject) ;

begin

Form2.Query1.Close;

Form2.Query1.SQL.Clear;

Form2.Query1.SQL.Add(Memol.Text) ;

Form2.Query1.Open;

Form2.Show; end;

9.10. Использование TQuery для получения агрегированных значений

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

Пусть, например, необходимо помещать уникальное значение в поле N_RASH (номер записи расхода со склада) в таблицу RASHOD. SQL-операторы INSERT, UPDATE не работают с автоинкрементными полями (локальные СУБД). Поэтому при занесении новой записи в таблицу RASHOD нужно определить уникальное значение поля N_RASH, для чего можно сделать запрос к таблице RASHOD:

WITH WorkQuery do begin

Close;

Clear;

SQL.Add('SELECT COUNT(*), MAX(N_RASH) AS M') ;

SQL.Add('FROM RASHOD') ;

Open;

END;//with

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

INSERT INTO RASHOD (N_RASH, DAT_RASH, KOLVO, TOVAR, POKUP)

VALUES(:N_RASH,:DAT_RASH, :KOLVO,:TOVAR, :POKUP)

Тогда в параметр :N_RASH следует поместить значение поля М набора данных компонента WorkQuery:

WITH InsertQuery do begin

ParamByName('N_RASH').AsInteger :=

WorkQuery.FieldByName('M').AsInteger + 1;

ExecSQL;

END; //with

ПОЯСНЕНИЕ. После выполнения запроса в компоненте WorkQuery выдается результирующий НД, содержащий одну строку. Указатель текущей записи во вновь открытом НД всегда устанавливается на первую запись. Поэтому, даже если таблица RASHOD пуста, в поле М будет значение NULL, преобразуемое затем свойством AsInteger в 0.

Набор данных в компоненте WorkQuery мог бы не содержать ни одной строки (если в ТБД RASHOD не было ни одной строки) тогда и только тогда, если бы подсчет максимума производился оператором

SELECT MAX(N_RASH) AS M FROM RASHOD

Однако, применяя вместо этого оператор

SELECT COUNT(*), MAX(N_RASH) AS M FROM RASHOD

мы всегда получаем хотя бы одну запись в результирующем НД, поскольку COUNT(*) всегда возвратит значение, отличное от NULL.

9.11. Использование компонента TQuery для локальных и удаленных БД

Использование компонента TQuery происходит аналогично для случая выполнения запросов к таблицам локальных СУБД (Paradox, dBase и т.д.) и для случая выполнения запросов к удаленным СУБД (InterBase, Oracle, Informix, Sybase, MS SQL Server). Имеются, правда, ограничения для случая запросов к таблицам локальных СУБД. Эти ограничения состоят в усеченных возможностях использования синтаксиса SQL-операторов.

Характеристики работы компонента TQuery для случая локальных и удаленных СУБД позволяют рекомендовать:

не использовать компонент TQuery для выполнения простых запросов к локальным таблицам там, где вполне можно обойтись компонентом TTable, поскольку TQuery работает медленнее - он всегда создает промежуточную таблицу для формирования возвращаемого набора данных;

• стараться как можно чаще использовать компонент TQuery для доступа к удаленным таблицам.

Явная предпочтительность использования компонента TQuery при доступе к удаленным таблицам определяется способом занесения записей в результирующий НД. Компонент TTable при своем открытии считывает все записи из удаленной таблицы. Если на записи в TTable наложена фильтрация (например, методом SetRange), она выполняется уже в клиентском приложении, что уже не оптимально. Применяемый для аналогичных целей компонент TQuery считывает нужное число записей (например, достаточное количество записей для визуализации в компоненте TDBGnd), а оставшиеся записи считывает по необходимости. Временные задержки при этом особенно актуальны для таблиц, состоящих из большого числа записей.

Одиночные изменения в удаленной таблице, вносимые из TTable при помощи методов Post, Delete, если они совершаются в рамках отдельной транзакции, также способны существенно замедлить работу с БД. Компонента TQuery, посылающие серверу БД запросы на удаление, добавление, корректировку записей, как правило, действуют над группами записей в рамках одной транзакции. Кроме того, даже по своей идеологии компоненты TQuery много больше соответствуют архитектуре "клиент-сервер" и идеологии серверных БД, поскольку одним из основных положений при работе с данными в SQL является оперирование множествами записей. TTable рассчитан на работу с одиночными записями и по своей сущности больше отвечает идеологии локальных (настольных, персональных) СУБД, исповедующих навигационный подход.

ЗАМЕЧАНИЕ. В примерах, приводимых к излагаемому материалу, в частности, по созданию приложений в архитектуре "клиент-сервер", встречается доступ к удаленным таблицам при помощи компонента TTable, но в основном из-за того, что реально "удаленная" БД работает под управлением локальной версии SQL-сервера Borland InterBase, поставляемого вместе с Delphi Client/Server Suite, а также из-за того, что объем данных в учебных таблицах не превышает 10 записей.

Исследовать процесс соединения с сервером и реальные процессы доступа к БД, которые порождают те или иные SQL-операторы, можно, запустив приложение на выполнение под средой Delphi и вызвав SQL Monitor (пункт главного меню Database \ SQL Monitor или запустив его как отдельное приложение). В качестве примера приведем окно SQL Monitor, показывающее (рис. 9.11 и 9.12) операции над БД из приложения для выполнения оператора SELECT, реализующего внутреннее соединение таблиц RASHOD (8 записей) и TOVARY (3 записи):

SELECT R.DAT_RASH, R.TOVAR, R.KOLVO, Т.ZENA FROM RASHOD R, TOVARY T WHERE R.TOVAR = T.TOVAR

Для некоторого убыстрения доступа к НД, который должен просматриваться последовательно только в направлении от начала до конца, можно установить в свойство UniDirectional компонента TQuery значение False:

property UniDirectional: Boolean;

Это позволит отключить для НД механизм двунаправленных курсоров в BDE, что для больших объемов записей способно привести к экономии времени и ресурсов.