Урок 1: Настройка BDE

Содержание урока 1:

Обзор

На этом уроке мы познакомимся с ядром баз данных компании Борланд - Borland Database Engine (BDE), а также научимся создавать и редактировать алиасы - механизм, облегчающий связь с базами данных. Кроме того, мы изучим, как конфигурировать ODBC драйверы.

Сущность BDE

Мощность и гибкость Delphi при работе с базами данных основана на низкоуровневом ядре - процессоре баз данных Borland Database Engine (BDE). Его интерфейс с прикладными программами называется Integrated Database Application Programming Interface (IDAPI). В принципе, сейчас не различают эти два названия (BDE и IDAPI) и считают их синонимами. BDE позволяет осуществлять доступ к данным как с использованием традиционного record-ориентированного (навигационного) подхода, так и с использованием set-ориентированного подхода, используемого в SQL-серверах баз данных. Кроме BDE, Delphi позволяет осуществлять доступ к базам данных, используя технологию (и, соответственно, драйверы) Open DataBase Connectivity (ODBC) фирмы Microsoft. Но, как показывает практика, производительность систем с использованием BDE гораздо выше, чем оных при использовании ODBC. ODBC драйвера работают через специальный “ODBC socket”, который позволяет встраивать их в BDE.

Все инструментальные средства баз данных Borland - Paradox, dBase, Database Desktop - используют BDE. Все особенности, имеющиеся в Paradox или dBase, “наследуются” BDE, и поэтому этими же особенностями обладает и Delphi.

Алиасы

Таблицы сохраняются в базе данных. Некоторые СУБД сохраняют базу данных в виде нескольких отдельных файлов, представляющих собой таблицы (в основном, все локальные СУБД), в то время как другие состоят из одного файла, который содержит в себе все таблицы и индексы (InterBase). Например, таблицы dBase и Paradox всегда сохраняются в отдельных файлах на диске. Каталог, содержащий dBase .DBF файлы или Paradox .DB файлы, рассматривается как база данных. Другими словами, любой каталог, содержащий файлы в формате Paradox или dBase, рассматривается Delphi как единая база данных. Для переключения на другую базу данных нужно просто переключиться на другой каталог. Как уже было указано выше, InterBase сохраняет все таблицы в одном файле, имеющем расширение .GDB, поэтому этот файл и есть база данных InterBase.

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

Для создания алиаса запустите утилиту конфигурации BDE (программу bdeadmin.exe), находящуюся в каталоге, в котором располагаются динамические библиотеки BDE.

Рис. 1: Главное окно утилиты конфигурации BDE

Главное окно утилиты настройки BDE имеет вид, изображенный на рис.1. Для создания алиаса выберите в меню “Object” пункт “New”. В появившемся диалоговом окне выберите имя драйвера базы данных. Тип алиаса может быть стандартным (STANDARD) для работы с локальными базами в формате dBase или Paradox или соответствовать наименованию SQL-сервера (InterBase, Sybase, Informix, Oracle и т.д.).

Рис. 2: В диалоговом окне добавления нового алиаса можно указать тип базы данных

После создания нового алиаса следует дать ему имя. Это можно сделать с помощью подпункта “Rename” меню “Object”. Однако просто создать алиас не достаточно. Вам нужно указать дополнительную информацию, содержание которой зависит от типа выбранной базы данных. Например, для баз данных Paradox и dBase (STANDARD) требуется указать лишь путь доступа к данным, имя драйвера и флаг ENABLE BCD, который определяет, транслирует ли BDE числа в двоично-десятичном формате (значения двоично-десятичного кода устраняют ошибки округления):

TYPE	STANDARD
DEFAULT DRIVER	PARADOX
ENABLE BCD	FALSE
PATH	c:\users\data

SQL-сервер InterBase и другие типы баз данных требуют задания большого количества параметров, многие из которых можно оставить установленными по умолчанию.

Системная информация утилиты настройки BDE

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

Рассмотрим, например, системную информацию драйвера PARADOX:

• NET DIR. Параметр содержит расположение каталога сетевого управляющего файла. Он нужен для того, чтобы обратиться к таблице PARADOX на сетевом диске.

• VERSION. Номер версии драйвера.

• TYPE. Тип драйвера.

• LANGDRIVER. Языковой драйвер, определяющий множество допустимых символов.

• BLOCK SIZE. Размер блока на диске, используемого для запоминания одной записи.

• FILL FACTOR. Содержит процент от блока на текущем диске. Параметр нужен для создания индексных файлов.

• LEVEL. Параметр определяет тип формата таблицы, используемой для создания временных таблиц.

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

Как уже отмечалось выше, утилита настройки BDE сохраняет всю конфигурационную информацию в файле IDAPI.CFG. Этот файл с предустановленными ссылками на драйверы и некоторыми стандартными алиасами создается при установке Delphi. Кроме того, он создается при установке файлов редистрибуции BDE (т.е. когда Вы переносите BDE и SQL Links на другие компьютеры).

Заключение

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

Урок 2: Создание таблиц с помощью Database Desktop

Содержание урока 2:

Обзор

На данном уроке мы изучим, как создавать таблицы базы данных с помощью утилиты Database Desktop, входящей в поставку Delphi. Хотя для создания таблиц можно использовать различные средства (SQL - компонент TQuery и компонент TTable), применение этой утилиты позволяет создавать таблицы в интерактивном режиме и сразу же просмотреть их содержимое - и все это для большого числа форматов. Это особенно удобно для локальных баз данных, в частности Paradox и dBase.

Утилита Database Desktop

Database Desktop - это утилита, во многом похожая на Paradox, которая поставляется вместе с Delphi для интерактивной работы с таблицами различных форматов локальных баз данных - Paradox и dBase, а также SQL-серверных баз данных InterBase, Oracle, Informix, Sybase (с использованием SQL Links). Исполняемый файл утилиты называется DBD32.EXE. Для запуска Database Desktop просто дважды щелкните по ее иконке.

Рис. 1: Выпадающий список в диалоговом окне Table Type позволяет выбрать тип создаваемой таблицы

После старта Database Desktop выберите команду меню File|New|Table для создания новой таблицы. Перед Вами появится диалоговое окно выбора типа таблицы, как показано на рис.1. Вы можете выбрать любой формат из предложенного, включая различные версии одного и того же формата.

После выбора типа таблицы Database Desktop представит Вам диалоговое окно, специфичное для каждого формата, в котором Вы сможете определить поля таблицы и их тип, как показано на рис.2.

Рис. 2: Database Desktop позволяет задать имена и типы полей в таблице

Имя поля в таблице формата Paradox представляет собой строку, написание которой подчиняется следующим правилам:

• Имя должно быть не длиннее 25 символов.

• Имя не должно начинаться с пробела, однако может содержать пробелы. Однако, если Вы предполагаете в будущем переносить базу данных в другие форматы, разумнее будет избегать включения пробелов в название поля. Фактически, в целях переносимости лучше ограничиться девятью символами в названии поля, не включая в него пробелы.

• Имя не должно содержать квадратные, круглые или фигурные скобки [], () или {}, тире, а также комбинацию символов “тире” и “больше” (->).

• Имя не должно быть только символом #, хотя этот символ может присутствовать в имени среди других символов. Хотя Paradox поддерживает точку (.) в названии поля, лучше ее избегать, поскольку точка зарезервирована в Delphi для других целей.

Имя поля в таблице формата dBase представляет собой строку, написание которой подчиняется правилам, отличным от Paradox:

• Имя должно быть не длиннее 10 символов.

• Пробелы в имени недопустимы.

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

Укажем еще правила, которым подчиняется написание имен полей в формате InterBase.

• Имя должно быть не длиннее 31 символа.

• Имя должно начинаться с букв A-Z, a-z.

• Имя поля может содержать буквы (A-Z, a-z), цифры, знак $ и символ подчеркивания (_).

• Пробелы в имени недопустимы.

• Для имен таблиц запрещается использовать зарезервированные слова InterBase.

Следующий (после выбора имени поля) шаг состоит в задании типа поля. Типы полей очень сильно различаются друг от друга, в зависимости от формата таблицы. Для получения списка типов полей перейдите к столбцу “Type”, а затем нажмите пробел или щелкните правой кнопкой мышки. Приведем списки типов полей, характерные для форматов Paradox, dBase и InterBase.

Итак, поля таблиц формата Paradox могут иметь следующий тип (для ввода типа поля можно набрать только подчеркнутые буквы или цифры):

Табл. A: Типы полей формата Paradox

Alpha	строка длиной 1-255 байт, содержащая любые печатаемые символы
Number	числовое поле длиной 8 байт, значение которого может быть положительным и отрицательным. Диапазон чисел - от 10-308 до 10308 с 15 значащими цифрами
$ (Money)	числовое поле, значение которого может быть положительным и отрицательным. По умолчанию, является форматированным для отображения десятичной точки и денежного знака
Short	числовое поле длиной 2 байта, которое может содержать только целые числа в диапазоне от -32768 до 32767
Long Integer	числовое поле длиной 4 байта, которое может содержать целые числа в диапазоне от -2147483648 до 2147483648
# (BCD)	числовое поле, содержащее данные в формате BCD (Binary Coded Decimal). Скорость вычислений немного меньше, чем в других числовых форматах, однако точность - гораздо выше. Может иметь 0-32 цифр после десятичной точки
Date	поле даты длиной 4 байта, которое может содержать дату от 1 января 9999 г. до нашей эры - до 31 декабря 9999 г. нашей эры. Корректно обрабатывает високосные года и имеет встроенный механизм проверки правильности даты
Time	поле времени длиной 4 байта, содержит время в миллисекундах от полуночи и ограничено 24 часами
@ (Timestamp)	обобщенное поле даты длиной 8 байт - содержит и дату и время
Memo	поле для хранения символов, суммарная длина которых более 255 байт. Может иметь любую длину. При этом размер, указываемый при создании таблицы, означает количество символов, сохраняемых в таблице (1-240) - остальные символы сохраняются в отдельном файле с расширением .MB
Formatted Memo	поле, аналогичное Memo, с добавлением возможности задавать шрифт текста. Также может иметь любую длину. При этом размер, указываемый при создании таблицы, означает количество символов, сохраняемых в таблице (0-240) - остальные символы сохраняются в отдельном файле с расширением .MB. Однако, Delphi в стандартной поставке не обладает возможностью работать с полями типа Formatted Memo
Graphic	поле, содержащее графическую информацию. Может иметь любую длину. Смысл размера - такой же, как и в Formatted Memo. Database Desktop “умеет” создавать поля типа Graphic, однако наполнять их можно только в приложении
OLE	поле, содержащее OLE-данные (Object Linking and Embedding) - образы, звук, видео, документы - которые для своей обработки вызывают создавшее их приложение. Может иметь любую длину. Смысл размера - такой же, как и в Formatted Memo. Database Desktop “умеет” создавать поля типа OLE, однако наполнять их можно только в приложении. Delphi “напрямую” не умеет работать с OLE-полями, но это легко обходится путем использования потоков
Logical	поле длиной 1 байт, которое может содержать только два значения - T (true, истина) или F (false, ложь). Допускаются строчные и прописные буквы
+ (Autoincrement)	поле длиной 4 байта, содержащее нередактируемое (read-only) значение типа long integer. Значение этого поля автоматически увеличивается (начиная с 1) с шагом 1 - это очень удобно для создания уникального идентификатора записи (физический номер записи не может служить ее идентификатором, поскольку в Парадоксе таковой отсутствует. В InterBase также отсутствуют физические номера записей, но отсутствует и поле Autoincrement. Его с успехом заменяет встроенная функция Gen_id, которую удобней всего применять в триггерах)
Binary	поле, содержащее любую двоичную информацию. Может иметь любую длину. При этом размер, указываемый при создании таблицы, означает количество символов, сохраняемых в таблице (0-240) - остальные символы сохраняются в отдельном файле с расширением .MB. Это полнейший аналог поля BLOb в InterBase
Bytes	строка цифр длиной 1-255 байт, содержащая любые данные

Поля таблиц формата dBase могут иметь следующий тип (для ввода типа поля можно набрать только подчеркнутые буквы или цифры):

Табл. B: Типы полей формата dBase

Character (alpha)	строка длиной 1-254 байт, содержащая любые печатаемые символы
Float (numeric)	числовое поле размером 1-20 байт в формате с плавающей точкой, значение которого может быть положительным и отрицательным. Может содержать очень большие величины, однако следует иметь в виду постоянные ошибки округления при работе с полем такого типа. Число цифр после десятичной точки (параметр Dec в DBD) должно быть по крайней мере на 2 меньше, чем размер всего поля, поскольку в общий размер включаются сама десятичная точка и знак
Number (BCD)	числовое поле размером 1-20 байт, содержащее данные в формате BCD (Binary Coded Decimal). Скорость вычислений немного меньше, чем в других числовых форматах, однако точность - гораздо выше. Число цифр после десятичной точки (параметр Dec в DBD) также должно быть по крайней мере на 2 меньше, чем размер всего поля, поскольку в общий размер включаются сама десятичная точка и знак
Date	поле даты длиной 8 байт. По умолчанию, используется формат короткой даты (ShortDateFormat)
Logical	поле длиной 1 байт, которое может содержать только значения “истина” или “ложь” - T,t,Y,y (true, истина) или F,f,N,n (false, ложь). Допускаются строчные и прописные буквы. Таким образом, в отличие от Парадокса, допускаются буквы “Y” и “N” (сокращение от Yes и No)
Memo	поле для хранения символов, суммарная длина которых более 255 байт. Может иметь любую длину. Это поле хранится в отдельном файле. Database Desktop не имеет возможности вставлять данные в поле типа Memo
OLE	поле, содержащее OLE-данные (Object Linking and Embedding) - образы, звук, видео, документы - которые для своей обработки вызывают создавшее их приложение. Может иметь любую длину. Это поле также сохраняется в отдельном файле. Database Desktop “умеет” создавать поля типа OLE, однако наполнять их можно только в приложении. Delphi “напрямую” не умеет работать с OLE-полями, но это легко обходится путем использования потоков
Binary	поле, содержащее любую двоичную информацию. Может иметь любую длину. Данное поле сохраняется в отдельном файле с расширением .DBT. Это полнейший аналог поля BLOb в InterBase

Поля таблиц формата InterBase могут иметь следующий тип:

Табл. C: Типы полей формата InterBase

SHORT	числовое поле длиной 2 байта, которое может содержать только целые числа в диапазоне от -32768 до 32767
LONG	числовое поле длиной 4 байта, которое может содержать целые числа в диапазоне от -2147483648 до 2147483648
FLOAT	числовое поле длиной 4 байта, значение которого может быть положительным и отрицательным. Диапазон чисел - от 3.4*10-38 до 3.4*1038 с 7 значащими цифрами
DOUBLE	числовое поле длиной 8 байт (длина зависит от платформы), значение которого может быть положительным и отрицательным. Диапазон чисел - от 1.7*10-308 до 1.7*10308 с 15 значащими цифрами
CHAR	строка символов фиксированной длины (0-32767 байт), содержащая любые печатаемые символы. Число символов зависит от Character Set, установленного в InterBase для данного поля или для всей базы данных (например, для символов в кодировке Unicode число символов будет в два раза меньше длины строки)
VARCHAR	строка символов переменной длины (0-32767 байт), содержащая любые печатаемые символы. Число символов также зависит от Character Set, установленного в InterBase для данного поля или для всей базы данных
DATE	поле даты длиной 8 байт, значение которого может быть от 1 января 100 года до 11 декабря 5941 года (время также содержится)
BLOB	поле, содержащее любую двоичную информацию. Может иметь любую длину. Database Desktop не имеет возможности редактировать поля типа BLOB
ARRAY	поле, содержащее массивы данных. InterBase позволяет определять массивы, имеющие размерность 16. Поле может иметь любую длину. Однако, Database Desktop не имеет возможности не только редактировать поля типа ARRAY, но и создавать их
TEXT BLOB	подтип BLOB-поля, содержащее только текстовую информацию. Может иметь любую длину. Database Desktop не имеет возможности редактировать поля типа TEXT BLOB

Типы полей могут отличаться от приведенных выше. Это зависит от версии драйвера базы данных.

Итак, мы изучили все типы полей, являющиеся “родными” для Delphi.

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

После создания таблицы, с ней можно связать некоторые свойства, перечень которых зависит от формата таблицы. Так, для таблиц формата Paradox можно задать:

• Validity Checks (проверка правильности) - относится к полю записи и определяет минимальное и максимальное значение, а также значение по умолчанию. Кроме того, позволяет задать маску ввода

• Table Lookup (таблица для “подсматривания”) - позволяет вводить значение в таблицу, используя уже существующее значение в другой таблице

• Secondary Indexes (вторичные индексы) - позволяют доступаться к данным в порядке, отличном от порядка, задаваемого первичным ключом

• Referential Integrity (ссылочная целостность) - позволяет задать связи между таблицами и поддерживать эти связи на уровне ядра. Обычно задается после создания всех таблиц в базе данных

• Password Security (парольная защита) - позволяет закрыть таблицу паролем

• Table Language (язык таблицы) - позволяет задать для таблицы языковый драйвер.

В таблицах dBase не существует первичных ключей. Однако, это обстоятельство можно преодолеть путем определения уникальных (Unique) и поддерживаемых (Maintained) индексов (Indexes). Кроме того, для таблиц dBase можно определить и язык таблицы (Table Language) - языковый драйвер, управляющий сортировкой и отображением символьных данных.

Определения дополнительных свойств таблиц всех форматов доступны через кнопку “Define” (для таблиц InterBase данная кнопка называется “Define Index...” и позволяет определять лишь только индекс, но не первичный ключ) в правой верхней части окна (группа Table Properties). Причем, все эти действия можно проделывать не только при создании таблицы, но и тогда, когда она уже существует. Для этого используется команда Table|Restructure Table (для открытой в данный момент таблицы) или Utilities|Restructure (с возможностью выбора таблицы). Однако, если Вы желаете изменить структуру или добавить новые свойства для таблицы, которая в данный момент уже используется другим приложением, Database Desktop откажет Вам в этом, поскольку данная операция требует монопольного доступа к таблице. Но зато все произведенные в структуре изменения сразу же начинают “работать” - например, если Вы определите ссылочную целостность для пары таблиц, то при попытке вставить в дочернюю таблицу данные, отсутствующие в родительской таблице, в Delphi возникнет исключительное состояние.

В заключение отметим еще часто используемую очень полезную возможность Database Desktop. Создавать таблицу любого формата можно не только “с чистого листа”, но и путем копирования структуры уже существующей таблицы. Для этого достаточно воспользоваться кнопкой “Borrow”, имеющейся в левом нижнем углу окна. Появляющееся диалоговое окно позволит Вам выбрать существующую таблицу и включить/выключить дополнительные опции, совпадающие с уже перечисленными свойствами таблиц. Это наиболее легкий способ создания таблиц.

Заключение

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

Урок 3: Создание таблиц с помощью SQL-запросов

Содержание урока 3:

Обзор

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

Создание таблиц с помощью SQL

Если Вы хотите воспользоваться компонентом TQuery, сначала поместите его на форму. После этого настройте свойство DatabaseName на нужный Вам алиас. После этого можно ввести SQL-предложение в свойство SQL. Для выполнения запроса, изменяющего структуру, вставляющего или обновляющего данные на сервере, нужно вызвать метод ExecSQL компонента TQuery. Для выполнения запроса, получающего данные с сервера (т.е. запроса, в котором основным является оператор SELECT), нужно вызвать метод Open компонента TQuery. Это связано с тем, что BDE при посылке запроса типа SELECT открывает так называемый курсор, с помощью которого осуществляется навигация по выборке данных (подробней об этом см. в уроке, посвященном TQuery).

Приведем упрощенный синтаксис SQL-предложения для создания таблицы на SQL-сервере InterBase (более полный синтаксис можно посмотреть в online-справочнике по SQL, поставляемом с локальным InterBase):

CREATE TABLE table

( [, | ...]);

где

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

- описание поля,

- описание ограничений и/или ключей (квадратные скобки [] означают необязательность, вертикальная черта | означает “или”).

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

= col {datatype | COMPUTED BY () | domain}

[DEFAULT {literal | NULL | USER}]

[NOT NULL] []

[COLLATE collation]

Здесь

col - имя поля;

datatype - любой правильный тип SQL-сервера (для InterBase такими типами являются SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION, DECIMAL, NUMERIC, DATE, CHAR, VARCHAR, NCHAR, BLOB), символьные типы могут иметь CHARACTER SET - набор символов, определяющий язык страны. Для русского языка следует задать набор символов WIN1251;

COMPUTED BY () - определение вычисляемого на уровне сервера поля, где - правильное SQL-выражение, возвращающее единственное значение;

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

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

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

COLLATE - предложение, определяющее порядок сортировки для выбранного набора символов (для поля типа BLOB не применяется). Русский набор символов WIN1251 имеет 2 порядка сортировки - WIN1251 и PXW_CYRL. Для правильной сортировки, включающей большие буквы, следует выбрать порядок PXW_CYRL.

Описание ограничений и/или ключей включает в себя предложения CONSTRAINT или предложения, описывающие уникальные поля, первичные, внешние ключи, а также ограничения CHECK (такие конструкции могут определяться как на уровне поля, так и на уровне таблицы в целом, если они затрагивают несколько полей):

= [CONSTRAINT constraint ]

Здесь

= {{PRIMARY KEY | UNIQUE} (col[,col...]) | FOREIGN KEY (col [, col ...]) REFERENCES other_table

| CHECK ()}

=

