Иптышев А.А., Морозов Д.И., Городилов А.А., Иптышев М.А. Базы данных

Подождите немного. Документ загружается.

Continent=‘South America’

После чего выставить свойство Filtered у Table1 в True.

При создании динамических фильтров можно изменять как выражение

фильтра целиком, так и его части. Например, ограничивающее значение для поля

можно задавать при помощи элементов управления формы, что позволяет

пользователю приложения управлять фильтрацией набора данных. Перед

изменением текста динамического фильтра или для отключения фильтра свойству

Filtered присваивается значение False, а как только его изменить на True, фильтр

начнет работать:

procedure TForm1.Button1Click(Sender: TObject);

begin

Tablel.Filtered:= False;

Tablel.Filter := ‘Continent=’+Edit1.Text;

Tablel.Filtered := True;

end;

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

для этого используется символ звездочки (*). Следущий пример оставит все записи

стран, названия которых начинаются на ‘S’:

Country=‘S*’

9.3. ФИЛЬТРАЦИЯ ПО ДИАПАЗОНУ

Процедура ApplyRange позволяет установить фильтр, который ограничивает

диапазон просматриваемых записей. Например, в таблице Employee, значения в

поле EmpNo имеют диапазон от 2 до 500. Если необходимо оставить только те

записи, в которых номер работника находится между 100 и 200, то можно

использовать метод ApplyRange, и еще два связанных с ним метода. Данные методы

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

Вот процедуры, которые чаще всего используются при установке фильтров по

диапазону:

- SetRangeStart;

- SetRangeEnd;

-ApplyRange;

-CancelRange;

Кроме того, у TTable есть дополнительные методы для управления фильтрами:

-EditRangeStart;

-EditRangeEnd;

-SetRange;

Алгоритм работы с этими процедурами следующий:

1. Сначала вызвается SetRangeStart и используя свойство Fields определяется

начало диапазона.

2. Затем вызвав SetRangeEnd и вновь использовав свойство Fields

определяется конец диапазона.

3. Первые два шага подготавливают фильтр, и теперь все что необходимо, это

вызвать ApplyRange, и новый фильтр вступит в силу.

4. Когда нужно прекратить действие фильтра – вызывается CancelRange.

Вот пример демонстрирующий указанный порядок действий:

Table1.SetRangeStart;

Table1.Fields[0].Value := ‘100’;

Table1.SetRangeEnd;

Table1.Fields[0].Value := ‘200’;

Table1.ApplyRange;

Для одновременного задания верхней и нижней границы диапазона можно

использовать метод SetRange:

Table1.SetRange([‘100’], [‘200’]);

Table1.ApplyRange;

Тем, какая граница будет у диапазона – открытая или закрытая, управляет

свойство KeyExclusive. Если оно имеет значение True, граничные значения в

диапазоне присутствовать не будут, в противном случае – они включаются в

заданный диапазон.

9.4. СРАВНЕНИЕ СПОСОБОВ ФИЛЬТРАЦИИ

При использовании фильтра его текст транслируется в синтаксис SQL и

передается для выполнения на сервер или через соответствующий драйвер в

локальную СУБД. Поэтому, при работе с большими базами данных фильтрация,

таким образом, работает намного быстрее, по сравнению с методом OnFilterRecord.

Однако, при работе с небольшими таблицами, падение быстродействия будет

незначительным, зато написание фильтров в обработчике события OnFilterRecord

даст большую гибкость и более полный контроль над процессом фильтрации. С

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

OnFilterRecord намного более трудоёмко, так как для этого требуется переназначать

это событие, что в большинстве случаев неудобно.

10. ЯЗЫК SQL: ВОЗМОЖНОСТИ, ПРИМЕНЕНИЕ, ОСНОВНЫЕ

ПРИНЦИПЫ ПОСТРОЕНИЯ

10.1. SQL-ЗАПРОСЫ В DELPHI

