Соединение, курсор

Доступ к базам данных с помощью технологий ADO.

Рассмотрим задачу: из базы данных, созданной в MS Access извлечь данные и поместить часть из них на форме, а часть – на листе MS Excel. Для решения этой задачи можно воспользоваться объектами ADO.

Важно! Этими объектами можно пользоваться и для управления текущей базой данных, т.е. в модулях базы данных.

Объекты ADO не связаны в иерархическую схему. Между ними, скорее сеть. Тем не менее, выделяется один главный объект – Connection – соединение. Его метод Open выполняет действия по организации связи с базой данных. Для того чтобы его применить, сначала необходимо создать объект, внести в его свойства параметры соединения, наконец, выполнить метод Open.

Dim adoConn As ADODB.Connection

Set adoConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False"

adoConn.ConnectionTimeout = 15

adoConn.CommandTimeout = 30

adoconn.CursorLocation = ADODB.adUseClient

'Подключаемся к базе данных (т.е. открываем БД)

adoConn.Open

В приведенном фрагменте самое утомительное – написать строку соединения. Можно несколько облегчить этот процесс и даже сделать его немного осмысленнее. Для этого сначала создайте пустой текстовый файл с произвольным именем и расширением udl (User Data Link), например, «try.udl». Затем дважды щелкните по этому файлу в проводнике (или Total Commander). Операционная система покажет вам окно с несколькими вкладками «Поставщик», «Подключение», «Дополнительно», «Все». Перейдите сначала на вкладку «Поставщик» и выберите требуемое программное средство. Достаточно часто можно по наименованию догадаться, кого выбрать, т.к. в имени обычно присутствует имя сервера базы данных. Если это не так, ищите в документации и справочных пособиях. Например, если вам необходимо подключиться к базе данных в формате MS SQL Server, выберите Microsoft OLE DB Provider for SQL Server). Для подключения к базе данных Oracle выберите Microsoft OLE DB Provider for Oracle. Для подключения к базе данных Access нужно выбрать Microsoft JET 4.0 OLE DB Provider. Затем переходим на вкладку «Подключение». Вид этой вкладки для разных поставщиков (провайдеров) разный. В случае подключения к базе MS Access вас попросят выбрать файл с базой данных при помощи кнопки «Обзор». Здесь же вы можете задать имя пользователя, от которого будет устанавливаться соединение, пароль. На вкладке «Дополнительно» можно задать некоторые параметры сетевого соединения, тип соединения (только для чтения или с возможностью изменения). На вкладке «Все» приведен весь перечень параметров, даже не указанных на предыдущих вкладках. Скорее всего, вам не потребуются две последние вкладки. Проверить правильность задания параметров можно, нажав на кнопку «Проверить подключение», расположенную на вкладке «Подключение». Если не выдано сообщение об ошибке, вы все сделали разумно. Разобравшись с файлом try.udl, откройте его в блокноте (или другом текстовом редакторе), скопируйте третью строку (она длинная, до конца файла!) и вставьте скопированное в программу, присвоив ее objConn.ConnectionString.

Процесс создания подключения через udl файл похож на создание ODBC источника. Обратите внимание на имеющиеся различия – свойства, параметры соединения. Эти свойства указывают на разные возможности, предоставляемые разными драйверами. Учтите, что на этих вкладках указаны не все параметры соединения. В программе вы сможете задать еще множество параметров.

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

Параметр CommandTimeout задает максимальное время ожидания выполнения команды. Будьте осторожны, если вы зададите слишком маленькое время, его может не хватить для выполнения некоторого сложного и объемного запроса. Если же вы зададите слишком большое время, то система в случае ошибки долго не будет выдавать сообщение.

Далее в программе должны следовать операторы работы с установленным соединением. Мы их пока пропустим. В конце программы (всей программы, например, по закрытию формы) следует корректно закрыть соединение. Это выполняется следующим образом: закрываем соединение методом Close, освобождаем объектную переменную, присвоив ей значение Nothing.

adoConn.Close

Set adoConn = Nothing

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

Dim adors As New ADODB.Recordset

adors.ActiveConnection = objConn

adors.CursorType = ADODB.adOpenForwardOnly

adors.LockType = ADODB.adLockReadOnly

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

Тип курсора CursorType задает возможные направления перемещения по набору записей, а также определяет, будут ли в данном наборе отражаться изменения, сделанные другими пользователями. С этим параметром дело обстоит весьма непросто. В документации содержатся некоторые описания возможностей набора заданного типа, но в действительности все обстоит не так, как описано.