{ { | ()}

| [NOT] BETWEEN AND

| [NOT] LIKE [ESCAPE ]

| [NOT] IN ( [, ...] |

= {

col [] | | |

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

= num | "string" | charsetname "string"

= {

COUNT (* | [ALL] | DISTINCT )

| SUM ([ALL] | DISTINCT )

| AVG ([ALL] | DISTINCT )

| MAX ([ALL] | DISTINCT )

| MIN ([ALL] | DISTINCT )

| CAST ( AS )

| UPPER ()

| GEN_ID (generator, )

}

= {= | < |=""> | <= |="">= | !< |="" !=""> | <> | !=}

= выражение SELECT по одному полю, которое возвращает в точности одно значение.

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

Пример A: Простая таблица с конструкцией PRIMARY KEY на уровне поля

CREATE TABLE REGION (

REGION REGION_NAME NOT NULL PRIMARY KEY,

POPULATION INTEGER NOT NULL);

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

CREATE DOMAIN REGION_NAME

AS VARCHAR(40) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

Пример B: Таблица с предложением UNIQUE как на уровне поля, так и на уровне таблицы

CREATE TABLE GOODS (

MODEL SMALLINT NOT NULL UNIQUE,

NAME CHAR(10) NOT NULL,

ITEMID INTEGER NOT NULL, CONSTRAINT MOD_UNIQUE

UNIQUE (NAME, ITEMID));

Пример C: Таблица с определением первичного ключа, внешнего ключа и конструкции CHECK, а также символьных массивов

CREATE TABLE JOB (

JOB_CODE JOBCODE NOT NULL,

JOB_GRADE JOBGRADE NOT NULL,

JOB_REGION REGION_NAME NOT NULL,

JOB_TITLE VARCHAR(25) CHARACTER SET WIN1251 COLLATE PXW_CYRL NOT NULL,

MIN_SALARY SALARY NOT NULL,

MAX_SALARY SALARY NOT NULL,

JOB_REQ BLOB(400,1) CHARACTER SET WIN1251,

LANGUAGE_REQ VARCHAR(15) [5],

PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_REGION),

FOREIGN KEY (JOB_REGION) REFERENCES REGION (REGION),

CHECK (MIN_SALARY <>

Данный пример создает таблицу, содержащую информацию о работах (профессиях). Типы полей основаны на доменах JOBCODE, JOBGRADE, REGION_NAME и SALARY. Определен массив LANGUAGE_REQ, состоящий из 5 элементов типа VARCHAR(15). Кроме того, введено поле JOB_REQ, имеющее тип BLOB с подтипом 1 (текстовый блоб) и размером сегмента 400. Для таблицы определен первичный ключ, состоящий из трех полей JOB_CODE, JOB_GRADE и JOB_REGION. Далее, определен внешний ключ (JOB_REGION), ссылающийся на поле REGION таблицы REGION. И, наконец, включено предложение CHECK, позволяющее производить проверку соотношения для двух полей и вызывать исключительное состояние при нарушении такого соотношения.

Пример D: Таблица с вычисляемым полем

CREATE TABLE SALARY_HISTORY (

EMP_NO EMPNO NOT NULL,

CHANGE_DATE DATE DEFAULT "NOW" NOT NULL,

UPDATER_ID VARCHAR(20) NOT NULL,

OLD_SALARY SALARY NOT NULL,

PERC_CHANGE DOUBLE PRECISION DEFAULT 0 NOT NULL

CHECK (PERC_CHANGE BETWEEN -50 AND 50),

NEW_SALARY COMPUTED BY

(OLD_SALARY + OLD_SALARY * PERC_CHANGE / 100),

PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),

FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO));

Данный пример создает таблицу, где среди других полей имеется вычисляемое (физически не существующее) поле NEW_SALARY, значение которого вычисляется по значениям двух других полей (OLD_SALARY и PERC_CHANGE).

Заключение

Итак, мы рассмотрели, как создавать таблицы с помощью SQL-выражений. Этот процесс, хотя и не столь удобен, как интерактивное средство Database Desktop, однако обладает наиболее гибкими возможностями по настройке Вашей системы и управления ее связями.

Урок 4: ОбъектTTable

Содержание урока 4:

Обзор

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

Более подробно здесь рассказывается о TTable и TDataSource.

Имеются несколько основных компонент(объектов), которые Вы будете использовать постоянно для доступа к БД. Эти объекты могут быть разделены на три группы:

• невизуальные: TTable, TQuery, TDataSet, TField

• визуальные: TDBGrid, TDBEdit

• связующие: TDataSource

Первая группа включает невизуальные классы, которые используются для управления таблицами и запросами. Эта группа сосредотачивается вокруг компонент типа TTable, TQuery, TDataSet и TField. В Палитре Компонент эти объекты расположены на странице Data Access.

Вторая важная группа классов - визуальные, которые показывают данные пользователю, и позволяют ему просматривать и модифицировать их. Эта группа классов включает компоненты типа TDBGrid, TDBEdit, TDBImage и TDBComboBox. В Палитре Компонент эти объекты расположены на странице Data Controls.

Имеется и третий тип, который используется для того, чтобы связать предыдущие два типа объектов. К третьему типу относится только невизуальный компонент TDataSource.

Класс TDataSet

TDataSet класс - один из наиболее важных объектов БД. Чтобы начать работать с ним, Вы должны взглянуть на следующую иерархию:

TDataSet

|

TDBDataSet

|

|-- TTable

|-- TQuery

|-- TStoredProc

TDataSet содержит абстрактные методы там, где должно быть непосредственное управление данными. TDBDataSet знает, как обращаться с паролями и то, что нужно сделать, чтобы присоединить Вас к определенной таблице. TTable знает (т.е. уже все абстрактные методы переписаны), как обращаться с таблицей, ее индексами и т.д.

Как Вы увидите в далее, TQuery имеет определенные методы для обработки SQL запросов.

TDataSet - инструмент, который Вы будете использовать чтобы открыть таблицу, и перемещаться по ней. Конечно, Вы никогда не будете непосредственно создавать объект типа TDataSet. Вместо этого, Вы будете использовать TTable, TQuery или других потомков TDataSet (например, TQBE). Полное понимание работы системы, и точное значение TDataSet, будут становиться все более ясными по мере прочтения этой главы.

На наиболее фундаментальном уровне, Dataset это просто набор записей, как изображено на рис.1

Рис.1: Любой dataset состоит из ряда записей (каждая содержит N полей) и указатель на текущую запись.

В большинстве случаев dataset будет иметь a прямое, один к одному, соответствие с физической таблицей, которая существует на диске. Однако, в других случаях Вы можете исполнять запрос или другое действие, возвращающие dataset, который содержит либо любое подмножество записей одной таблицы, либо объединение (join) между несколькими таблицами. В тексте будут иногда использоваться термины DataSet и TTable как синонимы.

Обычно в программе используются объекты типа TTable или TQuery, поэтому в следующих нескольких главах будет предполагаться существование объекта типа TTable называемого Table1.

Итак, самое время начать исследование TDataSet. Как только Вы познакомитесь с его возможностями, Вы начнете понимать, какие методы использует Delphi для доступа к данным, хранящимся на диске в виде БД. Ключевой момент здесь - не забывать, что почти всякий раз, когда программист на Delphi открывает таблицу, он будет использовать TTable или TQuery, которые являются просто некоторой надстройкой над TDataSet.

Открытие и закрытие DataSet

В этой главе Вы узнаете некоторые факты об открытии и закрытии DataSet.

Если Вы используете TTable для доступа к таблице, то при открытии данной таблицы заполняются некоторые свойства TTable (количество записей RecordCount, описание структуры таблицы и т.д.).

Прежде всего, Вы должны поместить во время дизайна на форму объект TTable и указать, с какой таблицей хотите работать. Для этого нужно заполнить в Инспекторе объектов свойства DatabaseName и TableName. В DatabaseName можно либо указать директорию, в которой лежат таблицы в формате dBase или Paradox (например, C:\DELPHI\DEMOS\DATA), либо выбрать из списка псевдоним базы данных (DBDEMOS). Теперь, если свойство Active установить в True, то при запуске приложения таблица будет открываться автоматически.

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

Table1.Open;

Или, если Вы предпочитаете, то можете установить свойство Active равное True:

Table1.Active := True;

Нет никакого различия между результатом производимым этими двумя операциями. Метод Open, однако, сам заканчивается установкой свойства Active в True, так что может быть даже чуть более эффективно использовать свойство Active напрямую.

Также, как имеются два способа открыть a таблицу, так и есть два способа закрыть ее. Самый простой способ просто вызывать Close:

Table1.Close;

Или, если Вы желаете, Вы можете написать:

Table1.Active := False;

Еще раз повторим, что нет никакой существенной разницы между двумя этими способами. Вы должны только помнить, что Open и Close это методы (процедуры), а Active - свойство.

Навигация (Перемещение по записям)

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

Следующий обширный набор методов и свойства TDataSet обеспечивает все , что Вам нужно для доступа к любой конкретной записи внутри таблицы:

procedure First;

procedure Last;

procedure Next;

procedure Prior;

property BOF: Boolean read FBOF;

property EOF: Boolean read FEOF;

procedure MoveBy(Distance: Integer);

Дадим краткий обзор их функциональных возможностей:

• Вызов Table1.First перемещает Вас к первой записи в таблице.

• Table1.Last перемещает Вас к последней записи.

• Table1.Next перемещает Вас на одну запись вперед.

• Table1.Prior перемещает Вас на одну запись Назад.

• Вы можете проверять свойства BOF или EOF, чтобы понять, находитесь ли Вы в начале или в конце таблицы.

• Процедура MoveBy перемещает Вас на N записей вперед или назад в таблице. Нет никакого функционального различия между запросом Table1.Next и вызовом Table1.MoveBy(1). Аналогично, вызов Table1.Prior имеет тот же самый результат, что и вызов Table1.MoveBy(-1).

Чтобы начать использовать эти навигационные методы, Вы должны поместить TTable, TDataSource и TDBGrid на форму, также, как Вы делали это в предыдущем уроке. Присоедините DBGrid1 к DataSource1, и DataSource1 к Table1. Затем установите свойства таблицы:

• в DatabaseName имя подкаталога, где находятся демонстрационные таблицы (или псевдоним DBDEMOS);

• в TableName установите имя таблицы CUSTOMER.

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

Однако, иногда нужно перемещаться по таблице “программным путем”, без использования возможностей, встроенных в визуальные компоненты. В следующих нескольких абзацах объясняется как можно это сделать.

Поместите две кнопки на форму и назовите их Next и Prior, как показано на рис.2.

Рис.2 : Next и Prior кнопки позволяют Вам перемещаться по БД.

Дважды щелкните на кнопке Next - появится заготовка обработчика события:

procedure TForm1.NextClick(Sender: TObject);

begin

end;

Теперь добавьте одну строчку кода так, чтобы процедура выглядела так:

procedure TForm1.NextClick(Sender: TObject);

begin

Table1.Next;

end;

Повторите те же самые действия с кнопкой Prior, так, чтобы функция связанная с ней выглядела так:

procedure TForm1.PriorClick(Sender: TObject);

begin

Table1.Prior;

end;

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

Теперь добавьте еще две кнопки и назовите их First и Last, как показано на рис.3

Рис.3: Программа со всеми четырьмя кнопками.

Сделайте то же самое для новых кнопок.

procedure TForm1.FirstClick(Sender: TObject);

begin

Table1.First;

end;

procedure TForm1.LastClick(Sender: TObject);

begin

Table1.Last;

end;

Нет ничего более простого чем эти навигационные функции. First перемещает Вас в начало таблицы, Last перемещает Вас в конец таблицы, а Next и Prior перемещают Вас на одну запись вперед или назад.

TDataSet.BOF - read-only Boolean свойство, используется для проверки, находитесь ли Вы в начале таблицы. Свойства BOF возвращает true в трех случаях:

• После того, как Вы открыли файл;

• После того, как Вы вызывали TDataSet.First;

• После того, как вызов TDataSet.Prior не выполняется.

Первые два пункта - очевидны. Когда Вы открываете таблицу, Delphi помещает Вас на первую запись; когда Вы вызываете метод First, Delphi также перемещает Вас в начало таблицы. Третий пункт, однако, требует небольшого пояснения: после того, как Вы вызывали метод Prior много раз, Вы могли добраться до начала таблицы, и следующий вызов Prior будет неудачным - после этого BOF и будет возвращать True.

Следующий код показывает самый общий пример использования Prior, когда Вы попадаете к началу a файла:

while not Table.Bof do begin

DoSomething;

Table1.Prior;

end;

В коде, показанном здесь, гипотетическая функция DoSomething будет вызвана сперва на текущей записи и затем на каждой следующей записи (от текущей и до начала таблицы). Цикл будет продолжаться до тех пор, пока вызов Table1.Prior не сможет больше переместить Вас на предыдущую запись в таблице. В этот момент BOF вернет True и программа выйдет из цикла. (Чтобы оптимизировать вышеприведенный код, установите DataSource1.Enabled в False перед началом цикла, и верните его в True после окончания цикла.)

Все сказанное относительно BOF также применимо и к EOF. Другими словами, код, приведенный ниже показывает простой способ пробежать по всем записям в a dataset:

Table1.First;

while not Table1.EOF do begin

DoSomething;

Table1.Next;

end;

Классическая ошибка в случаях, подобных этому: Вы входите в цикл while или repeat, но забываете вызывать Table1.Next:

Table1.First;

repeat

DoSomething;

until Table1.EOF;

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

EOF возвращает True в следующих трех случаях:

• После того, как Вы открыли пустой файл;

• После того, как Вы вызывали TDataSet.Last;

• После того, как вызов TDataSet.Next не выполняется.

Единственная навигационная процедура, которая еще не упоминалась - MoveBy, которая позволяет Вам переместиться на N записей вперед или назад в таблице. Если Вы хотите переместиться на две записи вперед, то напишите:

MoveBy(2);

И если Вы хотите переместиться на две записи назад, то:

MoveBy(-2);

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

Prior и Next - это простые функции, которые вызывают MoveBy.

Поля

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

property Fields[Index: Integer];

function FieldByName(const FieldName: string): TField;

property FieldCount;

Свойство FieldCount возвращает число полей в текущей структуре записи. Если Вы хотите программным путем прочитать имена полей, то используйте свойство Fields для доступа к ним:

var

S: String;

begin

S := Fields[0].FieldName;

end;

Если Вы работали с записью в которой первое поле называется CustNo, тогда код показанный выше поместит строку “CustNo” в переменную S. Если Вы хотите получить доступ к имени второго поля в вышеупомянутом примере, тогда Вы могли бы написать:

S := Fields[1].FieldName;

Короче говоря, индекс передаваемый в Fields (начинающийся с нуля), и определяет номер поля к которому Вы получите доступ, т.е. первое поле - ноль, второе один, и так далее.

Если Вы хотите прочитать текущее содержание конкретного поля конкретной записи, то Вы можете использовать свойство Fields или метод FieldsByName. Для того, чтобы найти значение первого поля записи, прочитайте первый элемент массива Fields:

S := Fields[0].AsString;

Предположим, что первое поле в записи содержит номер заказчика, тогда код, показанный выше, возвратил бы строку типа “1021”, “1031” или “2058”. Если Вы хотели получить доступ к этот переменный, как к числовой величине, тогда Вы могли бы использовать AsInteger вместо AsString. Аналогично, свойство Fields включают AsBoolean, AsFloat и AsDate.

Если хотите, Вы можете использовать функцию FieldsByName вместо свойства Fields:

S := FieldsByName(‘CustNo’).AsString;

Как показано в примерах выше, и FieldsByName, и Fields возвращают те же самые данные. Два различных синтаксиса используются исключительно для того, чтобы обеспечить программистов гибким и удобным набором инструментов для программного доступа к содержимому DataSet.

Давайте посмотрим на простом примере, как можно использовать доступ к полям таблицы во время выполнения программы. Создайте новый проект, положите на форму объект TTable, два объекта ListBox и две кнопки - “Fields” и “Values” (см рис.4).

Соедините объект TTable с таблицей CUSTOMER, которая поставляется вместе с Delphi (DBDEMOS), не забудьте открыть таблицу (Active = True).

Рис.4: Программа FLDS показывает, как использовать свойство Fields.

Сделайте Double click на кнопке Fields и создайте a метод который выглядит так:

procedure TForm1.FieldsClick(Sender: TObject);

var

i: Integer;

begin

ListBox1.Clear;

for i := 0 to Table1.FieldCount - 1 do

ListBox1.Items.Add(Table1.Fields[i].FieldName);

end;

Обработчик события начинается с очистки первого ListBox1, затем он проходит через все поля, добавляя их имена один за другим в ListBox1. Заметьте, что цикл показанный здесь пробегает от 0 до FieldCount - 1. Если Вы забудете вычесть единицу из FieldCount, то Вы получите ошибку “List Index Out of Bounds”, так как Вы будете пытаться прочесть имя поля которое не существует.

Предположим, что Вы ввели код правильно, и заполнили ListBox1 именами всех полей в текущей структуре записи.

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

Свойство Fields позволяет Вам получить доступ не только именам полей записи, но также и к содержимому полей. В нашем примере, для второй кнопки напишем:

procedure TForm1.ValuesClick(Sender: TObject);

var

i: Integer;

begin

ListBox2.Clear;

for i := 0 to Table1.FieldCount - 1 do

ListBox2.Items.Add(Table1.Fields[i].AsString);

end;

Этот код добавляет содержимое каждого из полей во второй listbox. Обратите внимание, что вновь счетчик изменяется от нуля до FieldCount - 1.

Свойство Fields позволяет Вам выбрать тип результата написав Fields[N].AsString. Этот и несколько связанных методов обеспечивают a простой и гибкий способ доступа к данным, связанными с конкретным полем. Вот список доступных методов который Вы можете найти в описании класса TField:

property AsBoolean

property AsFloat

property AsInteger

property AsString

property AsDateTime

Всякий раз (когда это имеет смысл), Delphi сможет сделать преобразования. Например, Delphi может преобразовывать поле Boolean к Integer или Float, или поле Integer к String. Но не будет преобразовывать String к Integer, хотя и может преобразовывать Float к Integer. BLOB и Memo поля - специальные случаи, и мы их рассмотрим позже. Если Вы хотите работать с полями Date или DateTime, то можете использовать AsString и AsFloat для доступа к ним.

Как было объяснено выше, свойство FieldByName позволяет Вам получить доступ к содержимому определенного поля просто указав имя этого поля:

S := Table1.FieldByName(‘CustNo’).AsString;

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

Работа с Данными

Следующие методы позволяют Вам изменить данные, связанные с TTable:

procedure Append;

procedure Insert;

procedure Cancel;

procedure Delete;

procedure Edit;

procedure Post;

Все эти методы - часть TDataSet, они унаследованы и используются TTable и TQuery.

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

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

Table1.Edit;

Table1.FieldByName(‘CustName’).AsString := ‘Fred’;

Table1.Post;

Первая строка переводит БД в режим редактирования. Следующая строка присваивает значение ‘Fred’ полю ‘CustName’. Наконец, данные записываются на диск, когда Вы вызываете Post.

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

Table1.Edit;

Table1.FieldByName(‘CustNo’).AsInteger := 1234;

Table1.Next;

Общее правило, которому нужно следовать - всякий раз, когда Вы сдвигаетесь с текущей записи, введенные Вами данные будут записаны автоматически. Это означает, что вызовы First, Next, Prior и Last всегда выполняют Post, если Вы находились в режиме редактирования. Если Вы работаете с данными на сервере и транзакциями, тогда правила, приведенные здесь, не применяются. Однако, транзакции - это отдельный вопрос с их собственными специальными правилами, Вы увидите это, когда прочитаете о них в следующих уроках.

Тем не менее, даже если Вы не работаете со транзакциями, Вы можете все же отменить результаты вашего редактирования в любое время, до тех пор, пока не вызвали напрямую или косвенно метод Post. Например, если Вы перевели таблицу в режим редактирования, и изменили данные в одном или более полей, Вы можете всегда вернуть запись в исходное состояние вызовом метода Cancel.

Существуют два метода, названные Append и Insert, который Вы можете использовать всякий раз, когда Вы хотите добавить новую запись в DataSet. Очевидно имеет больше смысла использовать Append для DataSets которые не индексированы, но Delphi не будет генерировать exception если Вы используете Append на индексированной таблице. Фактически, всегда можно использовать и Append, и Insert.

Продемонстрируем работу методов на простом примере. Чтобы создать программу, используйте TTable, TDataSource и TdbGrid. Открыть таблицу COUNTRY. Затем разместите две кнопки на форме и назовите их ‘Insert’ и ‘Delete’. Когда Вы все сделаете, то должна получиться программа, показанная на рис.5

Рис.5: Программа может вставлять и удалять запись из таблицы COUNTRY.

Следующим шагом Вы должен связать код с кнопками Insert и Delete:

procedure TForm1.InsertClick(Sender: TObject);

begin

Table1.Insert;

Table1.FieldByName('Name').AsString := 'Russia';

Table1.FieldByName('Capital').AsString := 'Moscow';

Table1.Post;

end;

procedure TForm1.DeleteClick(Sender: TObject);

begin

Table1.Delete;

end;

Процедура показанная здесь сначала переводит таблицу в режим вставки (новая запись с незаполненными полями вставляется в текущую позицию dataset). После вставки пустой записи, следующим этапом нужно назначить значения одному или большему количеству полей. Существует, конечно, несколько различных путей присвоить эти значения. В нашей программе Вы могли бы просто ввести информацию в новую запись через DBGrid. Или Вы могли бы разместить на форме стандартную строку ввода (TEdit) и затем установить каждое поле равным значению, которое пользователь напечатал в этой строке:

Table1.FieldByName(‘Name’).AsString := Edit1.Text;

Можно было бы использовать компоненты, специально предназначенные для работы с данными в DataSet.

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

Table1.FieldByName('Name').AsString := 'Russia';

Один из интересных моментов в этом примере это то, что нажатие кнопки Insert дважды подряд автоматически вызывает exception ‘Key Violation’. Чтобы исправить эту ситуацию, Вы должны либо удалить текущую запись, или изменять поля Name и Capital вновь созданной записи.

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

Если после вызова Insert, Вы решаете отказаться от вставки новой записи, то Вы можете вызвать Cancel. Если Вы сделаете это прежде, чем Вы вызовете Post, то все что Вы ввели после вызова Insert будет отменено, и dataset будет находиться в состоянии, которое было до вызова Insert.

Одно дополнительное свойство, которое Вы должны иметь в виду называется CanModify. Если CanModify возвращает False, то TTable находиться в состоянии ReadOnly. В противном случае CanModify возвращает True и Вы можете редактировать или добавлять записи в нее по желанию. CanModify - само по себе ‘read only’ свойство. Если Вы хотите установить DataSet в состояние только на чтение (Read Only), то Вы должны использовать свойство ReadOnly, не CanModify.

Использование SetKey для поиска в таблице

Для того, чтобы найти некоторую величину в таблице, программист на Delphi может использовать две процедуры SetKey и GotoKey. Обе эти процедуры предполагают, что поле по которому Вы ищете индексировано. Delphi поставляется с демонстрационной программой SEARCH, которая показывает, как использовать эти запросы.

Чтобы создать программу SEARCH, поместите TTable, TDataSource, TDBGrid, TButton, TLabel и TEdit на форму, и расположите их как показано на рис.6. Назовите кнопку Search, и затем соедините компоненты БД так, чтобы Вы видели в DBGrid1 таблицу Customer.

Рис.6: Программа SEARCH позволяет Вам ввести номер заказчика и затем найти его по нажатию кнопки.

Вся функциональность программы SEARCH скрыта в единственном методе, который присоединен к кнопке Search. Эта функция считывает строку, введенную в окно редактора, и ищет ее в колонке CustNo, и наконец помещает фокус на найденной записи. В простейшем варианте, код присоединенный к кнопке Search выглядит так:

procedure TSearchDemo.SearchClick(Sender: TObject);

begin

Table1.SetKey;

Table1.FieldByName(’CustNo’).AsString := Edit1.Text;

Table1.GotoKey;

end;

Первый вызов в этой процедуре установит Table1 в режим поиска. Delphi должен знать, что Вы переключились в режим поиска просто потому, что свойство Fields используется по другому в этом режиме. Далее, нужно присвоить свойству Fields значение, которое Вы хотите найти. Для фактического выполнения поиска нужно просто вызывать Table1.GotoKey.

Если Вы ищете не по первичному индексу файла, тогда Вы должны определить имя индекса, который Вы используете в свойстве IndexName. Например, если таблица Customer имеет вторичный индекс по полю City, тогда Вы должны установить свойство IndexName равным имени индекса. Когда Вы будете искать по этому полю, Вы должны написать:

Table1.IndexName := ’CityIndex’;

Table1.Active := True;

Table1.SetKey;

Table1.FieldByName(’City’).AsString := Edit1.Text;

Table1.GotoKey;

Запомните: поиск не будет выполняться, если Вы не назначите правильно индекс (св-во IndexName). Кроме того, Вы должны обратить внимание, что IndexName - это свойство TTable, и не присутствует в других прямых потомках TDataSet или TDBDataSet.

Когда Вы ищете некоторое значение в БД, всегда существует вероятность того, что поиск окажется неудачным. В таком случае Delphi будет автоматически вызывать exception, но если Вы хотите обработать ошибку сами, то могли бы написать примерно такой код:

procedure TSearchDemo.SearchClick(Sender: TObject);

begin

Cust.SetKey;

Cust.FieldByName('CustNo').AsString:= CustNoEdit.Text;

if not Cust.GotoKey then

raise Exception.CreateFmt('Cannot find CustNo %g',

[CustNo]);

end;

В коде, показанном выше, либо неверное присвоение номера, либо неудача поиска автоматически приведут к сообщению об ошибке ‘Cannot find CustNo %g’.

Иногда требуется найти не точно совпадающее значение, а близкое к нему, для этого следует вместо GotoKey пользоваться методом GotoNearest.

Использование фильтров для ограничения числа записей в DataSet

Процедура ApplyRange позволяет Вам установить фильтр, который ограничивает диапазон просматриваемых записей. Например, в БД Customers, поле CustNo имеет диапазон от 1,000 до 10,000. Если Вы хотите видеть только те записи, которые имеют номер заказчика между 2000 и 3000, то Вы должны использовать метод ApplyRange, и еще два связанных с ним метода. Данные методы работают только с индексированным полем.

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

procedure SetRangeStart;

procedure SetRangeEnd;

procedure ApplyRange;

procedure CancelRange;

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

procedure EditRangeStart;

procedure EditRangeEnd;

procedure SetRange;

Для использования этих процедур необходимо:

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

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

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

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

Программа RANGE, которая есть среди примеров Delphi, показывает, как использовать эти процедуры. Чтобы создать программу, поместите TTable, TDataSource и TdbGrid на форму. Соедините их так, чтобы Вы видеть таблицу CUSTOMERS из подкаталога DEMOS. Затем поместите два объекта TLabel на форму и назовите их ‘Start Range’ и ‘End Range’. Затем положите на форму два объекта TEdit. Наконец, добавьте кнопки ‘ApplyRange’ и ‘CancelRange’. Когда Вы все выполните, форма имеет вид, как на рис.7

Рис.7: Программа RANGE показывает как ограничивать число записей таблицы для просмотра.

Процедуры SetRangeStart и SetRangeEnd позволяют Вам указать первое и последнее значения в диапазоне записей, которые Вы хотите видеть. Чтобы начать использовать эти процедуры, сначала выполните double-click на кнопке ApplyRange, и создайте процедуру, которая выглядит так:

procedure TForm1.ApplyRangeBtnClick(Sender: TObject);

begin

Table1.SetRangeStart;

if RangeStart.Text <> '' then

Table1. Fields[0].AsString := RangeStart.Text;

Table1.SetRangeEnd;

if RangeEnd.Text <> '' then

Table1.Fields[0].AsString := RangeEnd.Text;

Table1.ApplyRange;

end;

Сначала вызывается процедура SetRangeStart, которая переводит таблицу в режим диапазона (range mode). Затем Вы должны определить начало и конец диапазона. Обратите внимание, что Вы используете свойство Fields для определения диапазона:

Table1.Fields[0].AsString := RangeStart.Text;

Такое использование свойства Fields - это специальный случай, так как синтаксис, показанный здесь, обычно используется для установки значения поля. Этот специальный случай имеет место только после того, как Вы перевели таблицу в режим диапазона, вызвав SetRangeStart.

Заключительный шаг в процедуре показанной выше - вызов ApplyRange. Этот вызов фактически приводит ваш запрос в действие. После вызова ApplyRange, TTable больше не в находится в режиме диапазона, и свойства Fields функционирует как обычно.

Обработчик события нажатия кнопки ‘CancelRange’:

procedure TForm1.CancelRangeBtnClick(Sender: TObject);

begin

Table1.CancelRange;

end;

Обновление (Refresh)

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

Функция Refresh связана с функцией Open, в том смысле что она считывает данные, или некоторую часть данных, связанных с данной таблицей. Например, когда Вы открываете таблицу, Delphi считывает данные непосредственно из файла БД. Аналогично, когда Вы Регенерируете таблицу, Delphi считывает данные напрямую из таблицы. Поэтому Вы можете использовать эту функцию, чтобы перепрочитать таблицу, если Вы думаете что она могла измениться. Быстрее и эффективнее, вызывать Refresh, чем вызывать Close и затем Open.

Имейте ввиду, однако, что обновление TTable может иногда привести к неожиданным результатам. Например, если a пользователь рассматривает запись, которая уже была удалена, то она исчезнет с экрана в тот момент, когда будет вызван Refresh. Аналогично, если некий другой пользователь редактировал данные, то вызов Refresh приведет к динамическому изменению данных. Конечно маловероятно, что один пользователь будет изменять или удалять запись в то время, как другой просматривает ее, но это возможно.

Закладки (Bookmarks)

Часто бывает полезно отметить текущее местоположение в таблице так, чтобы можно было быстро возвратиться к этому месту в дальнейшем. Delphi обеспечивает эту функциональную возможность посредством трех методов, которые используют понятие закладки.

function GetBookmark: TBookmark;

(устанавливает закладку в таблице)

procedure GotoBookmark(Bookmark: TBookmark);

(переходит на закладку)

procedure FreeBookmark(Bookmark: TBookmark);

(освобождает память)

Как Вы можете видеть, вызов GetBookmark возвращает переменную типа TBookmark. TBookmark содержит достаточное количество информации, чтобы Delphi мог найти местоположение к которому относится этот TBookmark. Поэтому Вы можете просто передавать этот TBookmark функции GotoBookmark, и будете немедленно возвращены к местоположению, связанному с этой закладкой.

Обратите внимание, что вызов GetBookmark распределяет память для TBookmark, так что Вы должны вызывать FreeBookmark до окончания вашей программы, и перед каждой попыткой повторного использования Tbookmark (в GetBookMark).

Создание Связанных Курсоров (Linked cursors)

Связанные курсоры позволяют программистам определить отношение один ко многим (one-to-many relationship). Например, иногда полезно связать таблицы CUSTOMER и ORDERS так, чтобы каждый раз, когда пользователь выбирает имя заказчика, то он видит список заказов связанных с этим заказчиком. Иначе говоря, когда пользователь выбирает запись о заказчике, то он может просматривать только заказы, сделанные этим заказчиком.

Программа LINKTBL демонстрирует, как создать программу которая использует связанные курсоры. Чтобы создать программу заново, поместите два TTable, два TDataSources и два TDBGrid на форму. Присоедините первый набор таблице CUSTOMER, а второй к таблице ORDERS. Программа в этой стадии имеет вид, показанный на рис.8

Рис.8: Программа LINKTBL показывает, как определить отношения между двумя таблицами.

Следующий шаг должен связать таблицу ORDERS с таблицей CUSTOMER так, чтобы Вы видели только те заказы, которые связанные с текущей записью в таблице заказчиков. В первой таблице заказчик однозначно идентифицируется своим номером - поле CustNo. Во второй таблице принадлежность заказа определяется также номером заказчика в поле CustNo. Следовательно, таблицы нужно связывать по полю CustNo в обоих таблицах (поля могут иметь различное название, но должны быть совместимы по типу). Для этого, Вы должны сделать три шага, каждый из которых требует некоторого пояснения:

1. Установить свойство Table2.MasterSource = DataSource1

2. Установить свойство Table2.MasterField = CustNo

3. Установить свойство Table2.IndexName = CustNo

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

Свойство MasterSource в Table2 определяет DataSource от которого Table2 может получить информацию. То есть, оно позволяет таблице ORDERS знать, какая запись в настоящее время является текущей в таблице CUSTOMERS.

Но тогда возникает вопрос: Какая еще информация нужна Table2 для того, чтобы должным образом отфильтровать содержимое таблицы ORDERS? Ответ состоит из двух частей:

1. Требуется имя поля по которому связанны две таблицы.

2. Требуется индекс по этому полю в таблице ORDERS (в таблице ‘многих записей’), которая будет связываться с таблицей CUSTOMER(таблице в которой выбирается ‘одна запись’).

Чтобы правильно воспользоваться информацией описанной здесь, Вы должны сначала проверить, что таблица ORDERS имеет нужные индексы. Если этот индекс первичный, тогда не нужно дополнительно указывать его в поле IndexName, и поэтому Вы можете оставить это поле незаполненным в таблице TTable2 (ORDERS). Однако, если таблица связана с другой через вторичный индекс, то Вы должны явно определять этот индекс в поле IndexName связанной таблицы.

В примере показанном здесь таблица ORDERS не имеет первичного индекса по полю CustNo, так что Вы должны явно задать в свойстве IndexName индекс CustNo.

Недостаточно, однако, просто yпомянуть имя индекса, который Вы хотите использовать. Некоторые индексы могут содержать несколько полей, так что Вы должны явно задать имя поля, по которому Вы хотите связать две таблицы. Вы должны ввести имя ‘CustNo’ в свойство Table2.MasterFields. Если Вы хотите связать две таблицы больше чем по одному полю, Вы должны внести в список все поля, помещая символ ‘|’ между каждым:

Table1.MasterFields := ‘CustNo | SaleData | ShipDate’;

В данном конкретном случае, выражение, показанное здесь, не имеет смысла, так как хотя поля SaleData и ShipDate также индексированы, но не дублируются в таблице CUSTOMER. Поэтому Вы должны ввести только поле CustNo в свойстве MasterFields. Вы можете определить это непосредственно в редакторе свойств, или написать код подобно показанному выше. Кроме того, поле (или поля) связи можно получить, вызвав редактор связей - в Инспекторе Объектов дважды щелкните на свойство MasterFields (рис.10)

Рис.10: Редактор связей для построения связанных курсоров.

Важно подчеркнуть, что данная глава охватила только один из нескольких путей, которым Вы можете создать связанные курсоры в Delphi. В главе о запросах будет описан второй метод, который будет обращен к тем кто знаком с SQL.

Основные понятия о TDataSource

Класс TDataSource используется в качестве проводника между TTable или TQuery и компонентами, визуализирующими данные, типа TDBGrid, TDBEdit и TDBComboBox (data-aware components). В большинстве случаев, все, что нужно сделать с DataSource - это указать в свойстве DataSet соответствующий TTable или TQuery. Затем, у data-aware компонента в свойстве DataSource указывается TDataSource, который используется в настоящее время.

TDataSource также имеет свойство Enabled, и оно может быть полезно всякий раз, когда Вы хотите временно отсоединить, например, DBGrid от таблицы или запроса. Эти требуется, например, если нужно программно пройти через все записи в таблице. Ведь, если таблица связана с визуальными компонентами (DBGrid, DBEdit и т.п.), то каждый раз, когда Вы вызываете метод TTable.Next, визуальные компоненты будут перерисовываться. Даже если само сканирование в таблице двух или трех тысяч записей не займет много времени, то может потребоваться значительно больше времени, чтобы столько же раз перерисовать визуальные компоненты. В случаях подобных этому, лучше всего установить поле DataSource.Eabled в False. Это позволит Вам просканировать записи без перерисовки визуальных компонент. Это единственная операция может увеличить скорость в некоторых случаях на несколько тысяч процентов.

Свойство TDataSource.AutoEdit указывает, переходит ли DataSet автоматически в режим редактирования при вводе текста в data-aware объекте.

Использование TDataSource для проверки состояния БД:

TDataSource имеет три ключевых события, связанных с состоянием БД

OnDataChange

OnStateChange

OnUpdateData

OnDataChange происходит всякий раз, когда Вы переходите на новую запись, или состояние DataSet сменилось с dsInactive на другое, или начато редактирование. Другими словами, если Вы вызываете Next, Previous, Insert, или любой другой запрос, который должен привести к изменению данных, связанных с текущей записью, то произойдет событие OnDataChange. Если в программе нужно определить момент, когда происходит переход на другую запись, то это можно сделать в обработчике события OnDataChange:

procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField);

begin

if DataSource1.DataSet.State = dsBrowse then begin

DoSomething;

end;

end;

Событие OnStateChange событие происходит всякий раз, когда изменяется текущее состояние DataSet. DataSet всегда знает, в каком состоянии он находится. Если Вы вызываете Edit, Append или Insert, то TTable знает, что он теперь находится в режиме редактирования (dsEdit или dsInsert). Аналогично, после того, как Вы делаете Post, то TTable знает что данные больше не редактируется, и переключается обратно в режим просмотра (dsBrowse).

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

TDataSetState = (dsInactive, dsBrowse, dsEdit, dsInsert,

dsSetKey, dsCalcFields);

В течение обычного сеанса работы, БД часто меняет свое состояние между Browse, Edit, Insert и другими режимами. Если Вы хотите отслеживать эти изменения, то Вы можете реагировать на них написав примерно такой код:

procedure TForm1.DataSource1StateChange(Sender: TObject);

var

S: String;

begin

case Table1.State of

dsInactive: S := 'Inactive';

dsBrowse: S := 'Browse';

dsEdit: S := 'Edit';

dsInsert: S := 'Insert';

dsSetKey: S := 'SetKey';

dsCalcFields: S := 'CalcFields';

end;

Label1.Caption := S;

end;

OnUpdateData событие происходит перед тем, как данные в текущей записи будут обновлены. Например, OnUpdateEvent будет происходить между вызовом Post и фактическим обновлением информации на диске.

События, генерируемые TDataSource могут быть очень полезны. Иллюстрацией этого служит следующий пример. Эта программа работает с таблицей COUNTRY, и включает TTable, TDataSource, пять TEdit, шесть TLlabel, восемь кнопок и панель. Действительное расположение элементов показано на рис.11. Обратите внимание, что шестой TLabel расположен на панели внизу главной формы.

Рис.11: Программа STATE показывает, как отслеживать текущее состояние таблицы.

Для всех кнопок напишите обработчики, вроде:

procedure TForm1.FirstClick(Sender: TObject);

begin

Table1.First;

end;

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

Edits: array[1..5] of TEdit;

Чтобы заполнить массив, Вы можете в событии OnCreate главной формы написать:

procedure TForm1.FormCreate(Sender: TObject);

var

i: Integer;

begin

for i := 1 to 5 do

Edits[i] := TEdit(FindComponent('Edit' + IntToStr(i)));

Table1.Open;

end;

Код показанный здесь предполагает, что первый редактор, который Вы будете использовать назовем Edit1, второй Edit2, и т.д. Существование этого массива позволяет очень просто использовать событие OnDataChange, чтобы синхронизировать содержание объектов TEdit с содержимом текущей записи в DataSet:

procedure TForm1.DataSource1DataChange(Sender: TObject;

Field: TField);

var

i: Integer;

begin

for i := 1 to 5 do

Edits[i].Text := Table1.Fields[i - 1].AsString;

end;

Всякий раз, когда вызывается Table1.Next, или любой другой из навигационных методов, то будет вызвана процедура показанная выше. Это обеспечивает то, что все редакторы всегда содержат данные из текущей записи.

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

procedure TForm1.DataSource1UpdateData(Sender: TObject);

var

i: Integer;

begin

for i := 1 to 5 do

Table1.Fields[i - 1].AsString := Edits[i].Text;

end;

Программа будет автоматически переключатся в режим редактирования каждый раз, когда Вы вводите что-либо в одном из редакторов. Это делается в обработчике события OnKeyDown (укажите этот обработчик ко всем редакторам):

procedure TForm1.Edit1KeyDown(Sender: TObject;

var Key: Word; Shift: TShiftState);

begin

if DataSource1.State <> dsEdit then

Table1.Edit;

end;

Этот код показывает, как Вы можете использовать св-во State DataSource, чтобы определить текущий режим DataSet.

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

procedure TForm1.DataSource1StateChange(Sender: TObject);

var

s : String;

begin

case DataSource1.State of

dsInactive : s:='Inactive';

dsBrowse : s:='Browse';

dsEdit : s:='Edit';

dsInsert : s:='Insert';

dsSetKey : s:='SetKey';

dsCalcFields : s:='CalcFields';

end;

Label6.Caption:=s;

end;

Данная программа является демонстрационной и ту же задачу можно решить гораздо проще, если использовать объекты TDBEdit.

Отслеживание состояния DataSet

В предыдущей части Вы узнали, как использовать TDataSource, чтобы узнать текущее состоянии TDataSet. Использование DataSource - это простой путь выполнения данной задачи. Однако, если Вы хотите отслеживать эти события без использования DataSource, то можете написать свои обработчики событий TTable и TQuery:

property OnOpen

property OnClose

property BeforeInsert

property AfterInsert

property BeforeEdit

property AfterEdit

property BeforePost

property AfterPost

property OnCancel

property OnDelete

property OnNewRecord

Большинство этих свойств очевидны. Событие BeforePost функционально подобно событию TDataSource.OnUpdateData, которое объяснено выше. Другими словами, программа STATE работала бы точно также, если бы Вы отвечали не на DataSource1.OnUpdateData а на Table1.BeforePost. Конечно, в первом случае Вы должен иметь TDataSource на форме, в то время, как во втором этого не требуется.

Урок 5: Компонент TTable. Создание таблиц
с помощью компонента TTable

Содержание урока 5:

Обзор

На этом небольшом уроке мы завершим изучение возможностей создания таблиц. Как Вы помните, мы уже освоили два способа создания таблиц - с помощью утилиты Database Desktop, входящей в поставку Delphi и с помощью SQL-запросов, которые можно использовать как в WISQL (Windows Interactive SQL - клиентская часть Local InterBase), так и в компоненте TQuery. Теперь мы рассмотрим, как можно создавать локальные таблицы в режиме выполнения с помощью компонента TTable.

Создание таблиц с помощью компонента TTable

Для создания таблиц компонент TTable имеет метод CreateTable. Этот метод создает новую пустую таблицу заданной структуры. Данный метод (процедура) может создавать только локальные таблицы формата dBase или Paradox.

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

var

Table1: TTable;

...

Table1:=TTable.Create(nil);

...

Перед вызовом метода CreateTable необходимо установить значения свойств

• TableType - тип таблицы

• DatabaseName - база данных

• TableName - имя таблицы

• FieldDefs - массив описаний полей

• IndexDefs - массив описаний индексов.

Свойство TableType имеет тип TTableType и определяет тип таблицы в базе данных. Если это свойство установлено в ttDefault, тип таблицы определяется по расширению файла, содержащего эту таблицу:

• Расширение .DB или без расширения: таблица Paradox

• Расширение .DBF : таблица dBASE

• Расширение .TXT : таблица ASCII (текстовый файл).

Если значение свойства TableType не равно ttDefault, создаваемая таблица всегда будет иметь установленный тип, вне зависимости от расширения:

• ttASCII: текстовый файл

• ttDBase: таблица dBASE

• ttParadox: таблица Paradox.

Свойство DatabaseName определяет базу данных, в которой находится таблица. Это свойство может содержать:

• BDE алиас

• директорий для локальных БД

• директорий и имя файла базы данных для Local InterBase

• локальный алиас, определенный через компонент TDatabase.

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

Свойство FieldDefs (имеющее тип TFieldDefs) для существующей таблицы содержит информацию обо всех полях таблицы. Эта информация доступна только в режиме выполнения и хранится в виде массива экземпляров класса TFieldDef, хранящих данные о физических полях таблицы (т.о. вычисляемые на уровне клиента поля не имеют своего объекта TFieldDef). Число полей определяется свойством Count, а доступ к элементам массива осуществляется через свойство Items:

property Items[Index: Integer]: TFieldDef;

При создании таблицы, перед вызовом метода CreateTable, нужно сформировать эти элементы. Для этого у класса TFieldDefs имеется метод Add:

procedure Add(const Name: string; DataType: TFieldType; Size: Word; Required: Boolean);

Параметр Name, имеющий тип string, определяет имя поля. Параметр DataType (тип TFieldType) обозначает тип поля. Он может иметь одно из следующих значений, смысл которых ясен из их наименования:

TFieldType = (ftUnknown, ftString, ftSmallint, ftInteger, ftWord, ftBoolean, ftFloat, ftCurrency, ftBCD, ftDate, ftTime, ftDateTime, ftBytes, ftVarBytes, ftBlob, ftMemo,

ftGraphic);

Параметр Size (тип word) представляет собой размер поля. Этот параметр имеет смысл только для полей типа ftString, ftBytes, ftVarBytes, ftBlob, ftMemo, ftGraphic, размер которых может сильно варьироваться. Поля остальных типов всегда имеют строго фиксированный размер, так что данный параметр для них не принимается во внимание. Четвертый параметр - Required - определяет, может ли поле иметь пустое значение при записи в базу данных. Если значение этого параметра - true, то поле является “требуемым”, т.е. не может иметь пустого значения. В противном случае поле не является “требуемым” и, следовательно, допускает запись значения NULL. Отметим, что в документации по Delphi и online-справочнике допущена ошибка - там отсутствует упоминание о четвертом параметре для метода Add.

Если Вы желаете индексировать таблицу по одному или нескольким полям, используйте метод Add для свойства IndexDefs, которое, как можно догадаться, также является объектом, т.е. экземпляром класса TIndexDefs. Свойство IndexDefs для существующей таблицы содержит информацию обо всех индексах таблицы. Эта информация доступна только в режиме выполнения и хранится в виде массива экземпляров класса TIndexDef, хранящих данные об индексах таблицы. Число индексов определяется свойством Count, а доступ к элементам массива осуществляется через свойство Items:

property Items[Index: Integer]: TIndexDef;

Метод Add класса TIndexDefs имеет следующий вид:

procedure Add(const Name, Fields: string;
Options: TIndexOptions);

Параметр Name, имеющий тип string, определяет имя индекса. Параметр Fields (также имеющий тип string) обозначает имя поля, которое должно быть индексировано, т.е. имя индексируемого поля. Составной индекс, использующий несколько полей, может быть задан списком имен полей, разделенных точкой с запятой “;”, например: ‘Field1;Field2;Field4’. Последний параметр - Options - определяет тип индекса. Он может иметь набор значений, описываемых типом TIndexOptions:

TIndexOptions = set of (ixPrimary, ixUnique, ixDescending,

ixCaseInsensitive, ixExpression);

Поясним эти значения. ixPrimary обозначает первичный ключ, ixUnique - уникальный индекс, ixDescending - индекс, отсортированный по уменьшению значений (для строк - в порядке, обратном алфавитному), ixCaseInsensitive - индекс, “нечувствительный” к регистру букв, ixExpression - индекс по выражению. Отметим, что упоминание о последнем значении также отсутствует в документации и online-справочнике. Опция ixExpression позволяет для таблиц формата dBase создавать индекс по выражению. Для этого достаточно в параметре Fields указать желаемое выражение, например: 'Field1*Field2+Field3'. Вообще говоря, не все опции индексов применимы ко всем форматам таблиц. Ниже мы приведем список допустимых значений для таблиц dBase и Paradox:

Опции индексов dBASE Paradox

---------------------------------------

ixPrimary 

ixUnique  

ixDescending  

ixCaseInsensitive 

ixExpression 

Необходимо придерживаться указанного порядка применения опций индексов во избежание некорректной работы. Следует отметить, что для формата Paradox опция ixUnique может использоваться только вместе с опцией ixPrimary (см. пример на диске - Рис. 1).

Итак, после заполнения всех указанных выше свойств и вызова методов Add для FieldDefs и IndexDefs необходимо вызвать метод класса TTable - CreateTable:

with Table1 do

begin

DatabaseName:='dbdemos';

TableName:='mytest';

TableType:=ttParadox;

{Создать поля}

with FieldDefs do

begin

Add('Surname', ftString, 30, true);

Add('Name', ftString, 25, true);

Add('Patronymic', ftString, 25, true);

Add('Age', ftInteger, 0, false);

Add('Weight', ftFloat, 0, false);

end;

{Сгенерировать индексы}

with IndexDefs do

begin

Add('I_Name', 'Surname;Name;Patronymic',
[ixPrimary, ixUnique]);

Add('I_Age', 'Age', [ixCaseInsensitive]);

end;

CreateTable;

end;

Рис. 1: Программа CREATABL демонстрирует технику создания таблиц во время выполнения

Индексы можно сгенерировать и не только при создании таблицы. Для того чтобы сгенерировать индексы для существующей таблицы, нужно вызвать метод AddIndex класса TTable, набор параметров которого полностью повторяет набор параметров для метода Add класса TIndexDefs:

procedure AddIndex(const Name, Fields: string;
Options: TIndexOptions);

При этом для метода AddIndex справедливы все замечания по поводу записи полей и опций индексов, сделанные выше.

Заключение

Итак, мы познакомились с еще одним способом создания таблиц - способом, использующим метод CreateTable класса TTable. Использование данного способа придаст Вашему приложению максимальную гибкость, и Вы сможете строить локальные таблицы “на лету”. Сопутствующим методом является метод AddIndex класса TTable, позволяющий создавать индексы для уже существующей таблицы. Подчеркнем еще раз, что данный способ применим только для локальных таблиц.

Урок 6: Объект TQuery

Содержание Урока 6:

Краткий Обзор

В этой главе Вы узнаете некоторые основные понятия о запросах (queries) и транзакциях. Это достаточно широкие понятия, поэтому обсуждение разбито на следующие основные части:

• Объект TQuery.

• Использование SQL с локальным и удаленным серверами (Select, Update, Delete и Insert).

• Использование SQL для создания объединения (joins), связанных курсоров (linked cursors) и программ, которые ведут поиск заданных записей.

Сокращение SQL означает Structured Query Language - Язык Структурированных Запросов, и обычно произноситься либо как "Sequel" либо " Ess Qu El”. Однако, как бы Вы его ни произносили, SQL - это мощный язык БД, который легко доступен из Delphi, но который отличается от родного языка Delphi. Delphi может использовать утверждения SQL для просмотра таблиц, выполнять объединение таблиц, создавать отношения один-ко-многим, или исполнить почти любое действие, которое могут сделать ваши основные инструменты БД. Delphi поставляется с Local SQL, так что Вы можете выполнять запросы SQL при работе с локальными таблицами, без доступа к SQL серверу.

Delphi обеспечивает поддержку “pass through SQL”, это означает то, что Вы можете составлять предложения SQL и посылать их непосредственно серверам Oracle, Sybase, Inrterbase и другим. “Pass through SQL” - это мощный механизм по двум причинам:

1. Большинство серверов могут обрабатывать SQL запросы очень быстро, а это означает, что используя SQL для удаленных данных, Вы получите ответ очень быстро.

2. Есть возможность составлять SQL запросы, которые заставят сервер исполнить специализированные задачи, недоступные через родной язык Delphi.

Перед чтением этой статьи Вы должны иметь, по крайней мере, элементарное понятие о серверах и различиях между локальными и удаленными (remote) данными.

Основные понятия о TQuery

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

Вы может создать SQL запрос используя компонент TQuery следующим способом:

1. Назначите Псевдоним (Alias) DatabaseName.

2. Используйте свойство SQL чтобы ввести SQL запрос типа
   “Select * from Country”.

3. Установите свойство Active в True

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

Две основных вещи, которые Вы должны понять прежде, чем перейти дальше:

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

• Delphi использует pass through SQL, поэтому для разных SQL серверов синтаксис может быть несколько разным. Версия SQL для локальных таблиц (Local SQL) очень сильно урезан, по сравнению со стандартом. Чтобы узнать о его возможностях, Вы должны прочитать не только эту статью, но также файл LOCALSQL.HLP.

Вы увидите, что объект TQuery один из наиболее полезных и гибких компонентов, доступных в Delphi. С ним Вы сможете воспользоваться всей мощью, предоставляемой лидерами среди промышленных SQL серверов, вроде InrterBase, Oracle или Sybase.

Свойство SQL

Свойство SQL - вероятно, самая важная часть TQuery. Доступ к этому свойству происходит либо через Инспектор Объектов во время конструирования проекта (design time), или программно во время выполнения программы (run time).

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

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

При программном использовании TQuery, рекомендуется сначала закрыть текущий запрос и очистить список строк в свойстве SQL:

Query1.Close;

Query1.SQL.Clear;

Обратите внимание, что всегда можно “безопасно” вызвать Close. Даже в том случае, если запрос уже закрыт, исключительная ситуация генерироваться не будет.

Следующий шаг - добавление новых строк в запрос:

Query1.SQL.Add(‘Select * from Country’);

Query1.SQL.Add(‘where Name = ’’Argentina’’’);

Метод Add используется для добавления одной или нескольких строк к запросу SQL. Общий объем ограничен только количеством памяти на вашей машине.

Чтобы Delphi отработал запрос и возвратил курсор, содержащий результат в виде таблицы, можно вызвать метод:

Query1.Open;

Демонстрационная программа THREESQL показывает этот процесс (см Рис.1)

Рис.1: Программа THREESQL показывает, как сделать несколько запросов с помощью единственного объекта TQuery.

Программа THREESQL использует особенность локального SQL, который позволяет использовать шаблоны поиска без учета регистра (case insensitive). Например, следующий SQL запрос:

Select * form Country where Name like ’C%’

возвращает DataSet, содержащий все записи, где поле Name начинается с буквы ‘C’. Следующий запрос позволит увидеть все страны, в названии которых встречается буква ‘C’:

Select * from Country where Name like ‘%C%’;

Вот запрос, которое находит все страны, название которых заканчивается на ‘ia’:

Select * from Country where Name like ‘%ia’;

Одна из полезных особенностей свойства SQL - это способность читать файлы, содержащие текст запроса непосредственно с диска. Эта особенность показана в программе THREESQL.

Вот как это работает. В директории с примерами к данному уроку есть файл с расширением SQL. Он содержат текст SQL запроса. Программа THREESQL имеет кнопку с названием Load, которая позволяет Вам выбрать один из этих файлов и выполнять SQL запрос, сохраненный в этом файле.

Кнопка Load имеет следующий метод для события OnClick:

procedure TForm1.LoadClick(Sender: TObject);

begin

if OpenDialog1.Execute then

with Query1 do begin

Close;

SQL.LoadFromFile(OpenDialog1.FileName);

Open;

end;

end;

Метод LoadClick сначала загружает компоненту OpenDialog и позволяет пользователю выбрать файл с расширением SQL. Если файл выбран, текущий запрос закрывается, выбраный файл загружается с диска в св-во SQL, запрос выполняется и результат показывается пользователю.

TQuery и Параметры

Delphi позволяет составить “гибкую” форму запроса, называемую параметризованным запросом. Такие запросы позволяют подставить значение переменной вместо отдельных слов в выражениях “where” или “insert”. Эта переменная может быть изменена практически в любое время. (Если используется локальный SQL, то можно сделать замену почти любого слова в утверждении SQL, но при этом та же самая возможность не поддерживается большинством серверов.)

Перед тем, как начать использовать параметризованные запросы, рассмотрим снова одно из простых вышеупомянутых предложений SQL:

Select * from Country where Name like ’C%’

Можно превратить это утверждение в параметризованный запрос заменив правую часть переменной NameStr:

select * from County where Name like :NameStr

В этом предложении SQL, NameStr не является предопределенной константой и может изменяться либо во время дизайна, либо во время выполнения. SQL parser (программа, которая разбирает текст запроса) понимает, что он имеет дело с параметром, а не константой потому, что параметру предшествует двоеточие ":NameStr". Это двоеточие сообщает Delphi о необходимости заменить переменную NameStr некоторой величиной, которая будет известна позже.

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

Есть два пути присвоить значение переменной в параметризованном запросе SQL. Один способ состоит в том, чтобы использовать свойство Params объекта TQuery. Второй - использовать свойство DataSource для получения информации из другого DataSet. Вот ключевые свойства для достижения этих целей:

property Params[Index: Word];

function ParamByName(const Value: string);

property DataSource;

Если подставлять значение параметра в параметризованный запрос через свойство Params, то обычно нужно сделать четыре шага:

1. Закрыть TQuery

2. Подготовить объект TQuery, вызвав метод Prepare

3. Присвоить необходимые значения свойству Params

4. Открыть TQuery

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

Вот фрагмент кода, показывающий как это может быть выполнено практически:

Query1.Close;

Query1.Prepare;

Query1.Params[0].AsString := ‘Argentina’;

Query1.Open;

Этот код может показаться немного таинственным. Чтобы понять его, требуется внимательный построчный анализ. Проще всего начать с третьей строки, так как свойство Params является “сердцем” этого процесса.

Params - это индексированное свойство, которое имеет синтаксис как у свойства Fields для TDataSet. Например, можно получить доступ к первой переменной в SQL запросе, адресуя нулевой элемент в массиве Params:

Params[0].AsString := ‘”Argentina”’;

Если параметризованный SQL запрос выглядит так:

select * from Country where Name = :NameStr

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

select * from Country where Name = “Argentina”

Все, что произошло, это переменной :NameStr было присвоено значение "Аргентина" через свойство Params. Таким образом, Вы закончили построение простого утверждения SQL.

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

Params[1].AsString := ‘SomeValue’;

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

ParamByName(‘NameStr’).AsString:=’”Argentina”’;

Итак, параметризованные SQL запросы используют переменные, которые всегда начинаются с двоеточия, определяя места, куда будут переданы значения параметров.

Прежде, чем использовать переменную Params, сначала можно вызвать Prepare. Этот вызов заставляет Delphi разобрать ваш SQL запрос и подготовить свойство Params так, чтобы оно "было готово принять” соответствующее количество переменных. Можно присвоить значение переменной Params без предварительного вызова Prepare, но это будет работать несколько медленнее.

После того, как Вы вызывали Prepare, и после того, как присвоили необходимые значения переменной Params, Вы должны вызвать Open, чтобы закончить привязку переменных и получить желаемый DataSet. В нашем случае, DataSet должен включать записи где в поле “Name” стоит “Argentina”.

Рассмотрим работу с параметрами на примере (программа PARAMS.DPR). Для создания программы, разместите на форме компоненты TQuery, TDataSource, TDBGrid и TTabSet. Соедините компоненты и установите в свойстве TQuery.DatabaseName псевдоним DBDEMOS. См. рис.2

Рис.2 : Программа PARAMS во время дизайна.

В обработчике события для формы OnCreate напишем код, заполняющий закладки для TTabSet, кроме того, здесь подготавливается запрос:

procedure TForm1.FormCreate(Sender: TObject);

var

i : Byte;

begin

Query1.Prepare;

for i:=0 to 25 do

TabSet1.Tabs.Add(Chr(Byte('A')+i));

end;

Текст SQL запроса в компоненте Query1:

select * from employee where LastName like :LastNameStr

Запрос выбирает записи из таблицы EMPLOYEE, в которых поле LastName похоже (like) на значение параметра :LastNameStr. Параметр будет передаваться в момент переключения закладок:

procedure TForm1.TabSet1Change(Sender: TObject;

NewTab: Integer;

var AllowChange: Boolean);

begin

with Query1 do begin

Close;

Params[0].AsString:=

'"'+TabSet1.Tabs.Strings[NewTab]+'%"';

Open;

end;

end;

Рис.3: Программа PARAMS во время выполнения.

Передача параметров через TDataSource

В предыдущем Уроке Вы видели способ создания отношения однин-ко-многим между двумя таблицами. Теперь речь пойдет о выполнении того же самого действия с использованием объекта TQuery. Этот способ более гибок в том отношении, что он не требует индексации по полям связи.

Объект TQuery имеет свойство DataSource, которое может использоваться для того, чтобы создать связь с другим DataSet. Не имеет значения, является ли другой DataSet объектом TTable, TQuery, или некоторый другим потомком TDataSet. Все что нужно для установления соединения - это удостовериться, что у того DataSet есть связанный с ним DataSource.

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

Рассмотрите следующий параметризованный запрос:

select * from Orders where CustNo = :CustNo

В этом запросе :CustNo - связывающая переменная, которой должно быть присвоено значение из некоторого источника. Delphi позволяет использовать поле TQuery.DataSource чтобы указать другой DataSet, который предоставит эту информацию автоматически. Другими словами, вместо того, чтобы использовать свойство Params и “вручную” присваивать значения переменной, эти значения переменной могут быть просто взяты автоматически из другой таблицы. Кроме того, Delphi всегда сначала пытается выполнить параметризованный запрос используя свойство DataSource, и только потом (если не было найдено какое-то значение параметра) будет пытаться получить значение переменной из свойства Params. При получении данных из DataSource считается, что после двоеточия стоит имя поля из DataSource. При изменении текущей записи в главном DataSet запрос будет автоматически пересчитываться.

Давайте переделаем пример из прошлого урока (LINKTBL - связывание двух таблиц). Создайте новый проект, положите на форму один набор TTable, TDataSource и TDBGrid. Привяжите его к таблице CUSTOMER. Положите на форму второй набор - TQuery, TDataSource и TDBGrid и свяжите объекты между собой. (см рис.4).

В свойстве SQL наберите текст запроса:

select * from Orders where CustNo = :CustNo

В свойстве DatabaseName для Query1 укажите DBDEMOS.

В свойстве DataSource для Query1 укажите DataSource1.

Поставьте Active = True и запустите программу.

Рис.4: Программа LINKQRY - связанные курсоры с помощью SQL

Выполнение соединения нескольких таблиц.

Вы видели что таблицы CUSTOMERS и ORDERS связаны в отношении один-ко-многим, основанному на поле CustNo. Таблицы ORDERS и ITEMS также связаны отношении один-ко-многим, только через поле OrderNo.

Более конкретно, каждый заказ который существует в таблице ORDERS будет иметь несколько записей в таблице ITEMS, связанных с этим заказом. Записи из таблицы ITEMS определяют тип и количество изделий, связанных с этим заказом.

Пример.

Некто Иванов Ф.П. 1 мая 1995г. заказал следующее:

1. Гайка 4х-угольная - 50 штук

2. Вентиль - 1 штука

А некто Сидорчук Ю.Г. 8 декабря 1994г. заказал:

1. М/схема КР580 ИК80 - 10 штук

2. Транзистор КТ315 - 15 штук

3. Моток провода - 1 штука

В ситуации подобной этой, иногда проще всего "соединить" данные из таблиц ORDERS и ITEMS так, чтобы результирующий DataSet содержал информацию из обеих таблиц:

Иванов Ф.П. 1 мая 1995г Гайка 4х-угольная 50 штук

Иванов Ф.П. 1 мая 1995г Вентиль 1 штука

Сидорчук Ю.Г. 8 декабря 1994г М/схема КР580 ИК80 10 штук

Сидорчук Ю.Г. 8 декабря 1994г Транзистор КТ315 15 штук

Сидорчук Ю.Г. 8 декабря 1994г Моток провода 1 штука

Слияние этих двух таблиц называется "соединение" и это одно из фундаментальных действий, которые Вы можете выполнить на наборе двух или больше таблиц.

Взяв таблицы ORDERS и ITEMS из подкаталога DEMOS\DATA, их можно соединить их таким путем, что поля CustNo, OrderNo и SaleDate из таблицы ORDERS будут “слиты” с полями PartNo и Qty из таблицы ITEMS и сформируют новый DataSet, содержащий все пять полей. Grid содержащий результирующий DataSet показан на рис.5

Рис.5: Соединение таблиц ORDERS и ITEMS может быть сделано так, что формируется новый DataSet содержащий поля из каждой таблицы.

Имеется существенное различие между связанными курсорами и соединенными таблицами. Однако они имеют две общие черты:

• И те, и другие используют две или более таблиц

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

Соединение таблиц ORDERS и ITEMS может быть выполнено единственным SQL запросом, который выглядит так:

select

O.CustNo, O.OrderNo, O.SaleDate, I.PartNo, I.Qty

from Orders O, Items I

where O.OrderNo = I.OrderNo

Этот запрос состоит из четырех различных частей:

1. Выражение Select определяет, что Вы хотите получить - курсор, содержащий некоторую форму DataSet.

2. Затем идет список полей которые Вы хотите включить в dataset. Этот список включает поля CustNo, OrderNo, SaleDate, PartNo и Qty. Первые три поля из таблицы ORDERS, а два других - из таблицы ITEMS.

3. Выражение from объявляет, что Вы работаете с двумя таблицами, одна называется ORDERS, а другая ITEMS. Для краткости, в запросе используется особенность SQL, которая позволяет Вам ссылаться на таблицу ORDERS буквой O, а на таблицу ITEMS буквой I.

4. Выражение where жизненно важно потому, что оно определяет поля связи для двух таблиц. Некоторые серверы могут вернуть DataSet, даже если Вы не включите выражение where в запрос, но почти всегда результирующий набор записей будет не тем, что Вы хотели видеть. Чтобы получить нужный результат, убедитесь что Вы включили выражение where.

Open или ExecSQL?

После того, как составлен SQL запрос, есть два различных способа выполнить его. Если Вы хотите получить курсор, то нужно вызывать Open. Если выражение SQL не подразумевает возвращение курсора, то нужно вызывать ExecSQL. Например, если происходит вставка, удаление или обновление данных (т.е. SQL запросы INSERT, DELETE, UPDATE), то нужно вызывать ExecSQL. Тоже самое можно сказать по-другому: Open вызывается при запросе типа SELECT, а ExecSQL - во всех остальных случаях.

Вот типичный SQL запрос, который используется для удаления записи из таблицы:

delete from Country where Name = ‘Argentina’;

Этот запрос удалил бы любую запись из таблицы COUNTRY, которая имеет значение "Argentina" в поле Имя.

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

delete from Country where Name = :CountryName

В этом случае переменная :CountryName может быть изменена во время выполнения:

Query2.Prepare;

Query2.Params[0] := ‘Argentina’;

Query2.ExecSQL;

Код сначала вызывает Prepare, чтобы сообщить Delphi что он должен разобрать SQL запрос и подготовить свойство Params. Следующим шагом присваивается значение свойству Params и затем выполняется подготовленный SQL запрос. Обратите внимание, что он выполняется через ExecSQL, а не Open.

Программа INSQUERY из примеров Delphi демонстрирует эту технику (проект C:\DELPHI\DEMOS\DB\INSQUERY.DPR)

Специальные свойства TQuery

Есть несколько свойств, принадлежащих TQuery, которые еще не упоминались:

property UniDirectional: Boolean;

property Handle: HDBICur;

property StmtHandle: HDBIStmt;

property DBHandle: HDBIDB;

Свойство UniDirectional используется для того, чтобы оптимизировать доступ к таблице. Если Вы установите UniDirectional в True, то Вы можете перемещаться по таблице более быстро, но Вы сможете двигаться только вперед.

Свойство StmtHandle связано со свойством Handle TDataSet. То есть, оно включено исключительно для того, что Вы могли делать вызовы Borland Database Engine напрямую. При нормальных обстоятельствах, нет никакой необходимости использовать это свойство, так как компоненты Delphi могут удовлетворить потребностями большинства программистов. Однако, если Вы знакомы с Borland Database Engine, и если Вы знаете что существуют некоторые возможности не поддерживаемые в VCL, то Вы можете использовать TQuery.StmtHandle, или TQuery. Handle, чтобы сделать вызов напрямую в engine.

Следующий фрагмент кода показывает два запроса к BDE:

var

Name: array[0..100] of Char;

Records: Integer;

begin

dbiGetNetUserName(Name);

dbiGetRecordCount(Query1.Handle, Records);

end;

Урок 7: Редактор DataSet, Вычисляемые поля

Содержание Урока 7:

Обзор

В этой статье вы узнаете о Редакторе DataSet и о способах управления компонентом TDBGrid во время выполнения программы. Здесь же будут рассмотрены вычисляемые поля - весьма ценная особенность Редактора DataSet.

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

Редактор DataSet

Редактор DataSet может быть вызван с помощью объектов TTable или TQuery. Чтобы начать работать с ним, положите объект TQuery на форму, установите псевдоним DBDEMOS, введите SQL запрос "select * from customer" и активизируйте его (установив св-во Active в True).

Откройте комбобокс “Object Selector” вверху Инспектора Объектов - в настоящее время там имеется два компонента: TForm и TQuery.

Нажмите правую кнопку мыши на объекте TQuery и в контекстном меню выберите пункт “Fields Editor”. Нажмите кнопку Add - появиться диалог Add Fields, как показано на рис.1

Рис.1: Диалог Add Fields Редактора DataSet.

По-умолчанию, все поля в диалоге выбраны. Нажмите на кнопку OK, чтобы выбрать все поля, и закройте редактор. Снова загляните в “Object Selector”, теперь здесь появилось несколько новых объектов, (см. рис.2)

Рис.2: Object Selector показывает в списке все объекты созданные в Редакторе DataSet. Вы можете также найти этот список в определении класса TForm1.

Эти новые объекты будут использоваться для визуального представления таблицы CUSTOMER пользователю.

Вот полный список объектов, которые только что созданы:

Query1CustNo: TFloatField;

Query1Company: TStringField;

Query1Addr1: TStringField;

Query1Addr2: TStringField;

Query1City: TStringField;

Query1State: TStringField;

Query1Zip: TStringField;

Query1Country: TStringField;

Query1Phone: TStringField;

Query1FAX: TStringField;

Query1TaxRate: TFloatField;

Query1Contact: TStringField;

Query1LastInvoiceDate: TDateTimeField;

Я вырезал и вставил этот список из определения класса TForm1, которое можно найти в окне Редактора исходного текста. Происхождение имен показанных здесь, должно быть достаточно очевидно. Часть "Query1" берется по-умолчанию от имени объекта TQuery, а вторая половина от имени поля в таблице Customer. Если бы мы сейчас переименовали объект Query1 в Customer, то получили бы такие имена:

CustomerCustNo

CustomerCompany

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

Любой объект, созданный в редакторе DataSet является наследником класса TField. Точный тип потомка зависит от типа данных в конкретном поле. Например, поле CustNo имеет тип TFloatField, а поле Query1City имеет тип TStringField. Это два типа полей, которые Вы будете встречать наиболее часто. Другие типы включают тип TDateTimeField, который представлен полем Query1LastInvoiceDate, и TIntegerField, который не встречается в этой таблице.

Чтобы понять, что можно делать с потомками TField, откройте Browser, выключите просмотр полей Private и Protected, и просмотрите свойства и методы Public и Published соответствующих классов.

Наиболее важное свойство называется Value. Вы можете получить доступ к нему так:

procedure TForm1.Button1Click(Sender: TObject);

var

d: Double;

S: string;

begin

d := Query1CustNo.Value;

S := Query1Company.Value;

d:=d+1;

S := 'Zoo';

Query1CustNo.Value := d;

Query1Company.Value := S;

end;

В коде, показанном здесь, сначала присваиваются значения переменным d и S. Следующие две строки изменяют эти значения, а последний две присваивают новые значения объектам. Не имеет большого смысла писать код, подобный этому, в программе, но этот код служит лишь для того, чтобы продемонстрировать синтаксис, используемый с потомками TField.

Свойство Value всегда соответствует типу поля, к которому оно относится. Например у TStringFields - string, TCurrencyFields - double. Однако, если вы отображаете поле типа TCurrencyField с помощью компонент, “чувствительных к данным” (data-aware: TDBEdit, TDBGrid etc.), то оно будет представлена строкой типа: "$5.00".

Это могло бы заставить вас думать, что у Delphi внезапно отключился строгий контроль типов. Ведь TCurrencyField.Value объявлена как Double, и если Вы пробуете присвоить ему строку, Вы получите ошибку “type mismatch” (несоответствие типа). Вышеупомянутый пример демонстрирует на самом деле свойства объектов визуализации данных, а не ослабление проверки типов. (Однако, есть возможность получить значение поля уже преобразованное к другому типу. Для этого у TField и его потомков имеется набор методов типа AsString или AsFloat. Конечно, преобразование происходит только тогда, когда имеет смысл.)

Если нужно получить имена полей в текущем DataSet, то для этого используется свойство FieldName одним из двух способов, показанных ниже:

S := Query1.Fields[0].FieldName;

S := Query1CustNo.FieldName;

Если вы хотите получить имя объекта, связанного с полем, то вы должны использовать свойство Name:

S := Query1.Fields[0].Name;

S := Query1CustNo.Name;

Для таблицы CUSTOMER, первый пример вернет строку "CustNo", а любая из строк второго примера строку "Query1CustNo".

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

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

• выполнение вычислений по двум или более полям в DataSet, и отображение результата вычислений в третьем поле.

• имитация соединения двух таблиц с возможностью редактировать результат соединения.

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

Эта программа связывает три таблицы в отношении один ко многим. В частности, ORDERS и ITEMS связаны по полю OrderNo, а ITEMS и PARTS связаны по полю PartNo. (В таблице ORDERS хранятся все заказы; в таблице ITEMS - предметы, указанные в заказах; PARTS - справочник предметов). В программе можно перемещаться по таблице ORDERS и видеть связанный с текущим заказом список включенных в него предметов. Программа CALC_SUM достаточно сложная, но хорошо иллюстрирует мощность вычисляемых полей.

Последовательность создания проекта CALC_SUM:

• Создайте новый проект (File|New Project) и удалите из него форму (в Менеджере Проекта View|Project Manager)

• Выберите эксперта форм БД из меню Help.

• На первом экране, выберите "Create a master/detail form" и "Create a form using TQuery Objects".

• Нажмите кнопку Next и выберите таблицу ORDERS.DB из псевдонима БД DBDEMOS.

• Нажмите Next и выберите поля OrderNo, CustNo, SaleDate, ShipDate и ItemsTotal из таблицы ORDERS.DB.

• Нажмите Next и выберите "Horizontal" из расстановки компонентов dbEdit на форме.

• Нажмите Next и выберите таблицу ITEMS.DB.

• В двух следующих экранах выберите все поля из таблицы и поместите их в grid.

• Нажмите Next и выберите поле OrderNo из Master и Detail ListBoxes, и Нажмите кнопку Add.

• Нажмите Next и сгенерируйте форму.

Требуется много слов для того, чтобы описать процесс показанный выше, но, фактически, выполнение команд в Эксперте форм БД легко и интуитивно.

Выделите первый из двух объектов TQuery и установят свойство Active в True. Для Query2 в свойстве SQL напишите текст запроса:

select * from Items I, Parts P

where (I.OrderNo =:OrderNo) and

(I.PartNo=P.PartNo)

Активизируйте объект Query2 (Active установите в True) и вызовите редактор DataSet (Fields Editor) для него. Вызовите диалог Add Fields и добавьте поля OrderNo, PartNo, Qty и ListPrice.

Нажмите Define и ведите слово Total в поле FieldName. Установите Field Type в CurrencyField. Проверьте что Calculated CheckBox отмечен. Нажмите Ok и закройте редактор DataSet.

Простой процесс описанный в предыдущем абзаце, показывает как создать вычисляемое поле. Если посмотреть в DBGrid, то можно видеть, что там теперь есть еще одно пустое поле. Для того, чтобы поместить значение в это поле, откройте в Инспекторе Объектов страницу событий для объекта Query2 и сделайте двойной щелчок на OnCalcFields. Заполните созданный метод так:

procedure TForm2.Query2CalcFields(DataSet: TDataSet);

begin

Query2NewTotalInvoice.Value := 23.0;

end;

После запуска программы поле Total будет содержит строку $23.00.

Это показывает, насколько просто создать вычисляемое поле, которое показывает правильно сформатированные данные. На самом деле это поле должно показывать нечто другое - произведение полей Qty (количество) и ListPrice (цена). Для этого вышеприведенный код для события OnCalcFields нужно изменить следующим образом:

procedure TForm1.Query2CalcFields(DataSet: TDataset);

begin

Query2Total.Value:=Query2Qty.Value*Query2ListPrice.Value;

end;

Если теперь запустить программу, то поле Total будет содержать требуемое значение.

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

Теперь давайте добавим вычисляемое поле для первой таблицы (Query1, ORDERS), которое будет отображать сумму значений из поля Total второй таблицы (Query2) для данного заказа. Вызовите редактор DataSet для объекта Query1 и добавьте вычисляемое поле NewItemsTotal типа CurrencyField. В обработчике события OnCalcFields для Query1 нужно подсчитать сумму и присвоить ее полю NewItemsTotal:

procedure TForm1.Query1CalcFields(DataSet: TDataset);

var

R : Double;

begin

R:=0;

with Query2 do begin

DisableControls;

Close;

Open;

repeat

R:=R+Query2Total.Value;

Next;

until EOF;

First;

EnableControls;

end;

Query1NewItemsTotal.Value:=R;

end;

В данном примере сумма подсчитывается с помощью простого перебора записей, это не самый оптимальный вариант - можно, например, для подсчета суммы использовать дополнительный объект типа TQuery. Метод DisableControls вызывается для того, чтобы отменить перерисовку DBGrid при сканировании таблицы. Запрос Query2 переоткрывается для уверенности в том, что его текущий набор записей соответствует текущему заказу.

Поместите на форму еще один элемент DBEdit и привяжите его к Query1, полю NewItemsTotal. Запустите программу, ее примерный вид показан на рис.3

Рис.3: Программа CALC_SUM

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

Управление TDBGrid во время выполнения

Объект DBGrid может быть полностью реконфигурирован во время выполнения программы. Вы можете прятать и показывать колонки, изменять порядок показа колонок и их ширину.

Вы можете использовать свойство Options объекта DBGrid, чтобы изменить ее представление. Свойство Options может принимать следующие возможные значения:

dgEditing	Установлен по-умолчанию в true, позволяет пользователю редактировать grid. Вы можете также установить свойство ReadOnly grid в True или False.
dgAlwaysShowEditor	Всегда показывать редактор.
dgTitles	Показывать названия колонок.
dgIndicator	Показывать небольшие иконки слева.
dgColumnResize	Может ли пользователь менять размер колонки.
dgColLines	Показывать линии между колонками.
dgRowLines	Показывать линии между строками.
dgTabs	Может ли пользователь использовать tab и shift-tab для переключения между колонками.
dgRowSelect	Выделять всю запись целиком.
dgAlwaysShowSelection	Всегда показывать выбранные записи.
dgConfirmDelete	Подтверждать удаление.
dgCancelOnExit	Отмена изменений при выходе из DBGrid.
dgMultiSelect	Одновременно может быть выделена больше чем одна запись.

Как объявлено в этой структуре:

DBGridOption = (dgEditing, dgAlwaysShowEditor, dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect,dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit, dgMultiSelect);

Например Вы можете установить опции в Runtime написав такой код:

DBGrid1.Options := [dgTitles, dgIndicator];

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

DBGrid1.Options := DBGrid1.Options + [dgTitles];

Пусть есть переменная ShowTitles типа Boolean, тогда следующий код позволяют включать и выключать параметр одной кнопкой:

procedure TForm1.Button3Click(Sender: TObject);

begin

if ShowTitles then

DBGrid1.Options := DBGrid1.Options + [dgTitles]

else

DBGrid1.Options := DBGrid1.Options - [dgTitles];

ShowTitles := not ShowTitles;

end;

Если Вы хотите скрыть поле в run-time, то можете установить свойство visible в false:

Query1.FieldByName(‘CustNo’).Visible := False;

Query1CustNo.Visible := False;

Обе строки кода выполняют идентичную задачу. Чтобы показать поле снова, установите видимый в true:

Query1.FieldByName(‘CustNo’).Visible := True;

Query1CustNo.Visible := True;

Если Вы хотите изменить положение колонки в Runtime, можете просто изменить индекс, (первое поле в записи имеет индекс нуль):

Query1.FieldByName(‘CustNo’).Index := 1;

Query1CustNo.Index := 2;

По-умолчанию, поле CustNo в таблице Customer является первым. Код в первой строке перемещает это поле во вторую позицию, а следующая строка перемещает его в третью позицию. Помните, что нумерация полей начинается с нуля, так присвоение свойству Index 1 делает поле вторым в записи. Первое поле имеет Index 0.

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

Если Вы хотите изменить ширину колонки в Runtime, только измените свойство DisplayWidth соответствующего TField.

Query1.FieldByName(‘CustNo’).DisplayWidth := 12;

Query1CustNo.DisplayWidth := 12;

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

Программа DBGR_RT показывает как работать с DBGrid в Runtime. Программа достаточно проста, кроме двух небольших частей, которые описаны ниже. Первая часть показывает, как создать check box в Runtime, а вторая показывает, как изменить порядок пунктов в listbox в Runtime.

При создании формы (событие OnCreate) ListBox заполняется именами полей, далее создается массив объектов CheckBox, соответствующий полям в таблице. Сперва все CheckBox’ы выбраны и все поля в таблице видимы. Программа узнает через TTable1 имена полей и присваивает их свойству Caption соответствующего CheckBox. Кроме того, обработчику события OnClick всех CheckBox’ов присваивается процедура ChBClick, которая и включает/выключает поля в DBGrid.

procedure TForm1.FormCreate(Sender: TObject);

var

i : Word;

R : Array[0..49] of TCheckBox;

begin

{Fill ListBox}

ListBox1.Clear;

for i:=0 to Table1.FieldCount-1 do

ListBox1.Items.Add(Table1.Fields[i].FieldName);

{Make CheckBoxes}

for i:=0 to Table1.FieldCount-1 do begin

R[I] := TCheckBox.Create(Self);

R[I].Parent := ScrollBox1;

R[I].Caption := Table1.Fields[i].FieldName;

R[I].Left := 10;

R[I].Top := I * CheckBox1.Height + 5;

R[I].Width := 200;

R[I].Checked := True;

R[I].OnClick := ChBClick;

end;

end;

Большая часть кода в этом примере выполняет относительно простые задачи, типа назначения имен и положений check boxes. Вот две ключевых строки:

R[I] := TCheckBox.Create(Self);

R[I].Parent := ScrollBox1;

Первая строки создает CheckBox с заданным Owner (Владельцем). Вторая строки назначает Parent (Родителя) для CheckBox. Чтобы понять различия между Родителем и Владельцем, посмотрите соответствующие свойства в online-help.

Программа содержит ListBox, который показывает текущий порядок полей в DataSet. Для изменения порядка полей в DataSet (а, следовательно, в DBGrid) используются две кнопки. При нажатии на одну из кнопок, выбранное в ListBox’е поле перемещается на одну позицию вверх или вниз. Синхронно с этим меняется и порядок полей в DBGrid. Код, показанный ниже, изменяет Index поля для Table1, изменяя, таким образом, позицию поля в DBGrid. Эти изменения касаются только визуального представления DataSet. Физически данные на диске не изменяются.

procedure TForm1.downButtonClick(Sender: TObject);

var

i : Integer;

begin

with ListBox1 do

if (ItemIndex<><>-1) then begin

i := ItemIndex;

{move ListBox item}

Items.Move(i, i+1);

ItemIndex := i+1;

{move Field}

Table1.Fields[i].Index:=i+1;

end;

end;

Последняя строка в примере как раз та, которая фактически изменяет индекс колонки, которую пользователь хочет переместить. Две строки кода непосредственно перед ней перемещают текущую строку в ListBox на новую позицию.

Внешний вид программы DBGR_RT показан на рис.4

Рис.4: Программа DBGR_RT

Урок 8: Управление соединением с базой данных (класс TDataBase, объект Session)

Содержание урока 8:

Обзор

В данной статье рассказывается об управлении соединением с базой данных при помощи компоненты TDataBase и объекта TSession, который создается в программе автоматически. Описываются процедуры создания локального псевдонима базы данных и доступа к таблицам Paradox по паролю.

Класс TDataBase

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

• Создания постоянного соединения с базой данных

• Определения собственного диалога при соединении с базой данных (опрос пароля)

• Создания локального псевдонима базы данных

• Изменения параметров при соединении

• Управления транзакциями

TDataBase является невидимым во время выполнения объектом. Он находится на странице “Data Access” Палитры Компонент. Для включения в проект TDataBase нужно “положить” его на главное окно вашей программы.

Создание постоянного соединения с базой данных

Если вы работаете с базой данных, то перед началом работы выполняется процедура соединения с этой базой. В процедуру соединения, кроме прочего, входит опрос имени и пароля пользователя (кроме случая работы с локальными таблицами Paradox и dBase через IDAPI). Если в программе не используется TDataBase, то процедура соединения выполняется при открытии первой таблицы из базы данных. Соединение с базой данных обрывается, когда в программе закрывается последняя таблицы из этой базы (это происходит в том случае, если свойство KeepConnections объекта Session установлено в False, но об этом чуть позже). Теперь, если снова открыть таблицу, то процедура установки соединения повторится и это может быть достаточно неудобно для пользователя. Чтобы соединение не обрывалось даже в том случае, когда нет открытых таблиц данной базы, можно использовать компонент типа TDataBase. В свойстве AliasName укажите псевдоним базы данных, с которой работает программа; в свойстве DatabaseName - любое имя (псевдоним БД), на которое будут ссылаться таблицы вместо старого псевдонима базы. Свойство Connected установите в True - процедура соединения с базой будет выполняться при запуске программы. И, наконец, свойство KeepConnection нужно установить в True (см. рис.1).

Рис.A: Свойства TDataBase в Инспекторе объектов

В нашем примере, после задания свойств DataBase1 нужно у всех таблиц, работающих с IBLOCAL в свойстве DatabaseName поставить Loc_IBLOCAL.

Определение собственного диалога при соединении с базой данных

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

Рис.B: Диалог авторизации пользователя

При желании можно изменить внешний вид диалога или вообще его отменить. Для этого используются свойства и события класса TDataBase - LoginPrompt, Params и OnLogin.

Чтобы отключить опрос имени и пароля установите свойство LoginPrompt в False. При этом в свойстве Params требуется в явном виде (во время дизайна либо во время выполнения) указать имя и пароль пользователя. Например, в программе можно написать (до момента соединения с базой, например в событии для Form1 OnCreate):

DataBase1.LoginPrompt:=False;

DataBase1.Params.Clear;

DataBase1.Params.Add(‘USER NAME=SYSDBA’);

DataBase1.Params.Add(‘PASSWORD=masterkey’);

DataBase1.Connected:=True;

Чтобы использовать свой собственный диалог, в котором можно опрашивать не только имя и пароль пользователя, но и, например, сетевой протокол - создайте обработчик события OnLogin для DataBase1:

procedure TForm1.Database1Login(Database: TDatabase;

LoginParams: TStrings);

begin

Form2.ShowModal;

if Form2.ModalResult = mrOK then

with LoginParams do begin

Values['USER NAME'] := User_Name;

Values['PASSWORD'] := User_Pass;

end;

end;

Здесь Form2 - новое окно-диалог для ввода имени и пароля, User_Name и User_Pass - строки, куда сохраняются введенные имя и пароль.

Создание локального псевдонима базы данных

Обычно, псевдоним базы данных(Alias) определяется в утилите конфигурации BDE и информация о нем сохраняется в файле конфигурации IDAPI.CFG. Однако, в программе можно использовать не только ранее определенный в утилите конфигурации BDE псевдоним базы данных, но и так называемый локальный (т.е. видимый только внутри данной программы) псевдоним. Это иногда бывает нужно, например, для того, чтобы обезопасить программу в случае удаления используемого псевдонима из файла конфигурации BDE.

Для того, чтобы создать локальный псевдоним БД, положите на главное окно проекта компонент DataBase1. Дальнейшие действия можно выполнить с помощью Инспектора Объектов, но удобнее это сделать через редактор компонент. Щелкните дважды мышкой на DataBase1 - появится диалог, показанный на рис.3

Рис.C: Редактор компоненты класса TDataBase

В этом диалоге требуется указать имя базы данных - это будет ее локальный псевдоним, на который ссылаются таблицы (свойство DatabaseName); тип драйвера (в нашем примере это INTRBASE); а также параметры, используемые при соединении с базой данных. Получить список параметров в поле “Parameter Overrides” можно по нажатию кнопки “Defaults”. Набор параметров зависит от типа БД, с которой вы работаете. Этим параметрам нужно присвоить требуемые значения - указать путь к серверу, имя пользователя и т.д. После выхода из редактора компонент имя, указанное в поле “Name” появится в списке имен баз данных для компонент типа TDataSet (TTable, TQuery etc.).

Изменение параметров при соединении

Иногда требуется изменить определенные в утилите конфигурации BDE параметры, используемые при установлении соединения с БД. Это можно сделать во время дизайна с помощью диалога, показанного на рис.3, в поле “Parameter Overrides”. Либо во время выполнения программы (до попытки соединения) прямым присвоением свойству Params объекта DataBase1:

DataBase1.Params.Add(‘LANGDRIVER=ancyrr’);

Управление транзакциями

TDataBase позволяет начать в БД транзакцию (метод StartTransaction), закончить (Commit) или откатить ее (RollBack). Кроме того, можно изменять уровень изоляции транзакций (свойство TransIsoltion).

TransIsolation Oracle Sybase and Informix InterBase

Microsoft SQL

Dirty read Read committed Read committed Dirty Read Read committed

Read committed(Default) Read committed Read committed Read committed Read committed

Repeatable read Repeatable read Read committed Repeatable Read Repeatable Read

“Dirty Read” - внутри вашей текущей транзакции видны все изменения, сделанные другими транзакциями, даже если они еще не завершились по Commit. “Read Committed” - видны только “закоммитченные” изменения, внесенные в базу. “Repeatable Read” - внутри транзакции видны те данные, что были в базе на момент начала транзакции, даже если там на самом деле уже имеются изменения.

Объект Session

Объект Session, имеющий тип TSession создается автоматически в программе, работающей с базами данных (в этом случае Delphi подключает в программу модуль DB). Вам не нужно заботиться о создании и уничтожении данного объекта, но его методы и свойства могут быть полезны в некоторых случаях. В этом компоненте содержится информация обо всех базах данных, с которыми работает программа. Ее можно найти в свойстве DataBases. Со свойством KeepConnections данного объекта мы уже знакомы. Это свойство определяет, нужно ли сохранять соединение с базой, если в программе нет ни одной открытой таблицы из этой базы. NetDir - директория, в которой лежит общий сетевой файл PDOXUSRS.NET, необходимый BDE. PrivateDir - директория для хранения временных файлов.

С помощью методов объекта Session можно получить информацию о настройках BDE, например, список всех псевдонимов, драйверов баз данных или список всех таблиц в базе.

Еще одно важное назначение объекта Session - доступ с его помощью к таблицам Paradox, защищенным паролем. Прежде, чем открыть такую таблицу, требуется выполнить метод AddPassword :

Session.AddPassword(‘my_pass’);

Удалить пароль можно с помощью метода RemovePassword или RemoveAllPasswords.

Указание сетевого протокола при соединении с БД

В случае с InterBase можно в явном виде указать, какой сетевой протокол используется при соединении с базой данных. Эта установка выполняется либо в утилите конфигурации BDE, либо в программе - нужно изменить параметр “SERVER NAME”, который содержит полный путь к файлу с базой данных.

Итак:

Протокол Параметр SERVER NAME

TCP/IP IB_SERVER:PATH\DATABASE.GDB ( nt:c:\ib\base.gdb ) ( unix:/ib/base.gdb )

IPX/SPX IB_SERVER:PATH\DATABASE.GDB ( nw@sys:ib\base.gdb )

NetBEUI \\IB_SERVER\PATH\DATABASE.GDB ( \\nt\c:\ib\base.gdb )

Chapter 23

Урок 9: Управление транзакциями

Содержание урока 9:

Обзор

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

SQL-выражения для управления транзакциями

Для управления транзакциями имеется три выражения:

SET TRANSACTION - Начинает транзакцию и определяет ее поведение.

COMMIT - Сохраняет изменения, внесенные транзакцией, в базе данных и завершает транзакцию.

ROLLBACK - Отменяет изменения, внесенные транзакцией, и завершает транзакцию.

Запуск транзакции

Выполнять транзакции можно, например, из Windows Interactive SQL, из программы, из сохраненной процедуры или триггера. В общем виде, синтаксис команды SQL для запуска транзакции:

SET TRANSACTION [Access mode] [Lock Resolution]

[Isolation Level] [Table Reservation]

Значения, принимаемые по-умолчанию:

выражение

SET TRANSACTION

равносильно выражению

SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL SNAPSHOT

Access Mode - определяет тип доступа к данным. Может принимать два значения:

• READ ONLY - указывает, что транзакция может только читать данные и не может модифицировать их.

• READ WRITE - указывает, что транзакция может читать и модифицировать данные. Это значение принимается по умолчанию.

Пример:

SET TRANSACTION READ WRITE

Isolation Level - определяет порядок взаимодействия данной транзакции с другими в данной базе. Может принимать значения:

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

• SNAPSHOT TABLE STABILITY - предоставляет транзакции исключительный доступ к таблицам, которые она использует. Другие транзакции смогут только читать данные из них.

• READ COMMITTED - позволяет транзакции видеть текущее состояние базы.

Конфликты, связанные с блокировкой записей происходят в двух случаях:

• Транзакция пытается модифицировать запись, которая была изменена или удалена уже после ее старта. Транзакция типа READ COMMITTED может вносить изменения в записи, модифицированные другими транзакциями после их завершения.

• Транзакция пытается модифицировать таблицу, которая заблокирована другой транзакцией типа SNAPSHOT TABLE STABILITY.

Lock Resolution - определяет ход событий при обнаружении конфликта блокировки. Может принимать два значения:

• WAIT - значение по умолчанию. Ожидает разблокировки требуемой записи. После этого пытается продолжить работу.

• NO WAIT - немедленно возвращает ошибку блокировки записи.

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

• PROTECTED READ - запрещает обновление таблицы другими транзакциями, но позволяет им выбирать данные из таблицы.

• PROTECTED WRITE - запрещает обновление таблицы другими транзакциями, читать данные из таблицы могут только транзакции типа SNAPSHOT или READ COMMITTED.

• SHARED READ - самый либеральный уровень. Читать могут все, модифицировать - транзакции READ WRITE.

• SHARED WRITE - транзакции SNAPSHOT или READ COMMITTED READ WRITE могут модифицировать таблицу, остальные - только выбирать данные.

Завершение транзакции

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

• COMMIT - сохраняет внесенные транзакцией изменения в базу данных. Это означает, что транзакция завершена успешно.

• ROLLBACK - откат транзакции. Транзакция завершается и никаких изменений в базу данных не вносится. Данная операция выполняется при возникновении ошибки при выполнении операции (например, при невозможности обновить запись).

Управление транзакциями в Delphi

Прежде всего, транзакции в Delphi бывают явные и неявные.

Явная транзакция - это транзакция, начатая и завершенная с помощью методов объекта DataBase: StartTransaction, Commit, RollBack. После начала явной транзакции, все изменения, вносимые в данные относятся к этой транзакции.

Другого способа начать явную транзакцию, нежели с использованием DataBase, нет. (Точнее говоря, такая возможность есть, но это потребует обращения к функциям API InterBase. Однако, это уже достаточно низкоуровневое программирование.) Следовательно, в рамках одного соединения нельзя начать две транзакции.

Неявная транзакция стартует при модификации данных, если в данный момент нет явной транзакции. Неявная транзакция возникает, например, при выполнении метода Post для объектов Table и Query. То есть, если Вы отредактировали запись, в DBGrid и переходите на другую запись, то это влечет за собой выполнение Post, что, в свою очередь, приводит к началу неявной транзакции, обновлению данных внутри транзакции и ее завершению. Важно отметить, что неявная транзакция, начатая с помощью методов Post, Delete, Insert, Append и т.д. заканчивается автоматически.

Для модификации данных может использоваться и PassThrough SQL - SQL-выражение, выполняемое с помощью метода ExecSQL класса TQuery. Выполнение модификации через PassThrough SQL также приводит к старту неявной транзакции. Дальнейшее поведение транзакции, начатой таким путем, определяется значением параметра SQLPASSTHRU MODE для псевдонима базы данных (или тот-же параметр в св-ве Params объекта DataBase). Этот параметр может принимать три значения:

• SHARED AUTOCOMMIT - слово SHARED указывает на то, что оба вида транзакций(через Passthrough SQL и через методы TTable и TQuery) разделяют одно и то же соединение к базе данных. Слово AUTOCOMMIT указывает на то, что неявная транзакция, начатая через Passthrough SQL, завершается после выполнения действия по модификации данных (автоматически выполняется COMMIT).

• SHARED NOAUTOCOMMIT - отличается от предыдущего тем, что неявная транзакция, начатая через Passthrough SQL, не завершается после выполнения, ее нужно явно завершить, выполнив SQL-выражение “COMMIT”.

• NOT SHARED - транзакции разных типов работают через разные соединения с базой. Данное значение параметра подразумевает также NOAUTOCOMMIT. То есть все неявные PassthroughSQL-транзакции нужно завершать явно - выполняя SQL-выражение “COMMIT” для Passtrough SQL.

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

В первом случае, если нет в данный момент начатой транзакции, то попытка модификация данных методами TTable или TQuery, как и выполнение через Passtrough SQL какой-либо операции приведет к старту неявной транзакции. После выполнения, такая транзакция будет автоматически завершена (если не возникло ошибки по ходу транзакции). Если уже имеется начатая явно (метод StartTransaction объекта DataBase) транзакция, то изменения будут проходить в ее контексте. Все транзакции используют одно и то-же соединение.

Во втором случае все происходит, как в первом. Отличие в том, что неявная PassthroughSQL-транзакция не завершается, пока не будет выполнена команда “COMMIT”.

В третьем случае, при выполнении команды Passthrough SQL, будет установлено еще одно соединение, начата неявная транзакция и выполнены действия по модификации данных. Транзакция не будет завершена, пока не будет выполнена команда “COMMIT”. Наличие транзакции, начатой явно с помощью DataBase никак не отразится на ходе выполнения PassthroughSQL-транзакции. Пока PassthroughSQL-транзакция не завершится, изменения, внесенные ей, не будут видны в объектах Table и Query, работающих через другое соединение. PassthroughSQL-транзакции можно рассматривать в некотором смысле, как транзакции из другого приложения.

Взаимодействие транзакций данной программы с транзакциями из других приложений определяется свойством TransIsolation объекта DataBase. Для InterBase имеет смысл два значения: tiReadCommitted и tiRepeatableRead. Выполнение метода StartTransaction в этих двух случаях равносильно выполнению SQL-выражений, соответственно:

SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL READ COMMITTED

и

SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL SNAPSHOT

Урок 10: Основы языка SQL

Содержание урока 10:

Обзор

SQL (обычно произносимый как "СИКВЭЛ" или “ЭСКЮЭЛЬ”) символизирует собой Структурированный Язык Запросов. Это - язык, который дает Вам возможность создавать и работать в реляционных базах данных, являющихся наборами связанной информации, сохраняемой в таблицах.

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

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

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

Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов) и в данное время также принимается ISO (Международной Организацией по Стандартизации). Однако, большинство коммерческих программ баз данных расширяют SQL без уведомления ANSI, добавляя различные особенности в этот язык, которые, как они считают, будут весьма полезны. Иногда они несколько нарушают стандарт языка, хотя хорошие идеи имеют тенденцию развиваться и вскоре становиться стандартами "рынка" сами по себе в силу полезности своих качеств.

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

