Поддержка разработки десктопных приложений баз данных.

Центр дистанционного обучения Лекция 6 Поддержка разработки десктопных приложений баз данных online.mirea.ru Центр дистанционного обучения Содержание Универсальный программный интерфейс доступа к базам данных ODBC Программные интерфейсы доступа OLE DB, ADO, JDBC Внедряемый (embedded) SQL. Статический и динамический внедряемый SQL. Курсоры (Cursors). Хранимые процедуры. online.mirea.ru Центр дистанционного обучения Универсальный API доступа к данным • ODBC (Open Database Connectivity) - это программный интерфейс доступа к базам данных, разработанный компанией Microsoft на основе спецификаций Call Level Interface (CLI). • Стандарт CLI призван унифицировать программное взаимодействие с СУБД, сделать его независимым от поставщика СУБД и программно-аппаратной платформы. • C помощью ODBC прикладные программисты могут разрабатывать приложения для использования одного интерфейса доступа к данным, не беспокоясь о тонкостях взаимодействия с несколькими источниками. Это достигается благодаря тому, что поставщики различных баз данных создают драйверы, реализующие конкретное наполнение стандартных функций из ODBC API с учётом особенностей их продукта. online.mirea.ru Центр дистанционного обучения Установка ODBC • В ОС MS Windows данный интерфейс устанавливается, как драйвер через автоматический установщик. В дальнейшем, настраивается в оболочке программирования для взаимодействия с разными источниками данных. • В ОС Linux и MacOS интерфейс устанавливается через оболочку bash по команде, распаковываясь из пакета. Настраивается через конфигурационные файлы, входящие в комплект поставки и конфигурационные файлы самой ОС online.mirea.ru Центр дистанционного обучения Настройка ODBC (на примере оболочки QT). Соединение с HOST online.mirea.ru Центр дистанционного обучения Настройка ODBC (на примере оболочки QT). Соединение через TCP/IP. online.mirea.ru Центр дистанционного обучения Определение и специфика OLE DB, ADO, JDBC • OLE DB (Object Linking and Embedding, Database) набор интерфейсов, которые позволяют приложениям унифицировано работать с данными разных источников и хранилищ информации. • ADO (ActiveX Data Objects ) — интерфейс программирования приложений для доступа к данным. ADO позволяет представлять данные из разнообразных источников (реляционных баз данных, текстовых файлов и т. д.) в объектно-ориентированном виде. • JDBC (Java DataBase Connectivity ) - платформенно независимый промышленный стандарт взаимодействия Java-приложений с различными СУБД, реализованный в виде пакета java.sql, входящего в состав Java SE. online.mirea.ru Центр дистанционного обучения Язык SQL в коде приложений • Команды SQL, при определенных условиях, могут напрямую исполняться из языка программирования (например, C или Java). При этом: • запросы могут взаимодействовать с внутренними переменными программы; • должны содержать команды на подключение и поддержание связи с базой данных; • Основные подходы к интеграции • Embedded (внедренный) SQL, статический • Embedded (внедренный) SQL, динамический online.mirea.ru Центр дистанционного обучения Язык SQL в коде приложений Почему при трансляции языка SQL в компьютерной программе возникают проблемы? 1. Переменные приложения баз данных не могут быть использованы в обычной инструкции SQL. 2. Запросы на выборку возвращающие несколько строк не могут быть обработаны с помощью инструкции SELECT. online.mirea.ru Центр дистанционного обучения Язык SQL в коде приложений Решение проблемы традиционного SQL: 1. Помещение инструкций языка SQL в программу, написанную на одном из традиционных языков программирования. Изменить правила работы с переменными. 2. Запросы на выборку возвращающие несколько строк обрабатывать с помощью курсоров. online.mirea.ru Центр дистанционного обучения Основные методы внедрения языка SQL в код приложения • Внедренные инструкции SQL обрабатываются специальным предкомпилятором SQL. Все инструкции SQL начинаются с символа начала и заканчиваются признаком конца, который помечает инструкцию SQL для предварительной компиляции. • Переменные из программы приложения могут использоваться в внедренных инструкциях SQL везде, где разрешены константы. • Запросы, возвращающие одну строку данных, обрабатываются с помощью одноэлементной инструкции SELECT. Эта инструкция задает как запрос, так и переменные программы, в которых возвращаются данные. • Запросы, возвращающие несколько строк данных, обрабатываются с помощью курсоров. • При открытом курсоре можно использовать инструкции позиционированного обновления и позиционированного удаления, чтобы обновить или удалить строку, выбранную в данный момент курсором. online.mirea.ru Центр дистанционного обучения Фрагмент кода программы с внедренным SQL online.mirea.ru Центр дистанционного обучения Комментарий к коду программы с внедренным SQL • Переменные узла объявляются в разделе, заключенном в ключевые слова BeginDeclareSection и EndDeclareSection. Каждое имя переменной узла имеет префикс с двоеточием (:). При появлении в внедренной инструкции SQL. Двоеточие позволяет предкомпилятору различать переменные программы и объекты базы данных, такие как таблицы и столбцы, имеющие одинаковое имя. • Типы данных, поддерживаемые СУБД и основным языком, могут различаться. Если тип языка программы, соответствующий типу данных СУБД, отсутствует, СУБД автоматически преобразует данные. • Инструкция, используемая для возврата данных, является одноэлементной инструкцией SELECT. В результате возвращается только одна строка данных. Поэтому в примере кода не объявляются и не используются курсоры. online.mirea.ru Центр дистанционного обучения 1. 2. 3. 4. 5. Схема компиляции внедренной программы SQL Внедренная программа SQL отправляется в предкомпилятор SQL, средство программирования. Предкомпилятор сканирует программу, находит внедренные инструкции SQL и обрабатывает их. Предкомпилятор создает два выходных файла. Первый файл является исходным файлом, в результате чего удаляются его внедренные инструкции SQL. Второй файл представляет собой копию всех внедренных инструкций SQL, используемых в программе. Этот файл иногда называют модулем запроса базы данных. Выходные данные исходного файла из предкомпиляции передаются стандартному компилятору для языка программирования программы. Компоновщик принимает модули объектов, созданные компилятором, связывает их с различными библиотечными подпрограммами и создает исполняемую программу. Модуль запроса базы данных, созданный предкомпилятором, отправляется специальной служебной программе привязки. online.mirea.ru Центр дистанционного обучения Статический и динамический SQL. • Внедренный SQL называется статическим в том случае, когда инструкции SQL, находящиеся в коде не изменяются каждый раз при запуске программы. • Статический SQL хорошо работает во многих ситуациях и может использоваться в любом приложении, для которого доступ к данным может быть определен во время разработки программы. Например, программа ввода заказа всегда использует одну и ту же инструкцию для вставки нового заказа, а система резервирования авиакомпании всегда использует ту же самую инструкцию для изменения состояния места с доступного на зарезервированное. • Так как такие инструкции могут быть жестко запрограммированы в программе, они анализируются, проверяются и оптимизируются только один раз во время компиляции. Это приводит к относительно быстрому коду. online.mirea.ru Центр дистанционного обучения Статический и динамический SQL. • Хотя статический SQL хорошо работает во многих ситуациях, существует класс приложений, в котором доступ к данным не может быть определен заранее. Например, предположим, что электронная таблица позволяет пользователю ввести запрос, который затем электронная таблица отправляет СУБД для получения данных. Очевидно, что содержимое этого запроса неизвестно программисту при написании программы электронной таблицы. • Чтобы решить эту проблему, электронная таблица использует форму внедренного SQL, который называется динамический SQL. В отличие от статических инструкций SQL, которые жестко запрограммированы в программе, динамические инструкции SQL могут быть построены во время выполнения и помещены в переменную кода приложения. Затем они отправляются в СУБД для обработки. • Самый простой способ выполнить динамическую инструкцию SQL выполнить инструкцию EXECUTE IMMEDIATE. Эта инструкция передает инструкцию SQL в СУБД для компиляции и выполнения. online.mirea.ru Центр дистанционного обучения Курсоры Операции в реляционной базе данных выполняются над множеством строк. Например, набор строк, возвращаемый инструкцией SELECT, содержит все строки, которые удовлетворяют условиям, указанным в предложении WHERE инструкции. Такой полный набор строк, возвращаемых инструкцией, называется результирующим набором. Приложения, особенно интерактивные, не всегда эффективно работают с результирующим набором как с единым целым. Им нужен механизм, позволяющий обрабатывать одну строку или небольшое их число за один раз. Курсоры являются расширением результирующих наборов, которые предоставляют такой механизм. online.mirea.ru Центр дистанционного обучения Основные функции курсоров Улучшение функции обработки результатов инструкций: • позиционируясь на отдельные строки результирующего набора; • получая одну или несколько строк от текущей позиции в результирующем наборе; • поддерживая изменение данных в строках в текущей позиции результирующего набора; • поддерживая разные уровни видимости изменений, сделанных другими пользователями для данных, представленных в результирующем наборе; • предоставляя инструкциям SQL в скриптах, хранимых процедурах и триггерах доступ к данным результирующего набора. online.mirea.ru Центр дистанционного обучения Типы курсоров TSQL. Однонаправленный. • Однонаправленный курсор указывается как FORWARD_ONLY. Он также называется курсором firehose и поддерживает только получение строк последовательно, от начала до конца курсора. Строки нельзя получить из базы данных, пока они не будут выбраны. • Так как курсор не может быть прокручен назад, большинство изменений, сделанных в строках базы данных после извлечения сроки, не видны через курсор. Если значение, использованное для определения положения строки в результирующем наборе, модифицируется, например в случае обновления столбца, входящего в кластеризованный индекс, то значение видимо через курсор. online.mirea.ru Центр дистанционного обучения Типы курсоров TSQL. Статический. • Полный результирующий набор статического курсора создается в базе данных tempdb при открытии курсора. Статический курсор всегда отображает результирующий набор точно в том виде, в котором он был при открытии курсора. • Курсор не отражает изменения в базе данных, влияющие на вхождение в результирующий набор или изменяющие значения в столбцах строк, составляющих набор строк. Статический курсор не отображает новые строки, вставленные в базу данных после открытия курсора, даже если они соответствуют критериям поиска инструкции SELECT курсора. • Статический курсор продолжает отображать строки, удаленные из базы данных после открытия курсора. • Статический курсор всегда доступен только для чтения. online.mirea.ru Центр дистанционного обучения Типы курсоров TSQL. Динамический. • Динамические курсоры отражают все изменения строк в результирующем наборе при прокрутке курсора. Значения типа данных, порядок и членство строк в результирующем наборе могут меняться для каждой выборки. Все инструкции UPDATE, INSERT, DELETE, выполняемые пользователями, видимы посредством курсора. • Обновления, сделанные вне курсора, не видны до момента фиксации, если только уровень изоляции транзакций с курсорами не имеет значение READ UNCOMMITTED. online.mirea.ru Центр дистанционного обучения Общий принцип обработки курсоров • Связать курсор с результирующим набором инструкции и задать его характеристики (например возможность обновления строк). • Выполнить инструкцию для заполнения курсора. • Получить в курсор необходимые строки. Операция получения в курсор одной и более строк называется выборкой. Выполнение серии выборок для получения строк в прямом или обратном направлении называется прокруткой. • При необходимости выполнить операции изменения (обновления или удаления) строки в текущей позиции курсора. • Закрыть курсор. online.mirea.ru Центр дистанционного обучения Пример использования курсора Курсор должен получать имена морячков, которые брали напрокат красную лодку, в алфавитном порядке. EXEC SQL DECLARE sinfo CURSOR FOR SELECT S.sname FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ ORDER BY S.sname online.mirea.ru Центр дистанционного обучения Пример кода вложенного SQL с курсором char SQLSTATE[6]; EXEC SQL BEGIN DECLARE SECTION char c_sname[20]; short c_minrating; float c_age; EXEC SQL END DECLARE SECTION c_minrating = random(); EXEC SQL DECLARE sinfo CURSOR FOR SELECT S.sname, S.age FROM Sailors S WHERE S.rating > :c_minrating ORDER BY S.sname; do { EXEC SQL FETCH sinfo INTO :c_sname, :c_age; printf(“%s is %d years old\n”, c_sname, c_age); } while (SQLSTATE != ‘02000’); EXEC SQL CLOSE sinfo; online.mirea.ru Центр дистанционного обучения Хранимые процедуры • Хранимая процедура это • программа запускаемая SQL командой пользователем • запускается в процедурном пространстве сервера • Процедуры обеспечивают: • обрабатывают входные параметры и возвращают вызывающей программе значения в виде выходных параметров; • содержат программные инструкции, которые выполняют операции в базе данных, включая вызов других процедур; • возвращают значения состояния вызывающей программе, таким образом передавая сведения об успешном или неуспешном завершении (и причины последнего). online.mirea.ru Центр дистанционного обучения Преимущества использования хранимых процедур • Снижение сетевого трафика между клиентами и сервером • Процедура проверяет, какие из процессов и действий могут выполняться, и защищает базовые объекты базы данных. • При вызове процедуры через сеть виден только вызов на выполнение процедуры. • Использование параметров в процедурах помогает предотвратить атаки типа «инъекция SQL». • Процедуры могут быть зашифрованы, что позволяет замаскировать исходный код. • Если какой-то код многократно используется в операции базы данных, то отличным решением будет произвести его инкапсуляцию в процедуры. online.mirea.ru Центр дистанционного обучения Типы хранимых процедур • Пользовательские процедуры. Могут быть созданы в пользовательской базе данных или любых системных базах данных. • Временные процедуры. Один из видов пользовательских процедур. Временные процедуры схожи с постоянными процедурами, за исключением того, что они хранятся в базе данных temp. • Системные функции. Физически они хранятся в специальной системной БД. Эти процедуры используются для планирования предупреждений и заданий. • Расширенные пользовательские процедуры. Процедуры позволяют создавать внешние подпрограммы на языках программирования. Они представляют собой библиотеки, которые могут динамически загружаться и выполняться сервером БД. online.mirea.ru Центр дистанционного обучения Пример создания хранимой процедуры USE AdventureWorks2012; GO CREATE PROCEDURE HumanResources.uspGetEmployeesTest2 @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory WHERE FirstName = @FirstName AND LastName = @LastName AND EndDate IS NULL; GO online.mirea.ru Центр дистанционного обучения Пример запуска хранимой процедуры на исполнение EXECUTE HumanResources.uspGetEmployeesTest2 N'Ackerman', N'Pilar’; -- или EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar'; -- или EXECUTE HumanResources.uspGetEmployeesTest2 @FirstName = N'Pilar', @LastName = N'Ackerman'; online.mirea.ru Центр дистанционного обучения Чтение на дом • Русский Кренке, стр. 559-580. • Изд. ЮРАЙТ (онлайн), В.М. Стасышин – Базы данных: технологии доступа, стр. 9-56, • Английский Кренке, 371-386. • MIT, стр. 185-214. online.mirea.ru Центр дистанционного обучения Вопросы для самостоятельного изучения 1. Перечислите типы курсоров Oracle 2. Поясните смысл использования курсорной инструкции FETCH. Напишите простой синтаксис этой инструкции. 3. Напишите хранимую процедуру для заполнения столбца inStock таблицы WH произвольными значениями. Исходная точка – столбец заполнен значениями NULL. Всего имеется 37 строк. 4. В чем принципиальное отличие OLE DB от ODBC драйвера? online.mirea.ru Центр дистанционного обучения Спасибо за внимание! online.mirea.ru