Во-первых, складывается впечатление, что типы Dynamic и Forward Only просто не реализованы! Причем не только для удаленного доступа, то есть для клиентского приложения, реализованного в некотором стороннем приложении, но и для локального, то есть из программы, встроенной в базу данных! В этом можно убедиться, написав программу, в которой делаются попытки создать курсор того или иного типа (задается значение параметра CursorType перед методом Open), потом набор открывается (метод Open), а затем анализируются возможности набора.

На основе проведенных вышеописанным способом экспериментов выяснено следующее. Если задается значение CursorLocation равным adUseClient, всегда после открытия набора тип курсора становится равным Static, независимо от того, какое было задано значение типа курсора перед открытием набора. Если задается значение adUseServer, всегда после открытия набора тип курсора становится равным KeySet. Но следует помнить, что при расположении курсора на стороне клиента мы получаем курсор, в котором верно показывается количество записей, доступны операции добавления, сортировки, фильтрации, поиска, возможно использование закладок. Если же расположение курсора задано adUseServer, то сортировка по данному курсору невозможна никогда. Добавление записей, поиск и фильтрация всегда доступны. А свойство recordCount и возможность работы с закладками Bookmark доступны, если перед методом Open мы задавали тип курсора равным KeySet или Static и недоступны, если задавали Dynamic или ForwardOnly.

В результате проведенных сообществом разработчиков экспериментов было выяснено, что заявленного повышения производительности при использовании курсора типа ForwardOnly не происходит.

Неполная и неясная реализация характерна не только для свойства CursorType. Особенно неприятно то, что в ADO изменены некоторые правила, привычные для программистов, ранее пользовавшихся технологией DAO.

Одно из таких отличий: разное определение метода Close для объекта Recordset. В технологии DAO метод Close аналогичен присваиванию объектной переменной класса RecordSet значения Nothing. В ADO этот метод не уничтожает объект. С закрытым объектом невозможно работать обычным образом, для него неприменимы методы перемещения по записям, выборка значений полей и т.п. Но многие свойства набора остаются сохраненными, например, условие фильтрации, сортировки. С таким подходом можно было бы согласиться, если бы, во-первых, существовал бы метод Clear – очистка этих свойств. Но главное, если бы при открытии набора по другой таблице свойства разумным образом изменялись. Реально вы можете неожиданно для себя, открыв набор, увидеть не все ожидаемые записи. Но хуже всего то, что при попытке открыть набор по другой таблице генерируются разные странные сообщения об ошибках. С другой стороны можно отнестись к этому, как к преимуществу. Если вы хотите одинаково отобрать записи из разных таблиц (или разными командами Select), то вам предлагается механизм, экономящий программный код. Тем не менее, если вы не собираетесь пользоваться свойствами набора, то закрытие его необходимо выполнять в два этапа: сначала вызвать метод close, а затем присвоить переменной типа RecordSet значение Nothing.

Если полагаться на рекомендации, даваемые сообществом разработчиков, «нет никаких весомых причин, по которым вам необходимо отказываться от технологии DAO и переходить на ADO».

Далее приводятся возможные значения параметров соединения по документации:

adOpenForwardOnly – движение только вперед, изменения не видны. Самый быстрый тип, но и самый ограниченный. Многие свойства объекта при этом значении параметра CursorType недоступны, например RecordCount (количество записей).

adOpenStatic – движение и вперед и назад, изменения других пользователей не видны.

adOpenKeyset – движение в произвольном направлении, видны изменения полей, произведенные другими пользователями, но не видны добавления и удаления записей.

adOpenDynamic – движение в произвольном направлении, видны изменения полей, произведенные другими пользователями, также видны добавления и удаления записей.

Тип блокировки – свойство LockType – дает возможность изменять набор, определяя, когда эти изменения будут переданы другим пользователям.

adLockReadOnly – не изменяемый набор.

adLockPessimistic – набор изменяемый, причем запись блокируется (становится недоступной для изменения других пользователей) как только текущий пользователь начинает ее редактировать.

adLockOptimistic – набор изменяемый, запись блокируется только при вызове метода Update.

adLockBatchOptimistic – набор изменяемый, запись блокируется только при вызове метода Update для обновления пакета записей.

