ADO и SQL Server. Руководство разработчика

ADO и SQL Server. Руководство разработчика.

1. Введение

Очевидно, что доступ к данным является важнейшим требованием при разработке современных бизнес-приложений. Технология ODBC обеспечивает доступ к реляционным базам данных и это первый шаг на пути решения этой проблемы. Однако, когда разработчики хотят включить в свои проекты нереляционные источники данных или работать в средах, подобных Интернет, они сталкиваются с дилеммой - либо разрабатывать собственные парадигмы доступа к данным, либо работать на уровне API, что несовместимо с новыми средами. ActiveX объекты доступа к данным (ADO) решают эту дилемму и обеспечивают единую модель, которая работает со всеми источниками данных в различных средах. Таким образом ADO обеспечивает последовательный, высокопроизводительный доступ к данным, с которыми вы можете создавать клиентские программы для работы с БД или бизнес-объекты среднего уровня, использующие приложения, инструментарий, язык или, даже, Интернет-смотрелку (естественно, Експлорер). ADO - это единый интерфейс доступа к данным, который вам необходим для создания одно- и многоуровневых приложений архитектуры клиент/сервер и Web-ориентированных информационных систем.

2. Обзор ADO

Технология ADO была впервые применена в Microsoft Internet Information Server как интерфейс доступа к БД. Использование ADO позволяет минимизировать сетевой траффик в ключевых Internet-сценариях и уменьшить количество промежуточных уровней между клиентским приложением и источником данных. ADO легко использовать, так как он (или они (объекты) или она (технология)) применяет привычную систему вызовов - интерфейс Автоматизации OLE, доступный сегодня в большинстве средств разработки приложений. Из-за легкости применения и изучения популярность ADO будет расти и в итоге ADO вытеснит технологии RDO и DAO, которые в настоящее время применяются очень широко. Технология ADO во многом подобна RDO и DAO, например, она использует те же соглашения языка. ADO также поддерживает аналогичную семантику и поэтому может быть легко освоена разработчиками ПО.

ADO является интерфейсом программного уровня к OLE DB, новейшей и мощнейшей (сильно сказано) парадигме доступа к данным от MS. OLE DB обеспечивает высокопроизводительный доступ ко многим источникам данных. ADO и OLE DB вместе представляют собой основу стратегии Универсального доступа к данным (Universal Data Access). OLE DB дает возможность универсального доступа ко многим данным и представляет разработчикам возможность сделать это достаточно легко. Так как ADO находится на вершине OLE DB, то применение ADO имеет все преемущества Универсального доступа к данным, которое обеспечивает OLE DB.

3. Обзор OLE DB

OLE DB - это открытая спецификация, разработанная на основе успеха спецификации ODBC и обеспечивает открытый стандарт доступа ко всем видам данным в системах масштаба предприятия. OLE DB - это ядро технологии поддерживающей Универсальный доступ к данным. В отличие от технологии ODBC, которая была создана для доступа к реляционным БД, технология OLE DB разработана для реляционных и нереляционных источников данных, таких как хранилища почты (mail stores), текстов и графики для Web, службы каталогов (directory services), IMS и VSAM хранилищ данных на мэйнфреймах.

Компоненты OLE DB состоят из провайдеров данных (data providers), которые представляют свои данные, потребителей данных (data consumers), которые используют данные, и сервисных компонент (service components), которые обрабатывают и транспортируют данные (например, процессор запросов и механизм курсоров). OLE DB включает в себя мост с ODBC, чтобы дать возможность разработчикам использовать ODBC-драйвера реляционных БД, широко распространенные в настоящее время.

OLE DB провайдеры

Существует два типа OLE DB приложений: потребители и провайдеры. Потребителями могут быть любые приложения, которые используют OLE DB интерфейсы. Например, Visual C++ приложение, которое использует OLE DB интерфейсы для связи с сервером БД - это OLE DB потребитель. Объектная модель ADO, которое использует OLE DB интерфейсы, - это тоже OLE DB потребитель. Любое приложение, которое использует ADO, косвенно использует OLE DB интерфейсы через объекты ADO.

OLE DB провайдер осуществляет OLE DB интерфейсы, поэтому, OLE DB провайдер дает возможность потребителям иметь доступ к данным единообразным способом через ряд документированных интерфейсов. В этом смысле OLE DB провайдер подобен ODBC драйверу, который обеспечивает универсальный механизм доступа к реляционным БД, но только для нереляционных типов данных. Более того, OLE DB провайдер встроен в вершину OLE COM интерфейсов, что придает ему большую гибкость, а ODBC драйвер встроен в вершину C API спецификации.

Microsoft OLE DB SDK version 1.1 поставляет два OLE DB провайдера: ODBC провайдер и провайдер текстов. Провайдер текстов служит примером, который демонстрирует подробную реализацию OLE DB провайдера. ODBC провайдер - это OLE DB провайдер для ODBC драйверов. Этот провайдер предоставляет механизм для потребителей, чтобы использовать существующие ODBC драйверы без необходимости срочной замены существующих ODBC драйверов на новые OLE DB провайдеры. Больше информации про OLE DB и OLE DB провайдеры можно посмотреть на http://www.microsoft.com/data в разделе OLE DB.

ODBC Провайдеры

