Интерфейс ODBC
Скажу несколько слов об интерфейсе ODBC . Он был создан для того, чтобы дать возможность приложению - Потребителю данных - получать данные из различных систем управления базами данных (СУБД - Data Base Management System - DBMS). При этом концептуальная схема работы такова - приложение через набор соответствующих API-функций, которые, собственно говоря, и составляют интерфейс ODBC, обращается к объекту, называемому Менеджер драйверов (Driver Manager). Последний, опять-таки через тот же набор API- функций обращается к драйверу источника данных. Замечу, что большинство существующих баз данных имеют соответствующие драйверы и, следовательно, являются ODBC-источниками данных. Следует также отметить, что языком запросов в этой модели выступает SQL. Взаимодействие четырех основных объектов: Потребителя, Менеджера Драйверов, Драйвера и Источника данных - и определяет концептуальную модель ODBC.
Интерфейс OLE DB
Целевая установка при разработке этого интерфейса была следующей. Решения в сфере бизнеса нужно принимать оперативно. Эти решения должны быть основаны на полной и достоверной информации. Конечно же, основным источником данных для принятия решений является корпоративная база данных, например, MS SQL Server или Oracle. Но только этих данных, как правило, недостаточно для принятия оперативного решения. Необходимо учитывать данные, хранящиеся в личных базах данных, таких как Microsoft Access или FoxPro. Необходимые данные необходимо уметь извлекать из систем электронной почты. Данные могут храниться в индексно-последовательных файлах системы Betrieve, может быть, просто в бинарных файлах, наконец, в документах Office 2000, например, в списках Excel. Все возрастающую роль, как источника данных, играют Web-страницы интернет. Чтобы обеспечить единообразный способ работы с разнообразными источниками данных и создавался интерфейс OLE DB, представляющий некоторое множество интерфейсов, основанных на стандарте COM (Component Object Model).
Кратко опишу основные понятия (объекты), составляющие концептуальную и объектную модель OLE DB. Процесс работы с данными можно описать в терминах взаимодействия двух объектов - Поставщика данных (Provider) и Потребителя данных (Consumer). Потребителем является приложение, запрашивающее данные и непосредственно вызывающее функции, заданные интерфейсом. Поставщик данных или Провайдер - это приложение, экспонирующее функции интерфейса, - это тот посредник, стоящий между приложением, запрашивающим данные, и источником данных. Функциональные возможности набора COM-интерфейсов могут зависеть от Провайдера, от того, как тот или иной Провайдер реализует функциональность интерфейса для конкретного источника данных. Для наиболее часто используемых источников данных фирмой Microsoft, так и другими фирмами, разработаны ряд Провайдеров, ставших стандартами "де факто".
Разработчики, создающие Провайдеров, отображают функциональность, присущую конкретному источнику данных в функциональность, определенную интерфейсом. Разработчики, создающие терминальное приложение - Потребителя данных, - вызывают функции, экспонируемые Провайдером, для получения доступа к данным. Позже я чуть подробнее расскажу о различных экземплярах объекта Provider, обеспечивающих связь с различными источниками данных. Провайдеры поддерживают не только непосредственный доступ к данным, но и другие службы (Services) - транзакции, удаленный доступ, структуризацию кэш-памяти (cache) и другие службы. Кэш-память - это промежуточная память, в которой происходит изменение данных, их обновление, удаление и добавление, пока не наступает момент фактического обмена данными между кэш-памятью и источником данных.
Когда Потребитель связывается с Провайдером и обращается к нему за данными, то он должен вначале создать и инициализировать экземпляр объекта, задающий источник данных данного Провайдера (data source object). С помощью этого объекта можно создать второй центральный объект Провайдера - объект Session. Имея в своем распоряжении объект, задающий сеанс работы, можно уже создать и работать с собственно объектами, определяющими работу с данными - транзакциями, командами, наборами строк (объектами Transaction, Command, Rowset). Говоря о связывании Потребителя и Провайдера, следует упомянуть, что OLE DB определяет возможность использования адресов ресурсов - Uniform Resource Locators (URLs) как альтернативу связывания с помощью строки связывания (Connection String). Адреса URLs могут быть использованы для указания хранилищ данных, строк данных, потоков, наборов строк, объектов Session. Процесс связывания объекта OLE DB с ресурсом, именованным с помощью URL, называется прямым связыванием. При таком способе связывания можно даже и не инициализировать объект Session.
На этом я закончу беглое знакомство с основными понятиями и концепциями OLE DB. Позже я более подробно остановлюсь на рассмотрении объектов верхнего уровня - объектах ADO, которые транслируются в объекты OLE DB, и объектная модель которых на более высоком уровне повторяет объектную модель OLE DB.
Методы объекта Command
У объекта Command всего три метода. Рассмотрим их:
Function Execute([RecordsAffected], [Parameters], [Options As Long = -1]) As Recordset. Запускает на выполнение команду, предписанную свойствами CommandText или CommandStream. В качестве результата возвращает либо ссылку на объект Recordset, либо поток, либо Nothing.
Параметры метода имеют следующий смысл:
RecordsAffected - переменная типа Long, возвращающая число записей, затронутых при выполнении операции. Этот параметр не дает числа записей результата. Для этого необходимо использовать свойство RecordCount.Parameters - массив значений параметров типа Variant. Этот аргумент позволяет переопределить все или некоторые из параметров, заданных свойством Parameters объекта Command.Options - значение, указывающее, как Провайдер должен транслировать текст входной строки или потока. Может быть битовой маской одного или более значений из перечислений CommandTypeEnum или ExecuteOptionEnum. Так, если результатом выполнения команды должен быть поток, то значение этого свойства следует задать как adExecuteStream.Sub Cancel(). Прерывает выполнение команды Execute. Подробности уже рассматривались при описании объекта Connection.Function CreateParameter([Name As String], [Type As DataTypeEnum = adEmpty], [Direction As ParameterDirectionEnum = adParamInput], [Size As Long], [Value]) As Parameter. Позволяет создать новый объект Parameter с заданными свойствами. Ссылка на этот объект и является возвращаемым значением. Параметры метода имеют следующий смысл: Name - имя параметра,Type -тип данных объекта Parameter, заданный перечислением DataTypeEnum. Вот лишь некоторые из возможных значений: adArray, adChar, adDate, adCurrency, adIUnknown.Direction - тип самого объекта Parameter, заданный перечислением ParameterDirectionEnum. Он определяет является ли параметром входным или выходным параметром запроса или процедуры, возвращаемым значением хранимой процедуры Вот его возможные значения: adParamInput, adParamInputOutput, adParamOutput, adParamReturnValue, adParamUnknown.Size - значение указывающее максимальную длину значения параметра в символах или байтах.Value - значение типа Variant, указывающее значение объекта Parameter.
Метод создает, но не присоединяет автоматически созданный параметр к коллекции параметров. Это позволяет перед присоединением корректно установить необходимые свойства в коллекции Properties. Например, при задании типа данных как adNumeric следует также установить значения свойств NumericScale и Precision.
Методы объекта Connection
У объекта Connection 8 методов, позволяющих открыть и закрыть соединение, выполнить команду и прервать ее выполнение, методы, связанные с выполнением транзакции. Давайте рассмотрим описание этих методов:
Sub Open([ConnectionString As String], [UserID As String], [Password As String], [Options As Long = -1]). Один из центральных методов, с которого обычно начинается работа с источником данных, и который позволяет установить соединение с Провайдером. Первый параметр метода можно не задавать, если предварительно определить свойство ConnectionString. При задании этого параметра в момент открытия можно переопределить некоторые аргументы, заданные одноименным свойством. Два следующих параметра необходимы при установлении соединения с защищенным паролем источником данных. Последний параметр, задаваемый предопределенной константой, определяет некоторые характеристики устанавливаемого соединения.Sub Close(). Метод закрывает открытое соединение. Он применяется не только тогда, когда надобность в соединении исчезает, а часто для того, чтобы изменить параметры соединения и вновь открыть его методом Open. Напомню, что ряд параметров соединения могут быть установлены только для закрытого соединения. Этим методом обладают целый ряд объектов ADO. Чтобы удалить соответствующий объект из памяти объекту нужно присвоить значение Nothing. Метод Close для этих целей не применяется, - он не удаляет объект.Function Execute(CommandText As String, [RecordsAffected], [Options As Long = -1]) As Recordset. Объект Connection позволяет не только открывать соединение, но и выполнять операции над открытым источником данных. Метод Execute возвращает ссылку на созданный Провайдером объект Recordset, являющийся результатом выполнения команды, заданной первым параметром метода. Параметр CommandText, задающий описание команды, может быть текстом SQL-оператора, именем таблицы или хранимой процедуры, URL-адресом или текстом, специфическим для данного Провайдера. Второй параметр - RecordAffected возвращает число записей, затронутых при выполнении команды. Параметр Options позволяет задать дополнительную информацию для Провайдера, используемую при преобразовании текста параметра CommandText в реальную команду. Поскольку в результате выполнения команды возвращается объект Recordset, обладающий некоторым ограниченным набором возможностей, то для выполнения команд и получения объекта Recordset чаще используют объекты Command или Recordset.Sub Cancel(). Большинство объектов ADO обладают этим методом. При его выполнении завершается асинхронный метод, то есть метод, для которого установлены свойства adAsyncConnect, adAsyncExecute или adAsyncFetch. Для объекта Connection завершаемым методом может быть метод Open либо Execute.Function BeginTrans() As Long, Sub CommitTrans(),Sub RollbackTrans() - три метода для работы с транзакциями. Первый из них запускает новую транзакцию, второй - сохраняет результаты транзакции и завершает ее, третий - позволяет сделать откат, отменяя все изменения, сделанные во время текущей транзакции, сама транзакция при этом завершается. Если Провайдер поддерживает гнездованные транзакции, то повторный вызов метода BeginTrans в уже открытой транзакции начинает новую (гнездованную) транзакцию. Этот метод, вызванный, как функция, возвращает уровень гнездования транзакции. Замечу, что не все Провайдеры поддерживают механизм транзакций, не говоря уже о гнездованных транзакциях. Значение одного из атрибутов свойства Attributes объекта Connection можно задать так, что при закрытии текущей транзакции методами CommitTrans и RollBackTrans будет открываться новая транзакция. Function OpenSchema(Schema As SchemaEnum, [Restrictions], [SchemaID]) As Recordset. Метод позволяет получить информацию от Провайдера о схеме базы данных. В качестве результата возвращается ссылка на объект Recordset, который будет открыт только для чтения со статическим курсором. Столбцы этого объекта содержат описание схемы. Возможные описания задаются спецификациями OLE DB, первый параметр метода определяет, какие именно столбцы появятся в объекте Recordset.
Методы объекта Recordset
У объекта Recordset много свойств, много и методов. С некоторыми из них мы уже знакомы, поскольку они появлялись в примерах. Теперь приступим к их систематическому рассмотрению. С помощью методов можно выполнять все необходимые операции над набором записей - перемещаться по набору, находить нужные записи, создавать новые записи и удалять существующие, менять содержимое записей и передавать состояние набора в базу данных. Начнем наше рассмотрение:
Sub AddNew([FieldList], [Values]), Sub Delete([AffectRecords As AffectEnum = adAffectCurrent]). Метод AddNew позволяет добавлять новые записи в набор. Конечно, для применения метода объект Recordset должен иметь статус обновляемого набора, что означает, что для него должен быть установлен соответствующий тип курсора. Можно проверить, допускает ли Провайдер добавление записей. Метод Support, о котором еще предстоит разговор, позволяет выяснить поддерживается ли Провайдером то или иное свойство. В данном случае для того, чтобы проверить возможность создания новых записей в наборе, его следует вызвать, задав в качестве аргумента константу adAddNew.
Что следует сделать, чтобы набор был обновляемым? Я напомню, что создать объект Recordset можно разными способами. Он создается при вызове методов Execute объектов Connection и Command, его можно создать, вызвав метод Open объекта Recordset. В примерах, которые я приводил ранее, это объект создавался при вызове методов Execute. Но, заметьте, объект, создаваемый таким способом всегда имеет статический тип курсора и не может обновляться. Для создания объекта Recordset, допускающего обновление, всегда нужно применять метод Open, задавая динамический тип курсора в момент открытия.
Метод AddNew можно вызывать без аргументов. В этом случае созданная новая запись становится текущей, ее поля можно заполнить обычным способом. Для того чтобы содержимое записи было перенесено в базу данных, следует вызвать метод Update. Можно предусмотреть возможность пакетного обновления и использовать метод UpdateBatch для передачи в базу данных группы записей. Приведу пример, в котором в базу данных добавляется новая запись и корректируется значение отдельных полей в некоторых записях:
Public Sub CreateNewRecords() 'добавление и изменение записей базы данных Dim recExist As Boolean 'Создать соединение CreateConnection 'Создать команду 'задание свойств объекта Command Cmd1.ActiveConnection = Con1 Cmd1.CommandText = "Select * From [Книги]" Cmd1.CommandType = adCmdText 'Открытие обновляемого объекта Recordset With Rst1 .Open Source:=Cmd1, CursorType:=adOpenDynamic, _ LockType:=adLockOptimistic 'Узнаем характеристики набора Debug.Print .Supports(adAddNew) Debug.Print .LockType Debug.Print .CursorType 'Изменение записей recExist = False .MoveFirst Do While Not .EOF 'Обработка текущей записи If !Название = "Офисное программирование" Then recExist = True If ![Год издания] < 2000 And !Цена < 100 Then !Цена = !Цена * 2 .Update End If .MoveNext Loop If Not recExist Then .AddNew !Автор = "Владимир Биллиг" !Название = "Офисное программмирование" ![Год издания] = 2001 ![Число страниц] = 599 !Цена = 150 .Update End If End With End Sub
Заметьте, здесь метод AddNew, вызываемый без параметров, создал новую запись в наборе, она стала текущей, обычным способом заполнены поля этой записи, затем вызван метод Update для переноса записи в базу данных. Этот же метод использовался для изменения в базе данных значения поля "Цена" ряда записей.
При вызове метода AddNew можно задавать аргументы. Параметры метода имеют следующий смысл:
FieldList - имя поля или массив таких имен. Вместо имен можно задавать порядковые номера полей.Values - значение поля или массив значений. Если аргументы задаются, то должны быть заданы оба аргумента, и они должны быть согласованы обычным образом - по числу элементов в массиве, тип значения должен также соответствовать типу поля.
Когда метод AddNew вызывается с аргументами, то создается запись с уже заполненными полями, для нее автоматически вызывается метод Update, так что нет необходимости вызывать его самостоятельно - эту заботу берет на себя система. Приведу пример такого способа добавления записей в набор и базу данных:
Public Sub CreateNewRecords2() 'добавление и изменение записей базы данных Dim recExist As Boolean 'Создать соединение CreateConnection 'Создать команду 'задание свойств объекта Command Cmd1.ActiveConnection = Con1 Cmd1.CommandText = "Select * From [Книги]" Cmd1.CommandType = adCmdText 'Открытие обновляемого объекта Recordset With Rst1 .Open Source:=Cmd1, CursorType:=adOpenDynamic, _ LockType:=adLockOptimistic
recExist = False .MoveFirst Do While Not .EOF 'Проверка существования записи If !Название = "Война и мир" Then recExist = True .MoveNext Loop If Not recExist Then .AddNew Array("Автор", "Название", "Год издания", _ "Число страниц", "Цена"), _ Array("Лев Толстой", "Война и мир", 2001, 799, 220) End If End With End Sub
На эти примеры я еще буду ссылаться при рассмотрении других методов объекта Recordset. Если метод AddNew добавляет записи, то обратный к нему метод Delete позволяет удалять записи из набора и соответственно из базы данных. Я не буду останавливаться на деталях, а продолжу рассмотрение других методов объекта Recordset.Sub Cancel(), Sub CancelBatch([AffectRecords As AffectEnum = adAffectAll]), Sub CancelUpdate(). Эта группа методов позволяет отменить выполнение метода, в частности отменить проделанную работу по обновлению отдельной записи или пакета записей, если в процессе этой работы становится ясно, что вызывать соответствующий метод Update не следует. Метод CancelUpdate обычно вызывается, когда операция Update или Delete завершилась неуспехом, о чем можно узнать, анализируя свойство EditMode, указывающее на незавершенность состояния редактирования. Function Clone([LockType As LockTypeEnum = adLockUnspecified]) As Recordset. Позволяет создать дубликат набора записей. Дубликат может иметь другие права доступа и использоваться, например, только для чтения.Sub Open([Source], [ActiveConnection], [CursorType As CursorTypeEnum = adOpenUnspecified], [LockType As LockTypeEnum = adLockUnspecified], [Options As Long = -1]), Sub Close(). Метод Open является одним из основных способов создания объекта Recordset. Соответствующая объектная переменная, конечно, должна быть создана, а в момент открытия, используя параметры метода, создается реальный набор записей. Первые два параметра задают источник данных и активное соединение. Источником может быть ссылка на объект Command, SQL-оператор, хранимая процедура. В вышеприведенном примере в качестве источника задавался объект Command, заметьте, в этом случае задавать активное соединение не следует, оно определено в объекте Command. Два следующих параметра задают тип курсора и тип доступа к данным. О важности задания этих параметров я уже говорил, - без них не обойтись, при создании обновляемого набора записей. Последний параметр Options должен быть задан в тех случаях, когда первый параметр Source не является ссылкой на объект Command, в этом случае он задает способ интерпретации первого параметра, принимая значения констант из перечислений CommandTypeEnum и ExecuteOptionEnum. Приведу еще один пример открытия объекта Recordset, когда первый параметр не является объектом Command:
Public Sub CreateRst2() 'Создать соединение CreateConnection ' Открытие объекта Recordset With Rst1 .Open Source:="Select * From [Заказчики]", ActiveConnection:=Con1, _ CursorType:=adOpenStatic, Options:=adCmdText .MoveFirst Do While Not .EOF 'Печать поля записи Debug.Print !Название .MoveNext Loop End With End Sub
В этом примере источник данных задается SQL-оператором, а последний параметр указывает способ интерпретации, имея чаще всего используемое значение - adCmdText.
Метод Close позволяет закрыть ранее открытый объект, освобождая ресурсы.Function CompareBookmarks(Bookmark1, Bookmark2) As CompareEnum. Поскольку внутреннее представление закладок зависит от Провайдера, то самому выполнять операции сравнения закладок невозможно. Для этих целей и используется метод CompareBookmarks, позволяющий сравнивать закладки, принадлежащие одному и тому же набору Recordset или набору и его клону. Фактически закладки передаются Провайдеру, который и возвращает результат сравнения. Значением результата является одна из констант перечисления CompareEnum: adCompareEqual, adCompareGreaterThan, adCompareLessThan, adCompareNotComparable, adCompareNotEqual. Sub Find(Criteria As String, [SkipRecords As Long], [SearchDirection As SearchDirectionEnum = adSearchForward], [Start]). Если для выполнения операций сортировки и фильтрации используются свойства объекта Recordset, то для поиска используются его методы. Метод Find позволяет найти в наборе первую строку, удовлетворяющую критерию поиска, где критерий задается первым параметром метода. Критерий ограничен, - позволяет задать условие поиска только по одному полю, он задается строкой, имеющий следующий синтаксис: <Имя поля> Оператор <Значение поля>, где оператор может быть одной из операций сравнения либо оператором Like, задающим сравнение с шаблоном. Параметры SkipRecords и Start позволяют задать начало поиска. Первый из них указывает, сколько записей следует пропустить, начиная от текущей, чтобы начать поиск. Если задан параметр Start, то поиск начинается с записи, заданной этим параметром. Параметр SearchDirection указывает направление поиска, - вперед или назад от начала поиска. Поиск заверша ется, когда найдена запись, удовлетворяющая критерию, или поиск достиг конца набора записей. Так что неуспех можно определить по истинности EOF или BOF в зависимости от направления поиска. В случае успеха искомая запись становится текущей.Function GetRows([Rows As Long = -1], [Start], [Fields]). Этот метод позволяет создать из набора записей переменную VBA - двумерный массив, состоящий из строк и столбцов набора. Первый параметр указывает число строк, переписываемых из набора в массив. Значение по умолчанию этого параметра указывает, что переписываются все строки до конца набора. Если Провайдер поддерживает закладки, то можно задать закладку, как значение параметра Start, определяющую строку, начиная с которой набор будет переписываться в массив. Можно в массив записать выборочные поля набора записей, задав параметр Fields соответствующим образом - в виде массива имен или порядковых номеров переписываемых полей.Function GetString([StringFormat As StringFormatEnum = adClipString], [NumRows As Long = -1], [ColumnDelimeter As String], [RowDelimeter As String], [NullExpr As String]) As String. Если предыдущий метод позволяет преобразовать набор записей в двумерный массив, то метод GetString позволяет конвертировать набор записей в строку. Не буду останавливаться на деталях такого преобразованияSub Move(NumRecords As Long, [Start]), Sub MoveFirst(), Sub MoveLast(),Sub MoveNext(),Sub MovePrevious(). Эта группа методов позволяет организовать перемещение по записям набора. Методы неоднократно появлялись в наших примерах, и я полагаю, семантика их не нуждается в дополнительных пояснениях.Function NextRecordset([RecordsAffected]) As Recordset. Метод Execute объекта Command или метод Open объекта Recordset может выполнять составные команды, возвращающие множество наборов данных. Например, командой может быть составной оператор SQL вида: "Select * From [Книги]; Select * From [Заказчики]". В таких случаях необходимо вызвать метод NextRecordset для получения очередного набора записей. Если множество наборов исчерпано, то метод возвращает значение Nothing. Синтаксически очередной объект может связываться с той же объектной переменной, если старый набор уже не нужен. Sub Requery([Options As Long = -1]). Вызов этого метода эквивалентен последовательности вызовов методов Open и Close. Он позволяет обновить состояние набора, синхронизируя его с текущим состоянием базы данных. Параметр Options определяет специфику выполнения обновления, его возможные значения задаются суммой констант из перечислений ExecuteOptionEnum и CommandTypeEnum. Так, например, значение adAsyncExecute указывает, что обновление будет идти асинхронно и о его завершении можно будет узнать, когда вызовется обработчик события RecordsetChangeComplete.Sub Resync([AffectRecords As AffectEnum = adAffectAll], [ResyncValues As ResyncEnum = adResyncAllValues]). Еще один способ синхронизации данных набора записей и базы данных. В отличие от выше описанного метода Requery запрос повторно не выполняется, - восстанавливается значения только тех записей, которые есть в наборе. Поэтому, если запись набора будет удалена другим пользователем, то возникнет ошибка, информация о которой сохранится в коллекции Errors. Метод полезен при работе со статическим курсором или курсором типа Forward Only.Sub Save([Destination], [PersistFormat As PersistFormatEnum = adPersistADTG]). Позволяет сохранить набор записей в файле или объекте Stream. Параметр Destination задает полный путь к файлу, в котором должен быть сохранен объект Recordset или ссылку на объект Stream. Второй параметр задает формат сохранения - XML или ADTG.Sub Seek(KeyValues, [SeekOption As SeekEnum = adSeekFirstEQ]). Еще один, наряду с методом Find, метод поиска записи в наборе. Напомню, поля в таблицах базы данных могут быть индексированными. Индексы позволяют организовать быстрый поиск нужной записи. Метод Seek позволяет по ключевым значениям индексируемых полей найти запись в наборе и сделать ее текущей. В его первом параметре KeyValues задается массив значений полей, составляющих индекс. Параметр SeekOption задает тип сравнения между столбцами индекса и соответствующими значениями KeyValues.
Метод используется в сочетании со свойством Index, которое задает соответствующий индекс.
Поскольку не все Провайдеры поддерживают работу с индексами, то перед вызовом метода полезно, как обычно, вызвать метод Supports с константой adSeek или adIndex, чтобы выяснить возможность такого вызова в используемом контексте. Провайдер базы данных Access не поддерживает работу с индексами и для него единственным методом поиска является метод Find.Function Supports(CursorOptions As CursorOptionEnum) As Boolean. Метод, позволяющий выяснить возможности Провайдера в данном контексте. Параметр CursorOptions задает одно из возможных свойств курсора, метод возвращает значение True, если Провайдер поддерживает это свойство и False в противном случае. Возможные значения параметра являются константами из перечисления CursorOptionEnum. Ряд констант были упомянуты при описании свойств и методов объекта Recordset.Sub Update([Fields], [Values]), Sub UpdateBatch([AffectRecords As AffectEnum = adAffectAll]). Два важных метода, позволяющих проводить обновление отдельной записи или пакета записей в базе данных. Примеры применения метода Update уже приводились.
О курсоре
Прежде чем продолжить рассмотрение свойств, есть смысл посвятить отдельный параграф курсору - важному понятию в модели ADO и при работе с базами данных. Курсор - это элемент базы данных, позволяющий управлять перемещением по записям, обновлением данных, видимостью изменений, сделанных другими пользователями.
В реляционных базах данных в результате запроса возвращается набор строк таблицы (записей). Приложению, работающему с этим набором в каждый текущий момент необходим не весь набор, а отдельная запись или небольшой блок из записей набора. Для работы с набором записей приложению необходим программный механизм, который будет управлять позициями записей в наборе при его изменении, скроллингом - перемещением вперед и назад по записям набора, разрешением конфликтов при одновременном доступе многих пользователей к одной и той же записи. Все эти службы и предоставляются совокупностью программных компонент, называемых курсорами. Они реализованы в виде библиотеки курсоров, являющейся, обычно, частью базы данных. Название "Курсор" связано с тем, что, так или иначе, курсор указывает на текущую запись в наборе.
Поскольку от решения вопросов, относящихся к курсорам, зависит эффективность работы с базой данных, и эти решения могут играть определяющую роль, то следует уметь правильно выбирать тип курсора, положение курсора и другие его характеристики. Тип курсора задает, как будет идти скроллинг, динамику изменения набора записей и многое другое. Положение курсора определяет, где будет идти основная работа с ним - на клиентской или серверной стороне. Рассмотрим возможные типы курсоров. Их четыре:
Forward Only - допускает перемещение по записям только вперед, не допуская полноценный скроллинг. Он обычно используется тогда, когда необходим только один проход по записям. Его можно использовать и в случаях нескольких проходов, закрывая и заново открывая курсор. После обработки очередной строки освобождаются ресурсы, связанные с ее хранением. По умолчанию этот курсор является динамическим, что означает, что он следит за всеми изменениями, сделанными другими пользователями в показываемых записях. Конечно, это не относится к уже просмотренным записям. Константа adOpenForwardOnly из перечисления CursorTypeEnum задает этот тип курсора.Static - курсор этого типа в отличие от динамического не следит за изменениями, сделанными другими пользователями. Он сохраняет состояние набора записей на момент открытия. В зависимости от реализации статические курсоры могут допускать только чтение записей или возможность их обновления, могут допускать скроллинг или только перемещение вперед. Как правило, статический курсор допускает видимость изменений, сделанных самим приложением. Этот вид курсора обычно применяется, когда необходим скроллинг, но нет необходимости следить за изменениями, сделанными другими пользователями. Константа adOpenStatic задает этот тип курсора.Keyset - курсор этого типа является, обычно, альтернативой курсору Forward Only. Он применяется, когда идет интенсивная работа с отдельными записями набора в произвольном порядке доступа к ним. Этот курсор называется курсором, управляемым набором ключей (keyset driven cursor). Для каждой строки из набора создается ключ, обеспечивающий быстрый доступ к любой строке набора.
Что касается наблюдения за изменениями в наборе, то этот вид курсора обеспечивает стратегию, промежуточную между статическим и динамическим курсором. Он позволяет проследить за изменениями значений записей, сделанных другими пользователями, и этим он похож не динамический курсор. Но он не позволяет проследить за изменениями в составе набора - добавлению или удалению строк, изменения порядка их следования. При создании ключей состав набора замораживается. Когда запись удаляется из набора, то, поскольку ключ для нее сохраняется, то такая запись будет видна, как пустая запись - "дыра" в наборе. Добавляемые записи будут видны в виде добавлений в конец набора. Константа adOpenKeyset задает этот тип курсора.Dynamic - динамический курсор обнаруживает все изменения, происходящие с набором записей, сделанные как самим приложением, так и всеми параллельно работающими пользователями. Все вставки, обновления или удаления, сделанные всеми пользователями видимы для этого типа курсора. Этот вид курсора выбирается, когда необходимо обеспечить совместную работу, но, нужно понимать, он требует от сервера больших затрат и при большом числе пользователей может существенно замедлить работу с набором данных. Константа adOpenDynamic задает этот тип курсора.
Поговорим теперь о такой важной характеристике курсора как его положение (Cursor Location). Константы adUseClient и adUseServer перечисления CursorLocationEnum задают положение курсора. В зависимости от установленного значения все необходимые ресурсы и сама работа с данными ведется на клиентской или серверной стороне. Достоинства работы на стороне клиента состоят в быстром отклике на обращение к записям, не требующим выхода в сеть. Когда работа с записями ведется в основном в режиме их чтения, то это положение курсора наиболее предпочтительно. Некоторым недостатком является то, что при больших наборах клиентскому компьютеру требуются большие ресурсы по памяти для хранения данных. Преимущества работы также теряются, когда интенсивно изменяется состав набора - записи удаляются, добавляются, так как эти изменения должны отражаться и на сервере. Если курсор расположен на серверной стороне, то объем передаваемых данных может быть существенно уменьшен, поскольку вся обра ботка ведется на сервере и клиенту переда ется только необходимые ему записи, а не весь набор. С другой стороны при большом числе активных пользователей каждому из них сервер должен выделить ресурсы, что может быть серьезной нагрузкой на сервер, с которой он может и не справиться. Еще одним недостатков курсора на серверной стороне является то, что в отличие от курсоров на клиентской стороне, не поддерживается работа в пакетном режиме (batch cursor), а возможна работа только с единственной записью.
Конечно, поддерживают работу на клиентской и серверной стороне разные библиотеки курсоров. При работе на стороне клиента для обеспечения работы с курсором ADO вызывает специальную службу - Microsoft Cursor Service for OLE DB, поддерживающую единую функциональность для различных Провайдеров.
Для того чтобы обеспечить целостность данных в СУБД имеются специальные механизмы, позволяющие временно закрыть доступ к записям базы другим пользователям, пока один из пользователей корректирует их содержание. С одной стороны, возможность закрытия доступа совершенно необходима в некоторых ситуациях, например, чтобы не продать один и тот же билет на самолет или поезд разным пассажирам. С другой стороны, когда, например, в интернете сотни тысяч пользователей обращаются одновременно к базе данных, то закрытие данных существенно снижает общую производительность системы. Система ADO позволяет указывать Провайдеру, какой тип закрытия данных следует применять при работе с объектами Recordset. Конкретный Провайдер может не поддерживать все типы закрытия, в этом случае он будет поддерживать возможный ближайший тип закрытия. Вернемся теперь к рассмотрению свойств объекта Recordset и продолжим это рассмотрение со свойств, связанных с курсором.
Объект Command
Объект Connection позволяет установить соединение с Провайдером источника данных и, вообще говоря, выполнять операции над данными. Но для выполнения команд есть специальный объект. Объект Command позволяет задать команду Провайдеру на выполнение той или иной операции над данными источника. Чтобы разобраться с возможностями этого объекта, давайте начнем с рассмотрения его свойств и методов. Замечу, что этот объект событий не имеет, - они связаны только с объектами Connection и Recordset.
Объект Connection
С чего начинается работа с источником данных? Прежде всего, нужно с ним соединиться. Объект Connection, как я уже говорил, позволяет установить соединение с Провайдером источника данных.
Объект Recordset
Этот объект представляет набор записей, возвращаемый в результате выполнения операции. В каждый момент можно работать только с одной выделенной записью набора, которая называется текущей записью. Естественно, есть методы, позволяющие перемещаться по записям набора. В предыдущем примере при работе с набором записей для этой цели использовались методы MoveFirst и MoveLast. При использовании ADO практически вся работа с данными ведется через этот объект. Он используется для того, чтобы читать записи из базы данных, изменять их содержимое, удалять и добавлять новые записи. Это самый сложный по своей организации объект ADO, - у него больше всего свойств, методов и событий. В предыдущем примере уже демонстрировалось применение этого объекта, где он создавался в результате выполнения команды, получающей результаты запроса к базе данных, затем полученные данные использовались для просмотра. Прежде чем обсудить все те возможности, которые предоставляет этот объект, давайте, как обычно, вначале рассмотрим его свойства, методы и события.
Объектная модель ADO
Рассмотрим объектную модель ADO и начнем с графического представления отношений между объектами в этой модели:
Рис. 5.1. Отношения между объектами в объектной модели ADO
Объекты ADO имеют следующее назначение:
Command - Определяет ту специальную команду, которую предполагается выполнить над источником данных.Connection - Задает открытое соединение с источником данных.Error - элемент коллекции Errors. Содержит описание ошибки доступа, которая возникла при выполнении Провайдером определенной операции. Список этих ошибок (коллекция Errors), возникших в течение одной операции создается Провайдером данных. Field - элемент коллекции Fields. Поле записи, задает столбец данных, все значения в котором имеют один и тот же тип.Parameter - элемент коллекции Parameters. Представляет параметр или аргумент, связанный с объектом Command, который определяет параметризованный запрос или хранимую процедуру. Параметры передаются запросу или процедуре.Property - элемент коллекции Properties. Представляет характеристики объектов ADO, используемые Провайдером.Record - Представляет единственную запись - строку в наборе записей - объекте Recordset, или каталог или файл в файловой системе.Recordset - Представляет все множество записей таблицы базы данных или результат выполнения некоторой команды. В каждый текущий момент объект Recordset ссылается только на одну запись, называемую текущей, внутри всего набора записей. Stream - Представляет бинарный поток данных, рассматриваемый как последовательность байтов.
Хочу обратить внимание на некоторую особенность данной объектной модели. На верхнем уровне иерархии находится целая группа объектов. Здесь нет центрального объекта, как это обычно бывает, в который вложены все остальные объекты. Замечу, что в предыдущей модели DAO такой объект был - это объект DBEngine, задающий некую машину базы данных. В данном случае Microsoft отошла от привычной для Office 2000 практики и отказалась от введения центрального объекта, в который вложены все остальные объекты иерархии.
Чтобы дать полную характеристику объектов, нужно рассмотреть их свойства, методы и события. Кроме того, нужно понимать отношения, связывающие объекты, а еще хорошо бы понимать, как пользоваться всем этим богатством. Поговорим об этом.
Обзор возможностей объекта Command и примеры применения
Подведу теперь некоторые итоги и укажу на те возможности, которые предоставляет объекта Command:
Задать описание команды, используя свойства CommandText и CommandStream. Второе из этих свойств позволяет, например, задавать XML-запросы.Связать объект Command с открытым соединением, используя свойство ActiveConnection.Вызывать команду на исполнение, используя метод Execute объекта Command, или для именованного объекта вызывать команду как метод объекта Connection.Формировать объект Recordset как результат выполнения команды.В момент вызова команды передавать параметры хранимой процедуре или параметризованному запросу.Формировать при необходимости объекты Parameter и создавать коллекцию Parameters.Повышать эффективность выполнения команды, используя свойства Prepared и CommandType.
Приведу пример создания и работы с объектом Command:
Public Sub CreateCommands() 'Создание команд, выполняющих операции 'с тестовой базой данных Access Dim Par1 As Object Dim strSQL1 As String, strSQL2 As String Dim strSQL3 As String, strSQL4 As String Dim KeyAuthor As String, KeyName As String Const Кавычка = "'" KeyAuthor = "Б. Гейтс": KeyName = "Дорога в будущее" 'Задание четырех SQL операторов strSQL1 = "Select * FROM [Книги]WHERE [Автор]= " _ & Кавычка & KeyAuthor & Кавычка strSQL2 = "Select * FROM [Книги]WHERE ([Название]=" _ & Кавычка & KeyName & Кавычка & " AND [Автор] = " _ & Кавычка & KeyAuthor & Кавычка & ")" strSQL3 = "Select * FROM [Заказчики в Твери]" strSQL4 = "Select * FROM [зак-из-гор]"
'задание свойств объекта Command Cmd1.ActiveConnection = Con1 Cmd1.CommandText = strSQL1 Cmd1.CommandType = adCmdText Cmd1.Prepared = True 'Cmd1.Name = "AuthorAndBook"
'вызов команды на исполнение методом Execute Set Rst1 = Cmd1.Execute 'печать результата Rst1.MoveFirst Debug.Print Rst1!Автор Debug.Print Rst1!Название Debug.Print Rst1!Цена Rst1.MoveLast Debug.Print Rst1!Автор Debug.Print Rst1!Название Debug.Print Rst1!Цена
'Изменение описания команды Cmd1.CommandText = strSQL2 Set Rst1 = Cmd1.Execute 'печать результата Debug.Print Rst1!Автор Debug.Print Rst1!Название Debug.Print Rst1!Цена
' Вызов хранимого запроса базы данных Cmd1.CommandText = strSQL3 Set Rst1 = Cmd1.Execute 'печать результата Rst1.MoveFirst Debug.Print Rst1!Название Rst1.MoveLast Debug.Print Rst1!Название
'Вызов хранимого запроса с параметрами Cmd1.CommandText = strSQL4 Set Par1 = Cmd1.CreateParameter("Town", adBSTR, adParamInput) Cmd1.Parameters.Append Par1 Par1.Value = "Тверь" Set Rst1 = Cmd1.Execute 'печать результата Rst1.MoveFirst Debug.Print Rst1!Название Rst1.MoveLast Debug.Print Rst1!Название
'печать характеристик Cmd1 Debug.Print "ActiveConnection = ", Cmd1.ActiveConnection Debug.Print "CommandTimeout = ", Cmd1.CommandTimeout Debug.Print "CommandType = ", Cmd1.CommandType Debug.Print "Name = ", Cmd1.Name Debug.Print "CommandText = ", Cmd1.CommandText Debug.Print "Prepared = ", Cmd1.Prepared Debug.Print "Parameters.Count = ", Cmd1.Parameters.Count Debug.Print "Properies.Count = ", Cmd1.Properties.Count Debug.Print "State = ", Cmd1.State Debug.Print "Properties(1).Name = ", Cmd1.Properties(1).Name Debug.Print "Properties(1).Value = ", Cmd1.Properties(1).Value
End Sub
Прокомментирую текст этой процедуры:
Я выполняю четыре разные команды над базой данных. Описание этих команд я задал в четырех SQL-операторах. Первый из них выбирает из таблицы "Книги" все книги, автор которых задан переменной KeyAuthor, имеющей в данном примере значение "Б. Гейтс". Замечу, что в таблице хранятся сведения о двух книгах этого автора. Во втором операторе запрос уточняется и ищется книга с фиксированным названием. Третий SQL-оператор обращается к стандартному запросу, хранимому в базе данных. Этот запрос выдает в качестве результата набор записей, содержащих заказчиков из города Тверь. Наконец, текст четвертой команды представляет вызов параметризованного запроса, где ищутся заказчики из города, название которого является параметром запроса.Прежде чем вызвать первую команду на исполнение, я формирую свойства объекта Command. Первым делом формирую свойство CommandText, задающее описание команды, но не только его. Я задал свойство ActiveConnection, что совершенно необходимо, а, кроме того, задал свойства Prepared и CommandType. Заметьте, в данном контексте невозможно именовать команду, поэтому мне пришлось закомментировать назначение этого свойства, чтобы избежать появления ошибки.Последовательно изменяя описание команды - свойство CommandText, - я выполнил все четыре команды. Конечно же, прежде чем выполнить четвертую команду, требующую задания параметра в момент ее выполнения, мне пришлось этот параметр создать. Для его создания я вызвал метод CreateParameter и созданный таким образом параметр присоединил к коллекции Parameters. Хочу обратить внимание на одну деталь, переменную Par1 мне пришлось описать как имеющую класс Object, а не класс Parameter, иначе возникало несоответствие типов при попытке присвоения ей ссылки на объект, созданный в результате выполнения метода CreateParameter.Для демонстрации корректности выполнения команд, я печатаю значения записей из набора Rst1, создаваемого при выполнении очередной команды. Заметьте, я использую некоторые операции над объектом Recordset - MoveFirst и MoveLast, позволяющие выбрать нужную запись в наборе. Чуть позже я подробно рассмотрю все свойства и методы этого объекта.В заключение, я печатаю некоторые свойства объекта Command. Из большой коллекции Properties я ограничился печатью количества элементов в этой коллекции, а также имени и значения первого элемента в этой коллекции. Приведу результаты отладочной печати:Б. Гейтс Дорога в будущее 23 Б. Гейтс Бизнес со скоростью мысли 150 Б. Гейтс Дорога в будущее 23 Книжная лавка ООО 'Тверькнига' Книжная лавка ООО 'Тверькнига' ActiveConnection = Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=c:\!O2000\DsCd\Ch14\dbPP2000.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False CommandTimeout = 30 CommandType = 1 Name = CommandText = Select * FROM [зак-из-гор] Prepared = True Parameters.Count = 2 Properies.Count = 88 State = 0 Properties(1).Name = Blocking Storage Objects Properties(1).Value = True
'Изменение описания команды Cmd1.CommandText = strSQL2 Set Rst1 = Cmd1.Execute 'печать результата Debug.Print Rst1!Автор Debug.Print Rst1!Название Debug.Print Rst1!Цена
' Вызов хранимого запроса базы данных Cmd1.CommandText = strSQL3 Set Rst1 = Cmd1.Execute 'печать результата Rst1.MoveFirst Debug.Print Rst1!Название Rst1.MoveLast Debug.Print Rst1!Название
'Вызов хранимого запроса с параметрами Cmd1.CommandText = strSQL4 Set Par1 = Cmd1.CreateParameter("Town", adBSTR, adParamInput) Cmd1.Parameters.Append Par1 Par1.Value = "Тверь" Set Rst1 = Cmd1.Execute 'печать результата Rst1.MoveFirst Debug.Print Rst1!Название Rst1.MoveLast Debug.Print Rst1!Название
'печать характеристик Cmd1 Debug.Print "ActiveConnection = ", Cmd1.ActiveConnection Debug.Print "CommandTimeout = ", Cmd1.CommandTimeout Debug.Print "CommandType = ", Cmd1.CommandType Debug.Print "Name = ", Cmd1.Name Debug.Print "CommandText = ", Cmd1.CommandText Debug.Print "Prepared = ", Cmd1.Prepared Debug.Print "Parameters.Count = ", Cmd1.Parameters.Count Debug.Print "Properies.Count = ", Cmd1.Properties.Count Debug.Print "State = ", Cmd1.State Debug.Print "Properties(1).Name = ", Cmd1.Properties(1).Name Debug.Print "Properties(1).Value = ", Cmd1.Properties(1).Value
End Sub
Прокомментирую текст этой процедуры:
Я выполняю четыре разные команды над базой данных. Описание этих команд я задал в четырех SQL-операторах. Первый из них выбирает из таблицы "Книги" все книги, автор которых задан переменной KeyAuthor, имеющей в данном примере значение "Б. Гейтс". Замечу, что в таблице хранятся сведения о двух книгах этого автора. Во втором операторе запрос уточняется и ищется книга с фиксированным названием. Третий SQL-оператор обращается к стандартному запросу, хранимому в базе данных. Этот запрос выдает в качестве результата набор записей, содержащих заказчиков из города Тверь. Наконец, текст четвертой команды представляет вызов параметризованного запроса, где ищутся заказчики из города, название которого является параметром запроса.Прежде чем вызвать первую команду на исполнение, я формирую свойства объекта Command. Первым делом формирую свойство CommandText, задающее описание команды, но не только его. Я задал свойство ActiveConnection, что совершенно необходимо, а, кроме того, задал свойства Prepared и CommandType. Заметьте, в данном контексте невозможно именовать команду, поэтому мне пришлось закомментировать назначение этого свойства, чтобы избежать появления ошибки.Последовательно изменяя описание команды - свойство CommandText, - я выполнил все четыре команды. Конечно же, прежде чем выполнить четвертую команду, требующую задания параметра в момент ее выполнения, мне пришлось этот параметр создать. Для его создания я вызвал метод CreateParameter и созданный таким образом параметр присоединил к коллекции Parameters. Хочу обратить внимание на одну деталь, переменную Par1 мне пришлось описать как имеющую класс Object, а не класс Parameter, иначе возникало несоответствие типов при попытке присвоения ей ссылки на объект, созданный в результате выполнения метода CreateParameter.Для демонстрации корректности выполнения команд, я печатаю значения записей из набора Rst1, создаваемого при выполнении очередной команды. Заметьте, я использую некоторые операции над объектом Recordset - MoveFirst и MoveLast, позволяющие выбрать нужную запись в наборе. Чуть позже я подробно рассмотрю все свойства и методы этого объекта.В заключение, я печатаю некоторые свойства объекта Command. Из большой коллекции Properties я ограничился печатью количества элементов в этой коллекции, а также имени и значения первого элемента в этой коллекции. Приведу результаты отладочной печати:Б. Гейтс Дорога в будущее 23 Б. Гейтс Бизнес со скоростью мысли 150 Б. Гейтс Дорога в будущее 23 Книжная лавка ООО 'Тверькнига' Книжная лавка ООО 'Тверькнига' ActiveConnection = Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=c:\!O2000\DsCd\Ch14\dbPP2000.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False CommandTimeout = 30 CommandType = 1 Name = CommandText = Select * FROM [зак-из-гор] Prepared = True Parameters.Count = 2 Properies.Count = 88 State = 0 Properties(1).Name = Blocking Storage Objects Properties(1).Value = True
Заметьте, число параметров в коллекции Parameters равно 2, а не 1, как должно было бы быть. Это связано с моей недоработкой и недоработкой Microsoft. Перед тем, как начать формировать коллекцию Parameters, я должен был очистить ее содержимое, вызвав метод Delete. Я не сделал этого, поскольку метод Delete не вызывается в данном контексте. По этой причине при повторном запуске процедуры произошло добавление параметра к уже имеющейся коллекции. В данном случае, когда выполняется запрос с одним параметром, это не приводит к ошибке, и я не стал усложнять уже и так довольно длинную процедуру. Но в принципе это серьезная ошибка, которая в другой ситуации может привести к неприятностям, например, если бы в следующей команде я попытался бы выполнить другой параметризованный запрос. Так что обратите внимание на эту ситуацию, и корректно работайте с коллекцией Parameters. Заметьте, проблемы исчезают для локально определенного объекта Command .
Обзор возможностей объекта Connection
Подведу теперь некоторые итоги и укажу на некоторые дополнительные возможности объекта Connection. Объект задает сеанс работы с источником данных. В случае, когда речь идет об удаленном источнике и клиент-серверном приложении установление соединения означает физическое подключение к серверу в сети.
Хотя большинство свойств и методов этого объекта определено для всех стандартных Провайдеров, тем не менее, есть и специфика, определяемая каждым конкретным Провайдером. Перечислю еще раз те возможности, которые предоставляют свойства и методы объекта:
Еще до открытия соединения можно установить его конфигурацию, задавая характеристики соединения с помощью таких свойств, как ConnectionString, ConnectionTimeOut, Mode. Первое из этих свойств имеет статус свойства по умолчанию.Можно обращаться на клиентской стороне к службе управления курсором - Cursor Service for OLE DB, задав adUseClient в качестве значения свойства CursorLocation.Можно установить для соединения базу данных по умолчанию, задав свойство DefaultDatabase, аналогично, свойство Provider устанавливает Провайдера по умолчанию.Используя методы Open и Close, можно многократно открывать и закрывать соединение, возможно, меняя его параметры от сеанса к сеансу.Используя метод Execute, можно выполнять те или иные команды, не обращаясь к объекту Command.Дана возможность управлять транзакциями в открытом соединении, в том числе гнездованными транзакциями, если только Провайдер поддерживает эти возможности.В соответствующих обработчиках событий можно проверять корректность задания параметров перед выполнением команд. После их выполнения можно анализировать объекты Error, чтобы понять причины, приведшие к возникновению ошибки по завершении команды.Можно независимо создать несколько объектов Connection, что позволяет запускать одновременно несколько сеансов работы с одним или разными источниками данных.Наконец, важно отметить такую возможность некоторых Провайдеров, как вызов объектом Connection именованных команд и хранимых процедур, точно также как, если бы они были методами этого объекта. Вот схема процедуры, демонстрирующая эту возможность:Sub Fragment() 'Это не исполняемый код, а демонстрационная заготовка Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rst As New ADODB.Recordset 'конфигурирование соединения cnn.Open ConnectionString:= "…" cmd.Name = "NameOfMyCommand" cmd.ActiveConnection = cnn 'запуск команды на выполнение, передавая ей параметры и объект Recordset cnn.NameOfMyCommand "parameter", rst 'запуск хранимой процедуры и передача ей параметров cnn.NameOfMyStoredProcedure "parameter" End Sub
Приведенный пример является лишь иллюстрацией, чтобы он заработал необходимо уточнить опущенные здесь детали. Такие примеры тоже полезны, но, пожалуй, интереснее работающие процедуры. Чтобы проводить эксперименты с объектами ADO, я создал некоторый модуль в программном проекте документа. Приведу сейчас процедуру этого модуля, в которой создается соединение.
'Модуль TestingADO 'Глобальные переменные Public Con1 As New ADODB.Connection Public Cmd1 As New ADODB.Command Public Rst1 As New ADODB.Recordset Public Strm1 As New ADODB.Stream Public Sub CreateConnection() 'Создание соединения с тестовой базой данных Access Dim strConnStr As String If Con1.State = adStateOpen Then Con1.Close 'закрыть соединение 'Вариант 1 'Конфигурирование соединения Con1 Con1.Provider = "Microsoft.jet.oledb.4.0" Con1.ConnectionString = "Data Source=c:\!O2000\DsCd\Ch14\dbPP2000.mdb" Con1.CursorLocation = adUseClient 'Открытие соединения Con1.Open 'Вариант 2 'strConnStr = "Provider=Microsoft.jet.oledb.4.0;" & _ '"Data Source=c:\!O2000\DsCd\Ch14\dbPP2000.mdb" 'Con1.Open strConnStr 'печать характеристик соединения Debug.Print "Attributes = ", Con1.Attributes Debug.Print "CommandTimeout = ", Con1.CommandTimeout Debug.Print "ConnectionString = ", Con1.ConnectionString Debug.Print "ConnectionTimeout = ", Con1.ConnectionTimeout Debug.Print "CursorLocation = ", Con1.CursorLocation Debug.Print "DefaultDatabase = ", Con1.DefaultDatabase Debug.Print "Mode = ", Con1.Mode Debug.Print "Properies.Count = ", Con1.Properties.Count Debug.Print "State = ", Con1.State Debug.Print "Version = ", Con1.Version End Sub
Приведу краткий комментарий.
Прежде всего, отмечу, что я начал работать с документом Excel, который будет получать данные из базы данных Access, создание которой я описал в предыдущей главе. В проекте этого документа я создал модуль с именем TestingADO, подключил к проекту библиотеку ActiveX Data Objects 2.5 Library, которая у меня на компьютере находится, как обычно, по адресу: "c:\Program Files\Common Files\System\ado\msado15.dll". Имя этой библиотеки, используемое в проектах на VBA, - ADODB.Затем я создал 4 глобальных объекта: Con1, Cmd1, Rst1, Strm1, принадлежащих соответственно классам Connection, Command, Recordset, Stream из библиотеки ADODB.Первая процедура, которую я написал в этом модуле - CreateConnection, проста. В ней идет работа с объектом Con1, устанавливается соединение с базой данных Access и распечатываются характеристики сделанного соединения.Работа начинается с проверки состояния соединения, поскольку попытка открыть уже открытое соединение приводит к ошибке.Я рассматриваю два варианта установления соединения. В первом - предварительно задаются свойства соединения Provider и ConnectionString, затем вызывается метод Open без параметров. Во втором варианте вся необходимая информация указывается при вызове метода Open в передаваемых ему параметрах.Приведу результаты печати свойств соединения после его открытия:Attributes = 0 CommandTimeout = 30 ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=c:\!O2000\DsCd\Ch14\dbPP2000.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False ConnectionTimeout = 15 CursorLocation = 3 DefaultDatabase = Mode = 16 Properies.Count = 94 State = 1 Version = 2.5
Обратите внимание на длинную строку ConnectionString. Она, конечно, намного длиннее той строки, которую задал я, определив источник данных. Помимо этого, строка содержит многие характеристики соединения, устанавливаемые по умолчанию. Заметьте также, я напечатал лишь число элементов коллекции Properties, не приводя значений всех 94 элементов.
Обзор возможностей объекта Recordset
Объект Recordset это основной объект, позволяющий работать с данными, извлеченными и помещаемыми в базу данных. Создается разными способами - методом Execute объектов Connection и Command, либо методом Open объекта Recordset. Содержит набор записей, полученных в результате выполнения запроса. В каждый текущий момент позволяет работать только с одной записью набора - текущей, на которую указывает курсор.
Методы и свойства объекта позволяют перемещать курсор по набору записей, производить фильтрацию записей, их сортировку, находить нужную запись. Можно изменять значения отдельных полей некоторых записей, добавлять новые и удалять записи из набора. Измененный набор можно сохранить в базе данных. При этом изменения можно делать оперативно - для каждой записи, подвергшейся изменению, или в пакетном режиме.
Во многом возможность выполнения операций над данными определяет тип и положение курсора, установленные свойствами объекта Recordset. В ADO определены четыре типа курсора и два возможных положения, указывающие, где выполняются операции - на клиентской или на серверной стороне.
Свою специфику, определяющую возможность выполнения отдельных операций и их особенность, накладывает используемый Провайдер. Свойство Supports позволяет выяснить, поддерживает ли Провайдер те или иные свойства выбранного курсора.
Свойство Fields является свойством по умолчанию объекта Recordset. Оно возвращает коллекцию полей, элементы которой являются объектами Field, каждый из которых позволяет работать с отдельным полем записи.
Объект Recordset может быть не только плоским набором записей, но и иметь иерархическую структуру, при которой записи набора могут иметь потомков.
На этом я завершу описание основных объектов ADO - Connection, Command и Recordset. Но разговор об объектах ADO еще далеко не завершен и будет продолжен в следующей главе.
Семейство объектов ADO
Перейдем теперь к подробному рассмотрению объектов ADO - наиболее интересных для VBA-программистов. Более точно, следует говорить о семействе объектов ADO, в которое входят три группы объектов:
ADO - ActiveX Data Objects. Иногда, когда говорят об объектах ADO, имеют в виду именно объекты этой группы. Эти объекты, как я уже говорил, являются надстройкой над объектами OLE DB. Их назначение - решать те же задачи, ради которых был создан интерфейс OLE DB, но на более высоком языковом уровне общения. В первую очередь эти объекты создавались для того, чтобы дать возможность создавать клиент-серверные и WEB-ориентированные приложения на VB/VBA и VBScript. Вот три главных объекта, находящихся на верхнем уровне иерархии этой группы объектов: Connection - позволяет установить соединение с Провайдером.Command - позволяет задать команду Провайдеру, определяющую операцию, выполняемую над данными.Recordset - сохраняет результаты выполненной команды и обеспечивает, тем самым, возможность работы с результатами запросов и хранимых процедур.ADOX - ActiveX Data Objects Extensions for Data Definition Language and Security. Объекты этой группы дополняют функциональные возможности объектов первой группы. Они позволяют манипулировать не с данными, а с объектами более высокого уровня - схемами данных. С их помощью можно модифицировать схему данных, создавать, изменять и удалять ее объекты, например таблицы базы данных. Другое назначение этих объектов - управлять безопасностью доступа к данным, - назначать и изменять права доступа к данным, создавать группы пользователей, обладающих определенными правами. Центральными объектами в этой группе, находящимися на верхнем уровне иерархии, являются объекты Catalog, User, Group. Первый из этих объектов задает источник данных, - он соответствует объекту DataBase в модели объектов DAO. Объекты User и Group позволяют управлять безопасностью доступа, как отдельного пользователя, так и пользователей, объединенных в группы. ADO MD - ActiveX Data Objects MultiDimensional. Также как и обычные ADO-объекты, эти объекты являются надстройкой над интерфейсом OLE DB. Их назначение состоит в том, чтобы обеспечить возможность работы с многомерными источниками данных. Для того, чтобы работа с этим объектами была возможной, соответствующий Провайдер должен быть Провайдером многомерных данных (MDP Provider) и удовлетворять специальным спецификациям - OLE DB for OLAP. В этом случае данные представляются не в виде таблиц, а в виде кубов данных. Центральными в этой группе являются объекты CubeDef и CellSet, которые обеспечивают определение куба данных и доступ к множеству ячеек этого куба.
Каждая из трех групп ADO-объектов находится в отдельной DLL библиотеке и подключается к программному проекту независимо друг от друга обычным способом - вручную через меню References, либо программно. Замечу, что описание объектов, которое я буду приводить, соответствует версии библиотеки 2.6, - последней на момент написания данного текста, хотя примеры, которые я буду приводить, разработаны на предыдущей версии 2.5.
События объекта Connection
Только два объекта ADO обладают событиями - Connection и Recordset. События связываются с выполнением той или иной операции и могут возникать перед выполнением и после завершения операции. При возникновении события операционной системе посылается уведомление, а она, в свою очередь, вызывает соответствующую процедуру обработки этого события. Конечно, программист должен определить эту процедуру в соответствующем месте проекта. Обработка событий, предшествующих выполнению операции, позволяет проверить правильность задания всех параметров и принять окончательное решение о запуске команды на выполнение или на ее прерывание. Обработка событий после завершения операции особенно важна при асинхронном способе выполнения команд, когда выполнение операций над данными выполняется параллельно с выполнением кода программного проекта. В этом случае необходимо получать уведомление о завершении очередной команды и выполнять в обработчике события определенные действия.
У объекта Connection достаточно много событий - 9, большая часть из которых возникает по завершении той или иной команды. Давайте рассмотрим эти события:
Event WillConnect(ConnectionString As String, UserID As String, Password As String, Options As Long, adStatus As EventStatusEnum, pConnection As Connection), Event WillExecute(Source As String, CursorType As CursorTypeEnum, LockType As LockTypeEnum, Options As Long, adStatus As EventStatusEnum, pCommand As Command, pRecordset As Recordset, pConnection As Connection). Первое из этих событий будет возникать перед выполнением соединения, второе - перед выполнением команды Execute или Open. Параметры pConnection, pCommand, pRecordset возвращают указатели на соответствующие объекты, а остальные параметры задают те или иные характеристики соединения или команды, которую предстоит выполнить. Как я уже говорил, в обработчике события можно проверить корректность задания параметров.Event BeginTransComplete(TransactionLevel As Long, pError As Error, adStatus As EventStatusEnum, pConnection As Connection),Event CommitTransComplete(pError As Error, adStatus As EventStatusEnum, pConnection As Connection),Event RollbackTransComplete(pError As Error, adStatus As EventStatusEnum, pConnection As Connection).Event ConnectComplete(pError As Error, adStatus As EventStatusEnum, pConnection As Connection),Event Disconnect(adStatus As EventStatusEnum, pConnection As Connection),Event ExecuteComplete(RecordsAffected As Long, pError As Error, adStatus As EventStatusEnum, pCommand As Command, pRecordset As Recordset, pConnection As Connection), Шесть событий этой группы возникают после завершения операций Connection, Execute или операций, связанных с транзакциями. Событие ConnectComplete возникает после того, как было открыто соединение, событие Disconnect - после закрытия соединения. Во всех случаях обработчику событий передаются указатели на соответствующие объекты и информация, необходимая, для того чтобы оценить, насколько корректно была выполнена та или иная операция. Если значение параметра adStatus равно adStatusOk, то операция окончилась успешно, и нет необходимости анализировать объект Error. Если же этот параметр имеет значение adStatusErrorsOccured, то необходимо анализировать объект Error, а, возможно, и всю коллекцию Errors.Event InfoMessage(pError As Error, adStatus As EventStatusEnum, pConnection As Connection). В отличие от остальных это событие встречается не до и не после выполнения операции соединения, а в момент ее выполнения, и выдает предупреждение о возможных ошибках. Параметры, передаваемые обработчику события, имеют привычный смысл.
События объекта Recordset
Только два объекта ADO обладают событиями - Connection и Recordset. События могут возникать перед началом выполнения той или иной команды, что позволяет проверить возможность ее выполнения и произвести отмену выполнения, не дожидаясь появления ошибки. У объекта Recordset таких событий четыре:
Event WillChangeField(cFields As Long, Fields, adStatus As EventStatusEnum, pRecordset As Recordset),Event WillChangeRecord(adReason As EventReasonEnum, cRecords As Long, adStatus As EventStatusEnum, pRecordset As Recordset),Event WillChangeRecordset(adReason As EventReasonEnum, adStatus As EventStatusEnum, pRecordset As Recordset),Event WillMove(adReason As EventReasonEnum, adStatus As EventStatusEnum, pRecordset As Recordset).
Эти события возникают перед тем, как выполняемая операция изменит поле записи, саму запись, например при выполнении операций AddNew или Delete, набор записей, например, при выполнении пакетного обновления, или при перемещении курсора на новую запись. Параметры, передаваемые событию, имеют понятный смысл:
pRecordset - задает указатель на набор записей,adStatus - определяет состояние, в котором находится операция. Перед началом выполнения операции свойству Status целесообразно присвоить значение adStatusCancel. В обработчике события можно проверить значение этого свойства, если оно имеет значение adStausOk, то операция выполняется нормально.AdReason - определяет выполняемую операцию, являющуюся причиной появления события.cFields и Fields - задают соответственно номер поля в массиве Fields, который содержит поля, подлежащие изменениям.cRecords - номер изменяемой записи.
Большая часть событий связана с окончанием выполнения команды. Вот четыре события, которые дополняют события Will:
Event FieldChangeComplete(cFields As Long, Fields, pError As Error, adStatus As EventStatusEnum, pRecordset As Recordset),Event RecordChangeComplete(adReason As EventReasonEnum, cRecords As Long, pError As Error, adStatus As EventStatusEnum, pRecordset As Recordset),Event RecordsetChangeComplete(adReason As EventReasonEnum, pError As Error, adStatus As EventStatusEnum, pRecordset As Recordset),Event MoveComplete(adReason As EventReasonEnum, pError As Error, adStatus As EventStatusEnum, pRecordset As Recordset).
У всех этих событий помимо уже описанных параметров обработчику события передается еще один параметр pError - указатель на объект Error, позволяющий провести обработку ошибок, если они возникли в ходе выполнения операции.
У объекта Recordset есть еще три события:
Event EndOfRecordset(fMoreData As Boolean, adStatus As EventStatusEnum, pRecordset As Recordset). Событие возникает при выполнении операции MoveNext, когда при перемещении по набору достигнут его конец. В обработчике события можно предусмотреть добавление новых записей в конец набора, и повторить выполнение операции. Заметьте, параметр fMoreData следует установить в этом случае как Variant_True.Event FetchProgress(Progress As Long, MaxProgress As Long, adStatus As EventStatusEnum, pRecordset As Recordset), Event FetchComplete(pError As Error, adStatus As EventStatusEnum, pRecordset As Recordset). Первое из этих событий периодически вызывается, чтобы уведомить сколь много записей было доставлено во время выполнения долгой асинхронной операции. Второе событие возникает по завершении операции доставки записей. Параметр Progress указывает число доставленных записей, а MaxProgress - число ожидаемых для получения записей.
Свойства объекта Command
У объекта Command 9 свойств, из которых 7 - терминальные, а два свойства возвращают коллекции Parameters и Propertiies, что отображено на рис.5.1. Давайте рассмотрим описание всех свойств этого объекта:
Property ActiveConnection As Connection. Заметьте, я был не точен, когда говорил, что только два свойства объекта Command возвращают объекты в качестве результата. Свойство ActiveConnection при открытом соединении также возвращает объект Connection, задающий соединение, связанное с командой. Наличие этого свойства отражает связи между объектами. Нельзя выполнить команду, если не установлено соединение. Свойство позволяет получить доступ к объекту Connection, задающему соединение. Здесь есть, однако, некоторая тонкость, из-за которой это свойство имеет двойственную природу. Дело в том, что при закрытом соединении вернуть ссылку на объект невозможно и потому возвращается строка, содержащая описание соединения.
Свойством ActiveConnection обладают и другие объекты - Recordset и Record.
Заметьте, следует корректно установить значение этого свойства - на открытый объект Connection или на корректно определенную строку, задающую соединение, еще до того, как будет выполняться метод Execute объекта Command, в противном случае возникнет ошибка.
Если между двумя выполнениями команды следует изменить соединение, то вначале необходимо свойству ActiveConnection присвоить значение Nothing, в результате Провайдер освободит ресурсы и корректно произведет операцию отсоединения. После чего можно установить новое соединение. Лишь некоторые Провайдеры позволяют проводить изменения в соединении без промежуточного присваивания значения Nothing.
Изменение соединения оставляет нетронутой коллекцию Parameters, если она заполнялась вручную, значения, устанавливаемые Провайдером, при этом очищаются.
При закрытии соединения, связанного с командой, значение свойства устанавливается в Nothing. Попытка связать команду с закрытым соединением приводит к ошибке.Property CommandText As String. Устанавливает или возвращает строку, текст которой содержит описание команды для Провайдера. Чаще всего, этот текст содержит описание SQL-оператора, но может быть именем таблицы, относительным URL-адресом или вызовом хранимой процедуры.
Если свойство Prepered установлено как True и объект Command связан с открытым соединением, то Провайдер транслирует текст команды в исполняемый запрос и сохраняет его в таком виде. Это повышает общую эффективность выполнения при многократных вызовах команды.
В зависимости от значения свойства CommandType возможно изменение значения свойства CommandText, чтобы привести его в соответствие со спецификой Провайдера.
Когда необходимо в команде задать ресурс, такой как файл или каталог, то в свойстве CommandText задается URL-адрес ресурса. URL-адрес, использующий http-схему, приводит к автоматическому вызову специального Провайдера - OLE DB Provider for Internet Publishing.Property CommandStream As Stream. Это свойство появилось в последней на момент написания данного текста версии ADO 2.6. Свойства CommandStream и CommandText является взаимоисключающими. Когда устанавливается свойство CommandStream, то свойство CommandText автоматически становится равным пустой строке. Эти два свойства задают альтернативные способы задания описания команды. Свойство CommandText задает описание строкой. Свойство CommandStream устанавливает или возвращает поток, используемый как ввод для объекта Command. Формат потока определяется спецификой Провайдера.Property Dialect As String. Свойство определено в версии ADO 2.6. Оно задает диалект - синтаксис и систему правил, которыми руководствуется Провайдер при разборе входной строки или потока, заданного свойствами CommandText и CommandStream. Свойство содержит GUID, который и задает диалект, используемый Провайдером.Property CommandTimeout As Long. Смысл этого свойства я описывал ранее при рассмотрении объекта Connection. Напомню, значение свойства задает время, в течение которого команда должна быть выполнена. При превышении времени возникнет ошибка.Property CommandType As CommandTypeEnum. Свойство позволяет установить или определить тип объекта Command, который может быть одним из предустановленных перечислением значений. Тип можно и не устанавливать, - значение свойства по умолчанию - adCmdUnknown. Другие возможные значения типа - adCmdFile, adCmdStoredProc, adCmdTable, adCmdTableDirect, adCmdText. Корректное указание типа повышает эффективность исполнения команды, позволяя Провайдеру не выполнять лишней работы по установлению типа. Вместе с тем неверное указание типа будет приводить к появлению ошибки при вызове метода Execute.Property Name As String. Возвращает или устанавливает имя команды. Я уже говорил о способе использования этого свойства и демонстрировал схему его применения при описании объекта Connection. Далее я расскажу о том, что мои попытки задать имя команде потерпели неудачу при работе с Провайдером, обеспечивающим работу с базой данных Access.Property Parameters As Parameters. Является одним из двух свойств-участников объекта Command. В качестве результата возвращает коллекцию параметров, передаваемых Провайдеру, когда команда задает запрос с параметрами или представляет вызов хранимой процедуры, у которой есть параметры. Это свойство является свойством объекта по умолчанию, что, напомню, означает возможность опускать имя свойства при его вызове, так что записи MyCmd.Parameters(i) и MyCmd(i) эквивалентны. Позже я еще буду рассматривать объекты Parameters и Parameter.Property NamedParameters As Boolean. Это булево свойство определено, начиная с версии ADO 2.6. Когда оно имеет значение True, то имена параметров передаются Провайдеру и могут быть использованы при вызовах команды, требующей передачи параметров. При значении False порядок передачи параметров жестко фиксирован. Property Prepared As Boolean. Булево свойство, имеет статус "чтение/запись". Я уже говорил, что установка значения True повышает эффективность исполнения команды при многократных вызовах, за счет того, что Провайдер единожды выполняет подготовительную работу и затем хранит скомпилированную версию команды. Не все Провайдеры поддерживают это свойство.Property Properties As Properties. Еще одно свойство, возвращающее коллекцию объектов. Каждый элемент этой коллекции - объект Property - задает одну из характеристик объекта Command, передаваемых Провайдеру. Этих характеристик, зависящих от Провайдера и объекта довольно много. Вспомните, в предыдущем примере, когда печатались свойства объекта Connection, его коллекция Properties насчитывала 94 элемента.Property State As Long. Я уже рассказывал об этом свойстве, которым обладают многие объекты ADO, и приводил пример работы с ним.
Свойства объекта Connection
У объекта Connection имеется 13 свойств, два из которых являются свойствами-участниками и возвращают коллекции объектов, остальные являются терминальными свойствами. Свойства, являющиеся объектами, показаны на рис.5.1, где приводится объектная модель ADO. Сейчас же давайте рассмотрим все свойства - терминальные и нетерминальные. Приведу краткое описание свойств:
Property Attributes As Long. Свойство определяет характеристики соединения и может использоваться как для чтения, так и для записи, что позволяет задать или выяснить, каким набором характеристик обладает объект Connection. Значением свойства является сумма значений устанавливаемых характеристик, значение каждой из которой задается соответствующей константой из перечисления XactAttributeEnum. По умолчанию никакие характеристики не задаются и значение этого свойства равно 0. Следует быть осторожным при задании свойства, поскольку значение, представляющее сумму значений несовместимых констант, приводит к возникновению ошибки. Свойством Attributes обладают и другие объекты ADO - Parameter, Field, Property.Property CommandTimeout As Long, Property ConnectionTimeout As Long. Первым из этих свойств обладают два объекта - Connection и Command. Свойства задают в секундах интервал времени, в течение которого должен выполниться метод Execute, когда он вызывается указанными объектами. Если при выполнении метода произойдет задержка сверх указанного времени, то установление соединения или выполнение команды, заданной методом, будет прервано и возникнет ошибка. По умолчанию значение ConnectionTimeout - максимальное время для установления соединения равно 30 секундам, для выполнения команды - 15 секунд. Оба свойства имеют статус "чтение/запись". Заметьте, что значение первого свойства, установленное для объекта Connection не наследуется объектом Command, связанного с этим соединением. Чтобы корректно установить значения свойств необходимо, конечно представлять каково может быть максимальное время установления соединения и время выполнения той или иной команды.Property ConnectionString As String. Одно из наиболее важных и постоянно используемых свойств. Свойство имеет статус "чтение/запись". Информация, заданная в тексте строки, используется для установления соединения с источником данных. Синтаксически строка соединения представляет пары вида: аргумент = значение, разделенные символом ";" (точка с запятой). ADO поддерживает пять аргументов, но в зависимости от Провайдера ему могут предаваться и другие аргументы, которые никак не обрабатываются средствами ADO и передаются непосредственно Провайдеру. Вот список общих для всех Провайдеров аргументов, поддерживаемых ADO: Provider - имя Провайдера, с которым устанавливается соединение. File Name - имя файла, содержащего предустановленную информацию о соединении, передаваемое провайдеру,.Remote Provider - имя Провайдера, используемое при открытии соединения на клиентской стороне. Используется только при работе со службой RDS.Remote Server - имя сервера (путь), используемое при открытии соединения на клиентской стороне. Используется только при работе со службой RDS.URL - адрес, идентифицирующий такие ресурсы, как файл или каталог. Заметьте, свойства, установленные в строке соединения, могут измениться после открытия соединения, поскольку может произойти трансляция аргументов в форму, предусмотренную Провайдером.Property CursorLocation As CursorLocationEnum. Свойство позволяет сделать выбор между различными библиотеками курсоров, доступных Провайдеру. Обычно применяемые значения констант - adUseClient и adUseServer позволяют выбрать библиотеку на стороне клиента или сервера. Курсоры, возвращаемые методом Execute, наследуют заданную установку. Также поступают и объекты Recordsets, наследуя установку из связанного с ними соединения.Property DefaultDatabase As String. Задает имя базы данных по умолчанию. Свойство имеет статус "чтение/запись".Property Errors As Errors. Свойство имеет статус "только чтение". Возвращает коллекцию объектов Error. Каждый из этих объектов создается автоматически Провайдером, если возникает ошибка при выполнении той или иной команды. Вся коллекция связана только с одной командой. При выполнении новой команды при появлении первой ошибки старая коллекция Errors очищается и начинает создаваться заново. Метод Clear позволяет организовать принудительную чистку коллекции. Заметьте, объекты Error создаются только при выполнении кода Провайдера. Ошибки ADO, возникающие в коде VB/VBA, приводят к появлению события OnError и могут быть обработаны как все динамические ошибки периода выполнения.Property IsolationLevel As IsolationLevelEnum. Свойство имеет статус "чтение/запись". Специальные константы задают так называемый уровень изоляции. По умолчанию значение константы - adXactChaos. При удаленном доступе на клиентской стороне допустимо только значение - adXactUnspecified.Property Mode As ConnectModeEnum. Свойство, значения которого задаются перечислением и определяют статус модификации данных. Это свойство имеют объекты Connection, Record и Stream. Для объекта Connection значение по умолчанию - adModeUnknown, для объекта Record - adModeRead, для Stream возможно то или другое значение в зависимости от того, как объект связан с источником данных. Установить значение этого свойства можно только тогда, когда объект закрыт. Изменить статус уже открытого объекта невозможно.Property Properties As Properties. Второе из свойств объекта Connection, возвращающее в качестве результата объект - коллекцию Properties. Этим же свойством обладают и другие объекты - Command и Recordset. Каждый из объектов Property содержит характеристику, передаваемую Провайдеру.Property Provider As String. Строка, задающая имя Провайдера. Это имя может быть установлено и другим способом, например, как один из аргументов при задании свойства ConnectionString.Property State As Long. Многие из объектов ADO обладают этим свойством, которое имеет статус "только для чтения" и определяет совокупность состояний объекта - открыт он или закрыт, а для объектов выполняющих асинхронный метод - состояние выполнения. Поэтому значение свойства задается суммой значений соответствующих констант. Возможные значения констант следующие: adStateClosed = 0, adStateConnecting = 2, adStateExecuting = 4, adStateFetching = 8, adStateOpen = 1. Если свойство возвращает, например, значение 5, то это означает, что объект открыт, а выполняемый метод, например, Execut находится в активном состоянии.Property Version As String. Свойство имеет статус "только для чтения" и возвращает версию библиотеки ADODB.
Свойства объекта Recordset
Объект Recordset имеет несколько десятков свойств. Попробуем разобраться в них:
Property AbsolutePage As PositionEnum, Property AbsolutePosition As PositionEnum, Property PageCount As Long, Property PageSize As Long, Property RecordCount As Long - группа свойств, определяющих нумерацию записей в наборе. Поскольку объект Recordset может определять достаточно большой набор записей, то, иногда целесообразно придать ему определенную структуру и выделить в нем страницы, на каждой из которых находится фиксированное число записей. Исключением является последняя страница, на которой записей, естественно, может быть меньше. Свойство PageSize определяет число записей на странице, по умолчанию значение этого свойства равно 10. Свойство PageCount задает число страниц, AbsolutePage - номер страницы, на которой расположена текущая запись набора. Значением этого свойства является целое в интервале от 1 до PageCount или значение из перечисления PositionEnum. Свойство RecordCount задает число записей в наборе, а свойство AbsolutePosition - порядковый номер записи, значением которого есть целое в и нтервале от 1 до RecordCount или одно из значений перечисления PositionEnum. Это перечисление содержит всего три значения: adPosBOF, adPosEOF, adPosUnknown, первые два из которых определяют позицию в начале и конце набора (перед первой и после последней записи набора), а третье значение задает неопределенную позицию. Заметьте, что пользоваться номерами записей для их идентификации следует с большой осторожностью, поскольку нумерация изменяется, когда в набор добавляются или из него удаляются записи. Для идентификации записей используются закладки, о которых будет сказано чуть ниже. Не все Провайдеры полностью поддерживают эти свойства.Property ActiveCommand As Object, Property ActiveConnection As Variant. Свойства отражают связи между объектами ADO. Значением этих свойств является ссылка на объект Command, породивший набор записей и ссылка на соединение, при котором команда выполнялась. Если соединение закрыто, то выдается строка, описывающая соединение. Если набор записей создавался без явного выполнения команды, то возвращается ссылка на пустой объект Null.Property BOF As Boolean, Property EOF As Boolean. Их имена пришли из соответствующих названий при работе с файлами - Begin Of File (BOF) и End Of File (EOF). Два булевых свойства, позволяющих определить достигнут ли конец набора записей при его последовательном просмотре в том или ином направлении. Вот пример двух классических схем прохода набора записей:
Public Sub AllRecords() ' Две схемы прохода по набору записей With Rst1 'Схема1: От начала к концу набора .MoveFirst Do While Not .EOF 'Обработка текущей записи Debug.Print Rst1!Название .MoveNext Loop 'Схема2: От конца к началу набора .MoveLast Do 'Обработка текущей записи Debug.Print Rst1!Название .MovePrevious Loop Until .BOF End With End Sub
Я запустил на выполнение эту процедуру сразу по окончании работы процедуры CreateCommands из предыдущего примера и получил корректные результаты.Property Bookmark As Variant. Часто в наборе записей необходимо иметь некоторое количество выделенных записей и периодически к ним обращаться. Как я уже говорил, номера записей для этой цели не годятся, поскольку они изменяются вместе с изменением самого набора. Для идентификации записей используются закладки, однозначно идентифицирующие запись при всех изменениях набора.
Когда открывается набор записей, то все записи имеют уникальные закладки. Сохранение закладок - дело рук программиста. Вы можете сохранить закладку в собственной переменной типа Variant, используя значение свойства Bookmark. В тот момент, когда значение этой переменной будет присвоено свойству Bookmark набора записей, автоматически текущей станет запись с указанной закладкой. Вот пример, иллюстрирующий работу с закладками:
Public Sub CreateBookmarks() 'Работа с закладкой Dim MyBookmark As Variant With Rst1 .MoveFirst Do While Not .EOF 'Обработка текущей записи If Rst1!Название = "Книжная лавка" Then 'Создаю закладку MyBookmark = .Bookmark End If .MoveNext Loop 'переход к записи с закладкой .Bookmark = MyBookmark Debug.Print Rst1!Название End With End Sub
Заметьте, по окончании цикла, текущая запись не определена, поскольку истинно свойство EOF, и это означает, что указатель сместился за последнюю запись. Но как только свойству Bookmark присвоено значение сохраненной закладки, текущей становится нужная нам запись.Property CacheSize As Long. Я уже говорил, что основная работа с записями базы данных ведется во временной памяти, называемой буфером или кэшем. Несмотря на название, это свойство явно не задает размер памяти буфера. Оно должно быть целым значением в интервале от 1 до Maximum Open Rows. Правая граница интервала задается одноименным свойством из коллекции Properties объекта Recordset. Заметьте, значение 0 приводит к ошибке, по умолчанию значение свойства равно 1. Свойство позволяет указать число записей, помещаемых Провайдером в кэш при одном обращении. Заметьте, идет постоянный своппинг (обмен данными) в процессе работы, поскольку, когда достигнута последняя запись в буфере и нужна следующая запись, будет идти подкачка в буфер очередной порции. Значение свойства настраивается в течение жизни объекта Recordset.
Свойства объекта Recordset (продолжение)
Property CursorLocation As CursorLocationEnum, Property CursorType As CursorTypeEnum, Property LockType As LockTypeEnum - группа свойств, связанных с курсором. Общее представление о курсоре уже дано, так что осталось сообщить лишь небольшие детали. Свойство позволяет задать положение курсора. Два его возможных значения: adUseClient и adUseServer определяют, на какой стороне идет работа с курсором - на клиентской или на серверной стороне. Свойство CursorType позволяют определить или задать тип курсора. Я уже определял четыре возможных значения, доступных при задании типа курсора. Свойство LockType позволяет управлять закрытием доступа. На серверной стороне можно задать одно из трех возможных значений этого свойства: adLockReadOnly, adLockOptimistic, adLockPessimistic. На клиентской стороне возможно только одно значение - adLockBatchOptimistic.Property DataSource As Unknown, Property DataMember As String. Два взаимосвязанных свойства. Первое из них задает источник данных для объекта Recordset. Данные в этом источнике могут представлять совокупность именованных разделов, называемых элементами источника данных - DataMember. Второе свойство и определяет конкретный элемент источника данных. В одной из последующих глав, посвященных компонентам OWC (Office Web Components), я расскажу подробнее и приведу примеры работы с подобными источниками данных и их элементами.Property EditMode As EditModeEnum. Свойство указывает статус редактирования текущей записи. Его возможные значения: adEditAdd, adEditDelete, adEditInProgress, adEditNone указывают, была ли запись добавлена, удалена, операция редактирования записи не завершена или не применялась. Анализируя значение этого свойства, можно принять правильное решение и вызвать, например, метод Update или CancelUpdate, чтобы принять или отменить результаты редактирования.Property Fields As Fields. Одно из немногих свойств, возвращающих объект - коллекцию полей, элементами которой являются объекты класса Field. Каждый такой объект задает одно поле в записи, а вся коллекция - совокупность всех полей. Позже я подробнее расскажу об этих объектах, о том, что можно делать с самой коллекцией, можно ли добавлять и удалять поля, о свойствах и методах, доступных при работе с отдельным полем.Property Filter As Variant. Это свойство позволяет наложить фильтр на набор записей и создать новый набор, состоящий из записей, удовлетворяющих условию фильтра. При задании корректного фильтра текущей записью становится первая запись нового набора, перемещение по набору идет только по отфильтрованным записям, остальные записи как бы скрываются и становятся недоступными до тех пор, пока фильтр не будет отменен. Задать фильтр можно тремя различными способами: Строкой критерия - представляет набор из элементарных условий, связанных логическими операциями AND и OR. Каждое элементарное условие имеет вид: <Имя поля> Operator <Значение>, где Operator является одной из операций сравнения или оператором Like, задающим сравнение с образцом. Образец может включать обычные для образцов метасимволы "?" и "*". Заметьте, на операциях AND и OR не задан приоритет, по этой причине для сложных критериев необходимо правильно задать скобочную запись критерия. Критерий должен представлять дизъюнкцию конъюнктов, это означает, что выражения в скобках должны соединяться знаком AND, а сами скобки знаком операции OR.Массивом закладок - каждая закладка, как я говорил выше, определяет одну запись, а их массив задает все записи, которые должны быть отобраны в результате применения фильтра.Стандартным фильтром - константой из перечисления FilterGroupEnum. Возможные значения в этом перечислении: adFilterAffectedRecords, adFilterConflictingRecords, adFilterFetchedRecords, adFilterPendingRecords, adFilterNone.
Приведу пример создания фильтра:
Public Sub CreateFilter() 'Работа с фильтром
'Создать соединение CreateConnection 'Создать и выполнить команду 'задание свойств объекта Command Cmd1.ActiveConnection = Con1 Cmd1.CommandText = "SElect * From [Заказы]" Cmd1.CommandType = adCmdText Cmd1.Prepared = True ' вызов команды на исполнение Set Rst1 = Cmd1.Execute
'Задать фильтр Rst1.Filter = " [Сотрудник] = 'Петрова О.' AND " _ & "[Заказчик] = 'Книжная лавка'" With Rst1 .MoveFirst Do While Not .EOF 'Обработка текущей записи Debug.Print "Стоимость заказа = ", Rst1!Стоимость .MoveNext Loop End With End Sub
По-видимому, в дополнительных комментариях текст процедуры не нуждается. Не буду приводить и отладочных результатов, замечу, что все работало правильно, и на печать была выдана стоимость всех заказов, удовлетворяющих условию фильтра. Продолжим рассмотрение свойств:Property Index As String. Устанавливает или возвращает строку, задающую имя индекса. Поля в таблицах базы данных могут быть индексированы. Система индексов позволяет быстрее находить нужные записи. Свойство Index используется в сочетании с методом поиска Seek для быстрого поиска записей в наборе по значениям индексируемого поля. Замечу, что не все Провайдеры позволяют работать с индексами, в частности Провайдер, обеспечивающий работу с базой данных Access, не поддерживает индексы.Property MarshalOptions As MarshalOptionsEnum. Свойство позволяет управлять транспортировкой измененных записей набора на сервер. Применяется оно только на клиентской стороне и позволяет указать, нужно ли передавать серверу все записи или только те записи, которые подверглись изменениям. Свойство имеет всего два возможных значения: adMarshalAll, adMarshalModifiedOnly.Property MaxRecords As Long. Позволяет ограничить число записей, одновременно передаваемых Провайдером от источника данных объекту Recordset. По умолчанию свойство имеет значение 0, указывающее, что ограничений на число передаваемых записей не устанавливается.Property Properties As Properties. Второе из свойств, возвращающее объект - коллекцию Properties объекта Recordset. В этой большой коллекции, насчитывающей около сотни элементов - объектов класса Property собраны характеристики объекта Recordset, меняя которые можно управлять объектом. Многие из них задаются по умолчанию, обращение к ним позволяет выяснить текущие свойства объекта.Property Sort As String. Если свойство Filter позволяет провести фильтрацию набора записей, то свойство Sort позволяет провести сортировку по одному или нескольким полям в порядке возрастания или убывания значений. Часто бывает удобнее сортировку и фильтрацию задавать не в операторе SQL при формировании объекта Recordset, а выполнять эти операции по мере необходимости, работая с уже созданным объектом и используя возможности, предоставляемые его свойствами. Вот пример подобной сортировки:
Public Sub CreateSortedSet() 'Сортировать полученный набор данных 'Создать соединение CreateConnection 'Создать и выполнить команду 'задание свойств объекта Command Cmd1.ActiveConnection = Con1 Cmd1.CommandText = "Select * From [Заказчики]" Cmd1.CommandType = adCmdText Cmd1.Prepared = True ' вызов команды на исполнение Set Rst1 = Cmd1.Execute 'Задание критерия сортировки Rst1.Sort = "Город ASC" 'Работа с отсортированным набором With Rst1 .MoveFirst Do While Not .EOF 'Обработка текущей записи Debug.Print "Организация = ", Rst1!Название, Rst1!Город .MoveNext Loop
End With End Sub
Для простоты я ограничился сортировкой по одному полю, указав ASC - возрастающий порядок следования значений по этому полю.Property Source As Variant. Свойство позволяет задать или выяснить, что является источником данных для объекта Recordset. При установке значением свойства может быть ссылка на объект Command или строка. Возвращается всегда только строка, если источником данных является объект Command, то строка содержит описание источника - соответствующий SQL-оператор. Источником может быть также имя таблицы или имя хранимой процедуры.Property State As Long, Property Status As Long. Я уже рассказывал о свойстве State, составное значение которого позволяет определить, открыт или закрыт объект и состояние выполняемого метода. Свойство Status в отличие от свойства State характерно только для объекта Recordset и определяет статус текущей записи по отношению к пакетным обновлениям или другим групповым операциям. Также как и для свойства State, его значение представляет сумму значений констант, в совокупности, задающих статус записи. Константы принадлежат перечислению RecordStatusEnum, задающему 18 различных значений, приведу лишь несколько из них: adRecNew, adRecInvalid, adRecUnmodified. Заметьте, как обычно в таких случаях, значения констант задаются степенями числа 2, так что их сумма всегда представляет уникальное значение и однозначно определяет все слагаемые. Программисты говорят в таких ситуациях, что значение свойства задает маску.Property StayInSync As Boolean. Система ADO позволяет создавать иерархические наборы записей, в которых запись объекта Recordset может ссылаться на своих потомков. Такие иерархические наборы поддерживаются специальной службой - Microsoft Data Shaping Service for OLE DB. Булево свойство StayInSync задается только для иерархических наборов и позволяет управлять навигацией по иерархии записей в таком наборе. Значение True указывает, что при изменении позиции родительской записи раздел (Chapter), содержащий потомков, будет указывать на новое положение родителя. При значении False ссылка не меняется, так что потомки получают нового родителя.
Универсальный доступ к данным и ADO
Microsoft предлагает своим пользователям и, прежде всего, разработчикам корпоративных приложений единую стратегию доступа к данным - Universal Data Access (UDA), не зависящую, в высокой степени, от типа используемых хранилищ данных, применяемых инструментальных средств и языков программирования. В основе этой стратегии лежат компоненты доступа к данным - Microsoft Data Access Components (MDAC). Три базисных интерфейса и, соответственно, три группы объектов определяют UDA и MDAC:
Низко уровневый, высоко эффективный по производительности COM-интерфейс баз данных - интерфейс OLE DB (OLE Data Base), позволяющий получать доступ как к реляционным (табличным), так и не реляционным (иерархическим и потоковым) базам данных.Низко уровневый, высоко производительный интерфейс ODBC (Open Data Base Connectivity), специально спроектированный для связи с различными реляционными базами данных и структурированным языком запросов SQL (Structured Query Language).Интерфейс высокого уровня ADO, являющийся надстройкой над интерфейсом OLE DB, позволяющий получать легкий доступ к данным при работе не только на VC, но и на VB/VBA и VB Script.
Начиная с Windows 2000, MDAC является частью операционной системы. Microsoft создала специальный Web-узел - Microsoft Universal Data Access Web site, на котором можно найти последние версии компонент и свободно произвести обновление операционной системы от Windows 95 до Windows Me.
Поскольку для VBA - разработчика, как я уже говорил, наибольший интерес представляет интерфейс верхнего уровня - ADO, то я ограничусь лишь штрихами к портрету низкоуровневых интерфейсов.
