Использование Caché SQL Gateway

2005 г.

Использование Caché SQL Gateway

Гришина Мария, InterSystems

В Caché реализован специальный механизм Caché SQL Gateway, позволяющий обращаться к внешним источникам данных посредством ODBC. Таким образом, этот механизм позволяет программам на языке Caché Object Script осуществлять обращение к внешним реляционным базам данных. В отличие от Caché ODBC (JDBC и др.), предоставляющих доступ к данным Caché для внешних реляционных источников, SQL Gateway – это средство, позволяющее взаимодействовать с реляционными источниками данных в противоположном направлении.

Поскольку для связи с внешними реляционными источниками данных Caché SQL Gateway использует ODBC, необходимо создать и/или настроить источник данных ODBC (DSN) соответствующей “внешней” базы данных. Для этого необходимо выполнить следующие действия (или убедиться, что они уже выполнены):

1) Инсталлировать драйверы ODBC соответствующей СУБД. Некоторые драйверы, например MS SQL Server как правило, уже присутствуют в Windows. Некоторые, такие как MSM SQL, MySQL устанавливаются отдельно. Некоторые (Oracle, Caché) инсталлируются при установке клиентской части СУБД.

2) Создать источник данных (DSN) из программы настройки ODBC. Например, в ОС Windows 2000 эта программа доступна из меню Windows: Start->Settings->Control panel->Administrative tools->Data Sources (ODBC) (Рис.1). Рекомендуется для работы через SQL Gateway создавать системные DSN.

3) После создания DSN появится диалог конфигурации, который специфичен для каждого отдельного драйвера. Как правило, необходимо указать имя сервера, имя пользователя и пароль, и некоторые другие настройки, например, БД по умолчанию.

Рис. 1. ODBC Data Source Administrator

Возможно несколько способов использования SQL Gateway:

Создание так называемых link-таблиц и хранимых (Persistent) классов Caché, использующих Gateway-хранение (т.е. данные которых хранятся во внешних СУБД);
выполнение запросов, обращаясь непосредственно к внешним таблицам (с использованием класса Caché %ResultSet (который обычно используется разработчиками для выполнения запросов к классам Caché) в сочетании с классами %SQLGatewayConnection и %DynamicQueryGW);
работа с SQL Gateway на низком уровне (используя функции SQL Gateway API).

Создание link-таблиц в Caché

В Caché имеется возможность создавать таблицы, представляющие собой связи с таблицами “внешних” ODBC-источников. Это так называемые link-таблицы Caché, данные которых физически хранятся во “внешней” СУБД. Для создания link-таблиц в Caché предусмотрен специальный мастер связывания таблиц (Link Table Wizard), доступный из SQL-менеджер. По окончании работы мастера создается таблица Caché и соответствующий этой таблице хранимый класс Caché, использующий GateWay-хранение (GSQLStorage).

Для того, чтобы пользоваться мастером создания связей с внешними таблицами, источники данных ODBC необходимо зарегистрировать в редакторе конфигурации. Это объясняется тем, что Caché не может самостоятельно получить список DSN.

Регистрация ODBC источников данных в Caché

Для регистрации DSN в Caché откройте Редактор конфигурации из меню Caché-куба и перейдите к вкладке “Дополнительно” (Рис.2). Вы увидите отдельный пункт “SQL Gateway”. Нажмите правую кнопку мыши для появления контекстного меню. И выберите пункт меню “Добавить”. Можно иначе - просто нажать кнопку “Добавить”.

В появившемся диалоговом окне (Рис. 3) укажите имя Gateway-соединения. Затем для созданного Gateway-соединения укажите имя ранее созданного DSN (обязательно), а также имя пользователя и пароль, если это необходимо.

После выполнения указанных действий DSN зарегистрирован в системе. Перезагрузка не требуется. Указанные действия можно повторить для других источников данных.

Рис. 2. Редактор конфигураций Caché

Рис. 3

Использование мастера связывания таблиц

Для создания в Caché таблиц, представляющих собой связи с таблицами “внешних” ODBC-источников предусмотрен мастер связывания таблиц (Link Table Wizard), который доступен из SQL-менеджер.