ODBC провадер устанавливает соответствие между OLE DB интерфейсами и ODBC API. С ODBC провадером OLE DB потребители могут связываться с сервером БД через существующие ODBC драйверы. Потребитель вызывает OLE DB интерфейс через ODBC провайдера. ODBC провайдер вазывает соответствующие ODBC API иннтерфейсы и посылает запросы к ODBC драйверу. Целью разработки ODBC провайдера является осуществление всей функциональности менеджера ODBC драйвера. Поэтому теперь нет необходимости в менеджере ODBC драйвера. Однако при использовании ODBC провайдером версии 1.1 менеджер ODBC драйвера все еще требуется для поддержки связи с ODBC приложениями.

4. Объектная модель ADO

Объектная модель ADO определяет набор (коллекцию) программируемых объектов, которые могут использоваться с Visual Basic, Visual C++, VBScripting, Java на любой платформе, которая поддерживает COM и Автоматизацию OLE. Объектная модель ADO разработана для выполнения большинства особенностей OLE DB.

ADO содержит семь объектов:

* Connection

* Command

* Parameter

* Recordset

* Field

* Property

* Eror

и четыре набора объектов (коллекции):

* Fields

* Properties

* Parameters

* Errors

Коллекция Properties и объект Property доступны через объекты Connection, Recordset и Command. Коллекция Properties и объект Property содержат свойства, которые могут быть доступны только для чтения или для чтения-записи.

Объекты Connection, Recordset и Command являются ключевыми объектами в объектной модели ADO. ADO приложение может использовать объект Connection для установки соединения с сервером БД, объект Command - для выдаче команды к БД, таких как запросы, обновления и т.п. и объект Recordset - для просмотра и манипулирования данными. Командный язык, используемый с объектом Command, зависит от провайдера для БД. В случае реляционных баз данных в качестве командного языка выступает SQL.

Объект Command может не использоваться в случае, если OLE DB провайдер не обеспечивает выполнение интерфейса Icommand. Как как OLE DB провайдер может находиться на вершине реляционных или нереляционных БД, то традиционные SQL-операторы для запроса данных могут быть недоступны для нереляционной БД и поэтому объект Command не потребуется. Если объект Command содержит параметры, то информацию о них можно просмотреть или определить через набор объектов Parameters и объект Parameter. Объект Parameter описывает информацию о параметрах для объекта Command.

В ADO версий 1.0 и 1.5 все объекты могут быть созданы, за исключением объектов Error, Field и Property. К набору объектов Errors и объекту Error можно получить доступ через объект Connection после того как случилась ошибка провайдера. К набору объектов Fields и объекту Field можно получить доступ через объект Recordset после того как в объекте Recordset появятся какие-либо данные. Информация о метаданных объекта Recordset может быть просмотрена через набор объектов Fields и объект Field.

5. Объект Connection

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

Основной OLE DB провайдер, используемый для соединения, не ограничивает использование других ODBC провайдеров. Другие провайдеры так же могут использоваться для соединения. Провайдер определяется посредством установки свойства Provider. Если это свойство не определено, то по умолчанию будет использован провайдер MSDASQL.

Метод Open объекта Connection используется для установки соединения. С ODBC провайдером, приложение ADO может использовать механизм ODBC соединения для подключения к серверу БД. ODBC позволяет приложениям устанавливать соединение через источники данных ODBC или явно определять источник данных (DSN-Less connection).

Перед соединением приложение может установить строку соединения, тайм-аут соединения, БД по умолчанию и атрибуты соединения. Объект Connection так же позволяет установить свойство CommandTimeout для всех командных объектов, связвнных с данным соединением. Запросы могу выполняться с использованием метода Execute.

Через объект Connection можно управлять транкзациями. Для этого у него есть методы BeginTrans, CommitTrans и RollbackTrans.

Следующий пример показывает использование ODBC провайдера - по умолчанию OLE DB провайдера в ADO, для подсоединения к SQL Server:

Dim Cn As New ADODB.Connection
Cn.ConnectionTimeout = 100
' DSN connection
#Cn.Open "pubs", "sa"
' DSN-Less connection for SQL Server
' Cn.Open 'Driver={SQL Server};Server=Server1;Uid=sa;Pwd=;Database=pubs'
Cn.Close

В примере сначала устанавливается тайм-аут в 100 сек, затем открываетсясоединение используя ODBC источник данных - pubs, который указывает на SQL Server. Для SQL Server требуется указать идентификатор пользователя (user ID), поэтому sa - это второй параметр метода Open. Пароля нет, поэтому и третьего параметра тоже нет.

Этот пример так же содержит закоментированную строку, которая показывает как присоединиться к SQL Server без источника данных ODBC. Для связи с SQL Server, называемом Server1, используется ODBC драйвер для SQL Server {SQL Server}. Идентификатор пользователя - sa и пароля нет. БД по умолчанию для этого соединения - pubs.

Следующий пример показывает использование свойства Provider для определения альтернативного OLE DB провайдера.

Dim Cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Cn.Provider = "sampprov"
Cn.Open "data source=c:\sdks\oledbsdk\samples\sampclnt\"
Set rs = Cn.Execute("customer.csv")

В этом примере определяется OLE DB провайдер - sampprov. Вместе с ODBC провайдером OLE DB SDK поставляется с текстовым провайдером. Провайдер текста позволяет приложению получать данные из текстового файла. В этом примере соединение устанавливается определением каталога источника данных c:\sdks\oledbsdk\samples\sampleclnt\ и данными в файле customer.csv, получаемыми в результате выполнения метода Execute.

Следующий пример демонстрирует использование методов BeginTrans, CommitTrans и RollbackTrans:

Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
' Open connection.
Cn.Open "pubs", "sa"
' Open titles table.
rs.Open "Select * From titles", Cn, adOpenDynamic, adLockPessimistic
Cn.BeginTrans
' <>
Cn.CommitTrans
' or rollback
' cn.RollbackTrans
Cn.Close

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