10.1.1. Компонент TQuery

Компоненты TQuery так же, как и TTable наследуется от TDataset. TDataset

обеспечивает необходимую функциональность для получения доступа к базам

данных (свойства DatabaseName, SessionName и пр.). При использовании TTable,

возможен доступ лишь ко всему набору записей из одной таблицы, TQuery же

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

многом, методика работы с объектом TQuery похожа на методику работы с TTable,

однако есть свои особенности.

10.1.2. Свойство SQL

Свойство SQL принадлежит компоненту TQuery и имеет тип TStrings, который

означает что это ряд строк, сохраняемых в списке. Доступ к этому свойству

происходит либо через Инспектор Объектов на этапе проектирования или

программно во время выполнения программы (динамическое изменение запроса).

Это дает ряд преимуществ, если требуется выполнить три SQL запроса, то не надо

размещать три компонента TQuery на форме. Вместо этого можно разместить один

и просто изменять свойство SQL три раза.

Проще всего создать SQL запрос используя компонент TQuery следующим

образом:

- Назначить требуемый псевдоним (Alias) в свойстве DatabaseName.

Например, DBDEMOS.

- Используя свойство SQL, ввести SQL-запрос типа “SELECT * FROM

Country”.

- Вызвать метод Open.

Следующий пример демонстрирует как можно получить доступ к свойству

SQL на этапе выполнения программы. Здесь SQL-запрос вводится в редакторе строк

Memo1:

procedure TMainForm.Button1Click(Sender: TObject);

begin

try

if Query1.Active then Query1.Close;

Query1.SQL.Clear;

Query1.SQL.Assign(Memo1.Lines);

Query1.Open;

except

one : EDBEngineError do ShowMessage('Ошибка!');

end;

10.1.3. Свойство Params

Этого должно быть достаточно для пользователя, знающего SQL. Тем не

менее, большинство пользователей не знает этого языка. Итак, ваша работа как

разработчика заключается в предоставлении интерфейса и создании SQL-запроса. В

Delphi, для создания SQL-запроса на лету можно использовать динамические

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

определения параметра в запросе используется двоеточие (:), за которым следует

имя параметра. Ниже приведе пример SQL-запроса с использованием

динамического параметра:

SELECT * FROM Employee

WHERE Salary = :Salary

Если вам нужно протестировать, или установить для параметра значение по

умолчанию, выберите свойство Params объекта Query1. Щелкните на кнопке '...'.

Должен появиться диалог настройки параметров. Добавьте параметр Salary. Затем в

выпадающем списке типов данных выберите Integer. Для того, чтобы задать

значение по умолчанию, введите нужное значение в поле редактирования "Value".

Для изменения SQL-запроса во время выполнения приложения, параметры

необходимо связать (bind). Параметры могут изменяться, запрос выполняться

повторно, а данные обновляться. Для непосредственного редактирования значения

параметра используется свойство Params или метод ParamByName. Свойство Params

представляет из себя массив TParams. Поэтому для получения доступа к параметру,

необходимо указать его индекс. Для примера,

Query1.Params[0].asInteger := StrToInt(Edit1.Text);

Свойство asInteger читает данные как тип Integer (название говорит само за

себя). Это не обязательно должно указывать на то, что поле имеет тип Integer.

Например, если тип поля VARCHAR(10), Delphi осуществит преобразование

данных. Так, приведенный выше пример мог бы быть записан таким образом:

Query1.Params[0].asString := Edit1.Text;

Если вместо номера индекса вы хотели бы использовать имя параметра, то

воспользуйтесь методом ParamByName. Данный метод возвращает объект TParam с

заданным именем. Например:

Query1.ParamByName(‘Salary’).asInteger := StrToInt(Edit1.Text);

Обратите внимание на процедуру, первым делом подготовливающую запрос.

При вызове метода Prepare, Delphi посылает SQL запрос на удаленный сервер.

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

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