Рассмотрим по шагам создание link-таблицы Caché (будем считать, что вы уже зарегистрировали нужный DSN в Редакторе конфигурации). Для того, чтобы запустить мастер связывания таблиц откройте SQL-менеджер и перейдите к нужной области, в которой вы хотите создать вашу link-таблицу. Мастер доступен из меню SQL-менеджера (Рис. 4. SQL-менеджер Caché): Файл->Связать таблицу (или щелкните правой кнопкой мыши на пункте “Таблицы” для выбранного пакета и в контекстном меню выберите “Создать таблицу”, а затем “Связать таблицу”).

Рис. 4. SQL-менеджер Caché

В результате этих действий откроется мастер создания связей с “внешними” таблицами (Рис. 5), работа мастера заключается в выполнении определенной фиксированной последовательности шагов. Как видно из рисунка 5, на первом шаге мастера не нужно выполнять никаких действий, нажмите кнопку “Далее”.

Рис. 5. Первый шаг мастера

На втором шаге мастера (Рис. 6) отображается список источников данных, зарегистрированных в Caché (зарегистрированных Gateway-соединений). Выберите нужное Gateway-соединение и нажмите кнопку “Далее”.

Рис. 6. Второй шаг мастера

На третьем шаге мастера (Рис. 7) отображается список таблиц, доступных в выбранном источнике данных. Выберите нужную таблицу и нажмите кнопку “Далее”.

Рис. 7. Третий шаг мастера

Рис. 8. Четвертый шаг мастера

На четвертом шаге мастера (Рис. 8) отображается список столбцов выбранной таблицы “внешнего” ODBC-источника, укажите столбцы, которые должны присутствовать в новой таблице и нажмите кнопку “Далее”.

Пятый шаг мастера. Здесь вы можете отредактировать имена полей, если необходимо. Обратите внимание на то, что названия полей должны соответствовать синтаксису Caché для свойств объектов, например, знаки подчеркивания недопустимы. Приведя имена столбцов создаваемой таблицы к нужному виду, нажмите кнопку “Далее”.

Рис. 9. Пятый шаг мастера

На шестом, заключительном шаге мастера вы можете указать, какое поле (поля) является первичным ключом и отредактировать имя таблицы Caché, которая будет создана мастером, и соответствующего данной таблице хранимого класса Caché. Имена таблицы и соответствующего ей класса не обязательно должны совпадать. Если, например, в вашем приложении в именах классов желательно придерживаться определенного стиля, или в приложении уже есть класс с данным именем, возможность изменять названия классов (и таблиц) может быть полезной. В том случае, если указанное имя класса содержит недопустимые, с точки зрения Caché символы (например, подчеркивания), Caché удалит эти символы из имени класса.

Рис. 10. Шестой шаг мастера

По окончанию работы мастера создается таблица Caché, данные которой физически расположены во “внешнем” источнике данных. Созданная link-таблица доступна из Caché SQL. Автоматически для созданной таблицы создается хранимый (Persistent) класс Caché, использующий GateWay-хранение. Объектами созданного класса можно пользоваться как обычно, т.е. можно описывать методы, вычислимые поля, суперклассы и т.д., но нельзя изменить схему GateWay-хранения. Впрочем, ничто не мешает добавить в описание класса альтернативные способы хранения. Когда вы сочтете нужным, можно будет поменять хранение с “внешнего” на “внутреннее” незаметно для окружающих.

Отличием от использования “внутренних” таблиц Caché является невозможность комбинировать в одном запросе “внутренние” (родные) и “внешние” таблицы.

Обратите внимание: если с обычными, “внутренними” таблицами вы сначала описываете хранимый класс, одной из проекций которого является SQL-представление, другими словами класс первичен, таблица вторична, то в случае с внешними таблицами все наоборот. Сначала вы описываете внешнюю таблицу с помощью мастера, затем автоматически создается новый класс, соответствующий новой таблице.

В Листинге 1 приведен пример класса, созданного с помощью мастера связывания таблиц. Как видно из примера, в качестве стратегии хранения для этого класса определена GSQLStorage, что означает, что данные хранятся в таблице внешнего ODBC-источника. Поскольку созданным классом можно пользоваться как обычно, т.е. добавлять методы, вычислимые поля и т.п., в данном примере, для иллюстрации такой возможности, к созданному классу добавлен SQL-запрос.

Листинг 1

Class dbo.Manager Extends %Persistent [ ClassType = 
persistent, SqlRowIdPrivate, SqlTableName = Manager,
StorageStrategy = GSQLStorage ]