Набор объектов Errors объект Error

Набор объектов Errors и объект Error позволяют получить информацию о случившейся ошибке провайдера. Ошибки могут генерироваться вызовами методов или свойств объектов Connection, Command или Recordset, но всегда получаются из объекта Connection. Набор объектов Errors не существует сам по себе. Он зависит от объекта Connection и ошибок, лежащих ниже ADO (OLE DB провайдеры, ODBC драйверы и источники данных), которые помещаются в набор объектов Errors. Недопустимые значения свойств или ADO интерфейсов не добавляют объекты Error в набор объектов Errors.

В наборе объектов Errors также запоминаются предупреждения (warnings). Предупреждения, в отличие от ошибок, не вызывают остановки выполнения кода. Объект Error позволяет получить описание и источник ошибки. При работе с ODBC провайдером из Error так же доступна информация об SQLSTATE и собственных ошибках БД (database-native error).

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

Dim Cn As New ADODB.Connection
Dim Errs1 As ADODB.Errors
Dim rs As New ADODB.Recordset
Dim i As Integer
Dim StrTmp
On Error GoTo AdoError
Cn.Open "pubs", "sa"
Set rs = Cn.Execute("Select * From TableDoesnotExist")
Done:
' Close all open objects.
Cn.Close
' Destroy anything not destroyed yet.
Set Cn = Nothing
' We're outta here.
Exit Sub
AdoError:
Dim errLoop As Error
Dim strError As String
' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next
GoTo Done

Соединение с SQL Server устанавливается через источник данных - pubs. Запрос select * from TableDoesnotExist выполняется с помощью метода Execute объекта Connection. Так как в запросе выполняется попытка получить записи из несуществующей таблицы, то возникает ошибка. После возникновения ошибки выполнение программы переходит на метку AdoError. Далее из объекта Connection получается набор объектов Errors и связывается с набором объектов Errs1. Затем в цикле в окно отладчика выводится информация об ошибке.

6. Объект Command

Объект Command позволяет передавать команды к БД. Эти команды могут готовить строки запросов и связянные с запросами параметры, но не ограничиваются только запросами. Командный язык и его особенности зависят от конкретного провайдера БД. Здесь содержится информация и примеры для ODBC провайдера от Microsoft, который поддерживает достаточно широкий диапазон реляционных БД. Более подробно про OLE DB и OLE DB провайдеров можно почитать на сайте http://www.microsoft.com/data в разделе OLE DB.

Объект Command может как открывать новое соединение, так и использовать уже существующее соединение для выполнения запросов, в зависимости от установки свойства ActiveConnection. Если свойство ActiveConnection установлено с ссылкой на объект Connection, то объект Command будет использовать существующее соединение из объекта Connection. Если свойство ActiveConnection определяется строкой соединения, то для объекта Command будет установлено новое соединение. Для однго объекта Connection могут использоваться несколько объектов Command.

Ваполнение запросов может генерировать набор записей, множественные наборы записей или не генерировать никаких записей. Например, выполнение запросов языка описания данных (data definition language - DDL) не генерирует наборов записей. Выполнение оператора SELECT может генерировать набор записей и выполнение пакета SELECT операторов или хранимой процедуры генерирует более чем один набор записей.

Строка запроса определяется свойством CommandText. Строка запроса может быть определена на стандартном языке манипулирования данными (data manipulation language - DML) с использованием SELECT, INSERT, DELETE, или UPDATE операторов или на языке описания данных, например CREATE или DROP. В строке запроса может также буть имя хранимой процедуры или таблицы.

Тип строки запроса определяется свойством CommandType. Значения свойства CommandType могут быть следующими: adCmdText, adCmdTable, adCmdStoreProc и adCmdUnknown. Когда строкой запроса является SQL оператор, свойство CommandType должно определяться как adCmdText. Значения adCmdStoreProc или adCmdTable применяются, если строка запроса определяется как хранимая процудура или имя таблицы.

Если определяется значение adCmdStoredProc, то объект Command выполняет строку запроса с синтаксисом {call procedure=name}. Если определяется значение adCmdTable, то объект Command выполняет строку запроса с синтаксисом select * from tablename. Если определяется значение adCmdUnknown, то объект Command должен выполнить дополнительные действаия по определению типа запроса, что уменьшает производительность системы.

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

Свойство Prepared также может быть полезным при неоднократном выполнении запросов с параметрами. Разные значения параметров будут подставляться при каждом выполнении запроса, вместо полной перестройки строки запроса. Объект Parameter может быть создан с помощью метода CreateParameter. Более подробно об этом написано в разделе "Использование подготовленных операторов".

В следующем примере показано выполнение оператора SELECT, которое возвращает объект Recordset.

Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
' Use a connection string or a Connection object.
Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "select * from titles"
Cmd.CommandTimeout = 15
Cmd.CommandType = adCmdText
Set rs = Cmd.Execute()
rs.Close

Свойство ActiveConnection определяется как строка ODBC соединения - DSN=pubs;UID=sa ODBC. Оператор select * from titles определяется в свойстве CommandText и тек как это SQL оператор, то свойство CommandType устанавливается в adCmdText. Затем устанавливается время ожидания в 15 сек. В результате выполнения запроса возвращается объект Recordset, который связывается с объектом rs.

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

Синтаксис хранимой процедуры следующий:.

drop proc myADOProc

go

create proc myADOProc As

create table #tmpADO (id int Not Null, name char(10) Not Null)

