Условия тестирования описанного ниже: Microsoft SQL Server 2008, Платформа 8.3.9.2170
В этот раз хотелось бы снова поговорить о прямых запросах к СУБД. Да, автор статьи слышал о том, что 1С крайне не рекомендует этим заниматься. Однако же заниматься этим мы будем с одной оговоркой – базы, с которыми предстоит поработать имеют отличное от 1С происхождение. Уже предчувствую возгласы – есть же «Внешние источники данных» в конфигурации, ими и пользуйтесь. Пользовались, умеем, однако же мнения публики об этом объекте конфигурации весьма противоречивы и многие из них далеко не восторженные. Основное преимущество их – понятная всякому, даже начинающему, программисту 1C методика работы с ними – либо аналогично объектам вроде документов и справочников, либо очень напоминающая непериодический независимый регистр сведений.
Вместе с тем есть и неприятные особенности. Прежде всего – отдельные типы данных «внешний источник» переваривает со скрипом. А чтобы положить в этот источник ссылку объекта в том виде, в каком она хранится в СУБД приходится указывать тип «Уникальный идентификатор», преобразовывать идентификаторы объектов в строки и заниматься перестановками групп символов. Случалось и так, что при указании строки подключения в пользовательском режиме ошибался, но вместо повторного запроса таковой просто получил бесконечные исключения при попытке обращения к нему.
В тоже время, для работы с иными базами данных «Внешний источник данных» нам совершенно не обязателен. Ведь научить 1C выполнять запросы к MS SQL или иной СУБД большой проблемы не составляет. Зато преимуществ получается сразу довольно много. Как ни крути, а СУБД – запросы как способ интеграции во многом выигрывают даже у WEB-сервисов, а у выгрузок/загрузок через файловую систему подавно.
Начнем с самого простого. Имеем стороннюю базу с полезными нам данными. Имеем 1С, которой эти данные полезны, но которая их не имеет. Как же их достать? В MS SQL отлаживаем запрос, на стороне 1С обеспечиваем формирование его текста с необходимыми условиями отбора и создаем пару функций, позволяющих подключиться к СУБД и получить из нее таблицу значений. Ну а дальше вопрос её обработки, зависящий от конкретной задачи и особенностей выбранного пути её решения.
Функция СоединитьСерверSQL() Экспорт
Настройки = inf_ПовтИспСеанс.ВернутьНастройкиСвязиSQL();
Server = Настройки.СерверSQL;
Base = Настройки.CATALOG;
User = Настройки.ЛогинSQL;
Pass = Настройки.ПарольSQL;
булWinLogin = Ложь;
con = Новый COMОбъект("ADODB.Connection");
con.ConnectionTimeout = 5;
con.CommandTimeout = 0;
con.CursorLocation = 3;
con.Provider = "MSDASQL";
Если (не ПустаяСтрока(User))и(не булWinLogin) тогда
con.ConnectionString = "driver={SQL Server};server="+Server+";uid="+User+";pwd="+Pass+";Database="+Base+";";
иначеЕсли не ПустаяСтрока(User) тогда
con.ConnectionString = "Driver={SQL Server Native Client 11.0};Server="+Server+";Database="+Base+";Trusted_Connection=yes;uid="+User+";pwd="+Pass+";";
иначе
con.ConnectionString = "Driver={SQL Server Native Client 11.0};Server="+Server+";Database="+Base+";Trusted_Connection=yes;";
КонецЕсли;
Попытка
con.Open();
Исключение
Сообщить(ОписаниеОшибки());
КонецПопытки;
Возврат con;
КонецФункции
Функция ПолучитьТаблицуДанныхSQL(ТекстЗапроса, con) Экспорт
Таблица = Новый ТаблицаЗначений;
cmd = Новый COMОбъект("ADODB.Command");
cmd.CommandTimeout = 0;
cmd.ActiveConnection = con;
cmd.CommandText = ТекстЗапроса;
rs = cmd.Execute();
Для НомерСтолбца = 0 По Rs.Fields.Count-1 Цикл
ИмяНовойКолонки = Rs.Fields(НомерСтолбца).Name;
Таблица.Колонки.Добавить(ИмяНовойКолонки);
КонецЦикла;
Если Не Rs.eof Тогда
rs.MoveFirst();
КонецЕсли;
Пока Не Rs.eof Цикл
НоваяСтрока = Таблица.Добавить();
Для каждого Колонка из Таблица.Колонки Цикл
ИмяКолонки = Колонка.Имя;
Значение = Rs.Fields.Item(ИмяКолонки).Value;
Если значение <> Null Тогда
НоваяСтрока[ИмяКолонки] = Значение;
КонецЕсли;
КонецЦикла;
rs.MoveNext();
КонецЦикла;
Возврат Таблица;
КонецФункции
Пример вызова:
Соединение = inf_ИнтеграцияССерверомSQL.СоединитьСерверSQL();
ТекстЗапроса = ШаблонЗапроса();
ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "&Номер&" , Ном.Начало);
ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "&Год&" , Ном.Год );
Т_SQL = inf_ИнтеграцияССерверомSQL.ПолучитьТаблицуДанныхSQL(ТекстЗапроса, Соединение);
Соединение.Close();
В общем-то на этом вопрос получения данных из внешней базы закрывается. Но остается второй, куда более обширный вопрос – как же наоборот, выгрузить данные из 1С в базу данных на «чистом» SQL? Web-сервисы, COM-соединения и промежуточные файлы не рассматриваем ввиду тематики статьи, хотя они имеют место быть и часто даже оказываются уместными, про эти вещи и без меня написано достаточно. А в рамках данной статьи нам будут интересны именно пути, предполагающие прямое взаимодействие с SQL.
Способ 1. Очевидный (но не лучший).
Уверен, что это первое, что приходит в голову разработчику, по крайней мере не из касты 1С – вооружаемся SQL-профайлером, любой консолью запросов и любой обработкой, раскрывающей структуру хранения базы данных и готовим прямой запрос к данным 1С. Далее этот запрос размещается где-нибудь в недрах стороннего продукта и обеспечивает получение данных. Это в теории. На практике же кроме использования на уровне СУБД нумерованных имен вместо отражающих смысл хранимой информации вылезают «подводные камни», подробнее о которых можно посмотреть здесь. Но в любом случае на выходе имеем нечто подобное:
SELECT T1._IDRRef as 'goods_ref'
,T1._Code as 'goods_Code'
,T1._Description as 'goods_Description'
,T1._Fld2522RRef as 'Vendor_ref'
,ISNULL(T3._Description,'') as 'Vendor_Description'
,T1._Fld26356RRef as 'Manufacturer_ref'
,ISNULL(T4._Description,'') as 'Manufacturer_Description'
,ISNULL(T4._Fld2091,'') as 'Manufacturer_Full_Description'
,ISNULL(T4._Fld2097,'') as 'Manufacturer_Site'
,T1._Fld2526RRef as 'Country_ref'
,ISNULL(T5._Code,'') as 'Country_code'
,ISNULL(T5._Description,'') as 'Country_Description'
,ISNULL(T5._Fld2042,'') as 'Country_Full_Description'
,ISNULL(T5._Fld2043,'') as 'Country_code_2'
,T1._Fld32598RRef as 'Tariff_ref'
,ISNULL(T6._Code,'') as 'Tariff_Code'
,ISNULL(T6._Description,'') as 'Tariff_Description'
,ISNULL(T6._Fld32593,'') as 'Tariff_Full_Description'
,ISNULL(T2._Fld35042,'') as 'Trademark'
,ISNULL(T2._Fld35043,'') as 'Packing'
,ISNULL(T2._Fld35044,'') as 'Packing_EP'
,ISNULL(T2._Fld35045,'') as 'Remark'
,ISNULL(T2._Fld35046,'') as 'Purpose'
,ISNULL(T2._Fld35047,'') as 'Purpose_2'
,ISNULL(T2._Fld35048,'') as 'Declaration_or_Certificate'
,ISNULL(T2._Fld35049RRef,0x00000000000000000000000000000000) as 'Declaration_ref'
,ISNULL(T7._Code,'') as 'Declaration_code'
,ISNULL(T2._Fld35157RRef,0x00000000000000000000000000000000) as 'Tariff_LT_ref'
,ISNULL(T8._Code,'') as 'Tariff_LT_Code'
,ISNULL(T8._Description,'') as 'Tariff_LT_Description'
,ISNULL(T8._Fld32593,'') as 'Tariff_LT_Full_Description'
,ISNULL(CAST(T1._Fld23987 AS NUMERIC(15, 5)),0.0) as 'Netto'
,ISNULL(T2._Fld35325,'2001-01-01 00:00:00') as 'Synchronization_Date_Time'
FROM [1C_VRUS_PRODUCTION].[dbo].[_Reference151] T1 WITH(NOLOCK)
INNER JOIN [1C_VRUS_PRODUCTION].[dbo].[_InfoRg35040] T2 WITH(NOLOCK)
ON (T1._IDRRef = T2._Fld35041RRef)
LEFT OUTER JOIN [1C_VRUS_PRODUCTION].[dbo].[_Reference122] T3 WITH(NOLOCK)
ON T1._Fld2522RRef = T3._IDRRef
LEFT OUTER JOIN [1C_VRUS_PRODUCTION].[dbo].[_Reference122] T4 WITH(NOLOCK)
ON T1._Fld26356RRef = T4._IDRRef
LEFT OUTER JOIN [1C_VRUS_PRODUCTION].[dbo].[_Reference111] T5 WITH(NOLOCK)
ON T1._Fld2526RRef = T5._IDRRef
LEFT OUTER JOIN [1C_VRUS_PRODUCTION].[dbo].[_Reference32592] T6 WITH(NOLOCK)
ON T1._Fld32598RRef = T6._IDRRef
LEFT OUTER JOIN [1C_VRUS_PRODUCTION].[dbo].[_Reference154] T7 WITH(NOLOCK)
ON T2._Fld35049RRef = T7._IDRRef
LEFT OUTER JOIN [1C_VRUS_PRODUCTION].[dbo].[_Reference32592] T8 WITH(NOLOCK)
ON T2._Fld35157RRef = T8._IDRRef
Из плюсов можно отметить разве что возможность передать эти задачи IT-специалистам из областей, отличных от 1С и в частности – занимающимся поддержкой целевой базы. Правда тут тоже есть свои ньюансы, т.к. радиус кривизны рук сильно варьируется не только среди 1С-ников, и на сервере с СУБД могут «завестись» интересные задания, гоняющие одни и те же данные через десяток таблиц и пытающиеся обработать миллионы строк из какого-нибудь регистра с себестоимостью в нашей 1С, а разобраться в них, не принимая участия в непосредственном создании окажется крайне затруднительно. Но не забывайте, что когда-нибудь могут попросить помочь с этим разобраться и Вас.
Способ 2. Удобный (хотя и не столь очевидный)
Мысль эта посещает не сразу, а когда посещает часто бывает уже поздновато, но все-таки: Если со стороны внешней базы не получается прочесть данные с нормальными именами полей, то почему бы не заставить 1С работать на запись? Реализация этого мероприятия несколько сложнее, чем чтения из внешней базы, тем не менее ничего сверхъестественного из себя не представляет. Прежде всего потребуется еще одна функция, которая будет выполнять SQL запросы, не предполагающие возврат какого-либо результата.
Процедура ВыполнитьЗапросSQL(ТекстЗапроса, con) Экспорт
cmd = Новый COMОбъект("ADODB.Command");
cmd.CommandTimeout = 0;
cmd.ActiveConnection = con;
cmd.CommandText = ТекстЗапроса;
rs = cmd.Execute();
КонецПроцедуры
Далее обратим внимание на структуру данных, таблиц, в которые будем писать. Помимо целевых полей, нам потребуется ключ, позволяющий однозначно идентифицировать принадлежность строк данных во внешней базе объектам 1С. Иначе говоря, смотрим как 1С хранит ссылки объектов и добавляем во всякой таблице с выгружаемыми нами данными поля с аналогичным типом данных и размерностью, т.е. binary(16)
Определяемся предполагает ли наша разработка соответствие одному объекту базы 1С одной записи внешней таблицы (как пример – отражение записи справочника), или же записей может быть несколько (отражение или имитация движений документов или табличных частей объектов). Для второго случая добавляем поле с номерами строк.
Не обязательно, но полезно при разборе полетов – Дата и время фактической записи во внешнюю таблицу.
Осталось самое главное – обеспечить запись данных в SQL. Как именно это будет выполняться – в подписке при непосредственном изменении (записи) объекта в 1C, регламентным заданием по расписанию, или по требованию пользователя зависит только от контекста задачи и личных вкусов разработчика. Нам же важен сам механизм. В зависимости от наличия одной строки (идентифицируется Ссылкой однозначно) или группы строк (т.е. строка идентифицируется по Ссылке и автономеру строки) запись будет слегка отличаться.
Процедура ОтразитьЕдиничныеОбъекты_SQL(Данные, ИмяТаблицы, ИмяПоляИдентификатора) Экспорт
Если Данные.Колонки.Найти(ИмяПоляИдентификатора) = Неопределено Тогда
Возврат;
КонецЕсли;
ОписаниеКолонок = ОписаниеКолонок(Данные);
ТекстЗапросаУдаление = ШаблонЗапросаНаУдаление(ИмяТаблицы, ИмяПоляИдентификатора);
ТекстЗапросаЗапись = ШаблонЗапросаНаЗапись(ИмяТаблицы, ОписаниеКолонок);
БлокЗначений = "";
БлокУдаление = "";
ДопПараметры = Новый Структура;
ДопПараметры.Вставить("МаксимальнаяДлинаСтроки", 200000); //Строка неограниченной длины не совсем неограниченная
ДопПараметры.Вставить("ОписаниеКолонок", ОписаниеКолонок);
ДопПараметры.Вставить("ИмяТаблицы" , ИмяТаблицы);
ДопПараметры.Вставить("ИмяПоляИдентификатора", ИмяПоляИдентификатора);
Соединение = СоединитьСерверSQL();
Попытка
Если ТипЗнч(Данные) = Тип("РезультатЗапроса") Тогда
Выборка = Данные.Выбрать();
Пока Выборка.Следующий() Цикл
ОтразитьСтрокуДанных(Выборка, Данные, ТекстЗапросаУдаление, ТекстЗапросаЗапись,
БлокЗначений, БлокУдаление, ДопПараметры, Соединение, Ложь)
КонецЦикла;
ИначеЕсли ТипЗнч(Данные) = Тип("ТаблицаЗначений") Тогда
Для Каждого Стр Из Данные Цикл
ОтразитьСтрокуДанных(Стр, Данные, ТекстЗапросаУдаление, ТекстЗапросаЗапись,
БлокЗначений, БлокУдаление, ДопПараметры, Соединение, Ложь)
КонецЦикла;
КонецЕсли;
Если ЗначениеЗаполнено(БлокУдаление) Тогда
ТекстЗапросаУдаление = СтрЗаменить(ТекстЗапросаУдаление, "%Идентификаторы%", БлокУдаление);
ВыполнитьЗапросSQL(ТекстЗапросаУдаление , Соединение);
КонецЕсли;
Если ЗначениеЗаполнено(БлокЗначений) Тогда
ТекстЗапросаЗапись = СтрЗаменить(ТекстЗапросаЗапись, "%Значения%", БлокЗначений);
ВыполнитьЗапросSQL(ТекстЗапросаЗапись, Соединение);
КонецЕсли;
Исключение //Ошибки ошибками, а соединение закрыть нужно
КонецПопытки;
Соединение.Close();
КонецПроцедуры
Процедура ОтразитьСтрокуДанных(Строка, Данные, ТекстЗапросаУдаление, ТекстЗапросаЗапись,
БлокЗначений, БлокУдаление, ДопПараметры, Соединение, ЭтоНаборЗаписей, НомСтр = 0)
ЗначенияДляСтроки = "";
ЗнУдаление = "";
Для Каждого Кл Из Данные.Колонки Цикл
Значение = Строка[Кл.Имя];
СтрЗначение = СтроковоеЗначениеПоля(Значение);
Если (НЕ ЭтоНаборЗаписей) И (Кл.Имя = ДопПараметры.ИмяПоляИдентификатора) Тогда
Если БлокУдаление = "" Тогда
БлокУдаление = СтрЗначение;
Иначе
БлокУдаление = БлокУдаление + Символы.ПС + ", " + СтрЗначение;
КонецЕсли;
КонецЕсли;
Если ЗначенияДляСтроки = "" Тогда
ЗначенияДляСтроки = СтрЗначение;
Иначе
ЗначенияДляСтроки = ЗначенияДляСтроки + Символы.ПС + ", " + СтрЗначение;
КонецЕсли;
КонецЦикла;
Если ДопПараметры.Свойство("ИмяПоляНумератора") Тогда
ЗначенияДляСтроки = ЗначенияДляСтроки + Символы.ПС + ", " + СтроковоеЗначениеПоля(НомСтр);
КонецЕсли;
РезДлина = СтрДлина(ТекстЗапросаЗапись) + СтрДлина(БлокЗначений) + СтрДлина(ЗначенияДляСтроки);
Если РезДлина > ДопПараметры.МаксимальнаяДлинаСтроки Тогда
Если ЗначениеЗаполнено(БлокУдаление) Тогда
ТекстЗапросаУдаление = СтрЗаменить(ТекстЗапросаУдаление, "%Идентификаторы%", БлокУдаление);
ВыполнитьЗапросSQL(ТекстЗапросаУдаление , Соединение);
ТекстЗапросаУдаление = ШаблонЗапросаНаУдаление(ДопПараметры.ИмяТаблицы, ДопПараметры.ИмяПоляИдентификатора);
БлокУдаление = "";
КонецЕсли;
Если ЗначениеЗаполнено(БлокЗначений) Тогда
ТекстЗапросаЗапись = СтрЗаменить(ТекстЗапросаЗапись, "%Значения%", БлокЗначений);
ВыполнитьЗапросSQL(ТекстЗапросаЗапись , Соединение);
ТекстЗапросаЗапись = ШаблонЗапросаНаЗапись(ДопПараметры.ИмяТаблицы, ДопПараметры.ОписаниеКолонок);
БлокЗначений = "";
КонецЕсли;
КонецЕсли;
Если БлокЗначений = "" Тогда
БлокЗначений = "(" + ЗначенияДляСтроки + ")";
Иначе
БлокЗначений = БлокЗначений + Символы.ПС + ", (" + ЗначенияДляСтроки + ")";
КонецЕсли;
КонецПроцедуры
Процедура ОтразитьНаборЗаписей_SQL(Данные, ИмяТаблицы, ИмяПоляИдентификатора, ИмяПоляНумератора) Экспорт
Если Данные.Колонки.Найти(ИмяПоляИдентификатора) = Неопределено Тогда
Возврат;
КонецЕсли;
ОписаниеКолонок = ОписаниеКолонок(Данные, ИмяПоляНумератора);
ТекстЗапросаУдаление = ШаблонЗапросаНаУдаление(ИмяТаблицы, ИмяПоляИдентификатора);
ТекстЗапросаЗапись = ШаблонЗапросаНаЗапись(ИмяТаблицы, ОписаниеКолонок);
БлокЗначений = "";
БлокУдаление = "";
ДопПараметры = Новый Структура;
ДопПараметры.Вставить("МаксимальнаяДлинаСтроки", 200000); //Строка неограниченной длины не совсем неограниченная //6054606
ДопПараметры.Вставить("ОписаниеКолонок", ОписаниеКолонок);
ДопПараметры.Вставить("ИмяТаблицы" , ИмяТаблицы);
ДопПараметры.Вставить("ИмяПоляИдентификатора", ИмяПоляИдентификатора);
Если ЗначениеЗаполнено(ИмяПоляНумератора) Тогда
ДопПараметры.Вставить("ИмяПоляНумератора", ИмяПоляНумератора);
КонецЕсли;
Соединение = СоединитьСерверSQL();
Попытка
Если ТипЗнч(Данные) = Тип("РезультатЗапроса") Тогда
ВыборкаРегистратор = Данные.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаРегистратор.Следующий() Цикл
ЗначениеКлюча = ВыборкаРегистратор[ИмяПоляИдентификатора];
СтрЗначение = СтроковоеЗначениеПоля(ЗначениеКлюча);
Если БлокУдаление = "" Тогда
БлокУдаление = СтрЗначение;
Иначе
БлокУдаление = БлокУдаление + Символы.ПС + ", " + СтрЗначение;
КонецЕсли;
Выборка = ВыборкаРегистратор.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
НомСтр = 0;
Пока Выборка.Следующий() Цикл
НомСтр = НомСтр + 1;
ОтразитьСтрокуДанных(Выборка, Данные, ТекстЗапросаУдаление, ТекстЗапросаЗапись,
БлокЗначений, БлокУдаление, ДопПараметры, Соединение, Истина, НомСтр);
КонецЦикла;
КонецЦикла;
ИначеЕсли ТипЗнч(Данные) = Тип("ТаблицаЗначений") Тогда
Т_ИД = Данные.Скопировать(,ИмяПоляИдентификатора);
Т_ИД.Свернуть(ИмяПоляИдентификатора);
Для Каждого СтрИД Из Т_ИД Цикл
ЗначениеКлюча = СтрИД[ИмяПоляИдентификатора];
СтрЗначение = СтроковоеЗначениеПоля(ЗначениеКлюча);
Если БлокУдаление = "" Тогда
БлокУдаление = СтрЗначение;
Иначе
БлокУдаление = БлокУдаление + Символы.ПС + ", " + СтрЗначение;
КонецЕсли;
ПараметрыОтбораСтрок = Новый Структура(ИмяПоляИдентификатора, СтрИД[ИмяПоляИдентификатора]);
СтрокиПоИД = Данные.НайтиСтроки(ПараметрыОтбораСтрок);
НомСтр = 0;
Для Каждого Стр Из СтрокиПоИД Цикл
НомСтр = НомСтр + 1;
ОтразитьСтрокуДанных(Стр, Данные, ТекстЗапросаУдаление, ТекстЗапросаЗапись,
БлокЗначений, БлокУдаление, ДопПараметры, Соединение, Истина, НомСтр);
КонецЦикла;
КонецЦикла;
КонецЕсли;
Если ЗначениеЗаполнено(БлокУдаление) Тогда
ТекстЗапросаУдаление = СтрЗаменить(ТекстЗапросаУдаление, "%Идентификаторы%", БлокУдаление);
ВыполнитьЗапросSQL(ТекстЗапросаУдаление , Соединение);
КонецЕсли;
Если ЗначениеЗаполнено(БлокЗначений) Тогда
ТекстЗапросаЗапись = СтрЗаменить(ТекстЗапросаЗапись, "%Значения%", БлокЗначений);
ВыполнитьЗапросSQL(ТекстЗапросаЗапись, Соединение);
КонецЕсли;
Исключение
КонецПопытки;
Соединение.Close();
КонецПроцедуры
Функция ОписаниеКолонок(Данные, ИмяПоляНумератора = "")
ОписаниеКолонок = "";
Для Каждого Кл Из Данные.Колонки Цикл
Если ОписаниеКолонок = "" Тогда
ОписаниеКолонок = Кл.Имя;
Иначе
ОписаниеКолонок = ОписаниеКолонок + Символы.ПС + ", " + Кл.Имя;
КонецЕсли;
КонецЦикла;
Если ЗначениеЗаполнено(ИмяПоляНумератора) Тогда
ОписаниеКолонок = ОписаниеКолонок + Символы.ПС + ", " + ИмяПоляНумератора;
КонецЕсли;
Возврат ОписаниеКолонок;
КонецФункции
Функция СтроковоеЗначениеПоля(Значение)
Если ТипЗнч(Значение) = Тип("Строка") Тогда
Значение = СтрЗаменить(Значение, "'", "''");
Значение = СокрЛП(Значение);
СтрЗначение = "'" + Значение + "'";
ИначеЕсли ТипЗнч(Значение) = Тип("Дата") Тогда
СтрЗначение = "'" + Формат(Значение, "Л=ru_RU; ДФ='yyyy-MM-dd HH:mm:ss'") + "'";
ИначеЕсли ТипЗнч(Значение) = Тип("Число") Тогда
СтрЗначение = Формат(Значение, "ЧРД=.; ЧН=0; ЧГ=");
ИначеЕсли ТипЗнч(Значение) = Тип("Булево") Тогда
СтрЗначение = Формат(Значение, "БЛ=0x00; БИ=0x01");
ИначеЕсли ТипЗнч(Значение) = Тип("Null") Тогда
СтрЗначение = "NULL";
Иначе
СтрЗначение = ПредставлениеСсылкиСУБД(Значение);
КонецЕсли;
Возврат СтрЗначение;
КонецФункции
Функция ШаблонЗапросаНаУдаление(ИмяТаблицы, ИмяПоляИдентификатора)
ТекстЗапроса =
"DELETE FROM %ИмяТаблицы%
|WHERE %ПолеИдентификатора% in (%Идентификаторы%)"
;
ТекстЗапроса = СтрЗаменить(ТекстЗапроса,"%ИмяТаблицы%", ИмяТаблицы);
ТекстЗапроса = СтрЗаменить(ТекстЗапроса,"%ПолеИдентификатора%", ИмяПоляИдентификатора);
Возврат ТекстЗапроса;
КонецФункции
Функция ШаблонЗапросаНаЗапись(ИмяТаблицы, ОписаниеКолонок)
ТекстЗапроса =
"INSERT INTO %ИмяТаблицы%
|
|(%ПоляТаблицы%
|)
|VALUES
| %Значения%
|"
;
ТекстЗапроса = СтрЗаменить(ТекстЗапроса,"%ИмяТаблицы%", ИмяТаблицы);
ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "%ПоляТаблицы%", ОписаниеКолонок);
Возврат ТекстЗапроса;
КонецФункции
Функция ПредставлениеСсылкиСУБД(Значение) Экспорт
Если Не ЗначениеЗаполнено(Значение) Тогда
Возврат "0x00000000000000000000000000000000";
КонецЕсли;
СтрокаИД = ЗначениеВСтрокуВнутр(Значение);
СтрокаИДПоКомпонентам = СтроковыеФункцииКлиентСервер.РазложитьСтрокуВМассивПодстрок(СтрокаИД, ":");
ПредставлениеСсылки0 = СтрокаИДПоКомпонентам[1];
Блок0 = Сред(ПредставлениеСсылки0,1,32);
ПредставлениеИсх0 ="0x"+Врег(Блок0);
Возврат ПредставлениеИсх0;
КонецФункции
Принцип работы таков:
- Обеспечиваем выборку и при необходимости программную обработку данных. Функциям в качестве основного аргумента подходит как результат запроса, так и таблица значений.
- Собираем параллельно два запроса в формате SQL, первый удаляет из внешней таблицы записи по значению ключевого поля, второй добавляет свежие. Не забываем, что длина неограниченной строки в реальной жизни ограничена и составляет в районе 200 000+ символов, после которых растет только значение, определяемое через СтрДлина(), но не сама строка. Данный факт сам собой задает нам размер объема данных, которые могут быть обработаны за одно обращение к SQL. После превышения предельной длины строки немедленно выполняем запросы и приступаем к формированию новых. Тем самым, обработка данных ведется блоками. Даже если бы нам очень не хотелось морочиться с её обеспечением, это пришлось бы сделать применительно к большинству практических задач.
- По завершении обхода еще раз выполняем запрос и записывает тем самым последнюю порцию данных.
Примеры обращения:
Процедура ЭкспортНоменклатуры()
ЗапросНоменклатура = Новый Запрос;
ЗапросНоменклатура.Текст =
"ВЫБРАТЬ РАЗЛИЧНЫЕ
| НоменклатураСпр.Ссылка КАК _GoodsID,
| НоменклатураСпр.Наименование КАК _NAME,
| НоменклатураСпр.Артикул КАК _ART_NO,
| ""n/a"" КАК _1C_ParentGroupID,
| ""n/a"" КАК _1C_GroupID,
| ""n/a"" КАК _1C_GroupName,
| ВЫБОР
| КОГДА ПОДСТРОКА(ЕСТЬNULL(ЦехСв.Значение.Наименование, """"), 1, 3) = ""801""
| ТОГДА ""CRAB""
| ИНАЧЕ ""FISH""
| КОНЕЦ КАК _WORKSHOP,
| НоменклатураСпр.Код КАК _CODE,
| ПОДСТРОКА(НоменклатураСпр.НаименованиеПолное, 1, 150) КАК _PRINTNAME,
| НоменклатураСпр.inf_ВесНеттоКг * 1000 КАК _PACKAGE_WEIGHT,
| ПРЕДСТАВЛЕНИЕ(НоменклатураСпр.inf_ВесНеттоКг * 1000) КАК _PACKAGE_WEIGHT_NVARCHAR,
| НоменклатураСпр.ЕдиницаХраненияОстатков.inf_СодержитШтук КАК _PACKAGES_PER_BOX,
| НоменклатураСпр.inf_КоробокНаПоддоне КАК _BOXES_PER_PALETTE,
| БрендСв.Значение КАК _BRAND_ID,
| ЕСТЬNULL(БрендСв.Значение.Наименование, """") КАК _BRAND_NAME,
| ВЫБОР
| КОГДА НоменклатураСпр.inf_ВсегоВесУпаковки = 0
| ТОГДА 1
| ИНАЧЕ НоменклатураСпр.inf_ВсегоВесУпаковки
| КОНЕЦ КАК _BRUTO_COEFF,
| НоменклатураСпр.ВидНоменклатуры КАК _TYPE_ID,
| НоменклатураСпр.ВидНоменклатуры.Наименование КАК _TYPE_NAME,
| ВЫБОР
| КОГДА НоменклатураСпр.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС10)
| ТОГДА 10
| КОГДА НоменклатураСпр.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС18)
| ТОГДА 18
| КОГДА НоменклатураСпр.СтавкаНДС = ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС20)
| ТОГДА 20
| ИНАЧЕ 0
| КОНЕЦ КАК _VAT,
| ЕСТЬNULL(inf_ДопКлассификаторыНоменклатуры.ТермическоеСостояние.Наименование, """") КАК _KEEPING_CONDITIONS,
| ЕСТЬNULL(inf_РазмерыОбъектов.ТипоразмерУпаковки.КоличествоВ_РядуЕвропаллета, 1) КАК _BOXES_IN_ROW,
| ЕСТЬNULL(НоменклатураСпр.КодТНВЭД.Код,"""") КАК _CUSTOMS_CODE,
| НоменклатураСпр.inf_ВесНеттоКоробка * 1000 КАК _BOX_WEIGHT_NETTO,
| ИСТИНА КАК _ForUseOMS,
| &ТекДата КАК _RecordDateTime
|ИЗ
| Справочник.Номенклатура КАК НоменклатураСпр
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЦехСв
| ПО НоменклатураСпр.Ссылка = ЦехСв.Объект
| И (&СвойствоЦех = ЦехСв.Свойство)
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК БрендСв
| ПО НоменклатураСпр.Ссылка = БрендСв.Объект
| И (&СвойствоБренд = БрендСв.Свойство)
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.inf_ДопКлассификаторыНоменклатуры КАК inf_ДопКлассификаторыНоменклатуры
| ПО (inf_ДопКлассификаторыНоменклатуры.Номенклатура = НоменклатураСпр.Ссылка)
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.inf_РазмерыОбъектов КАК inf_РазмерыОбъектов
| ПО (inf_РазмерыОбъектов.Объект = НоменклатураСпр.Ссылка)
|ГДЕ
| НоменклатураСпр.Ссылка В ИЕРАРХИИ(&Ссылки)
| И НоменклатураСпр.Наименование <> """"
| И НЕ НоменклатураСпр.ПометкаУдаления
| И НЕ НоменклатураСпр.inf_НеИспользовать
| И НЕ НоменклатураСпр.ЭтоГруппа"
;
ЗапросНоменклатура.УстановитьПараметр("Ссылки", Номенклатура);
ЗапросНоменклатура.УстановитьПараметр("СвойствоЦех" , inf_ПовтИспСеанс.СсылкаСвойстваОбъекта("Цех"));
ЗапросНоменклатура.УстановитьПараметр("СвойствоБренд" , inf_ПовтИспСеанс.СсылкаСвойстваОбъекта("Торговая марка"));
ЗапросНоменклатура.УстановитьПараметр("ТекДата" , ТекущаяДата());
Результат = ЗапросНоменклатура.Выполнить();
//inf_ИнтеграцияССерверомSQL.ОтразитьЕдиничныеОбъекты_SQL(Результат, "[VRUS_DW].[dbo].[GoodsExport_1C]", "_GoodsID");
//Возврат;
ТЗ = Результат.Выгрузить();
Для Каждого Стр Из ТЗ Цикл
Стр._PACKAGE_WEIGHT_NVARCHAR = Формат(Стр._PACKAGE_WEIGHT * 1000,"ЧРД=.; ЧН=0; ЧГ=") + " гр";
КонецЦикла;
inf_ИнтеграцияССерверомSQL.ОтразитьЕдиничныеОбъекты_SQL(ТЗ, "[VRUS_DW].[dbo].[GoodsExport_1C]", "_GoodsID");
КонецПроцедуры
Процедура ОтражениеПродаж()
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Продажи.Период КАК _Period,
| Продажи.Регистратор КАК _Recorder,
| Продажи.Номенклатура КАК _GoodsID,
| Продажи.Номенклатура.Наименование КАК _GoodsName,
| Продажи.Номенклатура.Артикул КАК _ART_NO,
| Продажи.Контрагент КАК _buyerID,
| Продажи.Контрагент.Наименование КАК _buyerName,
| Продажи.Количество КАК _quantity,
| Продажи.Стоимость КАК _cost,
| Продажи.НДС КАК _VAT,
| Продажи.Регистратор.Номер КАК _RecorderNumber,
| &ТекДата КАК _RecordDateTime
|ИЗ
| РегистрНакопления.Продажи КАК Продажи
|ГДЕ
| Продажи.Период МЕЖДУ &НачДата И &КонДата
|
|УПОРЯДОЧИТЬ ПО
| _Period,
| _Recorder,
| Продажи.НомерСтроки
|"
;
Запрос.УстановитьПараметр("НачДата", Период.ДатаНачала);
Запрос.УстановитьПараметр("КонДата", Период.ДатаОкончания);
Запрос.УстановитьПараметр("ТекДата", ТекущаяДата());
Результат = Запрос.Выполнить();
ТЗ = Результат.Выгрузить();
inf_ИнтеграцияССерверомSQL.ОтразитьНаборЗаписей_SQL(ТЗ, "[VRUS_DW].[dbo].[SalesExport_1C]", "_Recorder", "_LineNo");
КонецПроцедуры
И, наконец проверяем результат:
С некоторых пор при экспорте данных из 1С на уровне SQL стараюсь работать только на запись. Проблем с быстродействием замечено не было. Затраты времени – минимальны, фактически процесс разработки сводится к написанию запроса. Думается, что и для поклонников каноничного объекта «Внешние источники данных» аналогичный инструмент может быть легко реализован, хотя и без гарантий отсутствия некоторых проблем с совместимостью и преобразованиями типов. Среди основных достоинств замечены:
- Высокая оперативность при отражении данных (отражение может быть произведено непосредственно после записи объекта в 1С)
- Возможность использовать прием с имитацией движений документов во внешнюю базу. Пользователь что-то проводит, за ненадобностью внутрь 1С в еще один регистр не пишем, а сразу отправляем данные туда, где они требуются.
- Часто нет необходимости изобретать промежуточные форматы файлов/сообщений.
- В штатном режиме работы нет тяжелых операций по полному замещению объемной внешней таблицы её актуальной версией.
