gifts2017

Особенности работы 1С+ADO+MSSQL и Oracle.

Опубликовал provova в раздел Программирование - Практика программирования

В моей работе пришлось столкнуться с  задачей обмена данными XML через Шину (MDM), используя ADO. При построении такого обмена есть некоторые особенности, о которых я попытаюсь рассказать в данной статье.

Чтобы не нагружать данную статью, шиной будем считать некую таблицу на MSSQL (Oracle), в которую записываются пакеты обмена (XML) для определенной базы данных. Изначально шина была построена на MSSQL. Привожу пример записи пакета в шину для MSSQL:

Процедура СоздатьПараметрЗапроса(extCommSQL, названиеПараметр, текстПараметр)
    Для Инд = 0 по extCommSQL.Parameters.Count - 1 Цикл
        Если extCommSQL.Parameters.Item(Инд).Name = НазваниеПараметр тогда
            extCommSQL.Parameters.Item(Инд).Value = ТекстПараметр;
        КонецЕсли;
    КонецЦикла;
КонецПроцедуры

Функция ПолучитьСоединениеСБазой()
    extConnSQL = Новый ComОбъект("ADODB.Connection");
    СтрокаСоединения =  "Provider=SQLOLEDB.1;
    |User ID=user;
    |Pwd=pass;
    |Data Source=server;
    |Initial Catalog=Base";
    extConnSQL.ConnectionString = СтрокаСоединения;
    extConnSQL.Open();
    Возврат extConnSQL;
КонецФункции

Функция ЗаписьВSQL(ИмяБазыПолучателя,ИмяСправочника,УИД,ХМЛ, ОписаниеОшибки, Соединение=Неопределено)
    txtQuery="[gate1C].sp_Write";
    СоединениеСБазой = ?(Соединение = Неопределено, ПолучитьСоединениеСБазойSQL(), Соединение);
    ЗапросАДО=Новый COMОбъект("ADODB.Command");
    ЗапросАДО.ActiveConnection=СоединениеСБазой;
    ТекстЗапроса=txtQuery;
    ЗапросАДО.CommandText=ТекстЗапроса;
    ЗапросАДО.CommandType = 4;

    СоздатьПараметрЗапроса(ЗапросАДО, "@ReceiverSystemCode", ИмяБазыПолучателя);
    СоздатьПараметрЗапроса(ЗапросАДО, "@EntityType", ИмяСправочника);
    СоздатьПараметрЗапроса(ЗапросАДО, "@EntityID",УИД);
    СоздатьПараметрЗапроса(ЗапросАДО, "@XMLtext", ХМЛ);
    ЗапросАДО.Prepared = true;
    Попытка
        ЗапросАДО.Execute();
        Ошибка = Ложь;
    Исключение
        Ошибка = Истина;
        ОписаниеОшибки = ОписаниеОшибки();
    КонецПопытки;
    Если Соединение = Неопределено Тогда
        ЗакрытьСоединениеСБазой(СоединениеСБазой);
    КонецЕсли;
    Возврат не Ошибка;
КонецФункции

Понятно, что на сервере есть хранимая процедура (ЗапросАДО.CommandType = 4; 1- для запроса) [gate1C].sp_Write, с 4 параметрами, которая записывает данные в исходную таблицу. Здесь проблем нет, драйвер SQL OLEDB без проблем понимает и записывает большие пакеты. Пример функции чтения из шины для MSSQL:

Функция ЧтениеИзSQL(ИмяБазы)
    txtQuery="[gate1C].sp_GetAllUnLoaded";
    СоединениеСБазой=ПолучитьСоединениеСБазой();
    ЗапросАДО=Новый COMОбъект("ADODB.Command");
    ЗапросАДО.ActiveConnection=СоединениеСБазой;
    ТекстЗапроса=txtQuery;
    ЗапросАДО.CommandText=ТекстЗапроса;
    ЗапросАДО.CommandType = 4;
    СоздатьПараметрЗапроса(ЗапросАДО, "@ReceiverSystemCode", ИмяБазы);
    ЗапросАДО.Prepared = true;
    rsTABLE=ЗапросАДО.Execute();
    КоличествоПолейТаблица=rsTABLE.fields.Count-1;
    ТаблицаПоискаТаблица = Новый массив;
    Если (НЕ rsTABLE.EOF) Тогда
        ТаблицаПоискаТаблица = rsTABLE.GetRows().Выгрузить();
    КонецЕсли;

    тзРезультат = Новый ТаблицаЗначений;
    Для сч = 0 По КоличествоПолейТаблица Цикл
        тзРезультат.Колонки.Добавить(rsTABLE.fields.item(сч).name);
    КонецЦикла;

    Для Каждого стрТаблица Из ТаблицаПоискаТаблица Цикл
        стр=тзРезультат.Добавить();
        Для сч = 0 По КоличествоПолейТаблица Цикл
            попытка
                стр[сч] = СокрЛП(стрТаблица[сч]);
            исключение
                сообщить(ОписаниеОшибки());
            конецпопытки;
        КонецЦикла;
    КонецЦикла;
    ЗакрытьСоединениеСБазой(СоединениеСБазой);
    Возврат тзРезультат;