insert into #tmpADO values(1, 'test')

Хранимая процедура myADOProc создает временную таблицу tmpADO и вставляет строку в эту таблицу. Операторы CREATE и INSERT не генерируют результатов и поэтому при выполнение хранимой процедуры myADOProc не возвращается никаких результатов.

Visual Basic код, вызывающий процедуру myADOProc, имеет следующий вид:

Dim Cmd As New ADODB.Command
' Use a connection string or a Connection object.
Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "myADOProc"
Cmd.CommandTimeout = 15
Cmd.CommandType = adCmdStoredProc
Cmd.Execute

Хранимая процедура myADOProc определяется свойством CommandText. Свойство CommandType устанавливается в adCmdStoredProc для определения типа выполняемого объекта и в результате генерируется оператор {call myADOProc}. Так как в результате выполнения хранимой процедуры не возвращается никаких данных, то и нет привязки результатов выполнения процедуры к объекту Recordset.

Набор объектов Parameters и объект Parameter

Набор объектов Parameters обеспечивает объект Command информацией о параметрах и данных. Набор объектов Parameters состоит из объектов Parameter. И набор объектов Parameters и объект Parameter требуются только в том случае, если строка запроса в объекте Command требует параметров. Информация об индивидуальных параметрах (например, размер, тип данных, направление и значение) может быть считана или записана для каждого объекта Parameter. Существует четыре типа параметров направления: входной (input), выходной (output), входной и выходной (input and output) и возвращенное значение (return value). Объект Parameter может служить как входной параметр, как выходной параметр, который содержит данные, и как возвращенное значение хранимой процедуры. Применение метода Refresh коллекции параметров может заставить провайдера обновить информацию о параметрах, однако эта процедура потребует дополнительного времени.

При работе с значительными по размеру типами данных для записи части данным можно использовать метод AppendChunk. Дополнительная информация об этом содержится в разделе "Использование больших типов данных"

Следующий пример демонстрирует создание параметров для хранимой процедуры. Хранимая процедура имеет следующий синтаксис:

drop proc myADOParaProc 
go
create proc myADOParaProc 
@type char(12) 
As 
Select * from titles where Type = @type

Процедура myADOParaProc берет один @type входной параметр и возвращает данные, которые соответствуют определенному типу. Типом данных для параметра @type является тип character, который имеет длину 12.

Код Visual Basic, вызывающий эту хранимую процедуру, имеет следующий вид:

Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim prm As ADODB.Parameter
' Define a Command object for a stored procedure.
cmd.ActiveConnection = "DSN=pubs;uid=sa"
cmd.CommandText = "myADOParaProc"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 15
' Set up new parameter for the stored procedure.
Set prm = Cmd.CreateParameter("Type", adChar, adParamInput, 12, "Business")
Cmd.Parameters.Append prm
' Create a record set by executing the command.
Set rs = Cmd.Execute
While (Not rs.EOF)
Debug.Print rs(0)
rs.MoveNext
Wend

Свойства ActiveConnection, CommandText, CommandType, и CommandTimeout определяются так же, как и в предыдущем примере. Процедура myADOParaPro ожидает входного параметра, который имеет тип character и размер 12. Метод CreateParameter используется для создания объекта Parameter с соответствующими характеристиками: тип данных - adChar для символьных данных, тип параметра - adParamInput для входного параметра и длина - 12. Объект Parameter так же определяется именем Type и так как это входной параметр, то определяеся значение Business. После того, как параметр определен, с помощью метода Append он добавляется к набору объектов Parameters. Затем выполняется хранимая процедура и результат возвращается в объект Recordset.

7. Объект Recordset

Объект Recordset обеспечивает методы для манипулирования наборами данных. Объект Recordset позволяет добавлять, удалять, обновлять записи и перемещаться по записям внаборе данных. С помощью набора объектов Fields и объекта Field можно получить доступ к любой конкретной записи. Обновление объекта Recordset может быть сделано немедленно или в пакетном режиме. При создании объекта Recordset автоматически открывается курсор.

Объект Recordset позволяет определить тип курсора иего расположение для выбора результирующего набора данных. Используя свойство CursorType можно определить тип курсора: forward-only, static, keyset-driven, или dynamic. Тип курсора определяет поведение объекта Recordset при прокрутке записей вперед/назад или при обновлении записей. Тип курсора также влияет на видимость измененных записей.

По умолчанию тип курсора устанавливается в forward-only только для чтения. Если необходимо только читать данные в однонаправленном режиме (forward), то изменять тип курсора не следует. С другой стороны, можно определить тип курсора в зависимости от решаемой задачи.

С помощью свойства CursorLocation можно определить будет ли курсор серверным или клиентским. Расположение курсора играет большую роль при использовании несвязанных наборов записей. Подробнее об этом написано в разделе "Использование серверных курсоров". Объект Recordset может быть создан при выполнении метода Execute объекта Connection или Command.

Следующий пример показывает использование объекта для открытия соединения и получения результирующего набора данных:

Dim rs As New ADODB.Recordset
rs.Open "select * from titles", "DSN=pubs;UID=sa"
While (Not rs.EOF)
Debug.Print rs(0)
rs.MoveNext
Wend
rs.Close

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

Набор объектов Fields и объект Field

Набор объектов Fields и объект Field позволяют получить доступ к данным любой колонки в текущей записи. К набору объектов Fields можно получить доступ через объект Recordset. К объекту Field можно получить доступ через набор объектов Fields используя индекс. Объект Field можно использовать для составления новой записи или для изменения уже существующих данных и затем использовать методы AddNew, Update или UpdateBatch объекта Recordset для вставки новых или изменения существующих данных.