Альтернативой здесь может служить подготовка сервером запроса при каждом его

выполнении. Как только запрос подготовлен, подставляются необходимые новые

параметры, и запрос выполняется.

Способ подключения TQuery к TTable - через TDataSource. Есть два основных

способа сделать это. Во-первых, разместить код в обработчике события TDataSource

OnDataChange. Пример, использования события OnDataChange для просмотра

дочерних записей:

procedure TForm1.DataSource1DataChange(Sender: TObject; Field:

TField);

begin

if Query1.Active then Query1.Close;

if not(Query1.Prepared) then Query1.Prepare;

Query1.ParamByName('Salary').asInteger := Table1Salary.asInteger;

try

Query1.Open;

except

one : EDatabaseError do

MessageDlg(e.Message, mtError, [mbOK], 0);

end;

Техника с использованием OnDataChange очень гибка, но есть еще легче

способ подключения Query к таблице. Компонент TQuery имеет свойство

DataSource. Определяя TDataSource для свойства DataSource, объект TQuery

сравнивает имена параметров в SQL-запросе с именами полей в TDataSource. В

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

Фактически, техника использования DataSource не требует никакого

дополнительного кодирования.

Выберите у Query1 свойство SQL и введите:

SELECT * FROM Employee

WHERE Salary = :Salary

Выберите свойство DataSource и назначьте источник данных, связанный с

Table1 (DataSource2 в нашем примере). Выберите свойство Active и установите его в

True.

10.1.4. Команда Format

Команда Format заменяет параметры форматирования (%s, %d, %n и пр.)

передаваемыми значениями (такой способ задания параметров при выводе строк

широко используется на языке C). Например,

Format('SELECT * FROM %s', ['Employee'])

Результатом будет аналогичен результату запроса

SELECT * FROM Employee

Функция буквально делает замену параметров форматирования значениями

массива. При использовании нескольких параметров форматирования, замена

происходит слева направо. Например,

tblName := 'Employee';

fldName := 'EmpNo';

fldValue := 2;

Format('SELECT * FROM %s WHERE %s=%d', [tblName, fldName,

fldValue])

Результатом команды форматирования будет

SELECT * FROM Employee WHERE EmpNo=2

Такая функциональность обеспечивает чрезвычайную гибкость при

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

10.2. КОНТРОЛЬ ЗАПРОСОВ С ПОМОЩЬЮ SQL MONITOR

SQL Monitor (рис. 27) используется для контроля запросов, пересылаемых

клиентским приложением серверу баз данных посредством BDE, и их результатов, а

также измерения времени между ними. Для его запуска следует выбрать пункт SQL

Monitor из меню Database Delphi. Главное окно SQL Monitor состоит из двух частей.

В верхней части отображаются последовательно генерируемые SQL-предложения и

сведения об откликах сервера, а также порядковый номер и время их наступления, а

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

можно сохранить в файле для дальнейшего анализа. На рис.27 представлен

типичный вывод сведений при работе приложения, рассмотренного в предыдущей

статье данного цикла.

Рис. 27 Интерфейс программы SQL Monitor

10.3. SQL EXPLORER

SQL Explorer (рис.28) является инструментом для создания статических

запросов. Для его запуска следует выбрать пункт в меню Database команду Explore

или запустить его в качестве отдельного приложения. В левой части окна Explorer на

странице Databases представлены в виде раскрывающегося списка свойства всех

описанных в файле конфигурации BDE псевдонимов. В правой части окна можно

просмотреть содержимое таблиц, ввести и выполнить SQL-запрос к таблице, а также

получить сведения о псевдонимах баз данных (драйвер БД, местоположение,

используемый языковый драйвер и иные параметры, содержащиеся в файле

конфигурации BDE), сведения о таблицах (тип таблицы, версия, дата последнего

обновления и др.) и, при необходимости, внести в них изменения.