Точное описание особенностей языка приводится в документации на СУБД, которую Вы используете. SQL системы InterBase 4.0 соответствует стандарту ANSI-92 и частично стандарту ANSI-III.

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

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

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

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

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

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

Подчеркнем, что это не отдельные языки, а различные команды одного языка. Такое деление проведено только лишь с точки зрения различного функционального назначения этих команд.

Язык манипулирования данными используется, как это следует из его названия, для манипулирования данными в таблицах баз данных. Он состоит из 4 основных команд:

SELECT (выбрать)

INSERT (вставить)

UPDATE (обновить)

DELETE (удалить).

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

CREATE DATABASE (создать базу данных)

CREATE TABLE (создать таблицу)

CREATE VIEW (создать виртуальную таблицу)

CREATE INDEX (создать индекс)

CREATE TRIGGER (создать триггер)

CREATE PROCEDURE (создать сохраненную процедуру)

ALTER DATABASE (модифицировать базу данных)

ALTER TABLE (модифицировать таблицу)

ALTER VIEW (модифицировать виртуальную таблицу)

ALTER INDEX (модифицировать индекс)

ALTER TRIGGER (модифицировать триггер)

ALTER PROCEDURE (модифицировать сохраненную процедуру)

DROP DATABASE (удалить базу данных)