В отличи от RDO, в ADO нет метода Edit. Обновление объекта Field приводит к изменению данных и поэтому не требуется явных методов для редактирования данных.

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

Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
rs.Open "select * from titles", "DSN=pubs;UID=sa"
Set Flds = rs.Fields
Dim TotalCount As Integer
TotalCount = Flds.Count
i = 0
For Each fld In Flds
Debug.Print fld.Name
Debug.Print fld.Type
Debug.Print fld.Value
Next
rs.Close

После создания объекта Recordset в результате выполнения запроса select * from titles, возвращается набор объектов Fields. Затем в цикле для каждого объекта Field из набора объектов Fields выводятся на печать свойства Name, Type и Value.

Набор объектов Properties и объект Property

Набор объектов Properties и объект Property обеспечивают информацию о параметрах объектов Connection, Command, Recordset и Field. К набору объектов Properties можно получить доступ через объекты Connection, Command, Recordset и Field. К объекту Property можно получить доступ через набор объектов Properties используя индекс.

Набор объектов Properties состоит из объектов Property. Кроме значения и типа свойства объект Property позволяет получить доступ и к атрибутам свойства. Атрибуты описывают такие вещи, как будет или нет поддерживаться определенное свойство объекта или оно может быть доступно только для чтения или записи. Например, свойство ConnectionTimeout обеспечивает данные о том, сколько секунд будет длиться ожидание при установке соединения перед тем как будет сгенерирована ошибка завершения времени.

Следующий пример демонстрирует возвращение свойств ConnectionTimeout, CommandTimeout и Updatability:

Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Cn.Open "pubs", "sa"
' Find out ConnectionTimeout property.
Debug.Print Cn.Properties("Connect Timeout")
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "titles"
Cmd.CommandType = adCmdTable
Set rs = Cmd.Execute()
' find out CommandTimeout property.
Debug.Print Cmd.Properties("Command Time out")
Debug.Print rs.Properties("Updatability")

В этом примере набор объектов Properties возвращается из объектов Connection, Command и Recordset. Свойство ConnectionTimeout объекта Connection выводится на печать. Затем аналогичные шаги выполняются для объектов Command и Recordset.

8. Продвинутые возможности ADO

8.1. Использование языка определения данных (Data Definition Language - DDL)

Язык определения данных - это такие SQL операторы, которые поддерживают определения или объявления объектов БД, например CREATE TABLE, DROP TABLE или ALTER TABLE. Выполнение запросов DDL не генерирует никаких данных и, поэтому, нет необходимости использовать объект Recordset. Для выполнения запросов DDL идеальным является объект Command. Чтобы отличить запросы DDL от имен хранимых процедур или таблиц, свойство CommandType объекта Command должно быть установлено в adCmdText.

SQL Server обеспечивает ряд опций выполнения запросов, которые могут быть установлены оператором SET. Эти SET опции не генерируют никаких результирующих наборов данных и, поэтому они могут рассматриваться в обном ряду с запросами DDL.

Следующий пример показывает использование объекта Command для отключения опции SET NOCOUNT оператора SET SQL Server Transact-SQL - SET:

Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
' If the ADOTestTable does not exist
On Error GoTo AdoError
Cn.Open "pubs", "sa"
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "drop table ADOTestTable"
Cmd.CommandType = adCmdText
Cmd.Execute
Done:
Cmd.CommandText = "set nocount on"
Cmd.Execute
Cmd.CommandText = "create table ADOTestTable (id int, name char(100))"
Cmd.Execute
Cmd.CommandText = "insert into ADOTestTable values(1, 'Jane Doe')"
Cmd.Execute
Cn.Close
Exit Sub
AdoError:
Dim errLoop As Error
Dim strError As String
' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next
GoTo Done
End Sub

Этот пример сбрасывает таблицу, открывает таблицу и вставляет данные в таблицу с использованием метода Execute объекта Command. Для этого типа запросов не создаются объекты Recordset. Таблица ADOTestTable может не существовать в БД и поэтому выполнение drop table ADOTestTable может генерировать ошибку, которая покажет, что таблица не существует. Для обработки такой ситуации предусмотрена обработка ошибок. Затем выполняется установка опции set - set nocount on.

8.2. Использование подготовленных (Prepared) операторов

Запросы могут быть подготовлены перед их выполнением или могут быть непосредственно выполнены. Свойство Prepared объекта Command позволяет определить будет запрос подготавливаться или нет.

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

Свойство Prepared может также использоваться при выполнении запросов со многими параметрами. Приложение может выполнять запросы с параметрами более чкм один раз с подстановкой разных наборов параметров при каждом выполнении запроса вместо полной перестройки строки запроса всякий раз при изменении параметров. Однако, если запрос с параметрами выполняется один раз, то нет необходимости в его предварительной подготовке.

SQL Server не поддерживает непосредственно модель Prepare/Execute для ODBC. Когда оператор подготавливается, ODBC драйвер SQL server создает временную хранимую процедуру для этого оператора. Эта временная хранимая процедура существует в tempdb и не сбрасывается до тех пор, пока не закроются объекты Recordset или Connection.