КонецФункции

Функция возвращает ТаблицуЗначений с результатами SELECT-а из шины. [gate1C].sp_GetAllUnLoaded также хранимая процедура. Вместо нее можно использовать обыкновенный SELECT, указав ЗапросАДО.CommandType = 1

При переводе шины на Oracle, столкнулся сразу с 2-умя сложностями. Для хранения пакетов XML в таблице Oracle использовался тип данных CLOB (Character large object). Т.к. ODBC драйвер для Oracle не поддерживает пакеты больше 32кб, использовал драйвер OraOLEDB, Функция соединения с базой приведена ниже:

Функция ПолучитьСоединениеСШинойДанных() Экспорт
    extConnSQL = Новый ComОбъект("ADODB.Connection");
    СтрокаСоединения =  "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=orcl)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=15)))(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED)));User Id=user;Password=pass;";
    extConnSQL.ConnectionString = СтрокаСоединения;
    extConnSQL.Open();
    Сообщить("Подключились к шине!");
    Возврат extConnSQL;
КонецФункции

Но и с этим драйвером оказалось не все гладко, т.к. на пакеты более 32 кб, также выдавалась ошибка. Проблема решалась установкой параметра

SPPrmsLOB = true;

Только после этого запись пакета стала производиться успешно. Код процедуры ниже:

Процедура СоздатьПараметрЗапросаСШинойДанных(extCommSQL, названиеПараметр, текстПараметр,Тип=205,Вид=1 )
    Параметр= extCommSQL.CreateParameter(названиеПараметр,Тип,Вид,СтрДлина(текстПараметр)+1,текстПараметр);
    extCommSQL.Parameters.append(Параметр);
КонецПроцедуры


Функция ЗаписьВШинуДанных(ИмяБазыПолучателя,ИмяСправочника,УИД,ХМЛ, ОписаниеОшибки, Соединение=Неопределено) Экспорт
    txtQuery="BUS_EXPORT.sendMessage";
    СоединениеСБазой = ?(Соединение = Неопределено, ПолучитьСоединениеСШинойДанных(), Соединение);
    ЗапросАДО=Новый COMОбъект("ADODB.Command");
    ЗапросАДО.ActiveConnection=СоединениеСБазой;
    ТекстЗапроса=txtQuery;
    ЗапросАДО.CommandText=ТекстЗапроса;
    ЗапросАДО.CommandType = 4;

    СоздатьПараметрЗапросаСШинойДанных(ЗапросАДО, "messageData"    , ХМЛ,202);
    СоздатьПараметрЗапросаСШинойДанных(ЗапросАДО, "messageObject"  , ИмяСправочника,12);
    СоздатьПараметрЗапросаСШинойДанных(ЗапросАДО, "messageDest"   СокрЛП(ИмяБазыПолучателя),12);
    СоздатьПараметрЗапросаСШинойДанных(ЗапросАДО, "messageID"      , УИД,12);
    ЗапросАДО.Prepared = true;
    ЗапросАДО.Properties("SPPrmsLOB").value=Истина;
    Попытка
        ЗапросАДО.Execute();
        Ошибка = Ложь;
    Исключение
        Ошибка = Истина;
        ОписаниеОшибки = ОписаниеОшибки();
    КонецПопытки;
    Если Соединение = Неопределено Тогда
        ЗакрытьСоединениеСШиной(СоединениеСБазой);
    КонецЕсли;
    Возврат не Ошибка;
КонецФункции

Для поля типа CLOB тип значения в CreateParameter - 202, для varchar2 -12

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

PLSQLRSet=Истина

Пример чтения из шины:

Функция ЧтениеИзШины(ИмяБазы) Экспорт
    txtQuery="BUS_IMPORT.getNextMessagebyType";
    СоединениеСБазой=ПолучитьСоединениеСШинойДанных();
    ЗапросАДО=Новый COMОбъект("ADODB.Command");
    ЗапросАДО.ActiveConnection=СоединениеСБазой;
    ТекстЗапроса=txtQuery;
    ЗапросАДО.CommandText=ТекстЗапроса;
    ЗапросАДО.CommandType = 4;
    Если ЗначениеЗаполнено(ИмяОбъекта) тогда
        ТипыПакетов=СокрЛп(ИмяОбъекта);
    иначе
        ТипыПакетов="ALL";
    КонецЕсли;
    СоздатьПараметрЗапросаСШинойДанных(ЗапросАДО, "MessageType", ТипыПакетов,12);
    ЗапросАДО.Prepared = true;
    ЗапросАДО.Properties("SPPrmsLOB").value=Истина;
    ЗапросАДО.Properties("PLSQLRSet").value=Истина;
    rsCursor=ЗапросАДО.Execute();

    КоличествоПолейТаблица=rsCursor.fields.Count-1;

    ТаблицаПоискаТаблица = Новый массив;
    Если (НЕ rsCursor.EOF) Тогда
        ТаблицаПоискаТаблица = rsCursor.GetRows().Выгрузить();
    КонецЕсли;

    тзРезультат = Новый ТаблицаЗначений;
    Для сч = 0 По КоличествоПолейТаблица Цикл
        тзРезультат.Колонки.Добавить(rsCursor.fields.item(сч).name);
    КонецЦикла;

    Для Каждого стрТаблица Из ТаблицаПоискаТаблица Цикл
        стр=тзРезультат.Добавить();
        Для сч = 0 По КоличествоПолейТаблица Цикл
            попытка
                стр[сч] = СокрЛП(стрТаблица[сч]);
            исключение
                сообщить(ОписаниеОшибки());
            конецпопытки;
        КонецЦикла;
    КонецЦикла;
    ЗапросАДО.Properties("SPPrmsLOB").value=False;
    ЗапросАДО.Properties("PLSQLRSet").value=False;
    ЗакрытьСоединениеСШинойДанных(СоединениеСБазой);
    Возврат тзРезультат;
КонецФункции

См. также

Подписаться Добавить вознаграждение
Комментарии
0. provova 13.09.11 15:30
В моей работе пришлось столкнуться с задачей обмена данными XML через Шину (MDM), используя ADO. При построении такого обмена есть некоторые особенности, о которых я попытаюсь рассказать в данной статье.

Перейти к публикации

1. Саша Безымяный (help1Ckr) 13.09.11 21:24
Было бы интересно, если о самой шине немного просветили темных людей)
2. Роман Романов (romansun) 13.09.11 22:08
3. provova 13.09.11 22:29
help1Ckr
В 2ух словах тут не опишешь. Эта тема отдельной статьи, как будет время расскажу подробнее.

romansun
Еще 2 голоса, и разукрашу. Права на скачивание еще не заработал.
4. Роман Романов (romansun) 13.09.11 23:38
(3)
голосов автору! ))

provova пишет:
В 2ух словах тут не опишешь. Эта тема отдельной статьи, как будет время расскажу подробнее.


+1, тоже с удовольствием почитал бы
5. FFFF FFF (Gasdrubal) 14.09.11 10:02
Добрый день, не хватает кода и пояснений по всяким возможностям ADO и того, с чем это едят. Про шину - умолчу))
В целом, хочется конкретики, например, про Оракл - там не все просто с курсором. Т.е. ваш метод я дли Оракла и Для MSSQL работало?

А так все понравилось, плюсую!
6. provova 14.09.11 10:46
Да как раз с курсором и были сложности то есть хранимая процедура( TYPE refcur IS REF CURSOR; в заголовке пакеджа):

type TTB_BUF_OUT IS TABLE OF TB_BUF_OUT%ROWTYPE index by BINARY_INTEGER;

procedure getNextMessagebyType (MessageType in varchar2,Cur out refcur)
is
-- buf_row TB_BUF_OUT%ROWTYPE;
array_t TTB_BUF_OUT;
begin


update TB_BUF_OUT
set TB_BUF_OUT.MSG_STATUS = 'FAILED'
where TB_BUF_OUT.MSG_STATUS = '4'
;


-- полуечние списка строк для курсора
IF trim(MessageType) = 'ALL' THEN
OPEN Cur FOR
select *
from
TB_BUF_OUT
where
TB_BUF_OUT.MSG_STATUS in ('READY','1','2','3','4')
and ROWNUM < 100
order by
TB_BUF_OUT.ID_ESB
; -- EO OPEN
ELSE
-- для указанной сущности:
OPEN Cur FOR
select *
from
TB_BUF_OUT
where
TB_BUF_OUT.MSG_STATUS in ('READY','1','2','3','4')
and TB_BUF_OUT.MSG_TYPE like(RTRIM(MessageType) + '%')
and ROWNUM < 100
order by TB_BUF_OUT.ID_ESB;
END IF;


FETCH Cur BULK COLLECT INTO array_t;
FOR i in array_t.FIRST .. array_t.LAST
LOOP
UPDATE TB_BUF_OUT
set TB_BUF_OUT.MSG_STATUS = case length(TB_BUF_OUT.MSG_STATUS)
when 1 then to_char(to_number(TB_BUF_OUT.MSG_STATUS) + 1)
else '1'
end
WHERE TB_BUF_OUT.ID = array_t(i).id;
END LOOP;

end;
То есть процедура возвращает курсор, и чтобы результат был в виде рекордсет устанавливается параметр
PLSQLRSet =Истина.
Я достаточно долго пробовал разными путями получить хоть какой то результат, но получилось только этим.
7. Joda Jedi (hexus) 21.09.11 12:40
Спасибо за информацию, очень помогла разобраться с некоторыми моментами. Очень жду продолжения