DROP TABLE (удалить таблицу)

DROP VIEW (удалить виртуальную таблицу)

DROP INDEX (удалить индекс)

DROP TRIGGER (удалить триггер)

DROP PROCEDURE (удалить сохраненную процедуру).

Язык управления данными используется для управления правами доступа к данным и выполнением процедур в многопользовательской среде. Более точно его можно назвать “язык управления доступом”. Он состоит из двух основных команд:

GRANT (дать права)

REVOKE (забрать права).

С точки зрения прикладного интерфейса существуют две разновидности команд SQL:

• интерактивный SQL

• встроенный SQL.

Интерактивный SQL используется в специальных утилитах (типа WISQL или DBD), позволяющих в интерактивном режиме вводить запросы с использованием команд SQL, посылать их для выполнения на сервер и получать результаты в предназначенном для этого окне. Встроенный SQL используется в прикладных программах, позволяя им посылать запросы к серверу и обрабатывать полученные результаты, в том числе комбинируя set-ориентированный и record-ориентированный подходы.

Мы не будем приводить точный синтаксис команд SQL, вместо этого мы рассмотрим их на многочисленных примерах, что намного более важно для понимания SQL, чем точный синтаксис, который можно посмотреть в документации на Вашу СУБД.

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

Реляционные операции. Команды языка манипулирования данными

