Интеграции с сервером SQL. Быстро и просто

06.07.20

Интеграция - Внешние источники данных

Решаем вопросы экспорта/импорта данных в базы отличного от 1С происхождения.

Условия тестирования описанного ниже:  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;
	
КонецФункции


      Принцип работы таков:

  1. Обеспечиваем выборку и при необходимости программную обработку данных. Функциям в качестве основного аргумента подходит как результат запроса, так и таблица значений.
  2. Собираем параллельно два запроса в формате SQL, первый удаляет из внешней таблицы записи по значению ключевого поля, второй добавляет свежие. Не забываем, что длина неограниченной строки в реальной жизни ограничена и составляет в районе 200 000+ символов, после которых растет только значение, определяемое через СтрДлина(), но не сама строка. Данный факт сам собой задает нам размер объема данных, которые могут быть обработаны за одно обращение к SQL. После превышения предельной длины строки немедленно выполняем запросы и приступаем к формированию новых. Тем самым, обработка данных ведется блоками. Даже если бы нам очень не хотелось морочиться с её обеспечением, это пришлось бы сделать применительно к большинству практических задач.
  3. По завершении обхода еще раз выполняем запрос и записывает тем самым последнюю порцию данных.


Примеры обращения:

Процедура ЭкспортНоменклатуры()
	
	ЗапросНоменклатура = Новый Запрос;
	
	ЗапросНоменклатура.Текст = 

	"ВЫБРАТЬ РАЗЛИЧНЫЕ
	|	НоменклатураСпр.Ссылка КАК _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С)
  2. Возможность использовать прием с имитацией движений документов во внешнюю базу. Пользователь что-то проводит, за ненадобностью внутрь 1С в еще один регистр не пишем, а сразу отправляем данные туда, где они требуются.
  3. Часто нет необходимости изобретать промежуточные форматы файлов/сообщений.
  4. В штатном режиме работы нет тяжелых операций по полному замещению объемной внешней таблицы её актуальной версией.

SQL СУБД экспорт/импорт данных прямые запросы

См. также

Перенос данных из Парус 10 в ЗГУ ред.3

Внешние источники данных Кадровый учет Файловый обмен (TXT, XML, DBF), FTP Обмен между базами 1C Платформа 1С v8.3 Сложные периодические расчеты 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)

Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактических удержаниях, НДФЛ, вычетах, страховых взносах из базы Парус 10 учреждений в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (ЗГУ) и начать с ней работать с любого месяца года.

60000 руб.

05.10.2022    9158    9    8    

10

Перенос данных из Парус 8 в ЗГУ 3

Зарплата Внешние источники данных Бюджетный учет Платформа 1С v8.3 Сложные периодические расчеты 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)

Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактических удержаниях, НДФЛ, вычетах, страховых взносах из базы Парус 8 учреждений в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (ЗГУ) и начать с ней работать с любого месяца года.

84000 руб.

19.08.2020    22354    18    1    

21

Автоматическая многопоточная выгрузка данных 1С 8.3 в БД Clickhouse и MS SQL (для работы с данными 1С в BI-системах)

Внешние источники данных Платформа 1С v8.3 Управляемые формы Анализ и прогнозирование Конфигурации 1cv8 Узбекистан Беларусь Кыргызстан Молдова Россия Казахстан Платные (руб)

Готовое решение для автоматизированной выгрузки данных из 1С 8.3, а также MS Excel в базу данных ClickHouse, а также в Microsoft SQL для работы с данными 1С в Yandex Datalens, Visiology, Apache Superset (и не только) - "Экстрактор данных 1С в BI". Решение отлично работает со всеми типовыми (и не только) конфигурациями 1С 8.3 для управляемых форм. Gозволяет автоматизировать работу бизнес-аналитика по ежедневной выгрузке данных из 1С в БД ClickHouse для последующей работы с этой БД в Yandex Datalens/ Система полностью автоматизирует работу с хранилищем данных в БД Clickhouse/MS SQL. Не надо быть программистом, чтобы одной кнопкой получать любые данные из 1С в Вашей BI-системе

230000 руб.

15.11.2022    12926    12    47    

28

Перенос данных из Парус 7.хх в ЗГУ ред.3

Внешние источники данных Зарплата Бюджетный учет Платформа 1С v8.3 Сложные периодические расчеты 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)

Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактических удержаниях, НДФЛ, вычетах, страховых взносах из базы Парус 7.хх учреждений в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (ЗГУ) и начать с ней работать с любого месяца года.

24000 руб.

24.04.2017    48632    96    159    

86

Перенос начальных остатков из Парус 7.71 в БГУ