Расположение курсора CursorLocation – параметр, указывающий, на чьей стороне будет расположен (буферизован) курсор. Возможные значения – на стороне клиента adUseClient или на стороне сервера adUseServer

Важно! Параметры объекта RecordSet могут противоречить параметрам объекта Connection. Очевидно, что если соединение вы установили только для чтения, то создать изменяемый набор записей не удастся.

Наконец, можно выполнить метод Open. Параметр метода – либо имя таблицы (хранимого запроса), либо строка с текстом SQL-запроса.

adors.Open "вид_доставки"

Или так:

adors.Open "Select * from вид_доставки"

Можно задавать свойства получаемого набора записей и параметрами метода Open. Есть одна характеристика, которую можно задавать только параметром метода Open, это – Option. Этот параметр характеризует источник записей для набора. Можно задать adCmdText, что означает, что первый параметр метода Open - строка с SQL-запросом. adCmdTable – утверждение, что первый параметр – имя таблицы, и провайдер генерирует SQL-запрос, выбирающий строки таблицы. adCmdTableDirect – означает, что устанавливается связь или ссылки прямо на записи таблицы. adCmdStoredProc – обращение к хранимой процедуре.

Свойства и методы набора записей (курсора).

Методы объекта позволяют перемещаться по набору записей: MoveFirst, MoveLast, MoveNext, Move Previous.

Обнаружение начала и конца набора выполняется при помощи двух свойств:EOF, BOF. EOF устанавливается в true после прочтения последней записи или перемещения за последнюю запись. При попытке переместиться назад при нахождении курсора на первой записи возникает состояние BOF.

Для набора с неизвестным количеством записей (свойство recordCount = -1) подходит условный цикл вида

Do While Not adors.EOF

‘ работа с текущей строкой

Loop

Набор записей можно отсортировать (упорядочить). Для этого надо установить значение для свойства Sort равному имени одного или нескольких полей через запятую. Переход на первую запись в указанной сортировке осуществляется автоматически.

adors.Sort = "[вид_доставки]"

На записи, к которой вам надо будет вернуться позже, можно поставить закладку.

bm = adors.Bookmark

…

adors.Bookmark = bm

Можно искать запись по строковому выражению, составленному из имен полей, операций сравнения, логических операций. Строковые константы в строке поиска можно заключать в одинарные кавычки или в символы #, константы типа «дата» заключаются в символы #.

adors.Find "[ вид_доставки] = 'автотранспорт'"

Для наборов, расположенных на сервере можно применить метод Seek. Кстати, набор может быть определен как расположенный на сервере, хотя свойство CursorLocation для Connection может быть задано adUseClient. Еще одно обязательное требование – указание параметра метода Open Options:=adCmdTableDirect. Если не указать этот параметр, то провайдер посчитает, что необходимо создать запрос, получающий все записи таблицы, а не установить связь непосредственно с таблицей. А для временного набора записей, каковым является результат SQL-запроса, не существует индексов. Метод Seek – тоже поиск записи, как и метод Find. Но поиск осуществляется по значению ключа с применением индексного файла. Такой поиск работает значительно быстрее последовательного поиска, осуществляемого методом Find. Второй параметр метода Seek задает направление поиска от текущей записи.

Перед выполнением перехода необходимо установить индекс - свойство набора Index. Получить имя индекса можно из свойства объекта Catalog.

adors.CursorLocation = adUseServer

adors.CursorType = adOpenKeyset

adors.Open "вид_доставки", Options:=adCmdTableDirect

adors.Index = adocat.Tables("вид_доставки").Indexes(0).Name

adors.Seek Array("автотранспорт"), adSeekFirstEQ

Набор можно отфильтровать, удаляя ненужные записи. Строка для фильтра – условие для оставляемых записей.

adors.Filter = " = 'автотранспорт'"

В набор можно добавить запись методом AddNew. Если есть поле, для которого задан автоинкремент, то его не указывают в списке полей. Для ключевых неавтоинкрементарных полей обязательно указывать значения. Для остальных – не обязательно. Параметры метода AddNew: первый – массив имен или номеров полей, второй – массив значений. Важно задавать массивы согласованно.

adors.AddNew Array("вид_доставки ", "newcol"), Array("пешком", "113")

Методом Open можно открыть непараметрический запрос. Если запрос параметрический, чтобы его выполнить, следует обратиться к методу Execute объекта Command, связанного с объектом Connection. На месте параметра в тексте запроса пишется вопросительный знак. Параметр метода Execute – массив значений параметров запроса.