Наиболее важной командой языка манипулирования данными является команда SELECT. За кажущейся простотой ее синтаксиса скрывается огромное богатство возможностей. Нам важно научиться использовать это богатство!

На данном уроке предполагается, если не оговорено противное, что все команды языка SQL вводятся интерактивным способом. В качестве информационной основы для примеров мы будем использовать базу данных “Служащие предприятия” (employee.gdb), входящую в поставку Delphi и находящуюся (по умолчанию) в поддиректории \IBLOCAL\EXAMPLES.

Рис. 1: Структура базы данных EMPLOYEE

На рис.1 приведена схема базы данных EMPLOYEE для Local InterBase, нарисованная с помощью CASE-средства S Designor (см. доп. урок). На схеме показаны таблицы базы данных и взаимосвязи, а также обозначены первичные ключи и их связи с внешними ключами. Многие из примеров, особенно в конце урока, являются весьма сложными. Однако, не следует на этом основании делать вывод, что так сложен сам язык SQL. Дело, скорее, в том, что обычные (стандартные) операции настолько просты в SQL, что примеры таких операций оказываются довольно неинтересными и не иллюстрируют полной мощности этого языка. Но в целях системности мы пройдем по всем возможностям SQL: от самых простых - до чрезвычайно сложных.

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

• выборка (Restriction)

