Данная статья является пятой в цикле статей под общим названием "Изучаем программирование в TD/GUPTA", в котором мы постараемся дать начинающему пользователю программных продуктов GUPTA всю необходимую информацию для изучения программирования и получения навыков создания информационных систем. В ней мы рассмотрим работу c БД и средствами их построения. Для успешного усвоения материала читателю желательно познакомиться с предыдущими статьями из этого цикла, которые размещены на нашем сайте.
Введение
Несомненно, программный продукт TD можно использовать для широкого класса задач в области информационных технологий, и это, возможно, читателю уже понятно из предыдущих статей, однако наиболее эффективно применение этих средств разработки для приложений, работающих с базами данных (БД). Именно для этих задач данные средства разработки и были созданы. Для удобства в среду TD встроено очень удобное средство для интерактивной работы с БД - DataBase Explorer. Эта компонента позволяет создавать БД и таблицы, формировать выходные документы (Reports), заносить новые данные и выполнять SQL запросы и даже скрипты запросов. Существуют и другие возможности у этой компоненты, например, для создания хранимых процедур, но мы будем эти возможности рассматривать позднее в следующих статьях.
Для полноценной работы по созданию информационных систем, их разработки и отладки в пакет TD включен однопользовательский сервер SQLBase. Этот СУБД полностью совместим по всем характеристикам с многопользовательским сервером SQLBase, поэтому и БД и приложения, работающие с ними, без проблем переносятся в сетевую среду клиент-сервер. Кроме того, для выполнения удобной отладки из среды обеспечивается два подключения (connect), что позволяет одновременно отлаживать запросы в интерактивной среде DataBase Explorer и тестировать приложения.
И, наконец, читатель в данной статье увидит и, несомненно, положительно оценит простоту и наглядность работы с БД с помощью специальных функций, в которых можно явно указывать настраиваемые SQL запросы. Для первоначального знакомства с данными возможностями предназначается данная статья.
Для успешного и самостоятельного освоения материала данной статьи, который, мы очень надеемся, может служить своеобразным самоучителем программирования на Centura TD, желательно познакомиться и выполнить задания статей "Шаг 1-4".
Встроенное средство DataBase Explorer
Познакомимся первоначально с DataBase Explorer. И с его помощью создадим БД для построения приложения. Для его вызова, после запуска TD, нужно в пункте главного меню Tools выбрать позицию DataBase Explorer …. После это в правой части экрана вы получите перечень серверов БД, к которым у вас было настроено подключение при инсталляции TD. Выберете SQLBase в списке серверов, для чего дважды щелкните мышкой и получите список доступных БД для работы, эти БД уже созданы. Выберем БД ISLAND (это стандартная БД, которая разворачивается при инсталляции TD для поддержки примеров) и в специальном окне подключения (см. рисунок ниже) нажмем на кнопку "Connect", вводить имя пользователя и пароль для этого не нужно. В результате, после последовательного открытия дерева в правой части: БД ISLAND->Tables-> BUDGET, мы получим окно представленное на рисунке 1a, расположенном ниже. На рис. 1b показано окно, которое мы получим после выполнения операций создания новой БД COMPANY и ее таблиц (в процессе обучения). Перейдем теперь к действиям по созданию новой БД.
(а)
(b)
Рис. 1 Окно встроенной компоненты DataBase Explorer в TD. Начальное положение для стандартной БД ISLAND (a) и окно с создаваемой БД COMPANY (b)
Создание БД
Для создания новой БД, работающей под управлением SQLBase (в нашем случае локального сервера), необходимо в дереве DataBase Explorer выделить SQLBase и вызвать пользовательское меню правой кнопкой. В этом меню нужно выбрать позицию "New Database…" после этого на экране появиться окно, представленное на рисунке 2.
Рис. 2 Создание новой БД COMPANY в DataBase Explorer
В данном окне введем имя новой БД (DataBase Name), в нашем случае - COMPANY и нажмем кнопку "ОК". В результате будет создана и сконфигурирована новая БД для данного сервера - это Server1 (название сервера мы пока изменять не будем). Отметим, попутно, что в TD одновременно поддерживается работа с несколькими серверами БД, для чего имена серверов должны отличаться. Для подключения к новой БД выберем ее в дереве справа и с окне подключения нажмем кнопку "Connect" (Рис. 3). Пароль и пользователя в данном случае указывать не надо, так как по умолчанию задается SYSADM/SYSADM.
Рис. 3 Подключение к БД COMPANY в DataBase Explorer
Работа с БД в DataBase Explorer
После подключения к БД мы можем увидеть ее содержимое, которое разделено на четыре раздела: таблицы (Tables), отображения (Views), индексы (Indexes) и хранимые процедуры (Stored Procedures). В данной статье мы будем использовать пока только раздел таблиц.
Рис. 4 Просмотр дерева содержания БД в DataBase Explorer
Мы создадим БД, состоящую из 3-х таблиц:
- OTDEL - таблица отделов компании
- EMPLOY - таблица сотрудников компании
- DOLG - таблица должностей сотрудников компании.
Примечание: Для простоты БД и упрощения материала статьи, мы первоначально не будем задавать ключи (первичные и вторичные), индексы и включать другие возможности и механизмы СУБД, которые, несомненно, SQLBase поддерживает в полной мере.
Описание наших таблиц приведено ниже в формате языка SQL:
OTDEL: CREATE TABLE OTDEL
(CODE INTEGER, -- код отдела
NAME CHAR(40), -- название
SCOUNT INTEGER, -- число сотрудников
FONDZ FLOAT, -- фонд зарплаты отдела
FONDZN FLOAT, -- фонд надбавок к зарплате
CODEMAN INTEGER, -- код начальника
WORKOTD CHAR(140) ); -- описание функций отдела
EMPLOY: CREATE TABLE EMPLOY
( CODE INTEGER, -- код сотрудника
CODEOTD INTEGER, -- код отдела
NAME CHAR(20), -- имя
FAM CHAR(20), -- фамилия
OTCH CHAR(20), -- отчество
NADB FLOAT, - надбавка
OKLAD FLOAT, -- оклад
DATER DATETIME , -- дата рождения
CODEDOL INTEGER, -- код должности
WORK CHAR(80) ); -- характеристика работника
DOLG: CREATE TABLE DOLG
( CODEDOL INTEGER, -- код должности
NAMEDOL CHAR(20)); -- название должности
Текстовые пояснения в одной строке даны в скриптах для упрощения, при выполнении запросов их необходимо поместить в отдельной строке или опустить.
Создать БД можно: в интерактивном режиме, вводя описания полей вручную или выполнив скрипт загрузки БД (step5_DB.uld), который можно скачать здесь (для редактора: здесь нужно обеспечить скачивание скрипта - step5_DB.uld). Для автоматического создания таблиц и первоначальных данных нужно выполнить команду SQLBase:
LOAD SQL 'с:\ step5_DB.uld';
под управлением встроенного или автономного средства SQLTalk (см. ниже). Пути доступа к файлу загрузки, естественно, могут отличаться.
Рассмотрим процедуру создания таблицы в интерактивном режиме. Для этого нужно правой кнопкой на "Tables" вызвать пользовательское меню, в котором выбрать "New Table…". Далее нужно ввести информацию, показанную на рисунке 5. После ввода, используя пользовательского меню в зоне описания полей, нужно подтвердить изменения - "Apply Edits". Пустая таблица заданной структуры будет запомнена в БД. Обратите внимание, что мы автоматически использовали закладку "Definition" в правой части DB Explorer.
Рис. 5 Создание новой таблицы OTDEL в DataBase Explorer
Для ввода данных в таблицу необходимо выбрать закладку "Data". Окно DB Explorer в этом режиме показано на следующем рисунке (Рис. 6). В этом состоянии мы можем добавлять новые записи в таблицу, редактировать существующие и удалять ненужные. Для добавления записей посредством пользовательского меню (правая кнопка в таблице) нужно добавить строку - "New Row". Далее в нижней части экрана сформируется выделенная строка таблицы, в которую можно вводить новые данные (эта зона выделена разделительной линией). Вводить можно любое число строк, при этом для подтверждения окончательного ввода нужно выполнить операцию - "Apply Edits", а для отмены ввода - "Discard Edits". В нашем случае при вводе данных необходимо самостоятельно контролировать корректность кодов отделов - они не должны совпадать. Аналогичным образом вы можете создать и ввести данные в таблицы сотрудников (EMPLOY) и должностей (DOLG).
Примечание: Для выполнения операций можно использовать также и автоматически формируемый пункт меню в режиме DB Explorer - "Data Base Explorer". В этом пункте доступны подменю для выполнения всех основных операций с БД и таблицами.
Рис. 6 Ввод новых данных в таблицу OTDEL в DataBase Explorer
СУБД SQLBase
Когда вы работаете с DB Explorer, то у вас обеспечивается автоматическое подключение к СУБД SQLBase. В нашем случае мы подключаемся к СУБД, рассчитанным на 5 пользователей, который установлен отдельно. Если вы используете СУБД, входящую в поставку TD, то у вас будет однопользовательский вариант, единственное отличие которого проявиться в заголовке (вместо 5 Client у вас будет 1 Client). Все функции СУБД и доступные действия будут аналогичны. Внешний вид консоли СУБД приведен на рисунке ниже (Рис. 7). Здесь мы дадим только краткую характеристику СУБД и его консоли. Подробное описание СУБД и его возможностей дано в статье на нашем сайте.
Для управления и контроля предусмотрено меню и четыре информационных окна:
- Окно системной активности (System Activity) показывает текущую информацию о курсорах и выполняемых операциях, на уровне системного администратора.
- Окно активности процессов (Process Activity) показывает текущую информацию о процессах и выполняемых SQL запросах.
Примечание: Оба этих окна могут показывать информацию с разными уровнями детализации (доступно 5 уровней детализации).
- Окно Баз данных (Databases) показывает текущую информацию о БД, которые сконфигурированы и созданы в настоящий момент.
- Окно пользователей (Server status) показывает текущую информацию о пользователях, которые подключились к серверу в настоящий момент в настоящий момент.
В нашем случае мы видим БД COMPANY в окне БД и клиента WIN32CLI2000 подключенного к этой БД. Для сокращения рисунка часть окна, где указывается имя БД скрыто слева. Расположение окон консоли, их состав легко регулируется. Для доступа к консоли может быть установлен пароль администратора. Предусмотрена возможность формирования журналов для различных окон.
Рис. 7 Консоль СУБД SQLBase при подключении клиента WIN32CL2000
Консоль СУБД может быть эффективно использована для детального анализа запросов и разрешения внештатных ситуаций. Но дальнейшее ее рассмотрение выходить за рамки данной статьи, на дополнительных возможностях мы остановимся позже.
Компонента SQLTalk
При необходимости мы можем переключить DB Explorer в режим непосредственного выполнения SQL запросов и дополнительных команд, которые входят в расширение языка взаимодействия с СУБД как команды SQLTalk. Окно в режиме SQLTalk показано на рисунке 8. Для перехода в этот режим нужно при выбранной БД открыть закладку SQLTalk, которая разделена на две части: сверху расположено окно ввода SQL команд, а снизу результат их выполнения. На рисунке показаны результаты выполнения команд установки сервера, создания БД и подключения к ней. В нашем случае команды были выполнены в одиночном режиме, для чего после выделения строки с командой нужно нажать клавиши - Schift+F2. В этом режиме предусмотрена возможность выполнения целого скрипта SQL команд, для чего нужно нажать F5. Полный набор возможностей можно уточнить при использовании пользовательского меню, которое вызывается правой кнопкой мыши. Здесь доступно: открытие и сохранение скриптов, выполнение COMMIT и ROLLBACK, поиска и замены, а также управление выводом информации.
Рис. 8 DataBase Explorer в режиме ручного ввода запросов (SQL Talk)
Введите свои команды для создания таблицы DOLG (см. выше), а затем перейдите в режим ввода данных в таблицу (выбор таблицы DOLG и закладки Data) и введите набор должностей, как показано на рисунке 9.
Рис. 9 Данные, занесенные в таблицу DOLG
Создание приложения для работы с БД
После создания БД мы можем приступить к созданию приложения работающего с БД.
Мы построим приложение представленное одним окном (см. рисунок 10), в котором будут выполнятся основные операции: подключение к БД и отключение от БД; заполнение списка данными и заполнение таблиц на основе SQL запросов разного вида. Окно в начальном состоянии (до заполнения) представлено на рисунке ниже.
Рис. 10 Вид окна приложения для изучения работы с БД
Отметим, что до подключения к БД кнопки заполнения недоступны. Подключение к БД выполняется нажатием кнопки "Подключится". Если БД, пользователь и пароль не указаны, то у нас используются параметры подключения по умолчанию: COMPANY/SYSADM/SYSADM. При выполнении операции подключения кнопки заполнения включаются. При отключении от БД (кнопка "Отключиться") кнопки заполнения возвращаются в исходное состояние.
После выполнения операций заполнения объектов оконного интерфейса из БД оно должно выглядеть так, как показано на рисунке 11. Список должен быть заполнен данными (NAME) из таблицы DOLG, после нажатия кнопки "Добавить из БД". Поле "Выбрано" показывает значение текущего выбранного в списке элемента. Таблица "Автоматически - сотрудники" заполняется при нажатии кнопки "Добавить без колонок" из таблицы EMPLOY. Особенность такого заполнения заключается в том, что предварительно таблица, подлежащая заполнению, не содержит заранее определенных столбцов (колонок) - они формируются на основе самого SQL запроса. Таблица "Таблица по запросу для сотрудников" формируется на основе SQL запроса с колонками созданными и поименованными заранее. Этот запрос строиться на основе связки (JOIN) двух таблиц: сотрудников (EMPLOY) и должностей (DOLG).
Рис. 11 Окно приложения (step5) после заполнения данных их БД
Окно "Отделы" заполняется из БД при нажатии на кнопку "Добавить из БД отделы". Информация выбирается из таблицы OTDEL. Кнопки "Очистить …" предназначены для очистки таблиц. Отметим, что в данном случае (режиме заполнения), после заполнения оконных таблиц все таблицы БД освобождаются для дальнейшего использования. В нашем приложении мы применяем простейший способ заполнения интерфейсных объектов на основе функций популяции: SQLxxxPopulate (SQLListPopulate и SQLTblPopulate). Эти функции представляют "средний" уровень взаимодействия с БД. Более высоким уровнем является способ взаимодействия на основе специальных классов - QUICK OBJECT, а более низкими уровнями: взаимодействие на основе SQL API или на основе работы с выборкой (Result Set). Эти механизмы для работы с БД из TD мы рассмотрим в следующих статьях данного цикла. Здесь же остановимся на самом наглядном и достаточно эффективном способе. Суть его заключается в том, что для работы с БД используются специальные функции, в качестве одного из параметров является строка SQL запроса (тип String), записанного в естественном виде (как - "SELECT * FROM…"). Вторым обязательным параметром этих функций является курсор (Sql Handle) получаемый после подключения к БД (connect). Пока курсор существует, мы имеем возможность доступа к БД. После отключения (disconnect) БД становиться недоступной. Так как в языке SAL предусмотрены все возможности работы со строками, то сделать запрос, а вместе с ним и приложение динамически настраиваемым. Данные возможности мы также затронем впоследствии.
Ниже представлен перечень объектов нашего приложения. Думаю, что не нужны дополнительные пояснения их назначения, так как многое ясно по названию объектов в данном списке и легко их сопоставить с визуальными объектами окна приложения. Если же у вас возникнут затруднения в этом, обратитесь к программе приложения (step5.app), которая доступна для вас ниже.
Рис. 12 Объекты приложения для изучения операций с БД
Курсоры и их описание
Для подключения к БД необходимо определить переменные типа курсор (Sql Handle). Это делается в разделе переменных (Variables) окна, как показано на рис. 13.
Рис. 13 Описание курсоров окна
После нажатия кнопки подключения выполняется процедура, текст которой показан на рисунке 14 ниже. В этой процедуре устанавливается обработчик ошибок при подключении с помощью предложения When SqlError. Блок команд размещенных на вложенном уровне будет выполняться при возникновении ошибок.
Примечания:
- Предложение When SqlError должны распологаться на том же уровне что и оператор, работающий с БД (в нашем случае SqlConnect).
- При обработке ошибок можно использовать специальную информацию об ошибке, которую мы в данном примере не рассматриваем.
- Может быть предусмотрена возможность глобальной обработки SQL ошибок для всего приложения (On SAM_SqlError). Могут быть определены одновременно и локальные и глобальные обработчики ошибок, причем в этом случае, при наличии локальных обработчиков ошибок для данного оператора, глобальный обработчик отключается.
Далее в программе идет несложный для понимания (поэтому частично скрытый) текст проверки заполнения полей интерфейса, установленных для: БД (dfDBName), пользователя (dfUser) и пароля (dfPassword). На основе них или стандартных значений (по умолчанию) формируются значения глобальных, встроенных, системных переменных SAL, которые определяют подключение к БД. Это, соответственно, переменные: SqlDatabase, SqlUser и SqlPassword. Само подключение выполняется функцией SqlConnect, в качестве единственного параметра которой используется курсор (у нас -hSql). При успешном подключении выполняется группа операторов, в частности, получается еще один курсор (hSql2). Отметим, что число курсоров в одном приложении практически не ограничивается. Кроме этого с помощью функции SqlSetIsolationLevel устанавливается режим работы с данным курсором. О режимах конкуренции мы будем говорить ниже в других статьях. В нашем случае установим RL (Release Lock) - режим освобождения от блокировки после завершения запросов. И, наконец, в данном фрагменте, включаются кнопки заполнения оконных таблиц и выключается кнопка подключения к БД.
Подключение к БД и отключение от БД
Рис. 14 Процедура подключения к БД из программы
В случае успешного и неуспешного подключения к БД выдается информационное сообщение. Причем при возникновении ошибки первоначально формируется сообщение предусмотренного выше обработчика (When SqlError), а затем сообщение после проверки кода возврата функции SqlConnect. Если не было определено ни одного обработчика ошибок, то срабатывает встроенный механизм и сообщение об ошибке выдается в специальном окне SAL. Отключении от БД (если нажата кнопка "Отключиться") выполняется следующий фрагмент программы. Текст представлен на рисунке 15. Отключение для каждого курсора выполняется с помощью функции SqlDisconnect, единственным параметром этой функции является курсор (hSql и hSql2 соответственно).
Рис. 15 Процедура отключения от БД из программы
Заполнение комбинированного списка
Для заполнения комбинированного списка (cmBox1), которое происходит при нажатии кнопки pbAddList вызывается процедура, представленная на рисунке 16. Заполнение выполняется функцией SalListPopulate, имеющей три параметра: первый определяет список (объект для заполнения - cmBox1), второй параметр задает курсор (hSql), а третий параметр представляет собой строку SQL запроса, используемого для заполнения. Отметим попутно, что запрос может иметь и более сложную запись в соответвии со стандартом языка SQL (можно использовать WHERE составляющую, подзапросы и т.п.). После заполнения текущим элементом списка становиться нулевой элемент (напомним, что элементы нумеруются с нуля) и полю dfFromList присваивается это значение. При изменении состояния списка (SAM_Click для cmBox1) выполняются аналогичные действия для установки нового состояния поля dfFromList.
Рис. 16 Заполнение cmBox1 из БД функцией SalListPopulate и переключение списка.
Очистка списка выполняется при нажатии на кнопку "Очистить", функцию очистки SalListClear мы уже рассматривали ранее. Функции очистки таблиц аналогичны и мы здесь их не показываем. Для очистки таблиц использована функция SalTblReset.
Рис. 17 Очистка списка cmBox1
Автоматическое заполнение таблиц
При заполнении таблицы можно предварительно не создавать колонки они могут быть сформированы автоматически, в зависимости от содержания запроса. Такой пример показан на рисунке 18, при заполнении таблицы tblNoCol. Эти действия выполняются функцией SalTblPopulate, которая имеет уже четыре параметра. Первые три параметра совпадают с описанными ниже. Отметим только, что в данном случае мы даже не перечисляем паля таблицы EMPLOY, а указываем только "*", означающую выбор всех полей таблицы. При выполнении этой функции колонки будут сгенерированы автоматически. Четвертый параметр функции SalTblPopulate, определяет режим заполнения таблицы. Режим TBL_FillAll, означает заполнение всей таблицы а затем возврат в программу. Может быть использован режим TBL_FillNormal, в этом случае заполняется только видимая часть таблицы и выполнение программы продолжается. При прокрутке записи дополняются в таблицу. Возможен и третий режим - TBL_FillAllBackground. При его использовании заполнение оставшихся записей в таблице выполняется в фоновом режиме. Для второго и третьего режима нельзя использовать данный курсор до тех пор пока не заполнена вся таблица. В противном случае могут возникнуть проблемы с отображением записей. Недостатком первого режима является необходимость ожидания заполнения всей таблицы записями. В данном фрагменте показано, что операторы обработки ошибок (When SqlError) могут быть расположены и ниже текста самого запроса. Главное, чтобы они располагались на одном уровне одного фрагмента процедуры.
Рис. 18 Заполнение таблицы tblNoCol из БД функцией SalTblPopulate без выделения заносимых полей
Следующий пример (рис. 19) связан с заполнением таблицы tblOtdel и практически аналогичен предыдущему. Отличие в том, что мы явно указываем поля таблицы БД, которые будут передаваться в экранную таблицу.
Рис. 19 Заполнение таблицы tblOtdel из БД функцией SalTblPopulate с указанием полей таблицы БД (NAME и CODE)
Заполнение таблиц со связующими переменными
Для заполнения экранной таблицы tblTest необходимо предварительно описать колонки экранной таблицы, которые в дальнейшем будут корреспондироваться с выбираемыми колонками из таблицы БД. Мы уже рассматривали описание колонок таблицы в предыдущих статьях. Мы опишем три колонки: colName - для имени сотрудника (Имя - название колонки), colOklad- для оклада сотрудника (Оклад - название колонки) и colDolg- для должности сотрудника (Должность - название колонки). Описание показано на рисунке 20.
Pис. 20 Описание колонок экранной таблицы
В фрагменте, представленном на рисунке 21, используется запрос с явным указанием колонок экранной таблицы. Это достигается применением специального предложения INTO в SQL запросе. В конструкции INTO определяются связующие переменные (bind variables), которые соответствуют выбираемым полям SELECT запроса. Эти переменные перечислены через запятую, а каждой из них должно предшествовать двоеточие. Таков синтаксис предложения INTO. В дополнение скажем, что такой запрос определяет слияние двух таблиц (DOLG, EMPLOY) и называется JOIN. Для освобождения курсора hSql2 для других целей используется функция SqlCommit.
Рис. 21 Заполнение таблицы tblTest из БД функцией SalTblPopulate c явным указанием колонок таблицы (colName, colOklad и colDolg)
Если вы проделали правильно все операции: создали БД, заполнили ее значениями, создали окно и оконные объекты и внесли в их обработчики соответствующий текст, то вы получите приложение, представленное на рисунках 10 и 11. Можете попробовать изменить вид запросов, структуру таблиц как экранных, так и БД.
Приложение, которое вы получите (step5.app), можно скачать здесь.
Заключение
В следующей статье этого цикла мы рассмотрим другие методы работы с БД в рамках TD. В частности использование выборки для доступа к данным, синхронизацию оконных таблиц и таблиц БД. Создание связанных таблиц и другие важные элементы программирования при построении информационных систем.