Внешние источники данных Взаиморасчеты Учет ОС и НМА Логистика, склад и ТМЦ Бюджетный учет Платформа 1С v8.3 Бухгалтерский учет 1С:Бухгалтерия 2.0 1С:Бухгалтерия государственного учреждения Государственные, бюджетные структуры Россия Бюджетный учет Платные (руб)

Перенос словарей и начальных остатков из ПП Парус-Бухгалтерия Бюджет 7.71 в 1Сv8 БГУ2. Заполнение словарей и документов по вводу начальных остатков. Не требуется установка ПП Парус7. Возможна дозагрузка. Позволит автоматически и наиболее полно ввести данные в программу для начала работы. 

15600 руб.

08.12.2011    81473    128    123    

146

Перенос данных из Парус 10 (Торнадо) в ЗГУ ред.3 через Excel

Внешние источники данных Загрузка и выгрузка в Excel Зарплата Бюджетный учет Платформа 1С v8.3 Сложные периодические расчеты 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)

Обработка позволяет перенести кадровую информацию и данные по заработной плате из Парус 10(Торнадо) учреждений через файлы Excel в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (ЗГУ). В принципе, обработка может быть использована для загрузки из файлов Excel, полученных из любых информационных систем.

24000 руб.

16.11.2018    29981    20    31    

21
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. artbear 1447 06.07.20 12:15 Сейчас в теме
Возможность использовать прием с имитацией движений документов во внешнюю базу. Пользователь что-то проводит, за ненадобностью внутрь 1С в еще один регистр не пишем, а сразу отправляем данные туда, где они требуются.

Т.е. предлагаете внутри транзакции проведения документа обращаться к внешней БД, верно?
ИМХО решение так себе во многих случаях - быстродействие, блокировки и т.п.

ну и прямая работа с СУБД должна быть последним средством. До обращения к нему лучше рассмотреть\проверить другие, более штатные возможности интеграции - сервисы от самого приложения, которое связано с СУБД.
2. Infector 199 06.07.20 12:42 Сейчас в теме
(1)Далеко не всегда по ту сторону приложение от брендового разработчика с набором сервисов на все случаи жизни. Это может быть и база с оболочкой, написанная иностранцами-коллегами на коленке в достаточно старые годы и заточенная под одну-две укоспецифические задачи.

Ну и есть такой случай, когда на СУБД лежит таблица-прокладка. Мы пишем, они читают. По крайней мере удобнее, чем файл в виде такой прокладки.
3. alex_bob 246 06.07.20 13:47 Сейчас в теме
Когда-то давно использовал похожее решение (на запись), но потом отказался. Напишу на всякий случай о минусах:
1. Относительная сложность тестирования и отладки. Понятно, что это не так часто требуется.
2. Необходимо следить за наличием нужных драйверов у пользователей. Бывает, отваливается после обновления винды.
3. В какой-то момент я сам мигрировал на Linux и соответственно решения на Com-объектах стали неактуальны.
4. Самое главное, из-за чего отказался от прямой записи - необходимость костылей для проверки окружения - боевая база или копия, чтобы случайно не назаписывать лишнего в стороннюю базу.
4. Infector 199 06.07.20 14:07 Сейчас в теме
(3)по п.2. - в нынешних реалиях решается работой на стороне сервера. 99%, что обращение к СУБД пойдет оттуда, следовательно драйверов на сервере достаточно
п. 4 - именно так, такой костыль/заглушка необходим. Решается по большому счету одной функцией.

Функция ПроверитьИмяБазы() Экспорт
	
	СтрокаПодключения = СтрокаСоединенияИнформационнойБазы();
	ИмяБазы = "";
	
	ПараметрыСписком = СтроковыеФункцииКлиентСервер.РазложитьСтрокуВМассивПодстрок(СтрокаПодключения, ";");
	//вичи_ОбщиеФункции.ПолучитьКлючиСписком(СтрокаПодключения, ";");
	
	Для Каждого Параметр Из ПараметрыСписком Цикл
		Если Найти(Параметр,"Ref=")>0 Тогда
			ИмяБазы = СтрЗаменить(Параметр, "Ref=", "");
			Прервать;
		ИначеЕсли Найти(Параметр,"File=")>0 Тогда	
			ИмяБазы = СтрЗаменить(Параметр, "File=", "");
			Прервать;
		КонецЕсли;	
	КонецЦикла;	

	ИмяБазыНастройка = ИмяОсновнойБазы();
	
	Если ВРег(СокрЛП(ИмяБазы)) = ВРег(СокрЛП(ИмяБазыНастройка)) Тогда
		Возврат Истина;
	Иначе	
		Возврат Ложь;
	КонецЕсли;
	
КонецФункции
Показать

п.1,3 - так оно и есть, добавить нечего
Оставьте свое сообщение