• проекция (Projection)

• соединение (Join)

• объединение (Union).

Операция выборки позволяет получить все строки (записи) либо часть строк одной таблицы.

SELECT * FROM country Получить все строки
таблицы Country

COUNTRY CURRENCY

=============== ==========

USA Dollar

England Pound

Canada CdnDlr

Switzerland SFranc

Japan Yen

Italy Lira

France FFranc

Germany D-Mark

Australia ADollar

Hong Kong HKDollar

Netherlands Guilder

Belgium BFranc

Austria Schilling

Fiji FDollar

В этом примере и далее - для большей наглядности - все зарезервированные слова языка SQL будем писать большими буквами. Красным цветом будем записывать предложения SQL, а светло-синим - результаты выполнения запросов.

SELECT * FROM country

WHERE currency = “Dollar” Получить подмножество строк таблицы Country, удовлетворяющее условию Currency = “Dollar”

Результат последней операции выглядит следующим образом:

COUNTRY CURRENCY

=============== ==========

USA Dollar

Операция проекции позволяет выделить подмножество столбцов таблицы. Например:

SELECT currency FROM country Получить список
денежных единиц

CURRENCY

==========

Dollar

Pound

CdnDlr

SFranc

Yen

Lira

FFranc

D-Mark

ADollar

HKDollar

Guilder

BFranc

Schilling

FDollar

На практике очень часто требуется получить некое подмножество столбцов и строк таблицы, т.е. выполнить комбинацию Restriction и Projection. Для этого достаточно перечислить столбцы таблицы и наложить ограничения на строки.

SELECT currency FROM country

WHERE country = “Japan” Найти денежную
единицу Японии

CURRENCY

==========

Yen

SELECT first_name, last_name

FROM employee

WHERE first_name = "Roger" Получить фамилии
работников,
которых зовут “Roger”

FIRST_NAME LAST_NAME

=============== ====================

Roger De Souza

Roger Reeves

Эти примеры иллюстрируют общую форму команды SELECT в языке SQL (для одной таблицы):

SELECT (выбрать) специфицированные поля

FROM (из) специфицированной таблицы

WHERE (где) некоторое специфицированное условие является истинным

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

SELECT first_name, last_name, proj_name

FROM employee, project

WHERE emp_no = team_leader Получить список
руководителей проектов

FIRST_NAME LAST_NAME PROJ_NAME

============== ================= ====================

Ashok Ramanathan Video Database

Pete Fisher DigiPizza

Chris Papadopoulos AutoMap

Bruce Young MapBrowser port

Mary S. MacDonald Marketing project 3

Операция объединения позволяет объединять результаты отдельных запросов по нескольким таблицам в единую результирующую таблицу. Таким образом, предложение UNION объединяет вывод двух или более SQL-запросов в единый набор строк и столбцов.

SELECT first_name, last_name, job_country

FROM employee

WHERE job_country = "France"

UNION

SELECT contact_first, contact_last, country

FROM customer

WHERE country = "France" Получить список
работников и заказчиков,
проживающих во Франции

FIRST_NAME LAST_NAME JOB_COUNTRY

=============== ================= ===============

Jacques Glon France

Michelle Roche France

Для справки, приведем общую форму команды SELECT, учитывающую возможность соединения нескольких таблиц и объединения результатов:

SELECT [DISTINCT] список_выбираемых_элементов (полей)

FROM список_таблиц (или представлений)

[WHERE предикат]

[GROUP BY поле (или поля) [HAVING предикат]]

[UNION другое_выражение_Select]

[ORDER BY поле (или поля) или номер (номера)];

Рис. 2: Общий формат команды SELECT

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

Гибкость и мощь языка SQL состоит в том, что он позволяет объединить все операции реляционной алгебры в одной конструкции, “вытаскивая” таким образом любую требуемую информацию, что очень часто и происходит на практике.

Команда SELECT

Простейшие конструкции команды SELECT

Итак, начнем с рассмотрения простейших конструкций языка SQL. После такого рассмотрения мы научимся:

• назначать поля, которые должны быть выбраны

• назначать к выборке “все поля”

• управлять “вертикальным” и “горизонтальным” порядком выбираемых полей

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

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

• использовать литералы в списке выбираемых элементов

• ограничивать число возвращаемых строк

• формировать сложные условия поиска, используя реляционные и логические операторы

• устранять одинаковые строки из результата.

Список выбираемых элементов может содержать следующее:

• имена полей

• *

• вычисления

• литералы

• функции

• агрегирующие конструкции

Список полей

SELECT first_name, last_name, phone_no

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

FIRST_NAME LAST_NAME PHONE_NO

============= ==================== ====================

Terri Lee (408) 555-1234

Oliver H. Bender (408) 555-1234

Mary S. MacDonald (415) 555-1234

Michael Yanowski (415) 555-1234

Robert Nelson (408) 555-1234

Kelly Brown (408) 555-1234

Stewart Hall (408) 555-1234

...

Отметим, что PHONE_LIST - это виртуальная таблица (представление), созданная в InterBase и основанная на информации из двух таблиц - EMPLOYEE и DEPARTMENT. Она не показана на рис.1, однако, как мы уже указывали в общей структуре команды SELECT, к ней можно обращаться так же, как и к “настоящей” таблице.

Все поля

SELECT *

FROM phone_list получить список служебных телефонов
всех работников предприятия
со всей необходимой информацией

EMP_NO FIRST_NAME LAST_NAME PHONE_EXT LOCATION PHONE_NO

====== ========== ========= ========= ============= ==============

12 Terri Lee 256 Monterey (408) 555-1234

105 Oliver H. Bender 255 Monterey (408) 555-1234

85 Mary S. MacDonald 477 San Francisco (415) 555-1234

127 Michael Yanowski 492 San Francisco (415) 555-1234

2 Robert Nelson 250 Monterey (408) 555-1234

109 Kelly Brown 202 Monterey (408) 555-1234

14 Stewart Hall 227 Monterey (408) 555-1234

...

Все поля в произвольном порядке

SELECT first_name, last_name, phone_no,
location, phone_ext, emp_no

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

FIRST_NAME LAST_NAME PHONE_NO LOCATION PHONE_EXT EMP_NO

========== ========= ============== ============= ========= ======

Terri Lee (408) 555-1234 Monterey 256 12

Oliver H. Bender (408) 555-1234 Monterey 255 105

Mary S. MacDonald (415) 555-1234 San Francisco 477 85

Michael Yanowski (415) 555-1234 San Francisco 492 127

Robert Nelson (408) 555-1234 Monterey 250 2

Kelly Brown (408) 555-1234 Monterey 202 109

Stewart Hall (408) 555-1234 Monterey 227 14

...

Блобы

Получение информации о BLOb выглядит совершенно аналогично обычным полям. Полученные значения можно отображать с использованием data-aware компонент Delphi, например, TDBMemo или TDBGrid. Однако, в последнем случае придется самому прорисовывать содержимое блоба (например, через OnDrawDataCell). Подробнее об этом см. на уроке, посвященном работе с полями.

SELECT job_requirement
FROM job получить список
должностных требований
к кандидатам на работу

JOB_REQUIREMENT:

No specific requirements.

JOB_REQUIREMENT:

15+ years in finance or 5+ years as a CFO

with a proven track record.

MBA or J.D. degree.

...

Вычисления

SELECT emp_no, salary, salary * 1.15

FROM employee получить список номеров
служащих и их зарплату,
в том числе увеличенную на 15%

EMP_NO SALARY

====== ====================== ======================

2 105900.00 121785

4 97500.00 112125

5 102750.00 118162.5

8 64635.00 74330.25

9 75060.00 86319

11 86292.94 99236.87812499999

12 53793.00 61861.95

14 69482.62 79905.01874999999

...

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

Например, в выражении col1 + col2 * col3 сначала находится произведение значений столбцов col2 и col3, а затем результат этого умножения складывается со значением столбца col1. А в выражении (col1 + col2) * col3 сначала выполняется сложение значений столбцов col1 и col2, и только после этого результат умножается на значение столбца col3.

Литералы

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

SELECT first_name, "получает", salary, "долларов в год"

FROM employee получить список сотрудников
и их зарплату

FIRST_NAME SALARY

=========== ======== ========== ==============

Robert получает 105900.00 долларов в год

Bruce получает 97500.00 долларов в год

Kim получает 102750.00 долларов в год

Leslie получает 64635.00 долларов в год

Phil получает 75060.00 долларов в год

K. J. получает 86292.94 долларов в год

Terri получает 53793.00 долларов в год

...

Конкатенация

Имеется возможность соединять два или более столбца, имеющие строковый тип, друг с другом, а также соединять их с литералами. Для этого используется операция конкатенации (||).

SELECT "сотрудник " || first_name || " " || last_name

FROM employee получить список всех сотрудников

==============================================

сотрудник Robert Nelson

сотрудник Bruce Young

сотрудник Kim Lambert

сотрудник Leslie Johnson

сотрудник Phil Forest

сотрудник K. J. Weston

сотрудник Terri Lee

сотрудник Stewart Hall

...

Использование квалификатора AS

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

SELECT count(*) AS number

FROM employee подсчитать количество служащих

NUMBER

===========

42

SELECT "сотрудник " || first_name || " " || last_name AS employee_list

FROM employee получить список всех сотрудников

EMPLOYEE_LIST

==============================================

сотрудник Robert Nelson

сотрудник Bruce Young

сотрудник Kim Lambert

сотрудник Leslie Johnson

сотрудник Phil Forest

сотрудник K. J. Weston

сотрудник Terri Lee

сотрудник Stewart Hall

...

Работа с датами

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

• “October 27, 1995”

• “27-OCT-1994”

• “10-27-95”

• “10/27/95”

• “27.10.95”

Кроме абсолютных дат, в SQL-выражениях можно также пользоваться относительным заданием дат:

• “yesterday” вчера

• “today” сегодня

• “now” сейчас (включая время)

• “tomorrow” завтра

Дата может неявно конвертироваться в строку (из строки), если:

• строка, представляющая дату, имеет один из вышеперечисленных форматов;

• выражение не содержит неоднозначностей в толковании типов столбцов.

SELECT first_name, last_name, hire_date

FROM employee

WHERE hire_date > '1-1-94' получить список сотрудников,
принятых на работу после
1 января 1994 года

FIRST_NAME LAST_NAME HIRE_DATE

=============== ==================== ===========

Pierre Osborne 3-JAN-1994

John Montgomery 30-MAR-1994

Mark Guckenheimer 2-MAY-1994

Значения дат можно сравнивать друг с другом, сравнивать с относительными датами, вычитать одну дату из другой.

SELECT first_name, last_name, hire_date

FROM employee

WHERE 'today' - hire_date > 365 * 7 + 1
получить список служащих,
проработавших на предприятии
к настоящему времени
более 7 лет

FIRST_NAME LAST_NAME HIRE_DATE

=============== ==================== ===========

Robert Nelson 28-DEC-1988

Bruce Young 28-DEC-1988

Агрегатные функции

К агрегирующим функциям относятся функции вычисления суммы (SUM), максимального (SUM) и минимального (MIN) значений столбцов, арифметического среднего (AVG), а также количества строк, удовлетворяющих заданному условию (COUNT).

SELECT count(*), sum (budget), avg (budget),

min (budget), max (budget)

FROM department

WHERE head_dept = 100 вычислить: количество отделов,
являющихся подразделениями
отдела 100 (Маркетинг и продажи),
их суммарный, средний, мини- мальный и максимальный бюджеты

COUNT SUM AVG MIN MAX

====== =========== ========== ========== ===========

5 3800000.00 760000.00 500000.00 1500000.00

Предложение FROM команды SELECT

В предложении FROM перечисляются все объекты (один или несколько), из которых производится выборка данных (рис.2). Каждая таблица или представление, о которых упоминается в запросе, должны быть перечислены в предложении FROM.

Ограничения на число выводимых строк

Число возвращаемых в результате запроса строк может быть ограничено путем использования предложения WHERE, содержащего условия отбора (предикат, рис.2). Условие отбора для отдельных строк может принимать значения true, false или unnown. При этом запрос возвращает в качестве результата только те строки (записи), для которых предикат имеет значение true.

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

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

= равно

<> не равно

!= не равно

> больше

<>

>= больше или равно

<= меньше="" или="">

• BETWEEN

• IN

• LIKE

• CONTAINING

• IS NULL

• EXIST

• ANY

• ALL

Операции сравнения

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

Что же может быть элементом сравнения? Элементом сравнения может выступать:

• значение поля

• литерал

• арифметическое выражение

• агрегирующая функция

• другая встроенная функция

• значение (значения), возвращаемые подзапросом.

При сравнении литералов конечные пробелы игнорируются. Так, предложение WHERE first_name = ‘Петр ‘ будет иметь тот же результат, что и предложение WHERE first_name = ‘Петр’.

SELECT first_name, last_name, dept_no

FROM employee

WHERE job_code = "Admin" получить список сотрудников
(и номера их отделов),
занимающих должность
администраторов

FIRST_NAME LAST_NAME DEPT_NO

=============== ==================== =======

Terri Lee 000

Ann Bennet 120

Sue Anne O'Brien 670

Kelly Brown 600

SELECT first_name, last_name, dept_no,

job_country

FROM employee

WHERE job_country <> "USA" получить список сотрудников
(а также номера их отделов
и страну),
работающих вне США

FIRST_NAME LAST_NAME DEPT_NO JOB_COUNTRY

=============== ================ ======= ==============

Ann Bennet 120 England

Roger Reeves 120 England

Willie Stansbury 120 England

Claudia Sutherland 140 Canada

Yuki Ichida 115 Japan

Takashi Yamamoto 115 Japan

Roberto Ferrari 125 Italy

Jacques Glon 123 France

Pierre Osborne 121 Switzerland

BETWEEN

Предикат BETWEEN задает диапазон значений, для которого выражение принимает значение true. Разрешено также использовать конструкцию NOT BETWEEN.

SELECT first_name, last_name, salary

FROM employee

WHERE salary BETWEEN 20000 AND 30000
получить список сотрудников,
годовая зарплата которых
больше 20000 и меньше 30000

FIRST_NAME LAST_NAME SALARY

=============== ========== ===============

Ann Bennet 22935.00

Kelly Brown 27000.00

Тот же запрос с использованием операторов сравнения будет выглядеть следующим образом:

SELECT first_name, last_name, salary

FROM employee

WHERE salary >= 20000

AND salary <= 30000="" получить="" список="">
годовая зарплата которых
больше 20000 и меньше 30000

FIRST_NAME LAST_NAME SALARY

=============== ========== ===============

Ann Bennet 22935.00

Kelly Brown 27000.00

Запрос с предикатом BETWEEN может иметь следующий вид:

SELECT first_name, last_name, salary

FROM employee

WHERE last_name BETWEEN "Nelson" AND "Osborne"
получить список сотрудников,
фамилии которых начинаются
с “Nelson”
и заканчиваются “Osborne”

FIRST_NAME LAST_NAME SALARY

=============== =============== ================

Robert Nelson 105900.00

Carol Nordstrom 42742.50

Sue Anne O'Brien 31275.00

Pierre Osborne 110000.00

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

SELECT first_name, last_name, salary

FROM employee

WHERE last_name BETWEEN "Nel" AND "Osb"
получить список сотрудников,
фамилии которых находятся
между “Nel” и “Osb”

FIRST_NAME LAST_NAME SALARY

=============== =============== ================

Robert Nelson 105900.00

Carol Nordstrom 42742.50

Sue Anne O'Brien 31275.00

В данном примере значений “Nel” и “Osb” в базе данных нет. Однако, все сотрудники, входящие в диапазон, в нижней части которого начало фамилий совпадает с “Nel” (т.е. выполняется условие “больше или равно”), а в верхней части фамилия не более “Osb” (т.е. выполняется условие “меньше или равно” - а именно “O”, “Os”, “Osb”), попадут в выборку. Отметим, что при выборке с использованием предиката BETWEEN поле, на которое накладывается диапазон, считается упорядоченным по возрастанию.

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

SELECT first_name, last_name, hire_date

FROM employee

WHERE hire_date NOT BETWEEN "1-JAN-1989" AND "31-DEC-1993" получить список самых “старых”
и самых “молодых” (по времени
поступления на работу)
сотрудников

FIRST_NAME LAST_NAME HIRE_DATE

=============== ================ ===========

Robert Nelson 28-DEC-1988

Bruce Young 28-DEC-1988

Pierre Osborne 3-JAN-1994

John Montgomery 30-MAR-1994

Mark Guckenheimer 2-MAY-1994

IN

Предикат IN проверяет, входит ли заданное значение, предшествующее ключевому слову “IN” (например, значение столбца или функция от него) в указанный в скобках список. Если заданное проверяемое значение равно какому-либо элементу в списке, то предикат принимает значение true. Разрешено также использовать конструкцию NOT IN.

SELECT first_name, last_name, job_code

FROM employee

WHERE job_code IN ("VP", "Admin", "Finan")
получить список сотрудников,
занимающих должности
“вице-президент”, “администратор”,
“финансовый директор”

FIRST_NAME LAST_NAME JOB_CODE

=============== ================ ========

Robert Nelson VP

Terri Lee Admin

Stewart Hall Finan

Ann Bennet Admin

Sue Anne O'Brien Admin

Mary S. MacDonald VP

Kelly Brown Admin

А вот пример запроса, использующего предикат NOT IN:

SELECT first_name, last_name, job_country

FROM employee

WHERE job_country NOT IN

("USA", "Japan", "England")
получить список сотрудников,
работающих не в США, не в Японии
и не в Великобритании

FIRST_NAME LAST_NAME JOB_COUNTRY

=============== ================ ===============

Claudia Sutherland Canada

Roberto Ferrari Italy

Jacques Glon France

Pierre Osborne Switzerland

LIKE

Предикат LIKE используется только с символьными данными. Он проверяет, соответствует ли данное символьное значение строке с указанной маской. В качестве маски используются все разрешенные символы (с учетом верхнего и нижнего регистров), а также специальные символы:

% - замещает любое количество символов (в том числе и 0),

_ - замещает только один символ.

Разрешено также использовать конструкцию NOT LIKE.

SELECT first_name, last_name

FROM employee

WHERE last_name LIKE "F%" получить список сотрудников,
фамилии которых начинаются с буквы “F”

FIRST_NAME LAST_NAME

=============== ====================

Phil Forest

Pete Fisher

Roberto Ferrari

SELECT first_name, last_name

FROM employee

WHERE first_name LIKE "%er" получить список сотрудников,
имена которых заканчиваются буквами “er”

FIRST_NAME LAST_NAME

=============== ====================

Roger De Souza

Roger Reeves

Walter Steadman

А такой запрос позволяет решить проблему произношения (и написания) имени:

SELECT first_name, last_name

FROM employee

WHERE first_name LIKE "Jacq_es"
найти сотрудника(ов),
в имени которого
неизвестно произношение
буквы перед окончанием “es”

FIRST_NAME LAST_NAME

=============== ====================

Jacques Glon

Что делать, если требуется найти строку, которая содержит указанные выше специальные символы (“%”, “_”) в качестве информационных символов? Есть выход! Для этого с помощью ключевого слова ESCAPE нужно определить так называемый escape символ, который, будучи поставленным перед символом “%” или “_”, укажет, что этот символ является информационным. Escape символ не может быть символом “\” (обратная косая черта) и, вообще говоря, должен представлять собой символ, никогда не появляющийся в упоминаемом столбце как информационный символ. Часто для этих целей используются символы “@” и “~”.

SELECT first_name, last_name

FROM employee

WHERE first_name LIKE "%@_%" ESCAPE "@"
получить список сотрудников,
в имени которых содержится “_”
(знак подчеркивания)

CONTAINING

Предикат CONTAINING аналогичен предикату LIKE, за исключением того, что он не чувствителен к регистру букв. Разрешено также использовать конструкцию NOT CONTAINING.

SELECT first_name, last_name

FROM employee

WHERE last_name CONTAINING "ne"
получить список сотрудников,
фамилии которых содержат буквы
“ne”, “Ne”, “NE”, “nE”

FIRST_NAME LAST_NAME

=============== ====================

Robert Nelson

Ann Bennet

Pierre Osborne

