- PVSM.RU - https://www.pvsm.ru -
В сложных комплексных системах часто встаёт вопрос интеграции данных из разных источников.
Такие системы получили название интегрированных, федеративных или мультибаз [1].
В СУБД Caché такая интеграция осуществляется с помощью специального шлюза (Caché SQL Gateway [2]), который использует в своей основе ODBC/JDBC соединения к внешним источникам данных. Причём под источником в данном случае можно понимать не только СУБД, так как есть JDBC/ODBC драйвера для MS Excel, DBF, текстовых файлов, графических файлов, WMI и т.д.
Коротко, как задействовать Caché SQL Gateway:
Примечание: предвидя вопросы про поддержку гетерогенных запросов к разным источникам данных, спешу заметить про наличие некоторых ограничений [5] в запросах.
Другими словами, JOIN между таблицами из Oracle и MSSQL выполнить не удастся.
Данные ограничения применимы только для так называемых связанных таблиц. Если же воспользоваться Мастером Миграции [6] структур и данных вместо Мастера Связывания, то таких ограничений уже не будет.
Как уже было сказано выше, можно воспользоваться либо ODBC, либо JDBC драйвером для подключения к внешнему источнику данных. Рассмотрим оба варианта.
Для ODBC [7] необходимо вначале настроить системные DSN:
А для JDBC [8] — путь к виртуальной машине Java (далее все картинки кликабельны):
Далее в SMP необходимо создать соединения для Caché SQL Gateway нужного типа (JDBC [10] или ODBC [11]), от которого будут зависеть те или иные настройки. Здесь же можно проверить вновь созданное соединение:
Затем, воспользовавшись Мастером Связывания таблиц [14] или процедур [15], следует создать необходимые виртуальные таблицы или ХП:
Мастер запросит для каждой таблицы её новое имя, новые имена для каждого из полей, первичный ключ и т.д. В большинстве случаев вы можете все имена оставить по умолчанию, но бывают ситуации, когда некоторые из этих идентификаторов являются зарезервированными словами СУБД Caché.
Пример сгенерированного виртуального класса для внешней таблицы job_titles:
Class dbo.jobtitles Extends %Library.Persistent [ ClassType = persistent, Not ProcedureBlock, SqlRowIdPrivate, SqlTableName = job_titles, StorageStrategy = GSQLStorage ]
{
Parameter CONNECTION = "ems,NOCREATE";
Parameter EXTDBNAME = "Microsoft SQL Server";
Parameter EXTERNALTABLENAME = "dbo.job_titles";
Property INTERETHNICVALUE As %String(EXTERNALSQLNAME = "INTERETHNIC_VALUE", EXTERNALSQLTYPE = 12, MAXLEN = 50) [ Required, SqlColumnNumber = 5, SqlFieldName = INTERETHNIC_VALUE ];
Property INTERNATIONALVALUE As %String(EXTERNALSQLNAME = "INTERNATIONAL_VALUE", EXTERNALSQLTYPE = 12, MAXLEN = 50) [ Required, SqlColumnNumber = 4, SqlFieldName = INTERNATIONAL_VALUE ];
Property NATIONALVALUE As %String(EXTERNALSQLNAME = "NATIONAL_VALUE", EXTERNALSQLTYPE = 12, MAXLEN = 50) [ Required, SqlColumnNumber = 6, SqlFieldName = NATIONAL_VALUE ];
Property id As %Integer(EXTERNALSQLNAME = "id", EXTERNALSQLTYPE = 4) [ Required, SqlColumnNumber = 2, SqlFieldName = id ];
Property priority As %Integer(EXTERNALSQLNAME = "priority", EXTERNALSQLTYPE = 4) [ Required, SqlColumnNumber = 3, SqlFieldName = priority ];
Index MainIndex On id [ IdKey, PrimaryKey ];
}
Примечание: при необходимости вы можете код сгенерированного класса дополнить некоторыми плюшками от Caché — методами класса и/или объекта, вычисляемыми полями, суперклассами, ХП, — которые отсутствуют в изначальных таблицах.
Но не следует забывать, что при повторной генерации связанных таблиц/ХП весь ваш дополнительный код потеряется.
Всё, теперь вы можете, подключившись к Caché с помощью любого ODBC/JDBC клиента (см. одну из предыдущих статей [18]), увидеть все таблицы и процедуры, как внутренние, так и внешние; причём разницу между ними определить сразу сложно.
Ну, и конечно, можно вставлять/удалять/изменять данные, используя родной SQL-синтаксис тех или иных СУБД.
Механизм Caché SQL Gateway [19] может также использоваться и для встроенной бизнес-аналитики DeepSee [20], но это уже другая тема.
Однажды данная технология помогла моим коллегам, работающим с другими СУБД (не Caché). Поступили к нам на работу два новых сотрудника и им необходим был доступ к нескольким разным БД на разных серверах.
Поскольку бумага на открытие для них доступа к нужным серверам "блуждала" по кабинетам, а сроки поджимали, я предложил им с согласия своего руководства временный вариант с Caché: благо у них использовался какой-то фреймворк типа Hibernate, а у меня был доступ к нужным серверам. Создал в Caché область, добавил нужные таблицы из нужных БД, дал на них необходимые права.
Дальше они подключались через Caché, выступающей здесь как прокси-СУБД, к одной виртуальной БД и работали таким образом со своими таблицами из разрозненных БД. Коллегам даже не пришлось ставить драйвера для своих СУБД.
Помимо создания связанных таблиц, с внешними данными можно работать и программно:
Пример программного ODBC-доступа, используя системный DSN:
set db = ##class(%SQLGatewayConnection).%New()
set res = db.Connect("DSNName","username","password")
set rs = ##class(%ResultSet).%New("%DynamicQueryGW:SQLGW")
do rs.Prepare("SELECT * FROM users WHERE id = ?",,db)
do rs.Execute(46)
while rs.Next() {
for i = 1:1:rs.GetColumnCount() {
write rs.GetData(i)
if i '= rs.GetColumnCount() {
write ", "
} else {
write !
}
}
}
do db.Disconnect()
В данном случае уже не нужно создавать соединения для Caché SQL Gateway, поскольку "DSNName" — это имя DSN, определённое в самой OC.
Пример выше демонстрирует так называемый высокоуровневый доступ работы с ODBC с помощью классов %ResultSet и %DynamicQueryGW, но возможен и низкоуровневый доступ. В этом случае используются методы только класса %SQLGatewayConnection [23].
Далее приведён пример запроса к внешней таблице с использованием низкоуровневого доступа. Данный запрос выбирает все поля из таблицы INFO внешнего ODBC-источника (с именем DSNName), для которых значение поля Age=21, а значение поля Name начинается с буквы "D".
set db=##class(%SQLGatewayConnection).%New()
// устанавливаем соединение
do db.Connect("DSNName","sa","pwd")
// создание новой команды
set sc=db.AllocateStatement(.Stat)
// подготовка запроса
set sc=db.Prepare(Stat,"SELECT * FROM INFO WHERE Age=? AND Name LIKE ?")
// подготовка параметров
set sc=db.BindParameters(Stat,$listbuild(1,1),$listbuild(4,12),$listbuild(4,50),$listbuild(0,0),$listbuild(4,50))
set sc=db.SetParameter(Stat,$listbuild(21),1)
set sc=db.SetParameter(Stat,$listbuild("D%"),2)
// выполнение запроса
set sc=db.Execute(Stat)
for {
quit:'db.Fetch(Stat)
set sc=db.GetOneRow(Stat,.Row)
for j=1:1:$listlength(Row) write $listget(Row,j)_" "
write !
}
// удаление команды
set sc=db.DropStatement(Stat)
// разрыв соединения
set sc=db.Disconnect()
Итак, рассмотрим этот пример подробнее.
• Для соединения с ODBC-источником данных (DSN) используется метод Connect(DSN,User,Password):
set db=##class(%SQLGatewayConnection).%New()
// устанавливаем соединение
do db.Connect("DSNName","sa","pwd")
• Для разрыва соединения используется метод Disconnect():
set sc=db.Disconnect()
• Прежде, чем выполнять запрос, необходимо сначала создать команду Statement. Для создания команды используется метод AllocateStatement(), аргумент Handle методу AllocateStatement() передаётся по ссылке (перед именем аргумента нужно поставить точку "."):
set sc=db.AllocateStatement(.Stat)
• Для удаления команды используется метод DropStatement():
set sc=db.DropStatement(Stat)
• Перед выполнением запроса его необходимо "подготовить", для чего используется метод Prepare(Stat, sql). В качестве аргументов методу передаются созданная команда и строка sql-запроса. Возможно выполнение запросов с параметрами, тогда в строке sql на месте параметров ставятся знаки "?":
// подготовка запроса
set sc=db.Prepare(Stat,"SELECT * FROM INFO WHERE Age=? AND Name LIKE ?")
• Если передаётся sql-запрос с параметрами, то эти параметры необходимо подготовить и присвоить им определённые значения. Для подготовки параметров используется метод BindParameters(). Аргументы метода BindParameters():
• Все аргументы метода BindParameters(), начиная со второго, имеют тип %List и должны передаваться в качестве аргументов функции $listbuild(). Если в запросе несколько параметров, то в качестве аргументов $listbuild() передаются значения через запятую, соответствующие каждому параметру в порядке их следования в строке запроса, например:
set sc=db.BindParameters(Stat,$listbuild(1,1),$listbuild(4,12),$listbuild(4,50),$listbuild(0,0),$listbuild(4,50))
или
#include %occODBC ; не забудьте подключить необходимые файлы с макросами
set sc=db.BindParameters(
Stat,
$listbuild($$$SQLPARAMINPUT,$$$SQLPARAMINPUT),
$listbuild($$$ODBCTYPEinteger,$$$ODBCTYPEvarchar),
$listbuild(4,50),
$listbuild(0,0),
$listbuild(4,50)
)
Примечание: в представленном изначально коде используются числовые идентификаторы типов параметров. В реальном коде лучше использовать макросы из файлов %occODBC.inc или %msql.inc, например:
- $$$GetOdbcTypeNumber("INTEGER") или $$$ODBCTYPEinteger вернут 4;
- $$$SQLPARAMINPUT вернёт 1 (входной);
- $$$SQLPARAMINPUTOUTPUT вернёт 2 (входной/выходной);
- $$$SQLPARAMOUTPUT вернёт 4 (выходной);
- и т.д.
Полный список макросов можно найти в соответствующих файлах, используя, например, Caché Studio.
• Присваивание значения параметру осуществляется с использованием метода SetParameter(Stat, $listbuild(val), Numb). В качестве аргументов методу SetParameter() передаются:
• Метод SetParameter() вызывается отдельно для каждого переданного параметра:
set sc=db.SetParameter(Stat,$listbuild(21),1)
set sc=db.SetParameter(Stat,$listbuild("D%"),2)
• Для выполнения запроса используется метод Execute(Stat), которому в качестве аргумента передаётся созданная команда:
set sc=db.Execute(Stat)
Примечание: дополнительные примеры по программному доступу к внешним данным, используя JDBC/ODBC, можно найти в исходном коде классов %UnitTest.JDBCSQL и %UnitTest.ODBCSQL соответственно.
Автор: servitRM
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/razrabotka/12551
Ссылки в тексте:
[1] интегрированных, федеративных или мультибаз: http://citforum.ru/database/osbd/glava_102.shtml
[2] Caché SQL Gateway: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_gateway
[3] SMP: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=GSA_using_portal#GSA_using_portal_pages
[4] области: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=RGOT_complete#RGOT_Namespace
[5] некоторых ограничений: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_gateway#GSQL_gateway_intro_restrictions
[6] Мастером Миграции: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_gateway#GSQL_gateway_migration
[7] ODBC: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=BGOD
[8] JDBC: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=BGJD
[9] Image: http://db.tt/GVuykqyR
[10] JDBC: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=BGJD_gateway#BGJD_gateway_connections
[11] ODBC: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=BGOD_gateway#BGOD_gateway_connections
[12] Image: http://db.tt/Zt3bB5JG
[13] Image: http://db.tt/BHTIS3Bi
[14] таблиц: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_gateway#GSQL_gateway_linktable
[15] процедур: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_gateway#GSQL_gateway_linkproc
[16] Image: http://db.tt/vu9evva5
[17] Image: http://db.tt/aMJFk9WL
[18] предыдущих статей: http://habrahabr.ru/company/intersystems/blog/148530/
[19] Caché SQL Gateway: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=D2IMP_ch_connector
[20] DeepSee: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=D2GS_ch_intro
[21] JDBC: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=BGJD_gateway#BGJD_gateway_program
[22] ODBC: http://docs.intersystems.com/cache20121/csp/docbook/DocBook.UI.Page.cls?KEY=BGOD_gateway#BGOD_gateway_program
[23] %SQLGatewayConnection: http://docs.intersystems.com/cache20121/csp/documatic/%25CSP.Documatic.cls?PAGE=CLASS&LIBRARY=%25SYS&CLASSNAME=%25Library.SQLGatewayConnection
Нажмите здесь для печати.