Знакомство с "Caché SQL Gateway" для создания федеративных систем или мультибаз

    В сложных комплексных системах часто встаёт вопрос интеграции данных из разных источников.
    Такие системы получили название интегрированных, федеративных или мультибаз.

    В СУБД Caché такая интеграция осуществляется с помощью специального шлюза (Caché SQL Gateway), который использует в своей основе ODBC/JDBC соединения к внешним источникам данных. Причём под источником в данном случае можно понимать не только СУБД, так как есть JDBC/ODBC драйвера для MS Excel, DBF, текстовых файлов, графических файлов, WMI и т.д.

    Коротко, как задействовать Caché SQL Gateway:

    1. в Портале Управления Системой (SMP) создаёте соединение нужного типа, указав строку подключения, логин, пароль и т.д. Здесь же можно проверить созданное соединение;
    2. с помощью Мастера Связывания связываете из внешней СУБД требуемые таблицы и/или процедуры с Caché, используя соединение с шага выше. При этом никакие данные никуда не копируются, а создаются лишь специальные виртуальные классы, они же таблицы;
    3. теперь, подключившись к области — логическая БД в терминах Caché — вы увидите в ней таблицы, представления, хранимые процедуры (ХП) из внешних источников: Oracle, DB2, MSSQL, MySQL, Excel, DBF, CSV и т.д.
    4. далее вы можете работать с этими таблицами/ХП в обоих направлениях, как если бы они физически находились в Caché.

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

    Другими словами, JOIN между таблицами из Oracle и MSSQL выполнить не удастся.

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


    Создание связанных таблиц


    Как уже было сказано выше, можно воспользоваться либо ODBC, либо JDBC драйвером для подключения к внешнему источнику данных. Рассмотрим оба варианта.

    Для ODBC необходимо вначале настроить системные DSN:





    А для JDBC — путь к виртуальной машине Java (далее все картинки кликабельны):



    Далее в SMP необходимо создать соединения для Caché SQL Gateway нужного типа (JDBC или ODBC), от которого будут зависеть те или иные настройки. Здесь же можно проверить вновь созданное соединение:





    Затем, воспользовавшись Мастером Связывания таблиц или процедур, следует создать необходимые виртуальные таблицы или ХП:





    Мастер запросит для каждой таблицы её новое имя, новые имена для каждого из полей, первичный ключ и т.д. В большинстве случаев вы можете все имена оставить по умолчанию, но бывают ситуации, когда некоторые из этих идентификаторов являются зарезервированными словами СУБД 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 клиента (см. одну из предыдущих статей), увидеть все таблицы и процедуры, как внутренние, так и внешние; причём разницу между ними определить сразу сложно.

    Ну, и конечно, можно вставлять/удалять/изменять данные, используя родной SQL-синтаксис тех или иных СУБД.

    Механизм Caché SQL Gateway может также использоваться и для встроенной бизнес-аналитики DeepSee, но это уже другая тема.

    Альтернативный пример использования
    Однажды данная технология помогла моим коллегам, работающим с другими СУБД (не 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.

    Далее приведён пример запроса к внешней таблице с использованием низкоуровневого доступа. Данный запрос выбирает все поля из таблицы 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():

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

      • Все аргументы метода 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() передаются:

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

      • Метод 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 соответственно.
    InterSystems
    87,00
    Вендор: СУБД Caché, OLAP DeepSee, шина Ensemble
    Поделиться публикацией

    Комментарии 0

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

    Самое читаемое