{
Parameter CONNECTION = "Managers,NOCREATE";
Parameter EXTERNALTABLENAME = "dbo.Manager";
Property Id As %Integer(EXTERNALSQLNAME = "Id") 
[ SqlColumnNumber = 4, 
SqlFieldName = Id ];
Property Name As %String(EXTERNALSQLNAME = "Name",
  MAXLEN = 50) [ 
SqlColumnNumber = 2, SqlFieldName = Name ];
Property OrdersAmount As %Integer
(EXTERNALSQLNAME = "OrdersAmount") [ 
SqlColumnNumber = 3, SqlFieldName = OrdersAmount ];
Index MainIndex On Id [ IdKey, PrimaryKey ];
Query ManagerInfo() As %SQLQuery
{
SELECT Id,Name,OrdersAmount FROM Manager
ORDER BY Name
}

}

Использование класса %Library. с Caché SQL Gateway

Для осуществления выборки из внешних источников данных, удобно использовать библиотечные классы %Library.ResultSet и %Library.DynamicQueryGW.

Пожалуй, это наиболее простой и удобный способ доступа к внешним данным в тех случаях, когда требуется только получать множество строк путем вызова SQL-команды или хранимой процедуры. Для работы таким способом не требуется регистрировать DSN, не требуется описывать связанные таблицы, не требуется знать методы класса %SQLGatewayConnection (кроме Connect / Disconnect), используемые при работе с SQL GateWay на низком уровне. В Листинге 2 приведен пример выполнения выборки с использованием класса %ResultSet.

Листинг 2

Set Connection=##class(%SQLGatewayConnection).%New()
Set res = Connection.Connect("DSNName","username","pwd")
Set ResSet=##class(%ResultSet).%New("%DynamicQueryGW:SQLGW")
Do ResSet.Prepare("SELECT * FROM TABLE",,Connection)
Do ResSet.Execute()
while ResSet.Next()