Dim cm As ADODB.Command

Set cm = New ADODB.Command

cm.ActiveConnection = adoconn

cm.CommandText = _

"Select * from вид_доставки where вид_доставки=?"

cm.CommandType = adCmdText

Set adors = cm.Execute(Parameters:=Array("автотранспорт"))

Полученный в результате запроса набор записей можно сохранить в виде xml-файла в кодировке UTF-8.

adors.Save "L:/new_zapros1.xml", adPersistXML

' Set objRecordset = objConn.Execute("SELECT ФиоАвтора,НазваниеКниги FROM Книга")

' без SQL, (указываем таблицу, запрос и др...)

Set objRecordset = objConn.Execute("Книга, Автор") '- в данном сучае две таблицы

Do While Not objRecordset.EOF

Cells(i, 1) = objRecordset.Fields("автор.ФИОАвтора").Value

Cells(i, 2) = objRecordset.Fields("Названиекниги").Value

objRecordset.MoveNext 'переход на следующую запись

Loop

' работа с транзакциями

' Транзакция - это операция или несколько операций, которые либо выполняются полностью без ошибок, либо не выполняются вовсе

' (т.е. происходит откат системы до состояния начала транзакции)

' так же танзакции могут быть вложенными. Т.е разделяться по уровням.

' Транзакция "1" указывает, что вы открыли транзакцию верхнего уровня (то есть транзакция не вложена в пределах другой транзакции),

' Транзакция уровня "2" - транзакцию второго уровня (транзакция, вложенная в пределах транзакции верхнего уровня), и т.д.

' пржде чем откатить или закрыть т-ию первого уровня. необходимо откатить или закрыть транзакцию вторго уровня

' и т.д

' задача: на листе Екселя хранятся таблицы, которые необходимо перенести в Access/

' Но тут вдруг составитель таблиц обнаружил ошибки (например, секретарь, программист..... или директор увидел что работники все сделали и решил заставить работать, удалив некоторые данные)

' пометив ячейки с ошибками крсным цветом

' требуется созадть таблицы в Accessе из Екселя, учитывая, что таблицы с неверными данными создавать не надо

'

' так же не ообходимо установить метку, что базу данных можно изменять.... смотри параметры WillExecute(Source, CursorType, LockType, Options, adStatus, pCommand, pRecordset, pConnection))

' определимся с расположением таблиц в Ексле. Пусть таблицы располагаются друг под другом, разделенные пустой строкой. Сперва название таблицы, на следующей строке название столбцов

' очевидно, что надо пройти по всем строкам в Екселе. все - это сколько? когда остановиться? когда будут две подряд пустые

' Нашли пустую строку, следующая - это название таблицы. за тем строка с названием столбцов, затем строки таблицы

' Сперва подумаем над алгоритмом решения....

'0) организовать хранения таблиц на листе Екселя

'1) подключиться к БД

'2) Разобраться с добавлением таблиц

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' подсказка: создание таблицы в Access из Екселя

' Dim objConn As ADODB.Connection

' Dim objRecordset As ADODB.Recordset

' Set objConn = CreateObject("ADODB.Connection")

' Set objRecordset = objConn.Execute("CREATE TABLE newTable (newColumn INT PRIMARY KEY)")

' где в этой строчке тип данных? как описать несколько полей(столбцов)? какой столбец ключевой?

' как обозначаться будут типы данных: числовой, дата, текстовый, денежный?

' создать можно только одно ключевое поле

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'3) Для решения задачи будет три основных цикла: проход по таблицам, проход по строчкам таблицы и проход по столбцам таблицы

'4) Для каждой таблицы необходимо составлять SQL команду, наподобии: "CREATE TABLE ИмяТаблицы (имяКолонки1 ТипДанных1, имяКолонки2 типДанных2)"

' чтобы записать SQL запрос в строку необходимо разбить эту строку на меняющиеся части и постоянные.

' т.т строка SQL состоит из команды "CREATE TABLE " и и меняющейся части(ИмяТаблицы имяКолонки ТипДанных).

' Все ли известно? ИмяТаблицы получаем? если нет смотри пункт 3. ИмяКолонки получаем аналогично как и имя таблицы.

'

' Поразмышляли, вот теперь ближе к делу.

' Алгоритм решения простой: мы передвигаемся по строчкам екселя вниз пока не закончатся таблицы. Как определить конец?