Эта опция может быть отключена в диалоге SQL Server ODBC Data Source Setup если для подсоединения к SQL Server используется источник данных ODBC. Если эта опция отключена, то каждый раз при выполнении запроса SQL оператор сначала запоминается и затем посылается к серверу на выполнение.

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

Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim prm1 As New ADODB.Parameter
Dim prm2 As New ADODB.Parameter
Cn.Open "DSN=pubs", "sa"
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "update titles set type=? where title_id=?"
Cmd.CommandType = adCmdText
Cmd.Prepared = True
Set prm1 = Cmd.CreateParameter("Type", adChar, adParamInput, 12, "New Bus")
Cmd.Parameters.Append prm1
Set prm2 = Cmd.CreateParameter("Title_id", adChar, adParamInput, 6, "BU7832")
Cmd.Parameters.Append prm2
Cmd.Execute
Cmd("Type") = "New Cook"
Cmd("title_id") = "TC7777"
Cmd.Execute
Cmd("Type") = "Cook"
Cmd("title_id") = "TC7778"
Cmd.Execute
Cn.Close

Этот пример обновляетданные в таблице titles с использованием разных значений параметров. Строка запроса подготавливается так, чтобы при его выполнении могли применяться разные наборы параметров. Для операции обновления требуются два параметра: type и title_id. Они создаются с помощью метода CreateParameters и добавляются к набору объектов Parameters с помощью метода Append.

Первый набор параметров имеет значения New Bus и BU7832. Другие значения параметров могут использоваться перед выполнением метода Execute без перестройки строки запроса, так как свойство Prepared установлено в TRUE.

8.3. Выполнение хранимых процедур

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

Когда в объекте Command выполняется хранимая процедура , свойство CommandType должно быть определено как adCmdStoredProc. При таком определениии свойства CommandType генерируется соответствующий оператор SQL для основного провайдера. Для ODBC провайдера генерируются ODBC escape-последовательности для вызовов процедур {[?=]call procedure-name[([parameter][,[parameter]]...)]} и ODBC драйвер SQL Server оптимизирует запрос, чтобы использовать все преемущества этих последовательностей.

Следующий пример показывает выполнение хранимой процедуры sp_who:

Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Cmd.ActiveConnection = "DSN=pubs;uid=sa"
Cmd.CommandText = "sp_who"
Cmd.CommandType = adCmdStoredProc
Set rs = Cmd.Execute()
Debug.Print rs(0)
rs.Close

Для оптимальной производительности приложение никогда не должно подготавливать хранимые процедуры SQL Server. Иначе возникнут дополнительные накладные расходы при создании временных хранимых процедур.

Коды возврата и выходные параметры хранимых процедур

Хранимые процедуры могут содержать входные и выходные параметры и возвращать значения. Например, следующая хранимая процедура myProc содержит выходной параметр @ioparm и возвращает значение 99.

CREATE PROCEDURE myProc @ioparm int Output As
Select name FROM sysusers WHERE uid < 2
Select @ioparm = 88
Return 99

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

ADO следующий:

Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim param As Parameter
Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "myproc"
Cmd.CommandType = adCmdStoredProc
' Set up parameters.
Set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, , 0)
Cmd.Parameters.Append param
Set param = Cmd.CreateParameter("Output", adInteger, adParamOutput, , 0)
Cmd.Parameters.Append param
Set rs = Cmd.Execute
If Not rs.EOF And Not rs.BOF Then
Debug.Print rs(0)
rs.Close
End If
Debug.Print Cmd(0) ' The return code
Debug.Print Cmd(1) ' The Output parameter

Для хранимой процедуры myProc требуются два параметра: выходной параметр для сохранения возвращенного значения и выходной параметр @ioparam. В этом примере сначала создаются два параметра: Return и Output. Параметр Return создается как тип adParamReturnValue и имеет тип данных - adInteger, что соответствует integer. Параметр Output - adParamReturnValue для выходного параметра и тоже имеет тип данных integer. Так как оба параметра имеют тип integer, то нет необходимости определять длину данных.

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

8.4. Использование пакетного обновления (Batch Updating)

Метод Update объекта Recordset позволяет обновить текущую запись. Метод UpdateBatch позволяет применить все ожидающие новые, обновленные и удаленные записи к объекту Recordset. Используя LockType adLockBatchOptimistic, метод UpdateBatch позволяет выполнить все изменения, находящиеся в состоянии ожидания, на клиентском компьютере и одновременно послать все эти изменения серверу БД. Изменения, находящиеся в состоянии ожидания, могут быть отменены с помощью метода CancelBatch.

Только если все изменения в БД при выполнении метода UpdateBatch будут неудачными, будет возвращена ошибка. Если же только некоторые изменения будут неудачными, то будет возвращено предупреждение.

В SQL Server метод UpdateBatch является допустимым только, когда свойство LockType установлено в adLockBatchOptimistic и тип курсора - либо keyset-driven, либо static. Курсор типа keyset-driven может открываться с таблицами, имеющими уникальные индексы.

Следующий пример демонстрирует использование метода UpdateBatch:

Dim rs As New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockBatchOptimistic
rs.Open "select * from titles", "DSN=pubs;uid=sa"
' Change the type for a specified title.
While (Not rs.EOF)
If Trim(rs("Type")) = "trad_cook" Then
rs("Type") = "Cook"
End If
rs.MoveNext
Wend
rs.UpdateBatch
rs.Close

В этом примере сохдается набор записей используя курсор типа keyset-driven со свойством LockType установленным в adLockBatchOptimistic. После создания объекта Recordset, тип trad-cook изменяется на Cook, новый тип для всех записей в таблице title. После выполнения всех изменений, сделанные изменения данных подтверждаются применением метода UpdateBatch.

8.5. Генерация нескольких наборов записей

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