Рис. 28 Интерфейс программы SQL Explorer

Компонент TQuery позволяет использовать язык SQL для получения

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

TQuery можно использовать как с серверами данных файлового типа (например,

Paradox и dBASE), так и с серверами SQL. После назначения свойству DatabaseName

компонента TQuery псевдонима или пути к каталогу, можно ввести в его свойство

SQL те операторы языка SQL, которые необходимо выполнить в выбранной базе

данных. Например, если объект Queryl связан с псевдонимом DBDEMOS,

следующий код будет возвращать все записи в таблице BIOLIFE, в которых

значение поля Length (cm) больше 100:

select * from BIOLIFE where BIOLIFE."Length (cm)" > 100

Подобно другим наборам данных, запрос будет выполняться в том случае,

если его свойству Active присвоено значение True или после вызова метода Open ().

Если же требуется выполнить запрос, который не возвращает результирующий

набор данных

(например, insert into), для его выполнения необходимо использовать метод

ExecuteSQL(), а не метод Ореп().

Другим важным свойством компонента TQuery является свойство RequestLive.

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

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

значение следует установить равным True.

Простая установка значения свойства RequestLive не гарантирует, что

результирующий набор данных можно будет редактировать. В зависимости от

структуры запроса, ядро BDE может оказаться неспособным получить

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

которых содержится ключевое слово having, используется функция to_date или поле

с абстрактным типом данных (ADT — Abstract Data Type), предоставляют