' Что содержит строка, идущая после таблицы? А что будет содержать вторая строка после таблицы?

' Т.е. как тлько увидим две пустые строки значит это конец таблицам.(не забываем, что проверяем по первому столбцу)

'5) чтобы определить тип поля, можно записать макрос.Например, во время записи макроса изменить выделенным ячейкам формат данных

' из полученного макроса можно получить значения того, как Ексель представляет типы. Например, формат для даты получим "dd/mm/yy;@"

'6) Транзакция характеризуется началом и концом:

'

' Dim objConn As ADODB.Connection

' Set objConn = CreateObject("ADODB.Connection")

'....

'

' objConn.BeginTrans 'начало ТА

'......

' objConn.RollbackTrans 'откат транзакции

'......

' objConn.CommitTrans 'конец ТАНЗАКЦИи

' внутри каждой транзакции могут быть свои транзакции, так называемые ТА нижнего уровня.

' Нельзя завершить ТА верхн ' Dim objRecordset As ADODB.Recordset

его уровня, если не завершена ТА нижнего уровня. Аналогично матрешке.....

' Dim objRecordset As ADODB.Recordset

Sub создатьБД()

' Dim objRecordset As ADODB.Recordset

Dim objConn As ADODB.Connection

Dim objRecordset As ADODB.Recordset

Set objConn = CreateObject("ADODB.Connection")

'теперь необходимо определить ConnectString (параметры подключения к БД или к кому - либо объекту)

ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False"

objConn.ConnectionString = ConnectString

objConn.ConnectionTimeout = 15

objConn.CommandTimeout = 30

objConn.Open

Dim nstr As Integer

Dim nstolb As Integer

Dim nameFields(30) As String

nstr = 1

Do While Cells(nstr, 1) <> "" And Cells(nstr + 1, 1) <> ""

NameTBL = Cells(nstr, 1) 'имя таблицы

nstr = nstr + 1

' определим количество СТОЛБЦОВ И ИХ ТИПЫ(тип возьмем из ниже лежащей ячейки), и запишем их в строку

nstolb = 1

NameStolbs = ""

Do While Cells(nstr, nstolb) <> ""

nameFields(nstolb) = Cells(nstr, nstolb)

NameStolbs = NameStolbs & Cells(nstr, nstolb) & " "

Select Case Cells(nstr + 1, nstolb).NumberFormat

Case "dd/mm/yy;@" ' дата

NameStolbs = NameStolbs & "date, "

Case 0

NameStolbs = NameStolbs & "int, "

Case "#,##0.00$" '- денежный формат

NameStolbs = NameStolbs & "money, "

Case "@" '- текстовый

NameStolbs = NameStolbs & "string, "

End Select

nstolb = nstolb + 1

Loop

nstr = nstr + 1

NameStolbs = "(" & Left(NameStolbs, Len(NameStolbs) - 2) & ")"

'НАЧИНАЕМ ТРАНЗАКЦИЮ

otkat = False

objConn.BeginTrans

' создаем таблицу

Set objRecordset = objConn.Execute("CREATE TABLE " & NameTBL & NameStolbs)

' рекордсету необходимо задать тип доступа смотри описание (' WillExecute(Source, CursorType, LockType, Options, adStatus, pCommand, pRecordset, pConnection))

objRecordset.Open NameTBL, objConn, adOpenDynamic, adLockPessimistic

' начинаем добавление записей (можно в строчку, тогда Update не требуется, objRecordset.AddNew "newColumn" = 55654)

' теперь проходимся по всем столбцам и строкам новой таблицы

Do While Cells(nstr, 1) <> ""

objRecordset.AddNew

For i = 1 To nstolb - 1

objRecordset.Fields(nameFields(i)) = Cells(nstr, i)

' если есть заливка, тогда ставим метку на откат

If Cells(nstr, i).Interior.Pattern <> xlNone Then

otkat = True

End If

Next

objRecordset.Update

nstr = nstr + 1

Loop

' в этом месте закончили с одной таблицей

If otkat Then ' если произошел откат

objConn.RollbackTrans

Else

'ЗАКАНЧИВАЕМ ТАНЗАКЦИЮ

objConn.CommitTrans

End If

Set objRecordset = Nothing

nstr = nstr + 1

Loop

Set objRecordset = Nothing

objConn.Close

Set objConn = Nothing

End Sub

Дата добавления: 2014-01-04