IS NULL

В SQL-запросах NULL означает, что значение столбца неизвестно. Поисковые условия, в которых значение столбца сравнивается с NULL, всегда принимают значение unknown (и, соответственно, приводят к ошибке), в противоположность true или false, т.е.

WHERE dept_no = NULL

или даже

WHERE NULL = NULL.

Предикат IS NULL принимает значение true только тогда, когда выражение слева от ключевых слов “IS NULL” имеет значение null (пусто, не определено). Разрешено также использовать конструкцию IS NOT NULL, которая означает “не пусто”, “имеет какое-либо значение”.

SELECT department, mngr_no

FROM department

WHERE mngr_no IS NULL получить список отделов,
в которых еще не назначены
начальники

DEPARTMENT MNGR_NO

========================= =======

Marketing

Software Products Div.

Software Development

Field Office: Singapore

Предикаты EXIST, ANY, ALL, SOME, SINGULAR мы рассмотрим в разделе, рассказывающем о подзапросах.

Логические операторы

К логическим операторам относятся известные операторы AND, OR, NOT, позволяющие выполнять различные логические действия: логическое умножение (AND, “пересечение условий”), логическое сложение (OR, “объединение условий”), логическое отрицание (NOT, “отрицание условий”). В наших примерах мы уже применяли оператор AND. Использование этих операторов позволяет гибко “настроить” условия отбора записей.

Оператор AND означает, что общий предикат будет истинным только тогда, когда условия, связанные по “AND”, будут истинны.

Оператор OR означает, что общий предикат будет истинным, когда хотя бы одно из условий, связанных по “OR”, будет истинным.

Оператор NOT означает, что общий предикат будет истинным, когда условие, перед которым стоит этот оператор, будет ложным.

В одном предикате логические операторы выполняются в следующем порядке: сначала выполняется оператор NOT, затем - AND и только после этого - оператор OR. Для изменения порядка выполнения операторов разрешается использовать скобки.

SELECT first_name, last_name, dept_no,

job_code, salary

FROM employee

WHERE dept_no = 622

OR job_code = "Eng"

AND salary <=>

ORDER BY last_name получить список служащих,
занятых в отделе 622
или
на должности “инженер” с зарплатой
не выше 40000

FIRST_NAME LAST_NAME DEPT_NO JOB_CODE SALARY

============ ============= ======= ======== ===========

Jennifer M. Burbank 622 Eng 53167.50

Phil Forest 622 Mngr 75060.00

T.J. Green 621 Eng 36000.00

Mark Guckenheimer 622 Eng 32000.00

John Montgomery 672 Eng 35000.00

Bill Parker 623 Eng 35000.00

Willie Stansbury 120 Eng 39224.06

SELECT first_name, last_name, dept_no,

job_code, salary

FROM employee

WHERE (dept_no = 622

OR job_code = "Eng")

AND salary <=>

ORDER BY last_name получить список служащих,
занятых в отделе 622
или на должности “инженер”,
зарплата которых не выше 40000

FIRST_NAME LAST_NAME DEPT_NO JOB_CODE SALARY

============ ============= ======= ======== ===========

T.J. Green 621 Eng 36000.00

Mark Guckenheimer 622 Eng 32000.00

John Montgomery 672 Eng 35000.00

Bill Parker 623 Eng 35000.00

Willie Stansbury 120 Eng 39224.06

Преобразование типов (CAST)

В SQL имеется возможность преобразовать значение столбца или функции к другому типу для более гибкого использования операций сравнения. Для этого используется функция CAST.

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

Из типа данных В тип данных

---------------------------------------

NUMERIC CHAR, VARCHAR, DATE

CHAR, VARCHAR NUMERIC, DATE

DATE CHAR, VARCHAR, DATE

SELECT first_name, last_name, dept_no

FROM employee

WHERE CAST(dept_no AS char(20))

CONTAINING "00" получить список сотрудников,
занятых в отделах,
номера которых содержат “00”

FIRST_NAME LAST_NAME DEPT_NO

=============== ==================== =======

Robert Nelson 600

Terri Lee 000

Stewart Hall 900

Walter Steadman 900

Mary S. MacDonald 100

Oliver H. Bender 000

Kelly Brown 600

Michael Yanowski 100

Изменение порядка выводимых строк (ORDER BY)

Порядок выводимых строк может быть изменен с помощью опционального (дополнительного) предложения ORDER BY в конце SQL-запроса. Это предложение имеет вид:

ORDER BY <порядок строк=""> [ASC | DESC]

Порядок строк может задаваться одним из двух способов:

• именами столбцов

• номерами столбцов.

Способ упорядочивания определяется дополнительными зарезервированными словами ASC и DESC. Способом по умолчанию - если ничего не указано - является упорядочивание “по возрастанию” (ASC). Если же указано слово “DESC”, то упорядочивание будет производиться “по убыванию”.

Подчеркнем еще раз, что предложение ORDER BY должно указываться в самом конце запроса.

Упорядочивание с использованием имен столбцов

SELECT first_name, last_name, dept_no,

job_code, salary

FROM employee

ORDER BY last_name получить список сотрудников,
упорядоченный по фамилиям
в алфавитном порядке

FIRST_NAME LAST_NAME DEPT_NO JOB_CODE SALARY

============ ============= ======= ======== ===========

Janet Baldwin 110 Sales 61637.81

Oliver H. Bender 000 CEO 212850.00

Ann Bennet 120 Admin 22935.00

Dana Bishop 621 Eng 62550.00

Kelly Brown 600 Admin 27000.00

Jennifer M. Burbank 622 Eng 53167.50

Kevin Cook 670 Dir 111262.50

Roger De Souza 623 Eng 69482.62

Roberto Ferrari 125 SRep 99000000.00

...

SELECT first_name, last_name, dept_no,

job_code, salary

FROM employee

ORDER BY last_name DESC получить список сотрудников,
упорядоченный по фамилиям
в порядке, обратном алфавитному

FIRST_NAME LAST_NAME DEPT_NO JOB_CODE SALARY

============ ============= ======= ======== ===========

Katherine Young 623 Mngr 67241.25

Bruce Young 621 Eng 97500.00

Michael Yanowski 100 SRep 44000.00

Takashi Yamamoto 115 SRep 7480000.00

Randy Williams 672 Mngr 56295.00

K. J. Weston 130 SRep 86292.94

Claudia Sutherland 140 SRep 100914.00

Walter Steadman 900 CFO 116100.00

Willie Stansbury 120 Eng 39224.06

Roger Reeves 120 Sales 33620.62

...

Столбец, определяющий порядок вывода строк, не обязательно дожен присутствовать в списке выбираемых элементов (столбцов):

SELECT first_name, last_name, dept_no,

job_code

FROM employee

ORDER BY salary получить список сотрудников,
упорядоченный по их зарплате

FIRST_NAME LAST_NAME DEPT_NO JOB_CODE

=============== =============== ======= ========

Ann Bennet 120 Admin

Kelly Brown 600 Admin

Sue Anne O'Brien 670 Admin

Mark Guckenheimer 622 Eng

Roger Reeves 120 Sales

Bill Parker 623 Eng

Упорядочивание с использованием номеров столбцов

SELECT first_name, last_name, dept_no,

job_code, salary * 1.1

FROM employee

ORDER BY 5 получить список сотрудников,
упорядоченный по их зарплате
с 10% надбавкой

FIRST_NAME LAST_NAME DEPT_NO JOB_CODE

============ ============= ======= ======== ===========

Ann Bennet 120 Admin 25228.5

Kelly Brown 600 Admin 29700

Sue Anne O'Brien 670 Admin 34402.5

Mark Guckenheimer 622 Eng 35200

Roger Reeves 120 Sales 36982.6875

Bill Parker 623 Eng 38500

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

SELECT first_name, last_name, dept_no,

job_code, salary * 1.1

FROM employee

ORDER BY dept_no, 5 DESC, last_name
получить список сотрудников,
упорядоченный сначала по
номерам отделов,
в отделах - по убыванию их
зарплаты (с 10%),
а в пределах одной зарплаты - по фамилиям

FIRST_NAME LAST_NAME DEPT_NO JOB_CODE

=========== ========== ======= ======== ===============

Oliver H. Bender 000 CEO 234135

Terri Lee 000 Admin 59172.3

Mary S. MacDonald 100 VP 122388.75

Michael Yanowski 100 SRep 48400.000000001

Luke Leung 110 SRep 75685.5

Janet Baldwin 110 Sales 67801.59375

Takashi Yamamoto 115 SRep 8228000.0000001

Yuki Ichida 115 Eng 6600000.0000001

Устранение дублирования (модификатор DISTINCT)

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

Иногда (в зависимости от задачи) бывает необходимо устранить все повторы строк из результирующего набора. Этой цели служит модификатор DISTINCT. Данный модификатор может быть указан только один раз в списке выбираемых элементов и действует на весь список.

SELECT job_code

FROM employee получить список должностей сотрудников

JOB_CODE

========

VP

Eng

Eng

Mktg

Mngr

SRep

Admin

Finan

Mngr

Mngr

Eng

...

Данный пример некорректно решает задачу “получения” списка должностей сотрудников предприятия, так как в нем имеются многочисленные повторы, затрудняющие восприятие информации. Тот же запрос, включающий модификатор DISTINCT, устраняющий дублирование, дает верный результат.

SELECT DISTINCT job_code

FROM employee получить список должностей сотрудников

JOB_CODE

========

Admin

CEO

CFO

Dir

Doc

Eng

Finan

Mktg

Mngr

PRel

SRep

Sales

VP

Два следующих примера показывают, что модификатор DISTINCT действует на всю строку сразу.

SELECT first_name, last_name

FROM employee

WHERE first_name = "Roger" получить список служащих,
имена которых - Roger

FIRST_NAME LAST_NAME

=============== ====================

Roger De Souza

Roger Reeves

SELECT DISTINCT first_name, last_name

FROM employee

WHERE first_name = "Roger" получить список служащих,
имена которых - Roger

FIRST_NAME LAST_NAME

=============== ====================

Roger De Souza

Roger Reeves

Соединение (JOIN)

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

После изучения этого раздела мы будем способны:

• соединять данные из нескольких таблиц в единую результирующую таблицу;

• задавать имена столбцов двумя способами;

• записывать внешние соединения;

• создавать соединения таблицы с собой.

Операции соединения подразделяются на два вида - внутренние и внешние. Оба вида соединений задаются в предложении WHERE запроса SELECT с помощью специального условия соединения. Внешние соединения (о которых мы поговорим позднее) поддерживаются стандартом ANSI-92 и содержат зарезервированное слово “JOIN”, в то время как внутренние соединения (или просто соединения) могут задаваться как без использования такого слова (в стандарте ANSI-89), так и с использованием слова “JOIN” (в стандарте ANSI-92).

Связывание производится, как правило, по первичному ключу одной таблицы и внешнему ключу другой таблицы - для каждой пары таблиц. При этом очень важно учитывать все поля внешнего ключа, иначе результат будет искажен. Соединяемые поля могут (но не обязаны!) присутствовать в списке выбираемых элементов. Предложение WHERE может содержать множественные условия соединений. Условие соединения может также комбинироваться с другими предикатами в предложении WHERE.

Внутренние соединения

Внутреннее соединение возвращает только те строки, для которых условие соединения принимает значение true.

SELECT first_name, last_name, department

FROM employee, department

WHERE job_code = "VP" получить список сотрудников,
состоящих в должности “вице-
президент”, а также названия
их отделов

FIRST_NAME LAST_NAME DEPARTMENT

=============== ================ ======================

Robert Nelson Corporate Headquarters

Mary S. MacDonald Corporate Headquarters

Robert Nelson Sales and Marketing

Mary S. MacDonald Sales and Marketing

Robert Nelson Engineering

Mary S. MacDonald Engineering

Robert Nelson Finance

Mary S. MacDonald Finance

...

Этот запрос (“без соединения”) возвращает неверный результат, так как имеющиеся между таблицами связи не задействованы. Отсюда и появляется дублирование информации в результирующей таблице. Правильный результат дает запрос с использованием операции соединения:

SELECT first_name, last_name, department

FROM employee, department

WHERE job_code = "VP"

AND employee.dept_no = department.dept_no

имена таблиц

получить список сотрудников,
состоящих в должности “вице-
президент”, а также названия
их отделов

FIRST_NAME LAST_NAME DEPARTMENT

=============== ================ ======================

Robert Nelson Engineering

Mary S. MacDonald Sales and Marketing

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

Замечание 1: в одном запросе нельзя смешивать использование написания имен таблиц и их алиасов.

Замечание 2: алиасы таблиц могут совпадать с их именами.

SELECT first_name, last_name, department

FROM employee e, department d

WHERE job_code = "VP"

AND e.dept_no = d.dept_no

алиасы таблиц

получить список сотрудников,
состоящих в должности “вице-
президент”, а также названия
их отделов

FIRST_NAME LAST_NAME DEPARTMENT

=============== ================ ======================

Robert Nelson Engineering

Mary S. MacDonald Sales and Marketing

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

SELECT first_name, last_name, job_title,

department

FROM employee e, department d, job j

WHERE d.mngr_no = e.emp_no

AND e.job_code = j.job_code

AND e.job_grade = j.job_grade

AND e.job_country = j.job_country
получить список сотрудников
с названиями их должностей
и названиями отделов

FIRST_NAME LAST_NAME JOB_TITLE DEPARTMENT

========== ============ ======================= ======================

Robert Nelson Vice President Engineering

Phil Forest Manager Quality Assurance

K. J. Weston Sales Representative Field Office: East Coast

Katherine Young Manager Customer Support

Chris Papadopoulos Manager Research and Development

Janet Baldwin Sales Co-ordinator Pacific Rim Headquarters

Roger Reeves Sales Co-ordinator European Headquarters

Walter Steadman Chief Financial Officer Finance

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

Мы рассмотрели внутренние соединения с использованием стандарта ANSI-89. Теперь опишем новый (ANSI-92) стандарт:

• условия соединения записываются в предложении FROM, в котором слева и справа от зарезервированного слова “JOIN” указываются соединяемые таблицы;

• условия поиска, основанные на правой таблице, помещаются в предложение ON;

• условия поиска, основанные на левой таблице, помещаются в предложение WHERE.

SELECT first_name, last_name, department

FROM employee e JOIN department d

ON e.dept_no = d.dept_no

AND department = "Customer Support"

WHERE last_name starting with "P"
получить список служащих
(а заодно и название отдела),
являющихся сотрудниками отдела
“Customer Support”, фамилии кото-
рых начинаются с буквы “P”

FIRST_NAME LAST_NAME DEPARTMENT

============= =============== ===================

Leslie Phong Customer Support

Bill Parker Customer Support

Самосоединения

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

SELECT one.last_name, two.last_name,

one.hire_date

FROM employee one, employee two

WHERE one.hire_date = two.hire_date

AND one.emp_no <>
получить пары фамилий сотрудников,
которые приняты на работу в один
и тот же день

LAST_NAME LAST_NAME HIRE_DATE

==================== ==================== ===========

Nelson Young 28-DEC-1988

Reeves Stansbury 25-APR-1991

Bishop MacDonald 1-JUN-1992

Brown Ichida 4-FEB-1993

SELECT d1.department, d2.department, d1.budget

FROM department d1, department d2

WHERE d1.budget = d2.budget

AND d1.dept_no <>
получить список пар отделов с
одинаковыми годовыми бюджетами

DEPARTMENT DEPARTMENT BUDGET

======================== ========================= =========

Software Development Finance 400000.00

Field Office: East Coast Field Office: Canada 500000.00

Field Office: Japan Field Office: East Coast 500000.00

Field Office: Japan Field Office: Canada 500000.00

Field Office: Japan Field Office: Switzerland 500000.00

Field Office: Singapore Quality Assurance 300000.00

Field Office: Switzerland Field Office: East Coast 500000.00

Внешние соединения

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

Вспомним, запрос вида

SELECT first_name, last_name, department

FROM employee e, department d

WHERE e.dept_no = d.dept_no

возвращает только те строки, для которых условие соединения (e.dept_no = d.dept_no) принимает значение true.

Внешнее соединение возвращает все строки из одной таблицы и только те строки из другой таблицы, для которых условие соединения принимает значение true. Строки второй таблицы, не удовлетворяющие условию соединения (т.е. имеющие значение false), получают значение null в результирующем наборе.

Существует два вида внешнего соединения: LEFT JOIN и RIGHT JOIN.

В левом соединении (LEFT JOIN) запрос возвращает все строки из левой таблицы (т.е. таблицы, стоящей слева от зарезервированного словосочетания “LEFT JOIN”) и только те из правой таблицы, которые удовлетворяют условию соединения. Если же в правой таблице не найдется строк, удовлетворяющих заданному условию, то в результате они замещаются значениями null.

Для правого соединения - все наоборот.

SELECT first_name, last_name, department

FROM employee e LEFT JOIN department d

ON e.dept_no = d.dept_no
получить список сотрудников
и название их отделов,
включая сотрудников, еще
не назначенных ни в какой отдел

FIRST_NAME LAST_NAME DEPARTMENT

=============== ============== =====================

Robert Nelson Engineering

Bruce Young Software Development

Kim Lambert Field Office: East Coast

Leslie Johnson Marketing

Phil Forest Quality Assurance

...

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

А вот пример правого соединения:

SELECT first_name, last_name, department

FROM employee e RIGHT JOIN department d

ON e.dept_no = d.dept_no
получить список сотрудников
и название их отделов,
включая отделы, в которые еще
не назначены сотрудники

FIRST_NAME LAST_NAME DEPARTMENT

=============== ============= =========================

Terri Lee Corporate Headquarters

Oliver H. Bender Corporate Headquarters

Mary S. MacDonald Sales and Marketing

Michael Yanowski Sales and Marketing

Robert Nelson Engineering

Kelly Brown Engineering

Stewart Hall Finance

Walter Steadman Finance

Leslie Johnson Marketing

Carol Nordstrom Marketing

Software Products Div.

Bruce Young Software Development

...

В результирующий набор входит и отдел “Software Products Div.” (а также отдел “Field Office: Singapore”, не представленный здесь), в котором еще нет ни одного сотрудника.

Урок 11: Генерация отчетов

Содержание

1.Компоненты для построения отчетов

На странице палитры компонентов QReport расположено более двух десятков компонентов, применяемых для построения отчетов.

Центральным компонентом является TQuickRep, определяющий поведение отчета в целом. С помощью других компонентов создаются составные части отчета.

TQRBand – заготовка для расположения данных, заголовков, титула отчета и др. Отчет, в основном, строится из компонентов TQRBand, которые реализуют:

• область заголовка отчета;

• область заголовка страницы;

• область заголовка группы;

• область названий столбцов отчета;

• область детальных данных, предназначенную для отображения данных самого нижнего уровня детализации;

• область подвала группы;

• область подвала страницы;

• область подвала отчета.

TQRStringsBand – имеет то же назначение, что и TQRBand. Отличается встроенным списком строк Items, содержимое которого становится видным в режиме печати и предварительного просмотра, если на компонент TQRStringsBand положен компонент TQRExpr. Для каждой строки в Items выводится своя полоса TQRStringsBand.

TQRSubDetail – дочерняя полоса. Привязывается к родительской полосе и служит для ее расширения. Любая полоса может стать родительской с помощью установки значения True в ее свойство HasChild.

TQRGroup – применяется для группировок данных в отчетах.

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

TQRDBText – служит для вывода в отчет содержимого текстового поля набора данных.

TQRExpr – применяется для вывода значений, являющихся результатом вычислений выражений. Алгоритм вычисления выражений строится при помощи редактора формул данного компонента.

TQRSysData – служит для вывода в отчете системной величины: даты, времени, номера страницы и т.п.

TQRMemo – вставляет в отчет многостраничный текст.

TQRExprMemo – используется для создания многострочных вычисляемых полей.

TQRRichText – вставляет в отчет многострочный текст в формате RTF.

TQRDBRichText – служит для вывода в отчете полей НД, содержащих многострочный текст в формате RTF.

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

TQRImage – служит для вывода в отчете графической информации, источником которой является поле набора данных.

TQRPreview – базовый компонент для создания нестандартных окон предварительного просмотра. Стандартное окно реализуется с помощью метода Preview компонента TQuickRep.

TQRXXXFilter – фильтрующие компоненты для преобразования отчета в текст, страницу HTML и т.п. при печати отчета.

TQRChart – служит для встраивания в отчет графиков.

2.Компонент TQuickRep

При размещении этого компонента на форме в ней появляется сетка отчета (рис.1). В дальнейшем в этой сетке располагаются составные части отчета, например, полосы TQRBand (рис.2).

Рис. 1. Пустая сетка отчета. Образуется после размещения на форме компонента TQuickRep.

Рис. 2. Сетка отчета с размещенными в ней компонентами отчета.

Перечислим важнейшие свойства, методы и события компонента TQuickRep.

Свойства

Свойство	Назначение
property Bands: TQuickRepBands;	Объект Bands содержит логические свойства, которые после установки в них значений True включают в отчет: HasColumnHeader – заголовки столбцов; HasDetail – детальную информацию; HasPageFooter – подвал страницы; HasPageHeader – заголовок страницы; HasSummary – подвал отчета; HasTitle – заголовок отчета.
property Dataset: TDataSet;	Указывает набор данных на основе которого создается отчет. Если нужно вывести связанную информацию из нескольких таблиц БД, ее объединяют в одном НД при помощи компонента TQuery. Информацию из нескольких связанных НД можно включать в отчет, если эти НД связаны в приложении отношением главный-подчиненный. В этом случае в качестве НД отчета указывается главный набор, а ссылка на соответствующие подчиненные наборы осуществляется в компонентах TQRSubDetail. Если в отчет нужно включить информацию из несвязанных НД, применяется композитный отчет, то есть отчет, составленный из группы других отчетов.
property Frame: TQRFrame;	Определяет параметры рамки отчета: Color – цвет линий; DrawBottom – наличие линии снизу; DrawLeft – наличие линии слева; DrawRight – наличие линии справа; DrawTop – наличие линии сверху; Style – стиль линии (сплошная, пунктирная и т.п.); Width – толщина линии в пикселях.
property Options: TQuickReportOptions;	Содержит множество из следующих логических значений: HasFirstHeader – разрешает печатать заголовок первой страницы; HasLastFooter – разрешает печатать подвал последней страницы; Compression – разрешает сжимать отчет при выводе его в метафайл.
property Page: TQRPage;	Определяет параметры страницы отчета. Все подсвойства этого сложного свойства доступны в окне Report Setting (см. ниже группы Page size и Margin окна редактора свойств).
property PrintIfEmpty: boolean;	Разрешает/запрещает печатать отчет в том случае если он не содержит данных.
property ReportTitle: String;	Имя отчета (не его заголовок !). Используется для идентификации отчета в задании на сетевую печать, возвращается компонентом QRSysData при Data = ReportTitle и может использоваться для набора одного из нескольких доступных отчетов.
property ShowProgress: boolean;	Разрешает/запрещает показывать индикатор процесса печати отчета.
property SnapToGrid: boolean;	Если содержит True, размещаемые в отчете компоненты привязываются к сетке отчета.
type TQRUnits = (Inches, MM, Pixels, Native, Characters); property Units: TQRUnits;	Определяет единицы измерения расстояний в отчете: Inches – дюймы; MM – миллиметры; Pixels – пиксели; Native – внутренние единицы TQuickRep (равны 0,1 мм); Characters – символы текста.
property Zoom: Integer;	Определяет масштаб отображения отчета (в процентах от его размеров на листе бумаги) на этапе разработки. Может иметь значение в диапазоне 1..300. Значение свойства не учитывается при печати отчета или в режиме его предварительного показа.

Многие свойства отчета можно установить на этапе конструирования с помощью редактора свойств – вызовите локальное меню компонента TQuickRep и выберите опцию Report Settings.

Рис. 3. Окно установки параметров отчета.

Группа Paper size задает характеристики страницы: ее формат (A4 210 x 270 mm), ширину (Width), длину (Length) и направление печати – вдоль короткой стороны листа (Portait) или вдоль длинной (Landscape).

Группа элементов Margin указывает поля отчета: сверху (Top), снизу (Bottom), слева (Left), справа (Right), а также количество колонок (Number of columns) и расстояние между ними (Column Space).