{
For i=1:1:ResSet.GetColumnCount()

{
             if (i'=ResSet.GetColumnCount())
             {
                         Write ResSet.GetData(i)_", "
             }
             else
             {
                         Write ResSet.GetData(i),!
             }

}

}
d Connection.Disconnect()

Рассмотрим этот пример по шагам:

-Сначала создается соединение с DSN для чего создается экземпляр класса %Library.SQLGatewayConnection, затем вызывается метод Connect(), которому в качестве аргументов передается имя созданного источника данных (DSN), а также, если это необходимо, имя пользователя и пароль:

Set Connection=##class(%SQLGatewayConnection).%New()
Set res = Connection.Connect("DSNName"," username", “pwd")

-Затем создаем объект класса %ResultSet

ResSet=##class(%ResultSet).%New("%DynamicQueryGW:SQLGW")

-Определяем и выполняем запрос (обратите внимание Connection, т.е. созданное SQLGateWay соединение передается третьим параметром):

Do ResSet.Prepare("SELECT * FROM TABLE",,Connection)
Do ResSet.Execute()
while ResSet.Next()

{
For i=1:1:ResSet.GetColumnCount()

{
             if (i'=ResSet.GetColumnCount())
             {
                         Write ResSet.GetData(i)_", "
             }
             else
             {
                         Write ResSet.GetData(i),!
             }

}

}

-Разрываем соединение:

Do Connection.Disconnect()

Важно отметить, что объект %SQLGatewayConnection создается отдельно не случайно. Само соединение с удаленным источником данных занимает довольно много времени, поэтому было бы нерационально открывать соединение для каждого отдельного запроса.

Работа с SQL GateWay на низком уровне

С SQL GateWay можно работать и на низком уровне, используя функции SQL Gateway API. Большинство функций SQL Gateway API инкапсулированы в классе %Library.SQLGatewayConnection, что несколько облегчает кодирование. Необходимо отметить, что %SQLGatewayConnection – это только часть всего того, что относится к SQL Gateway в Caché. В основном методы %SQLGatewayConnection соответствуют по названию и использованию соответствующим функциям API. Все методы класса %SQLGatewayConnection возвращают значение типа %Status, что позволяет осуществлять обработку ошибок. Рассмотрим отдельно некоторые методы.

Для соединения с ODBC-источником данных (DSN) используется метод Connect (DSN,User,Password):

Set Connection=##class(%SQLGatewayConnection).%New()
// устанавливаем соединение
Do Connection.Connect(DSN, "User", "pwd")

Для разрыва соединения используется метод Disconnect():

Set sc=Connection.Disconnect()

Прежде, чем выполнять запрос, необходимо сначала создать команду (Statement). Для создания команды используется метод AllocateStatement(), аргумент (Handle) методу AllocateStatement() передается по ссылке (перед именем аргумента ставится “.”):

Set sc=Connection.AllocateStatement(.Stat)

Для удаления команды используется метод DropStatement():

Set sc=Connection.DropStatement(Stat)

Перед выполнением запроса его необходимо “подготовить”, для чего используется метод Prepare(Stat, sql). В качестве аргументов методу передается созданная команда и строка sql-запроса. Возможно выполнение запросов с параметрами, тогда в строке sql на месте параметров ставятся знаки “?”:

//подготовка запроса
Set sc=Connection.Prepare
  (Stat,"SELECT * FROM TABLE WHERE Name=? AND Age=?")

Если передается sql-запрос с параметрами, то эти параметры необходимо подготовить и присвоить им определенные значения. Для подготовки параметров используется метод BindParameters(). Аргументы метода BindParameters():

созданная команда;
типы параметров: 1 – in (входной), 2-in/out (входной/выходной), 4-out (выходной);
cписок типов данных ODBC (например: 4-int, 9-DATETIME, 12-VARCHAR, 8-DOUBLE, …);
размеры буферов в байтах;
число знаков после точки (только для Decimal и Float);
список длин типов данных в байтах;

Все аргументы метода BindParameters(), начиная со второго имеют тип %List и должны передаваться в качестве аргументов функции $LB(). Если в запросе несколько параметров, то в качестве аргументов $LB() передаются значения через запятую, соответствующие каждому параметру в порядке их следования в строке запроса, например:

Set sc=Connection.BindParameters(Stat,$LB(1,1),$LB(12,4),
$LB(50,4),$LB(0,0),$LB(50,4))

Значение нужного типа данных ODBC можно посмотреть в глобале ^%qCacheSQL (область %CACHELIB) по первому индексу равному "odbcdt", тогда второй индекс соответствует нужному типу данных (Рис. 11).

Присваивание значения параметру осуществляется с использованием метода SetParameter(Stat, $LB(val), Numb). В качестве аргументов методу SetParameter() передаются:

созданная команда;
значение параметра как аргумент функции $LB();
порядковый номер параметра в строке запроса.

Метод SetParameter() вызывается отдельно для каждого переданного параметра:

Set sc=Connection.SetParameter(Stat,$LB(“Alice”),1)
Set sc=Connection.SetParameter(Stat,$LB(5),2)

Для выполнения запроса используется метод Execute(Stat), которому в качестве аргумента передается созданная команда:

Set sc=Connection.Execute(Stat)

В Листинге 3 приведен пример запроса к внешней таблице с использованием методов класса %Library.SQLGatewayConnection. Данный запрос выбирает все поля из таблицы INFO внешнего ODBC-источника (с именем DSNName), для которых значение поля Age=21, а значение поля Name начинается с буквы “D”.

Листинг 3

Set 
Connection=##class(%SQLGatewayConnection).%New()
// устанавливаем соединение
Do Connection.Connect("DSNName","sa","pwd")
// создание новой команды
Set sc=Connection.AllocateStatement(.Stat)
// подготовка запроса
Set sc=Connection.Prepare(Stat,"SELECT * FROM INFO WHERE 
Age=? AND Name LIKE ?")
// подготовка параментов
Set sc=Connection.BindParameters(Stat,$LB(1,1),$LB(4,12),
$LB(4,50),$LB(0,0),$LB(4,50))
Set sc=Connection.SetParameter(Stat,$LB(21),1)
Set sc=Connection.SetParameter(Stat,$LB("D%"),2)
// выполнение запроса
Set sc=Connection.Execute(Stat)
For { 
Q:'Connection.Fetch(Stat)
         S Sc=Connection.GetOneRow(Stat,.Row)
         For J=1:1:$LL(Row) 
Write $LG(Row,J)_" "
         Write !

}
// удаление команды
Set sc=Connection.DropStatement(Stat)
// разрыв соединения
Set sc=Connection.Disconnect()