результаты, которые нельзя отредактировать. (Для получения полного перечня

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

определить, можно ли будет редактировать результаты запроса, проверьте перед его

открытием значение свойства CanModif у.

В следующей главе мы подробнее поговорим о свойствах компонента TQuery,

таких как параметрические запросы и SQL-оптимизация.

11. СИНТАКСИС ОСНОВНЫХ ЗАПРОСОВ, ИХ ОПЦИЙ И

ПАРАМЕТРОВ

11.1. ЯЗЫК SQL. ВВЕДЕНИЕ

11.1.1. Состав языка SQL

Structured Query Language (SQL) представляет собой непроцедурный язык,

используемый для управления данными реляционных СУБД. В этом языке

отсутствуют алгоритмические конструкции, такие как метки, операторы цикла,

условные переходы и др. Язык SQL предназначен для манипулирования данными в

реляционных базах данных, определения структуры баз данных и для управления

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

Поэтому, в язык SQL в качестве составных частей входят:

- язык манипулирования данными (Data Manipulation Language, DML)

- язык определения данных (Data Definition Language, DDL)

- язык управления данными (Data Control Language, DCL).

11.1.2. Обзор

На данном уроке мы познакомимся возможностью создания таблиц через

посылку SQL-запросов. С помощью Database Desktop удобно создавать или

локальные базы данных или только простейшие SQL-серверные базы данных,

состоящие из небольшого числа таблиц, не очень сильно связанных друг с другом.

Если же Вам необходимо создать базу данных, состоящую из большого числа

таблиц, имеющих сложные взаимосвязи, можно воспользоваться языком SQL. При

этом можно воспользоваться компонентом Query в Delphi, каждый раз посылая по

одному SQL-запросу, а можно записать всю последовательность SQL-предложений

в один так называемый скрипт и послать его на выполнение. Конечно, для этого

нужно хорошо знать язык SQL, но сложного в этом ничего нет! Конкретные

реализации языка SQL незначительно отличаются в различных SQL-серверах,

однако базовые предложения остаются одинаковыми для всех реализаций.

11.2. СОЗДАНИЕ ТАБЛИЦ С ПОМОЩЬЮ SQL

Если Вы хотите воспользоваться компонентом TQuery, сначала поместите его

на форму. После этого настройте свойство DatabaseName на нужный Вам алиас.

После этого можно ввести SQL-предложение в свойство SQL. Для выполнения

запроса, изменяющего структуру, вставляющего или обновляющего данные на

сервере, нужно вызвать метод ExecSQL компонента TQuery. Для выполнения

запроса, получающего данные с сервера (т.е. запроса, в котором основным является

оператор SELECT), нужно вызвать метод Open компонента TQuery. Это связано с

тем, что BDE при посылке запроса типа SELECT открывает так называемый курсор,

с помощью которого осуществляется навигация по выборке данных (подробней об

этом см. в уроке, посвященном TQuery).

Приведем упрощенный синтаксис SQL-предложения для создания таблицы на

SQL-сервере InterBase (более полный синтаксис можно посмотреть в online-

справочнике по SQL, поставляемом с локальным InterBase):

CREATE TABLE table

(<col_def> [, <col_def> | <tconstraint> ...]);

где

table - имя создаваемой таблицы,

<col_def> - описание поля,

<tconstraint> - описание ограничений и/или ключей (квадратные скобки []

означают необязательность, вертикальная черта | означает “или”).

Описание поля состоит из наименования поля и типа поля (или домена - см. урок 9),

а также дополнительных ограничений, накладываемых на поле:

<col_def> = col {datatype | COMPUTED BY (<expr>) | domain}

[DEFAULT {literal | NULL | USER}]

[NOT NULL] [<col_constraint>]

[COLLATE collation]

Здесь

col - имя поля;

datatype - любой правильный тип SQL-сервера (для InterBase такими типами

являются SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION, DECIMAL,

NUMERIC, DATE, CHAR, VARCHAR, NCHAR, BLOB), символьные типы могут

иметь CHARACTER SET - набор символов, определяющий язык страны. Для

русского языка следует задать набор символов WIN1251;

COMPUTED BY (<expr>) - определение вычисляемого на уровне сервера поля,

где <expr> - правильное SQL-выражение, возвращающее единственное значение;

domain - имя домена (обобщенного типа), определенного в базе данных;

DEFAULT - конструкция, определяющая значение поля по умолчанию;

NOT NULL - конструкция, указывающая на то, что поле не может быть

пустым;

COLLATE - предложение, определяющее порядок сортировки для выбранного

набора символов (для поля типа BLOB не применяется). Русский набор символов

WIN1251 имеет 2 порядка сортировки - WIN1251 и PXW_CYRL. Для правильной

сортировки, включающей большие буквы, следует выбрать порядок PXW_CYRL.

Описание ограничений и/или ключей включает в себя предложения

CONSTRAINT или предложения, описывающие уникальные поля, первичные,

внешние ключи, а также ограничения CHECK (такие конструкции могут

определяться как на уровне поля, так и на уровне таблицы в целом, если они

затрагивают несколько полей):

<tconstraint> = [CONSTRAINT constraint <tconstraint_def>]

Здесь

<tconstraint_def> = {{PRIMARY KEY | UNIQUE} (col[,col...]) |

FOREIGN KEY (col [, col ...]) REFERENCES other_table

| CHECK (<search_condition>)}

<search_condition> =

{<val> <operator> {<val> | (<select_one>)}

| <val> [NOT] BETWEEN <val> AND <val>

| <val> [NOT] LIKE <val> [ESCAPE <val>]

| <val> [NOT] IN (<val> [, <val> ...] |

<val> = {

col [<array_dim>] | <constant> | <expr> | <function>

| NULL | USER | RDB$DB_KEY } [COLLATE collation]

<constant> = num | "string" | charsetname "string"

<function> = {

COUNT (* | [ALL] <val> | DISTINCT <val>)

| SUM ([ALL] <val> | DISTINCT <val>)

| AVG ([ALL] <val> | DISTINCT <val>)

| MAX ([ALL] <val> | DISTINCT <val>)

| MIN ([ALL] <val> | DISTINCT <val>)

| CAST (<val> AS <datatype>)

| UPPER (<val>)

| GEN_ID (generator, <val>)

}

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}