С помощью элементов группы Other можно задать шрифт (Font), его высоту (Size) и используемые единицы измерения длины (Units).

Группа Page frame определяет свойства рамки: наличие линии сверху (Top), снизу (Bottom), слева (Left), справа (Right), цвет линий (Color) и их толщину (Width).

Группа Bands определяет наличие полос заголовков и подвалов (Page header – заголовок страницы; Title – заголовок отчета; Column header – заголовок колонок; Detail band – полоса для детальной информации; Page footer – подвал страницы; Summary – подвал отчета), а также высоту соответствующей полосы (строка Length справа от переключателя выбора). После выбора типа и высоты полосы она появляется в отчете, если окно закрыто кнопкой OK или была нажата кнопка Applay. Элементы Print first page header и Print last page footer управляют соответственно печатью заголовка на первой странице и подвала на его последней странице.

Методы

Метод	Назначение
procedure NewColumn;	Реализует вывод информации в следующей колонке отчета, а если определена единственная колонка, – в его следующей странице.
procedure NewPage;	Реализует вывод информации в следующей странице отчета.
procedure Prepare;	Готовит отчет для вывода в файл (см. ниже примечание 1).
procedure Preview;	Выводит стандартное окно предварительного просмотра (см. ниже примечание 2).
procedure Print;	Печатает отчет на принтере.
procedure PrintBackGround;	Инициирует печать отчета в фоновом режиме (в отдельном потоке команд). После завершения печати вызывается обработчик события OnAfterPrint.
procedure PrinterSetup;	Вызывает стандартное окно установки параметров принтера.

Примечание 1.

Для вывода отчета в файл нужно сначала подготовить его с помощью обращения к методу Prepare, затем сохранить в файле методом Save объекта TQuickRep.QRPrinter, после чего уничтожить этот объект и поместить NIL в свойстве TQuickRep.QRPrinter:

MyReport.Prepare;

MyReport.QRPrinter.Save(‘REport.QRP’);

MyReport.QRPrinter.Free;

MyReport.QRPrinter := NIL;

Примечание 2.

Стандартное окно предварительного просмотра показано на рис. 4.

Рис. 4. Окно предварительного просмотра отчета.

Чтобы на этапе конструирования просмотреть в окне предварительного просмотра содержимое отчета в том виде, как он будет выводиться на печать, нужно выбрать опцию Preview во вспомогательном меню компонента QuickRep. Следует заметить, что при этом не будут видны некоторые данные, например значения вычисляемых полей. Они будут выводиться только во время выполнения.

Назначение инструментальных кнопок окна:

Масштабирует отчет так, чтобы его страница полностью показывалась в окне.

Отображает отчет в масштабе 1:1.

Масштабирует отчет так, чтобы ширина страницы отчета соответствовала ширине окна.

Показывает первую (последнюю) страницу отчета.

Показывает предыдущую (следующую) страницу отчета.

Вызывает стандартное окно настройки принтера (печатает отчет).

Сохраняет отчет в файле (загружает отчет из файла).

События

Событие	Назначение
property AfterPreview : TQRAfterPreviewPrint;	Возникает в момент закрытия окна предварительного просмотра отчета.
property AfterPrint: TQRAfterPrintEvent;	Наступает после печати отчета или его подготовки к печати.
property BeforePrint: TQRBeforePrintEvent;	Наступает в момент начала генерации отчета (до выдачи окна предварительного просмотра отчета или до его печати).
property OnEndPage: procedure (Sender: TObject);	Возникает в момент подготовки к генерации последней страницы отчета.
property OnNeedData: procedure (Sender: TObject; var MoreData: boolean);	Используется при создании отчета по данным, которые берутся не из НД, а из текстового файла, списка строк, массива и т.п. В параметре MoreData обработчик должен вернуть True, если источник данных еще не исчерпан.
property OnPreview: procedure (Sender: TObject);	Используется для связывания с отчетом нестандартного окна просмотра (см. ниже).
property OnStartPage: procedure (Sender: TObject);	Возникает в момент подготовки к генерации первой страницы отчета.

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

Procedure RepForm.MyREportOnPreviewEvent(Sender: TObject);

begin

MyPrevForm.QRPreview1.QRPrinter := TQRPrinter(Sender);

MyPreviewForm.Show;

end;

Чтобы явное приведение типа TQRPrinter(Sender) стало возможным, необходима ссылка на модуль QRPrntr в предложении Uses соответствующего модуля (в примере – модуля RepForm).

3.Компонент TQRBand

Компоненты TQRBand являются основными частями отчета и используются для размещения на них отображающих компонентов, таких как TQRLabel, TQRDBText, TQRImage и т.п.

Свойства компонента:

Свойство	Назначение
property AlignToBottom: boolean;	Если имеет значение True полоса печатается непосредственно над подвалом страницы вместо обычного расположения справа/снизу от предыдущей полосы.
type TQRBandType = (rbTitle, rbPageHeader, rbDetail, rbPageFooter, rbSummary, rbGroupHeader, rbGroupFooter, rbSubDetail, rbColumnHeader); property BandType: TQRBandType;	Указывает назначение полосы: rbTitle – содержит заголовок отчета; rbPageHeader – содержит заголовок страницы (на первой странице печатается под rbTitle); rbDetaul – содержит информацию из НД; выводится всякий раз при переходе на новую запись НД; эта полоса повторяется для всех записей DataSet, начиная с первой записи и заканчивая последней; позицирование на первую запись и последовательный их перебор осуществляется компонентом TQuickRep автоматически; rbPageFooter – содержит подвал страницы; выводится в конце каждой страницы отчета после всех других полос; rbSummary – подвал отчета; выводится на последней странице отчета после всей иной информации, но перед подвалом последней страницы; rbGroupHeader – содержит заголовок группы; применяется при группировках информации в отчете и выводится всякий раз при выводе новой группы; rbGroupFooter – содержит подвал группы; выводится всякий раз при окончании вывода группы, после всех данных группы; rbSubDetail – содержит детальную информацию из подчиненного НД при выводе в отчете информации из двух или более наборов данных, связанных в приложении как главный-подчиненный; этот тип назначается полосе автоматически при размещении на форме компонента TQRSubDetail; rbColumnHeader – содержит заголовки столбцов; размещается на каждой странице отчета после заголовка страницы.
property Enabled: boolean;	Разрешает/запрещает печать полосы.
property ForceNewColumn: boolean;	Если содержит True, полоса печатается в следующей колонке.
property ForceNewPage: boolean;	Если содержит True, полоса печатается на новой странице.
property HasChild: boolean;	Если содержит True, полоса имеет дочернюю полосу TChildBand. Установка True в это свойство автоматически создает в отчете дочернюю полосу.

События

property AfterPrint: TQRAfterPrintEvent;

и

property BeforePrint: TQRBeforePrintEvent;

наступают соответственно до и после печати полосы. Метод

function AddPrintable(PrintableClass: TQRNewComponentClass): TQRPrintable;

используется для вставки в полосу отображающего компонента в процессе прогона программы. Он автоматически устанавливает между полосами отношение собственности. Два следующих фрагмента выполняют одинаковую работу:

with DetailBand1.AddPrintable(TQRLabel) do

begin

Size.Left := 20;

Size.Top := 5;

Caption := ‘Новая полоса’;

end;

var

aLabel : TQRLabel;

begin

aLabel := TQRLabel.Create(ReportForm);

aLabel.Parent := DetailBand1;

with aLabel do

begin

Size.Left := 20;

Size.Top := 5;

Caption := ‘Новая полоса’;

end;

end;

4.Создание простейшего отчета

Компоненты TQuickRep и TQRBand являются минимально достаточными для создания простейшего отчета, не содержащего внутри себя группировок информации.

Пусть имеется таблица БД Rashod.DB, содержащая сведения об отпуске материалов со склада. В состав ТБД входят поля

• N_RASH – уникальный номер события отпуска товара;

• DEN – номер дня;

• MES – номер месяца;

• GOD – номер года;

• TOVAR – наименование отпущенного товара;

• POKUP – наименование покупателя;

• KOLVO – количество единиц отпущенного товара.

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

Создадим простейший отчет, состоящий из заголовка и сведений об отпуске товара. В отчет включаются все факты отпуска товара. Сортировка производится по номеру события отпуска товара. Для этого разместим на форме компонент TTable, свяжем его с таблицей Rashod.DB и откроем (Active = True). Разместим на форме компонент TQuickRep. Поместим в его свойство DataSet значение Table1, назначив таким образом отчету НД, записи которого будут выводиться в отчете. Добавим в отчет компонент TQRBand. В его свойство BandType компонента QRBand1 по умолчанию будет установлено значение rbTitle, то есть компонент QRBand1 определяет заголовок отчета Разместим на QRBand1 компонент TQRLabel. Установим в свойство Caption этого компонента значение Отпуск товаров со склада и выберем в свойстве Font жирный наклонный шрифт высотой 16 пунктов. Вид формы отчета к этому моменту показан на рис.5.

Рис. 5. В отчете определен только его заголовок.

Теперь разместим в отчете данные, соответствующие текущей записи таблицы Rashod. Для этого поместим в отчет новый компонент TQRBand (имя QRBand2) и установим в его свойство BandType значение rbDetail. Затем разместим на полосе QRBand2 шесть компонентов TQRDBText. Свяжем эти компоненты с полями НД – N_RASH, TOVAR, KOLVO, DEN, MES, GOD. Для этого в свойство DataSet каждого компонента QRDBText установим значение Table1, а в свойство DataField – имя соответствующего поля. Вид отчета к этому моменту показан на рис.6.

Рис. 6. Отчет с заголовком и группой детальной информации.

Для просмотра получившегося отчета щелкнем по нему правой кнопкой мыши и из всплывающего меню выберем элемент Preview. Окно предварительного просмотра отчета показано на рис. 7.

Рис. 7. Содержимое отчета в окне предварительного просмотра.

Чтобы окно предварительного просмотра открывалось при активизации формы, создадим такой обработчик события OnActivate формы:

procedure TForm1.FormActivate(Sender: TObject);

begin

QuickRep1.Preview;

end;

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

procedure TForm1.QuickRep1AfterPreview(Sender: TObject);

begin

Form1.Close;

end;

5.Использование компонента TQREXPR

Из рис.7 видно, что в простейшем отчете выводится дата, составленная из трех полей – DEN, MES, GOD. Объединим значения из этих полей в одно значение, являющееся результатом вычисления выражения. Выражение в отчетах формируется при помощи компонента TQRExpr. Удалим из компонента QRBand2 компоненты QRDBText4, QRDBText5 и QRDBText6, связанные с полями DEN, MES, GOD. Вместо них разместим в отчете компонент TQRExpr (имя QRExpr1).

Выражения в TQRExpr формируются с помощью специального редактора, который вызывается в окне инспектора объектов кнопкой в поле данных свойства Expression этого компонента (рис.8).

Рис. 8. Окно редактора формул компонента TQRExpr.

В поле Enter expression можно ввести или отредактировать выражение, которое обычно состоит из имен полей НД, преобразующих функций и переменных, связанных операциями отношения. Имена полей НД добавляются в текущее положение курсора (поле Enter expression) с помощью вспомогательного окна, связанного с кнопкой Function, а переменные – с кнопкой Variable.

Нажмите кнопку Function, в левом окне выберите категорию Other (другие) и функцию STR в правом окне – эта функция преобразует числовое значение в строковое. Нажмите Continue, чтобы перейти к вводу параметров (рис.9). Надпись над строкой ввода окна Expression Wizard напоминает о том, что выбранная нами функция имеет один числовой параметр.

Рис. 9. Формирование части выражения.

Для его ввода нажмите кнопку справа от строки ввода – на экране вновь появится начальное окно редактора формул. Поскольку мы хотим преобразовать в строку номер дня, нажмите кнопку Database field и выберите поле DEN в списке полей таблицы Table1. Нажмите OK, чтобы завершить ввод параметра. В поле Enter expression будет сформирована часть формулы – STR(Table1.DEN). На панели Insert at cursor position нажмем кнопку «+» и вручную введем разделитель ‘.’ (рис.10).

Рис. 10. Создание части формулы выражения.

Продолжите формировать выражение так, чтобы в конце концов оно приобрело такой вид:

STR(Table1.DEN) + ‘.’ + STR(Table1.MES) + ‘.’ + STR(Table1.GOD)

(возможно проще ввести его вручную). Затем нажмите кнопку OK, чтобы закрыть окно редактора формул. С помощью Инспектора объектов установите в свойство AutoSize компонента QRExpr1 значение False, измените размеры компонента так, чтобы он мог отображать примерно 10 символов, и установите выравнивание вправо (свойство Alignment = taRightJustify). Запустите режим предварительного просмотра содержимого отчета (рис.11). Как видим, дата отпуска товара приобрела более привычный вид.

Рис. 11. Результат вычисления выражения появился в отчете.

Замечание.

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

procedure TForm1.TableCalcFields(DataSet: TDataSet);

begin

Table1SumData.Value := Table1DEN.AsString + ‘.’ +

Table1MES.AsString + ‘.’ + Table1GOD.AsString;

end;

6.Использование TQRBand для представления заголовков столбцов

Компонент TQRBand, у которого в свойство BandType установлено значение rbColumnHeader, используется для размещения заголовков столбцов. Собственно заголовки столбцов формируются при помощи компонентов TQRLabel.

В рассмотренном в предыдущих разделах отчете разместим компонент TQRBand (имя QRBand3) и установим в свойства Caption этих компонентов соответственно значения №№, Товар, Количество, Дата. В свойствах Font компонентов выберем наклонный и подчеркнутый шрифт. Вызовем окно предварительного просмотра отчета – для каждой страницы отчета теперь будут выводиться названия столбцов (рис.12).

Рис.12. В отчете появились заголовки столбцов.

7.Использование TQRBand для показа заголовка и подвала страницы.

Компонент TQRBand, у которого в свойство BandType установлено значение rbPageHeader, используется для показа заголовка страницы, а если это свойство установлено в rbPageFooter, – для показа подвала страницы. Заголовок выводится в начале каждой страницы, а подвал – в ее конце. Информация в заголовке и подвале страницы может формироваться на основе статического текста (компоненты TQRLabel), значений полей (компоненты TQRDBText) и результатов вычислений выражений (компоненты TQRExpr).

Вернувшись к предыдущему примеру, разместим в отчете компонент TQRBand (имя QRBand4) и установим в его свойство BandType значение rbPageHeader. Не будем размещать в заголовке никакого текста, просто отчеркнем линию вверху страницы. Для этого установим в свойство компонента страницы Frame.DrawTop значение True, что обеспечивает вывод линии по верхнему краю области, занимаемой компонентом. Аналогичным образом определим в отчете компонент подвала страницы (имя QRBand5) и установим в его свойство Frame.DrawBottom значение True, что обеспечивает вывод линии по нижнему краю области, занимаемой компонентом.

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

8.Использование компонента TQRSysData

Компонент TQRSysData используется для показа вспомогательной и системной информации. Вид показываемой информации определяется свойством

property Data: TQRSysDataType;

Ниже указаны возможные значения этого свойства.

Значение	Что выводится
qrsColumnNo	Номер текущей колонки отчета (для одноколоночного отчета всегда 1).
qrsDate	Текущая дата.
qrsDateTime	Текущие дата и время.
qrsDetailCount	Количество записей в НД, а при использовании нескольких НД – количество записей в главном наборе. Для случая, когда НД представлен компонентом TQuery, эта возможность может быть недоступной, что связано с характером работы компонента TQuery, который возвращает столько записей, сколько необходимо для использования в текущий момент, а остальные предоставляет по мере надобности.
qrsDetailNo	Номер текущей записи в НД.
qrsPageNumber	Номер текущей страницы отчета.
qrsPageCount	Общее количество страниц отчета.
qrsReportTitle	Заголовок отчета.
qrsTime	Текущее время

Разместим в компоненте QRBand5 подвала отчета два компонента TQRSysData. В свойство Data первого из них установим значение qrsDate, второго – qrsPageNumber. В режиме предварительного просмотра увидим, что теперь в подвале страницы выводятся номер страницы и текущая дата (рис.13)

Рис. 13. Показ номера страницы и текущей даты в подвале страницы.

9.Группировки данных

Для группировок информации используется компонент TQRGroup. Его свойство Expression указывает некоторое выражение, которое используется для группировки, иными словами, в группу входят записи, удовлетворяющие условию этого выражения. При смене выражения происходит смена группы.

Для каждой группы выводятся ее заголовок и подвал. В качестве заголовка группы используется компонент TQRBand со значением свойства BandType, равным rbColumnHeader, а в качестве подвала – со значением rbGroupFooter. Свойство FooterBand компонента TQRGroup должно содержать ссылку на компонент подвала группы. В заголовке группы, как правило, выводится группирующее выражение, а в подвале группы – агрегированная информация: суммарные, средние и т.п. значения по группе в целом.

Пример.

Построим отчет о расходе товара со склада, в котором информация группируется по наименованию товара. Для этого определим набор данных отчета (компонент TTable, имя Table1). Установим у НД текущим индекс по полю TOVAR (в свойстве FieldIndexNames или IndexName). Разместим в отчете:

• заголовок отчета – компонент TQRBand с именем QRBand1, свойство BandType=rbTitle;

• заголовок столбцов – компонент TQRBand с именем QRBand2, свойство BandType=rbColumnHeader;

• группу – компонент TQRGroup с именем QRGroup1;

• область детальной информации – TQRBand с именем QRBand3, свойство BandType=rbDetail;

• подвал группы – TQRBand с именем QRBand4, свойство BandType=rbGroupFooter;

В компоненте QRGroup1 установим:

• в свойство FooterBand значение QRBand4;

• в свойство Expression значение Table1.TOVAR, которое является формулой и строится в редакторе формул.

Поскольку свойство Expression не визуализирует значения выражения, необходимо разместить в группе компонент TQRExpr (имя QRExpr1) и определить значение его свойства Expression так, чтобы оно содержало Table1.TOVAR.

В компоненте подвала группы QRBand4 будем подсчитывать сумму по полю KOLVO (сумму отпущенного конкретного товара). Для этого разместим в подвале группы компонент TQRExpr (имя QRExpr2) и определить значение его свойства Expression так, чтобы оно содержало формулу SUM(Table1.TOVAR).

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

Рис. 14. Макет отчета с группировкой по товару.

Рис. 15. Отчет с группировкой по товару в окне предварительного прсмотра.

10.Множественная группировка данных

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

Пусть требуется представить в отчете сведения о расходе товаров со склада группируя данные по товарам, а внутри группы – по покупателям. Установим текущий индекс по полям TOVAR, POKUP. Общий вид отчета на этапе разработки приводится на рис.17, а в окне предварительного просмотра – на рис.18.

Рис.17. Макет отчета с вложенными группами.

Рис. 18. Отчет с вложенными группами.

11.Построение отчета главный-детальный

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

1. с помощью компонента TQuery произвести соединение данных из нескольких таблиц БД в один НД, после чего определить в отчете нужные группировки;

2. создать в приложении по одному НД на каждую таблицу, соединить эти наборы между собой связью главный-детальный (используя свойства MasterSource, MasterFields набора данных) и применить в отчете компонент (или несколько компонентов) TQRSubDetail для вывода информации из детального НД (или группы детальных НД); для вывода информации из главного НД, как и в обычных отчетах, применяется компонент TQRBand, у которого в свойстве BandType установлено значение rbDetail.

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

Компонент TQRSubDetail предназначен для показа в отчете информации из детального НД. Его свойство

Property DataSet: TDataSet;

указывает имя детального НД, информация из которого будет выводиться в пространстве компонента TQRSubDetail. В остальном использование этого компонента аналогично использованию компонента TQRBand, у которого в свойство BandType установлено значение rbDetail.

Пусть имеется таблица БД TOVARY.DB, содержащая помимо прочих поле TOVAR (название товара). Пусть также имеется таблица БД RASHOD.DB, содержащая сведения об отпуске материалов со склада. В ее состав входят поля N_RASH (уникальный номер события отпуска товара), DEN (номер дня), MES (номер месяца), GOD (номер года), TOVAR (наименование отпущенного товара), POKUP (наименование покупателя) и KOLVO (количество единиц отпущенного товара).

Таблицы TOVARY.DB и RASHOD.DB находятся в отношении один-ко-многим, то есть одному товару может соответствовать более одного факта отпуска товара со склада.

Разместим на форме компонент TTable (им TovaryTable), ассоциированный с ТБД TOVARY.DB, и связанный с ним компонент TDataSource (имя DS_TovaryTable). Разместим также еще один компонент TTable (им RashodTable), ассоциированный с ТБД RASHOD.DB, и установим между НД связь главный-детальный. Для этого установим в свойство RashodTable.MasterSource значение DS_TovaryTable, а в свойство RashodTable.MasterFields значение TOVAR (рис.19).

Рис. 19. Установка связи главный-детальный.

Заметим, что после установления связей НД и НД RashodTable текущим индексом должен быть индекс по полю Tovar (свойство RashodTable.IndexFieldNames).

Приступим к разработке отчета. Определим заголовок отчета – компонент TQRBand с именем QRBand1, в свойство BandType которого установлено значение rbTitle. Установим в качестве основного НД отчета TovaryTable, указав QuickRep1.DataSet = TovaryTable. Разместим в отчете компонент TQRBand с именем QRBand2 и установим в его свойство BandType значение rbDetail. Этот компонент будет использоваться для отображения детальной информации из НД TovaryTable.

Разместим в отчете компонент TQRSubTetail (имя QRSubDetail). Установим в его свойство DataSet значение RashodTable, связав таким образом данный компонент с подчиненным НД. Разместим в области компонента QRSubDetail три компонента TQRDBText и свяжем их соответственно с полями Pokup, Kolvo и D НД RashodTable (поле D определено в НД RashodTable как вычисляемое по значениям полей DEN, MES, GOD). Разместим в области компонента QRBand2 заголовки столбцов.

Вид формы отчета показан на рис.20.

Рис. 20. Макет отчета, в котором показываются записи из связанных наборов данных.

В результирующем отчете (рис.21) для каждой записи НД TovaryTable выводятся подчиненные ей записи из НД RashodTable.

Рис. 21. Отчет, в котором показываются записи из связанных наборов данных.

Замечание.

Если необходимо определить заголовок и подвал для информации, группируемой в компоненте TQRSubDetail, следует воспользоваться свойством

property Bands: TQRSubDetailGroupBands;

этого компонента, которое имеет два логических подсвойства (HasHeader и HasFooter), указывающих на наличие или отсутствие соответственно заголовка и подвала.

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

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

Композитный отчет реализуется при помощи компонента TQRCompositeReport. В его обработчике события OnAddReport ранее определенные простые отчеты добавляются в списковое свойство Report. Например, так:

property TCompositnyjOtchet.QRCompositeReport1AddReports(Sender: TObject);

begin

with QRCompositeReport1 do

begin

Reports.Add(ManyGroup.QuickRep1);

Reports.Add(Prostoj.QuickRep1);

end

end;

В этом примере композитный отчет составляется из двух отчетов: QuickRep1 (определенный в форме ManyGroup) и QuickRep1 (определенный в форме Prostoj). Почать композитного отчета или его предварительный просмотр осуществляется так же, как для простых отчетов, например

QRCompositeReport1.Preview;

На рис.22 показан композитный отчет, построенный из двух ранее разработанных нами отчетов – простейшего отчета и отчета с группировками данных.

Рис. 22. Композитный отчет, составленный из двух простых отчетов.

Разработка баз данных в Delphi

Вводный	Вводный урок	Вводный урок.doc
Урок 01	Настройка BDE	Урок01.doc
Урок 02	Создание таблиц с помощью Database Desktop	Урок 02.doc
Урок 03	Создание таблиц с помощью SQL-запросов	Урок 03.doc
Урок 04	Обзор визуальных компонент. Компоненты работы с базами данных	Урок 04.doc
Урок 05	Компонент Ttable. Создание таблиц с помощью компонента TTable	Урок 05.doc
Урок 06	Компонент TQuery	Урок 06.doc
Урок 07	Редактор DataSet, вычисляемые поля	Урок 07.doc
Урок 08	Компонент TDatabase	Урок 08.doc
Урок 09	Управление транзакциями	Урок 09.doc
Урок 10	Основы языка SQL	Урок 10.doc
Урок 11	Генерация отчетов	Урок 11.doc