Вопрос: Access и ODBC Совет: Для начала ODBC (Open DataBase Connectivity) - это открытый интерфейс доступа к базам данных, разработанный фирмой Microsoft. Он представляет собой API довольно низкого уровня и предназначен, в основном, для прямого использования в программах, написанных на C, C++ или, в крайнем случае, на VB. Несмотря на своё происхождение, этот интерфейс является кроссплатформным и с успехом работает и в Windows, и в UNIX/Linux, и в MacOS. Нет ничего невозможного и в том, чтобы воспользоваться функциями этого замечательного интерфейса из VBA Access. Надо только ясно представлять себе, что большая часть работы уже и так проделана разработчиками MS Access. Для того, чтобы понять, что же осталось на нашу долю, рассмотрим более подробно сам механизм ODBC. Как это работает За относительно долгую историю ODBC API про него написано уже достаточно статей и даже книг, в которых подробнейшим образом описано из чего состоит, как работает этот интерфейс и как им пользоваться. Нам будет вполне достаточно лишь общего представления о его структуре. Итак, для нас ODBC - это, прежде всего, Менеджер драйверов ODBC (для платформы Win32 это odbc32.dll). Драйверы непосредственно взаимодействуют с источниками данных. Что это за драйверы и как осуществляется такое взаимодействие для нас не важно. Просто, когда мы хотим получить доступ, например, к MS SQL Server 7.0, то нужно установить драйвер MS SQL Server 7.0, когда к серверу MySQL - драйвер MySQL. Работа с Менеджером драйверов ODBC (далее - просто Менеджер) заключается в вызове необходимых функций с определёнными параметрами и в определенной последовательности. В функции Менеджера входит: установка и завершение связи с источником данных (сервером БД); подготовка и выполнение SQL-операторов; получение результатов и навигация по полученным наборам записей, если имеется такая возможность; управление транзакциями; идентификация ошибок; получение различной вспомогательной информации и прочие функции. Для выполнения этих функций Менеджер вначале должен подготовить некоторые системные ресурсы: Идентификатор окружения HENV. Он указывает на область памяти для общей информации (сведения обо всех соединениях с базами данных, информация о том, какое соединение является текущим и т.п.). Идентификатор соединения HDBC. Этот идентификатор указывает на область памяти для информации о конкретном соединении. Идентификатор соединения ассоциируется с единственным идентификатором окружения, в то время как этот идентификатор окружения может иметь несколько связанных с ним идентификаторов соединения. Идентификатор оператора HSTMT. Он указывает на область памяти для информации об SQL-операторе. Идентификатор оператора связан с единственным идентификатором соединения. Идентификатор соединения может иметь более одного связанного с ним идентификатора оператора. В конце Менеджер должен освободить эти ресурсы и вернуть их системе. С точки зрения программы на VBA, все эти идентификаторы - просто переменные типа Long, в которых хранятся адреса соответствующих областей памяти. При выделении ресурсов идентификатору, его переменная передаётся в специальную функцию по ссылке. При последующем использовании - по значению. Dim HENV As Long Dim HDBC As Long Dim HSTMT As Long Declare Function SQLAllocEnv Lib "odbc32.dll" (ByRef HENV As Long) As Integer Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal HENV As Long) As Integer Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal HENV As Long, _ ByRef HDBC As Long) As Integer Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal HDBC As Long) As Integer Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal HDBC As Long, _ ByRef HSTMT As Long) As Integer Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal HSTMT As Long, _ ByVal EndOption As Integer) As Integer Немного практики Перво-наперво, создадим отдельный модуль, в котором будем держать все объявления констант, функций, типов и переменных, так или иначе связанных с ODBC API. Бесхитростно назовём его "ODBC_API" и, для начала, включим следующие строки: Option Compare Database Option Explicit 'Константы ODBC Global Const MAX_DATA_BUFFER = 2047 Global Const SQL_SUCCESS = 0 Global Const SQL_SUCCESS_WITH_INFO = 1 Global Const SQL_ERROR = -1 Global Const SQL_NO_DATA_FOUND = 100 Global Const SQL_FETCH_FIRST = 2 Global Const SQL_FETCH_NEXT = 1 'Декларации функций ODBC Declare Function SQLAllocEnv Lib "odbc32.dll" (ByRef HENV As Long) As Integer Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal HENV As Long) As Integer Declare Function SQLDrivers Lib "odbc32.dll" (ByVal HENV As Long, _ ByVal Direction As Long, _ ByVal Description As String, _ ByVal DescriptionMax As Integer, _ ByRef DescriptionLen As Integer, _ ByVal Attributes As String, _ ByVal AttributesMax As Integer, _ ByRef AttributesLen As Integer) As Integer Declare Function SQLDataSources Lib "odbc32.dll" (ByVal HENV As Long, _ ByVal Direction As Long, _ ByVal DSN As String, _ ByVal DSNMax As Integer, _ ByRef DSNLen As Integer, _ ByVal Description As String, _ ByVal DescriptionMax As Integer, _ ByRef DescriptionLen As Integer) As Integer Здесь SQLDrivers и SQLDataSources уже не просто выполняют сервисные функции, а позволяют получить конкретные данные об установленных в операционной системе драйверах ODBC и именах источников данных (DSN - DataSource Name). Выглядят объявления этих функций довольно громоздко, но далее я подробно их опишу. Так как эти функции получают информацию непосредственно от Менеджера, потребность в выделении ресурсов на соединение и операторы отсутствует. Достаточно лишь инициализировать идентификатор окружения HENV. Сделать это лучше всего, создав специальный модуль класса. Почему? Потому, что выделение и освобождение ресурсов можно осуществить, соответственно, в конструкторе (Class_Initialize) и деструкторе (Class_Terminate) класса и, тем самым, исключить ситуацию, когда при сбросе программы функция освобождения ресурсов SQLFreeEnv не будет вызвана. Итак, назовём его ODBC_HENV: Option Compare Database Option Explicit Dim lngHENV As Long 'Переменная окружения (Environment Handle). Dim booValid As Boolean 'Индикатор возможности использования переменной окружения. Public Property Get HENV() As Long 'Это свойство содержит уже готовый идентификатор окружения. On Error Resume Next HENV = lngHENV End Property Public Property Get Valid() As Boolean 'Это свойство представляет собой флаг, который показывает возможность (True) или 'невозможность (False) использования идентификатора окружения. Оно необходимо, 'так как нет гарантии, что любое ненулевое значение идентификатора окружения является 'пригодным для использования. Данный флаг устанавливается в True, только если функция 'инициализации окружения отрапортует об отсутствии ошибки. On Error Resume Next Valid = booValid End Property Private Sub Class_Initialize() On Error Resume Next Dim intStatus As Integer intStatus = SQLAllocEnv(lngHENV) 'Инициализируем идентификатор окружения. If intStatus = SQL_SUCCESS Then booValid = True Else booValid = False MsgBox "Unable to Allocate ODBC Environment Handle!", vbCritical, "Error" End If End Sub Private Sub Class_Terminate() On Error Resume Next Dim intStatus As Integer intStatus = SQLFreeEnv(lngHENV) 'Освободим ресурсы окружения. If intStatus = SQL_ERROR Then MsgBox "Error Freeing ODBC Environment!", vbCritical, "Error" End If End Sub Теперь, при создании экземпляра класса, будет происходить выделение ресурсов окружения, а при уничтожении - их освобождение. Это очень похоже на работу с сеансом (Workspace) MS Assess. На этом подготовку можно считать законченной. Перейдём к следующему этапу - получению какой-нибудь полезной информации с помощью ODBC API. Например, попробуем получить списки всех зарегистрированных драйверов и источников данных. Именно зарегистрированных, потому что эта информация просто читается из реестра Windows или из определённых конфигурационных файлов. Если физически удалить файл драйвера, то он всё равно останется в списке, но при попытке обращения к нему Менеджер будет выдавать ошибку. Аналогично, если новый драйвер просто скопировать в системную папку Windows, он не появится в списке установленных драйверов и работать с ним будет невозможно. Именно поэтому, драйверы ODBC обычно комплектуются специальной программой установки. Чтобы получить интересующие нас списки, есть пара объявленных ранее функций: SQLDrivers и SQLDataSources. Они настолько похожи, что рассматривать я буду только первую из них, хотя использоваться будут обе. Первым параметром в ней идёт идентификатор окружения. Будем считать, что он у нас уже есть: Dim ODBC_ENV As New ODBC_HENV Дальше идёт параметр Direction. Он определяет порядок просмотра результирующего множества. И надо сказать, что и другие функции ODBC, работающие с наборами записей, устроены аналогично. Когда мы хотим получить первый элемент набора, надо передать в функцию значение SQL_FETCH_FIRST, следующее - SQL_FETCH_NEXT, предыдущее - SQL_FETCH_PRIOR и т.д. Dim Direction As Long Потом следуют два блока из трёх параметров каждый, которые служат для получения собственно информации о драйверах. Первый блок - название драйвера, второй - список атрибутов. Не пугайтесь, для этого списка не понадобится последовательный вызов ещё какой-нибудь функции. Имена атрибутов и их значения перечислены через стандартный для языка С (а именно на этот язык и рассчитан интерфейс ODBC в первую очередь) код завершения строки - 0 (ноль). В первом параметре каждого из двух блоков передаётся строка определённой длины, специально выделенная заранее: Dim Description As String * MAX_DATA_BUFFER Dim Attributes As String * MAX_DATA_BUFFER Второй параметр - длина этой строки. Он не позволит функции сформировать слишком длинную строку, испортив при этом соседние ячейки памяти. В нашем случае - это просто MAX_DATA_BUFFER. Третьим идёт указатель на целое число, в которое функция запишет, сколько она на самом деле использовала символов в отведённой строке: Dim DescriptionLen As Integer Dim AttributesLen As Integer Вот и вся премудрость. Дальше - дело техники: в цикле заполняем пару таблиц данными о драйверах (название и атрибуты) и источниках данных (имя и название драйвера), а потом выводим их содержимое с помощью простенькой формы. Полностью пример для Access'97 можно взять здесь. Уровни функциональных возможностей ODBC API В ODBC существует несколько уровней функциональных возможностей, которые обеспечивают различные уровни взаимодействия с прикладными программами. Например, функция SQLConnect, относящаяся к базовому уровню, загружает драйвер и устанавливает соединение с источником данных на основе лишь идентификатора соединения и строки подключения, в которой, как правило, указываются имя источника данных, имя пользователя и пароль. Первый уровень расширения функциональных возможностей ODBC включает в себя функцию SQLDriverConnect (она будет рассмотрена далее), которая позволяет запрашивать у пользователя более подробную информацию о подготавливаемом соединении, а также задавать степень обязательности этого запроса. Второй уровень ODBC API включает в себя функцию SQLBrowseConnect, поддерживающую пошаговый метод ввода данных, которые необходимы для установления соединения с источником данных. В реальности, соответствие ODBC драйвера некоторому уровню означает то, что он поддерживает все функции данного уровня. При этом в нём, как правило, реализованы ещё и некоторые из функций более высокого уровня. Существуют специальные функции, которые позволяют точно определить возможности драйвера. Уровни соответствия SQL Уровень соответствия SQL - это показатель того, какие возможности языка SQL и типы данных поддерживает драйвер. Грамматика ODBC SQL может быть базовой (она соответствует стандартным требованиям), минимальной (более низкий уроверь по сравнению с базовым) и расширенной (обеспечивает некоторые общепринятые расширения SQL). Как и в случае уровней ODBC API, существуют функции, позволяющие определить, какие операторы и типы данных поддерживает драйвер. Более сложная задача В предыдущей части я описал лишь основы использования интерфейса доступа к данным ODBC. Создание полнофункциональных приложений, основанных непосредственно на этом API, по сравнению с разработкой на MS Access, представляет собой исключительно трудную задачу. Чтобы не быть голословным, я приведу здесь небольшой пример, который иллюстрирует, насколько много нужно написать кода, для того, чтобы всего лишь получить список пользовательских таблиц с выбранного сервера БД. Перво-наперво, откажемся от использования классов для «обёртывания» функций, отвечающих за выделение и возврат ресурсов. Это не прибавит нашей программе надёжности, но зато значительно сократит её объём и улучшит читаемость кода. А функций таких будет много - полный комплект для всех трёх уровней структуры API (окружение-соединение-оператор). Далее, дополним модуль ODBC_API новыми объявлениями констант и функций: 'Константы ODBC 'Константы для SQLDriverConnect Global Const SQL_DRIVER_NOPROMPT = 0 Global Const SQL_DRIVER_COMPLETE = 1 Global Const SQL_DRIVER_PROMPT = 2 Global Const SQL_DRIVER_COMPLETE_REQUIRED = 3 'Константы для FreeStmt Global Const SQL_CLOSE = 0 Global Const SQL_DROP = 1 Global Const SQL_UNBIND = 2 Global Const SQL_RESET_PARAMS = 3 'Типы данных Global Const SQL_C_CHAR = 1 'Декларации функций ODBC Declare Function SQLDriverConnect Lib "odbc32.dll" (ByVal HDBC As Long, _ ByVal hWnd As Long, _ ByVal ConStrIn As String, _ ByVal ConStrInMax As Integer, _ ByVal ConStrOut As String, _ ByVal ConStrOutMax As Integer, _ ByRef ConStrOutLen As Long, _ ByVal DriverCompleation As Integer) As Integer Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal HDBC As Long) As Integer Declare Function SQLTables Lib "odbc32.dll" (ByVal HSTMT As Long, _ ByVal TableQualifier As String, _ ByVal TableQualifierLen As Integer, _ ByVal TableOwner As String, _ ByVal TableOwnerLen As Integer, _ ByVal TableName As String, _ ByVal TableNameLen As Integer, _ ByVal TableType As String, _ ByVal TableTypeLen As Integer) As Integer Declare Function SQLFetch Lib "odbc32.dll" (ByVal HSTMT As Long) As Integer Declare Function SQLGetData Lib "odbc32.dll" (ByVal HSTMT As Long, _ ByVal ColNumber As Long, _ ByVal DataType As Integer, _ ByVal DataValue As String, _ ByVal DataValueMax As Integer, _ ByRef DataValueLen As Long) As Integer Для непосредственной работы создадим форму, в которой разместим список драйверов ODBC, поля для ввода параметров подключения к серверу (имя сервера, имя базы данных на нем, логин и пароль пользователя), а также список таблиц, который и будем пытаться заполнить. Ещё предусмотрим пару кнопок для придания большей наглядности нашим опытам (хотя, можно обойтись и обработкой событий списков). Список драйверов заполним по аналогии с предыдущим примером, только не будем создавать и наполнять таблицу. Вместо этого, в качестве типа источника строк выберем список значений, а при загрузке формы запишем в переменную наименования всех найденных драйверов и присвоим её свойству «Источник строк» списка. Следующий важный шаг - установить соединение с сервером. Информация, которая должна понадобиться - это, кроме имени драйвера, имена сервера и БД, а также логин и пароль пользователя, от лица которого будет осуществляться подключение. Вся вместе она образует так называемую строку подключения (Connection String), где перечисляются соответствующие ключи и их значения: strConStr = "DRIVER=" & Me.lbxDrivers & _ ";SERVER=" & Me.txtServer & _ ";DATABASE=" & Me.txtDataBase & _ ";UID=" & Me.txtLogin & _ ";PWD=" & Me.txtPassword & ";" Нетрудно заметить, что в качестве значений мы указываем как раз содержимое элементов управления нашей тестовой формы. Теперь передадим эту строку вместе с уже имеющимся идентификатором окружения в предварительно заготовленную функцию, которая выполнит всю работу и, в случае успеха, вернёт ненулевой идентификатор соединения. Или нулевой - в случае ошибки. Вот она: Public Function OpenConnection(HENV As Long, ConnectionString As String) As Long Dim intStatus As Integer 'Для кодов возврата функций ODBC. Dim lngHDBC As Long 'Идентификатор соединения. Dim strConStrIn As String * MAX_DATA_BUFFER 'Строковый буфер для передачи данных. Dim strConStrOut As String * MAX_DATA_BUFFER 'Строковый буфер для получения данных. Dim lngConStrOutLen As Long 'Реальная длина данных в буфере. OpenConnection = 0 If Len(ConnectionString) < MAX_DATA_BUFFER Then 'Строка не должна быть слишком длинной. strConStrIn = ConnectionString & Chr(0) 'Добавим нулевой символ как знак завершения строки. intStatus = SQLAllocConnect(HENV, lngHDBC) 'Получим идентификатор соединения. If intStatus = SQL_SUCCESS Then 'Если соединение успешно создано, пытаемся открыть его. intStatus = SQLDriverConnect(lngHDBC, _ 0, _ strConStrIn, _ MAX_DATA_BUFFER, _ strConStrOut, _ MAX_DATA_BUFFER, _ lngConStrOutLen, _ SQL_DRIVER_NOPROMPT) If intStatus = SQL_SUCCESS Then 'Если соединение успешно открыто, возвращаем его идентификатор. OpenConnection = lngHDBC Else 'Иначе - освободим ресурсы, выделенные соединению, а функция вернёт 0. intStatus = SQLFreeConnect(lngHDBC) End If End If End If End Function Основа нашей функции - функция ODBC API SQLDriverConnect. Первым параметром в ней идёт уже знакомый нам идентификатор соединения, который надо предварительно получить с помощью функции SQLAllocConnect. Второй параметр - идентификатор окна, которое будет являться родительским для окна диалога ввода параметров соединения. Этот диалог обеспечивается используемым драйвером ODBC, если мы того захотим и укажем в последнем параметре вызова функции соответствующий режим. В нашем случае диалог не требуется и поэтому мы передаём нулевой идентификатор окна (в противном случае можно передать, например, hWndAccessApp) и SQL_DRIVER_NOPROMPT соответственно. Третьим и четвёртым парамертами идут строка подключения и размер текстового буфера, отведенного этой строке. Последующие три параметра функции предназначены для возврата строки подключения, сформированной самой функцией и, в нашем случае, не используются. После того, как соединение установлено, подготовим идентификатор оператора с помощью функции SQLAllocStmt и вызовем функцию SQLTables. Эта функция возвращает набор сведений о таблицах (TABLE), представлениях (VIEW) и других аналогичных объектах базы данных. Использовать её нужно также, как и все функции, работающие с результирующим множеством, каким является, например, результат выполнения запроса на выборку. В двух словах, порядок действий такой: сначала нужно определить количество столбцов в результирующем множестве (SQLNumResultCols), затем их имена (SQLColumns) и типы данных (SQLDescribeCol). Для нашего примера всё это не нужно, так как точно известно, что имена таблиц содержатся в третьем столбце, имеют символьный тип данных, а название столбца не имеет значения. Теперь можно выделить буферы для данных каждого из столбцов с помощью функции SQLBindCol. Мы этого делать не будем, а воспользуемся другим способом извлечения данных (SQLGetData). Далее необходимо, устанавливая указатель на нужную запись с помощью функции SQLFetch или SQLExtendedFetch (последняя поддерживается не всеми драйверами), считывать данные столбца в подготовленную для них переменную. Как видите, всё это очень сложно и громоздко. Поэтому, многими фирмами разработаны различные программные компоненты более высокого уровня, существенно облегчающие использование таких интерфейсов, как ODBC. В MS Access для этого можно использовать механизмы присоединённых таблиц и запросов к серверу. В программах на VBA удобно использовать объекты доступа к данным DAO. Все перечисленные механизмы имеют ряд особенностей, которые необходимо знать, учитывать и использовать при работе с данными через интерфейс ODBC. Однако, это уже тема для отдельного разговора.