Слайд 1Основы языка SQL
Создание приложений с использованием запросов на языке SQL
Автор: к.т.н.
Герасимов Н.А.
Слайд 2Занятие 6. Создание приложений с использованием запросов на языке SQL.
Тема занятия
6: Знакомство с приемами использования запросов, построенных на языке SQL, в приложениях, созданных в Visual Basic (или Visual Basic for Application), в VBScript и Active Server Page.
Слайд 3ADO Компоненты
Одним из популярных способов организации доступа к базам данных из
приложений является использование библиотек объектов ADO ( ActiveX Data Object), которые используют драйверы OLEDB.
Фактически библиотека компонент OLEDB представляет собой открытый стандарт универсально интерфейса доступа к базам данных. Причем имеется ввиду, не только реляционные базы данных, но и не реляционные (например, многомерные хранилища, базы почтовых сообщений, базы данных на мэйнфреймах и т.п.)
Слайд 4Архитектура объектов ADO.Net
Слайд 5Технология OLE DB
OLE DB – это технология доступа к данным, основанная
на модели COM (Component Model Object), которая используется для разработки приложений под Windows.
ODBC – является надстройкой над драйверами OLEDB и позволяет создать более абстрактный уровень доступа к данным
Слайд 6Структура компонент OLEDB
Компоненты OLEDB состоят из трех элементов: провайдера (provider),
потребителя (consumer) и служебного элемента, выполняющего обработку и передачу данных. Задача провайдера обеспечить интерфейс OLEDB с помощью специального низкоуровневой программы (драйвера).
В качестве потребителя OLEDB могут выступать компоненты службы ODBC, которые используют драйверы для доступа к реляционным базам данных (см. рис.1).
Драйверы разрабатываются с ориентации на конкретную СУБД. На практике в клиентских приложениях часто используются следующие драйверы:
Драйверы для связи с базами данных, которые созданы в среде Access:
- Microsoft Jet 3.51 OLE DB Provider, собственный провайдер для баз данных с базами Access 7.0 и боле ранних версий.
- Microsoft Jet 4.0 OLE DB Provider, собственный провайдер для баз данных с базами Access 2000 и боле ранних версий.
Слайд 7Драйверы для SQL Server
Драйверы, которые работают с СУБД SQL Server :
-
Microsoft OLE DB Provider for SQL Server, собственный провайдер для сервера SQL 6.х и более поздних версий.
В роли потребителя могут выступать ADO-компоненты, как показано на рис.1. выше. Модель ADO представляет собой набор объектов, использование которых в приложениях значительно упрощают доступ к информации в базах данных.
Слайд 8Важное свойство ADO компонент
Интерфейс ADO доступен как из клиентских , так
и из серверных приложений. ADO компоненты используются в языках VB (VBA), так в VBScript, JavaScript, Python и др.
Поэтому, отладив приложение в локальном режиме, его легко можно перенести в серверную систему. Например, создав VBА-программу расчета с доступом к базе данных Access, затем текст этой программы можно перенести в скрипт языка VBScript, который реализует аналогичный расчет в Web-среде.
Слайд 9Преимущества ADO технологии
Это позволяет создавать удобный и эффективный Web-интерфейс с базой
данных без утомительных разработок на языках типа C++.
Разработка приложений к базам данных становится доступной не только программистам, но и специалистам в прикладных областях (например, экономистам, социологам, юристам и т.п.)
Слайд 106.1. Приложение в средe VBA
Разработка клиентского приложения в VBA (или VB)
начинается с подключения библиотек ADO к проекту. Для этого необходимо создать новую книгу в Excel, войти в редактор проектов VBA (через клавиши Alt+F11) и выполнить команду Tools→References, затем в окне списка библиотек компонентов выбрать библиотеку ActiveX Data Objects 2.Library.
Слайд 11Способы задания ADO объектов в VB (VBA) и VBScript (JavaScript)
Определение новых
объектов:
DIM obj AS New ADODB.Connection
DIM rs AS New ADODB.Recordset
Создание объектов с помощью оператора Set
Set obj=CreateObject(“ADODB.Connection”)
Set rs=CreateObject(“ADODB.Recordset)
Слайд 12Создание проекта VBA
Создадим проект, в котором данные из базы данных Access
(например,avtoshop.mdb) читаются по SQL-запросу и переносятся на рабочий лист книги (например, на Лист1).
Создадим модуль (командой Insert→Module) и на модуле разработаем процедуру READ_DB(), которая будет состоять из следующих блоков:
Блок 1 – создание объекта для коннекции с базой данных;
Блок 2 – создание объекта набора записей, соответствующих SQL-запросу;
Блок 3 – вывод набора записей на рабочий лист.
Разметим строками комментариев процедуру READ_DB(), как показано на рис. 2.
Слайд 13Блок схема процедуры READ_DB(),
Блок 1
создание объекта для коннекции с базой
данных
Блок 2
создание объекта набора записей, соответствующих SQL-запросу
Блок 3
вывод набора записей на рабочий лист
Слайд 14Вариант разметки процедуры RERAD_DB()
Слайд 15Реализация блока 1 для связи с базой Access
Для связи с локальной
базой данных Access достаточно задать: тип провайдера (Microsoft.Jet.OLEDB.4.0) и полный путь к базе данных.
' Блок 1 – создание объекта для коннекции с базой данных
Dim obj As New ADODB.Connection
PathDB = " D:\Учебное пособие по SQL В Access\"
NameDb = "avto.mdb"
FullNameDb = PathDB & NameDb
NameProv = "Microsoft.Jet.OLEDB.4.0"
'---------- открытие базы данных -------------
With obj
.Provider = NameProv
.ConnectionString = FullNameDb
.Mode = adModeShareDenyWrite
.Open
End With
'--------------------------------------------------------
Слайд 16Другой способ создания
соединения с БД (через DNS)
Создание через DNS:
-
Сначала создаем имя DNS (например, для БД D:\Учебное пособие по SQL В Access\avto.mdb создадим DNS test_db)
Затем используем DNS-имя для связи с БД
'-Блок 1 Открытие базы данных по DSN---
Set Db = CreateObject("ADODB.Connection")
strConn = "DSN=test_db"
Db.Open strConn, Db
Слайд 17Коннекция к Access через OLEDB
'– Блок 1 - Открытие базы данных
по DSN ---
driver = "{Microsoft Access Driver(*.mdb)}"
namedb = "D:\Базы данных\Лабы\Лаб3\Orders.mdb"
us = "Admin"
psw = " "
'---------
strConn = "driver=" & driver & ";dbq=" & namedb & "; UID=" & us & ";PASSWORD=" & psw
'--------
MsgBox "strConn-->" & strConn
Db.Open strConn, Db
Слайд 19Пример OLEDB для СУБД SQL Server
Для базы данных Orders, которая создана
СУБД SQL Server:
Rem - Блок1– Открытие базы данных по OLEDB
strDriver = "{SQL Server}"
strServer = "localhost"
strUserID = "sa"
strPassword = ""
name_db = "Orders“
strConn = "driver=" & strDriver & ";server=" & strServer & ";database=" & name_db & "; uid=" & strUserID & "; psw=" & strPassword & ";“
MsgBox "strConn-->" & strConn
Db.Open strConn, Db
Слайд 20Реализация Блока 2
Реализация функции Блока 2 требует задания выражения SQL-запроса и
создания объекта типа Recordset. Созданный объект типа RecordSet необходимо связать с объектом Connection (в нашем случае с объектом obj) и передать ему SQL-запрос, который задан в стринговой форме. Положим, что нам надо отобрать все записи, которые удовлетворяют запросу:
SELECT * FROM Customers ;
Слайд 21Текcт программы Блока 2
Dim rs As New ADODB.Recordset
'-------------------------------------
StrSQL =
" SELECT * FROM Customers "
'-----------------------------------------
With rs
Set .ActiveConnection = obj
.Source = StrSQL
.LockType = adLockReadOnly
.CursorLocation = adUseClient
.Open
.MoveFirst
End With
'--------------------------------------------------------
Слайд 22Блок 3
(вывод набора записей на Лист)
'-Блок 3
'-3.1- вывод строки заголовка
---
With Sheets("Лист1")
nstr = 1: k = 1
For Each fl In rs.fields
.Cells(nstr, k).Value = fl.Name : k = k + 1
Next
'-3.2-- вывод строк тела таблицы ---
nstr = 2
Do While (rs.EOF = False)
k = 1
For Each fl In rs.fields
.Cells(nstr, k).Value = fl.Value : k = k + 1
Next
nstr = nstr + 1: rs.movenext
Loop
'-----
End With
MsgBox "Вывод по SQL-запросу окончен"
Слайд 24Вывод набора записей в HTML файл
'—Block 3--------- output table on
HTML page -----------
tt = "
Страница отчета"
tt = tt & "
SQL запрос:" & strSQl & "
"
'---- head of table ----------------
tt = tt & "
"
For Each fl In rs.fields
tt = tt & "" & fl.Name & " | "
Next
tt = tt & "
"
'------- body of table ------
Do While rs.EOF = False
tt = tt & "
"
For Each fl In rs.fields
tt = tt & "" & fl.Value & " | "
Next
'-----
tt = tt & "
"
rs.MoveNext
Loop
'-----
tt = tt & "
"
document.write tt
Слайд 25Пример реализации ADO-технологий в Web-среде
Слайд 26Примеры строк для коннекции
Для СУБД MySQL
strConn = "DRIVER={MySQL ODBC 3.51
Driver};
SERVER=localhost; DATABASE=test;USER=root; PORT=3306;OPTION=3 "
Для СУБД IBM DB2
strConn = "Provider=IBMDADB2.DB2;
Data Source=SAMPLE;User ID=db2admin; password=admin;Location=localhost;"
Слайд 27Результаты по разделу
Модель ADO представляет собой набор объектов, использование которых в
приложениях значительно упрощают доступ к информации в базах данных.
Применение ADO технологии позволяет создавать удобный и эффективный Web-интерфейс с базой данных без утомительного программирования
Используя механизмы DNS и изменяя только стринг коннекции можно создавать приложения практически независимые от СУБД