Когда генерируются множественные наборы записей, важно иметь возможность последовательно выбирать наборы записей до тех пор, пока наборы записей являются доступными. Метод NextRecordset объекта Recordset позволяет выбрать любые последующие наборы записей. Если нет больше доступных наборов записей, то возвращенный объект Recordset будет установлен в Nothing.

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

Синтаксис хранимой процедуры следующий:

drop proc myNextproc
go
create proc myNextproc As
Select * from titles
Select * from publishers

Эта хранимая процедура генерирует два набора записей: один - в результате выполнения запроса Select * from titles, другой - в результате выполнения запроса Select * from publishers.

ADO код имеет следующий вид:

Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset 
Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "myNextProc"
Cmd.CommandType = adCmdStoredProc
Set rs = Cmd.Execute()
While Not rs Is Nothing
If (Not rs.EOF) Then
Debug.Print rs(0)
End If
Set rs = rs.NextRecordset()
Wend

После выполнения хранимой процедуры myNextProc создается объект Recordset. Так как в результате выполнения процедуры myNextProc создается два набора записей, каждые объект Recordset может быть получен при помощи метода NextRecordset.

8.6. Использование серверных курсоров

SQL Server обеспечивает целый ряд типов серверных курсоров для использования в приложениях. По умолчанию ADO приложение не использует серверных курсоров когда вместе с SQL Server используется ODBC провайдер. По умолчанию ADO приложение использует несерверный курсор типа forward и только для чтения.

Серверные курсоры полезны при обновлении, вставке или удалении записей. Серверные курсоры также позволяют иметь множественные активные операторы (active statements) при одном соединении. SQL Server намеренно не позволяет множественных активных операторов на соединение, пока используются серверные курсоры. Активный оператор предназначен для того, чтобы в обработке оператора существовало несколько незавершенных результатов (pending results). Если серверные курсоры не используются и приложение пытается иметь более одного активного оператора, то приложение получит сообщение об ошибке "Connection Busy with Another Active Statement".

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

* Установить тип курсора отличный от устанавливаемого по умолчанию при помощи метода RecordsetOpen. По умолчанию тип курсора устанавливается в adOpenForwardOnly и изменение типа на adOpenKeyset, adOpenDynamic или adOpenStatic приведет к использованию серверного курсора.

* Установить LockType отличный от устанавливаемого по умолчанию при помощи метода RecordsetOpen. По умолчанию LockType установлен в adLockReadOnly и любое изменение его на adLockPessimistic, adLockOptimistic или adLockBatchOptimistic приведет к использованию серверного курсора.

* Установить значение свойства CacheSize на любое другое, отличное от устанавливаемого по умолчанию (по умолчанию устанавливается 1).

Серверные курсоры:

* Создаются только для операторов, начинающахся с SELECT, EXEC[ute] procedure_name или {call procedures_name}. Даже если приложение явно потребует создания серверного курсора, серверный курсор не будет создан для операторов таких как INSERT.

* Не могут использоваться с операторами, которые генерируют белее одного набора записей. Это ограниечение применимо ко всем операторам, описанным в разделе "Генерация множественных наборов записей". Если серверный курсор используется с любым оператором, генерирующим множественные наборы записей, то приложение может получить следующие ошибки:

* "Cannot open a cursor on a stored procedure that has anything other than a single select statement in it."

* "sp_cursoropen. The statement parameter can only be a single select or a single stored procedure."

Следующий пример демонстрирует открытие динамического (dynamic) серверного курсора:

Dim rs As New ADODB.Recordset
rs.Open "select * from titles", "DSN=pubs;UID=sa", adOpenDynamic, adLockOptimistic
rs.Close

8.7. Использование объемных типов данных

Объемными типами данных в SQL server являются типы text и image. Тексты и рисунки иногда могут поместиться в памяти, но они также могут оказаться такими большими, что не смогут быть возвращены за одну операцию или целиком разместиться в памяти. Если объемные данные помещаются в памяти, то для получения всех данных за одну операцию может использоваться свойство Value объекта Field. Если данные слишком велики, чтобы поместиться в памяти, то операции с такими данными должны проводиться по частям. Существует два способа манипулирования данными большого размера. Первый - через объект Field и второй - через объект Parameter. Оба объекта Field и Parameter поддерживают метод AppendChunk, а объект Field, кроме того, обеспечивает метод GetChunk для операций с данными большого размера.

Объект Field позволяет записать или прочитать данные через объект Recordset. Метод AppendChunk объекта Field позволяет добавить данные к концу текущих данных когда запрос уже выполнен. Метод GetChunk позволяет читать данные по частям.

Объект Parameter обрабатывает данные большого размера подобным образом. Только у объекта Parameter нет метода GetChunk и нет объекта Recordset при работе с данными большого размера в режиме выполнения. С объектом Parameter данные большого размера свянаны в режиме выполнения и выполняются с объектом Command.

Существует несколько ограничений при работе с данными большого размера при использовании ODBC провайдера. Если не используется серверный курсор, то все колонки с объемными данными должны располагаться справа от всех остальных колонок (с обычными данными). Если есть несколько колонок с данными большого размера, то доступ к ним должен производиться в порядке слева-направо.

Следующий пример демонстрирует использование методов AppendChunk и GetChunk для чтения и записи данных большого размера:

Структура таблицы-приемника следующая:

drop table myBLOB
go
create table myBLOB( id int unique, info Text)
go
insert into myBLOB values(1, 'test')
go

Таблица myBLOB - это таблица-приемник, в которую будут вставлены данные большого размера:

ADO код имеет следующий вид:

Dim Cn As New ADODB.Connection
Dim rsRead As New ADODB.Recordset
Dim rsWrite As New ADODB.Recordset
Dim strChunk As String
Dim Offset As Long
Dim Totalsize As Long
Dim ChunkSize As Long
Cn.Open "pubs", "sa"
rsRead.CursorType = adOpenStatic
rsRead.Open "select pr_info from pub_info", Cn
rsWrite.CursorType = adOpenKeyset
rsWrite.LockType = adLockBatchOptimistic
rsWrite.Open "select * from myBLOB", Cn
ChunkSize = 1000
Totalsize = rsRead("pr_info").ActualSize
Do While Offset < Totalsize
strChunk = rsRead("pr_info").GetChunk(ChunkSize)
Offset = Offset + ChunkSize
rsWrite("info").AppendChunk strChunk
Loop
rsWrite.UpdateBatch
rsWrite.Close
rsRead.Close
End Sub

В этом примере данные pr_info считываются из таблицы pub_info и вставляются в таблицу myBLOB. После создания наборов записей rsRead и rsWrite, размер данных запоминаетсяв переменной Totalsize. Затем в цикле WHILE данные вставляются частями по 1000 байт. Выход из цикла предусмотрен, когда размер вставленных данных превысит размер первоначальный размер данных. После вставки для подтверждения сделанных изменений применяется метод UpdateBatch.

9. Сервисы удаленных данных (Remote Data Services)

Remote Data Services (ранее называемые Advanced Data Connector) разработан, чтобы устранить статичность Web страниц. Традиционная Web технология доступа к БД позволяет получить данные с сервера БД в виде HTML страницы и после этого данные становятся статичными и ими нельзя манипулировать без установления нового соединения с сервером БД.

Remote Data Services устраняет это ограничение, предоставляя дистанционный набор записей на клиентском компьютере, которым можно манипулировать. Remote Data Services позволяет тспользовать набор записей ADO, полученный с удаленного сервера. Такой набор записей может размещаться на клиентском компьютере без продолжения активного соединения с сервером БД.

Remote Data Services интегрирован в модель объектов ADO. Advanced Data Connector, распространаемый с OLE DB SDK version 1.1, обеспечивает механизмы для несвязанных наборов записей (disconnected record sets) и кэширования на стороне клиента, что дает возможность создавать динамические Web приложения с доступом к БД.

Другим подходом, предлагаемым с Remote Data Services, является использование прикладных объектов или объектов промежуточного уровня (business objects). Remote Data Services также позволяет отделить business-правила от данных. Вместо того, чтобы выкладывать всю программную или business логику на Web страницу клиента, можно разместить всю программную или business логику в приккладном объекте.

Прикладные объекты (Business Object) и ADO

Прикладные объекты - это программные модули OLE DLL, которые можно создавать с помощью Visual Basic, Visual C++ или Microsoft Visual J++. Основная цель прикладных объектов - разделить логику приложения и бизнес-правила и защитить бизнес-правила и данные от редистрибьюции. Например, бизнес-правилом может быть правило для расчета цен на авиабилеты. Авиакомпания не хочет, чтобы заказчики знали о том, как расчитываются цены на авиабилеты, а также может захотеть изменять правила расчета цены в любое время. Такие бизнес-правила могут быть реализованы на удаленном компьютере и при необходимости они могут быть изменены без влияния на компьютеры клиентов.

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

Для создания прикладных объектов можно использовать по умолчанию объект AdvancedDataFactory. Этот объект обеспечивает только чтение и запись данных. Объектная модель ADO является другой альтернативой для выполнения прикладных объектов. Объекты ADO обеспечивают не только всестороннее манипулирование данными, но и механизм удаленного набора записей.

Разъединение набора записей в прикладном объекте

Перед удалением (в смысле не delete, а remote) набора записей, он должен быть в состоянии функционировать как объект Recordset без реального соединения с сервером БД. После размещения набора записей на клиентском компьютере, соединение с данными не может и не размещается с набором записей и, поэтому, набор записей должен быть отсоединен от сервера БД. Набор записей без реального соединения с сервером БД называется разъединенным набором записей (disconnected record set).

Разъединенный набор записей можно создать с использованием свойства CursorLocation объекта Recordset. Свойство CursorLocation позволяет определить будет ли курсор клиентским или серверным и, что более важно, плзволяет проводить изменения данных в пакетном режиме (batch mode). Если свойство CursorLocation определено как adUseClient, то любые обновления в разъединенном наборе записей будут сделаны в пакетном режиме и для кэширования набора записей будет использоваться клиентский курсор.

Следующий пример демонстрирует создание разъединенного набора записей:

Public Function GetData() As Object
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "dsn=testing;uid=sa;pwd=;"
rs.CursorLocation = adUseClient
rs.Open "select * from authors for browse", cn, adOpenUnspecified, adLockUnspecified, adCmdUnspecified
Set GetData = rs
End Function

Помещение удаленных изменений обратно в прикладной объект

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

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

Set rs = BusObj.GetData
Adc1.recordset=rs
<>
Set rso = adc.recordset
rso.MarshalOptions=1
BusObj.SetData rso

Reconnecting and Applying Changes

После того, как обновленный набор данных передан от клиентского компьютера, прикладной объект может переустановить связь с набором данных и использовать метод UpdateBatch для обновления сделанных изменений в данных на сервере БД. Прикладной объект может переустановить связь с набором данных при помощи метода Open, как показано в следующем примере:

Public Function SetData(rso As Object)
Dim rs As New ADODB.Recordset
rs.Open rso, "dsn=pubs;uid=sa"
rs.UpdateBatch
End Function