Запрос к внешней базе данных MS SQL Server

20.01.19

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

Обработка позволяет выполнить запрос к базе данных MS SQL Server на классическом SQL. Своего рода консоль запросов, некий аналог Server Management Studio в среде 1С.

Скачать файлы

Наименование Файл Версия Размер
Запрос к внешней базе данных MS SQL Server:
.epf 18,23Kb
19
.epf 18,23Kb 19 Скачать

На многих предприятиях возникает необходимость "подружить" между собой различные системы учета. В моем случае это были 1С и Navision. Суть взаимодействия систем была проста - какие-то данные мигрировали из одной системы в другую и наоборот. А когда есть две системы с (в теории) идентичными данными эту самую идентичность нужно еще достичь. Потому как на практике по различным причинам что-то может не попасть в обмен или уже на стороне приемника отсечься как некорректное и т.п. Заниматься поиском расхождений задача относительно несложная, но кропотливая. Чтоб облегчить себе жизнь и не скакать между различными окнами, пришла в голову идея находясь в 1С получать данные из Navision. А так как данные он хранит в базе MS SQL сервера, нужно было всего лишь придумать инструмент для выполнения произвольного запроса на классическом SQL.

Конечно можно ничего не выдумывать и пользоваться Server Management Studio. Но во-первых, пускать туда какого-нибудь продвинутого аналитика небезопасно. Во-вторых, далеко не всегда сисадмины позволяют ставить подобный софт. В-третьих, если инструмент внедрен в 1С, то задачу по поиску можно делегировать, банально сохранив запрос, и пусть бухгалтер сам ищет что у него не ушло/пришло.

Для начала нужно установить соединение с SQL Server. Далее описать команду и результат выполнения вернуть в набор записей. Нам понадобятся три COM объекта: ADODB.Connection, ADODB.Command и ADODB.Recordset. Код выглядит следующим образом:

	Если АутентификацияWindows Тогда
		СтрокаПодключения = "DRIVER={SQL Server};SERVER="+Сервер+";DATABASE="+База+";LANGUAGE=русский;Trusted_connection=yes";
	Иначе
		СтрокаПодключения = "DRIVER={SQL Server};SERVER="+Сервер+";UID="+Пользователь+";
							|PWD="+Пароль+";DATABASE="+База+";LANGUAGE=русский";
	КонецЕсли;
	Соединение = Новый COMОбъект("ADODB.Connection");
	Соединение.ConnectionString = СтрокаПодключения;

	cmd = Новый COMОбъект("ADODB.Command");
    cmd.CommandTimeout = 360;        
    cmd.ActiveConnection = Соединение;
    RS = Новый COMОбъект("ADODB.Recordset");
	cmd.CommandText = ТекстЗапроса;

	Try
		RS=cmd.execute(); 
	Except
        #Если Клиент Тогда 
            Сообщить(ОписаниеОшибки(), СтатусСообщения.Важное);
        #КонецЕсли       
        RS = Неопределено;
    EndTry;

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

	Нав = Новый ТаблицаЗначений;
	Для каждого П из RS.Fields Цикл
		Попытка
			Нав.Колонки.Добавить(ПривестиИмяКДопустимому(П.Name), , П.Name);
		Исключение
			Предупреждение("Имя поля <" + П.Name + "> в запросе встречается более двух раз, задайте ему псевдоним.");
		КонецПопытки;
	КонецЦикла;
	
	Пока RS.EOF() = 0 Цикл
	   	строка = Нав.Добавить();
		й = 0;
		Для каждого П из RS.Fields Цикл
			ТекущееПоле = ИменаПолей[й].Поле;
			строка[ТекущееПоле] = RS.fields(й).Value;
			й = й + 1;
		КонецЦикла;
	 	RS.MoveNext();    
    КонецЦикла;    
	RS.Close();	

В коде встречается функция ПривестиИмяКДопустимому. Из названия не трудно догадаться, что ее предназначение убрать из имени всякие символы (%./\ и т.п), недопустимые в наименовании колонок таблицы. И попутно сохранение соответствия имен полей в ТЧ ИменаПолей.

Функция ПривестиИмяКДопустимому(Стр)
	Рез = СтрЗаменить(Стр," ","_"); 			// удалим пробелы
	Рез = СтрЗаменить(Рез,Символы.Таб,""); 		// удалим знак табуляции
	Рез = СтрЗаменить(Рез,"(","_"); 			// удалим знаки скобок
	Рез = СтрЗаменить(Рез,")","_"); 			// удалим знаки скобок
	Рез = СтрЗаменить(Рез,"-","_"); 			// удалим дефис
	Рез = СтрЗаменить(Рез,"%","_"); 			// удалим знак %
	Рез = СтрЗаменить(Рез,"#","_"); 			// удалим знак #
	Рез = СтрЗаменить(Рез,"№","_"); 			// удалим знак №
	Рез = СтрЗаменить(Рез,"/","_"); 			// удалим знак /
	Рез = СтрЗаменить(Рез,"\","_"); 			// удалим знак \
	
	НайденнаяСтрока = ИменаПолей.Найти(Рез,"Поле");
	Если НайденнаяСтрока = Неопределено Тогда
		НП = ИменаПолей.Добавить();
		НП.Поле = Рез;
		НП.Описание = Стр;
	Иначе
    	Рез = Рез + "_";
		НП = ИменаПолей.Добавить();
		НП.Поле = Рез;
		НП.Описание = Стр;
	КонецЕсли;

	Возврат Рез;
КонецФункции

На этом этапе у нас есть результат в виде таблицы значений. В обычной форме вывести его особых трудов не составляет.

	Результат = Нав.Скопировать();
	ЭлементыФормы.Результат.СоздатьКолонки();

В управляемой форме основная логика обработки та же самая. Некоторые трудности вызвало динамическое отображение результата. Дело в том, что я не нашел аналога методу СоздатьКолонки() для управляемых форм. Пришлось добавлять/удалять элементы формы вручную. Код получился слегка громоздким. Перед выполнением запроса очищаем то, что осталось от предыдущего. Сначала удаляем элементы, а затем связанные с ними реквизиты.

		//Очистим все результаты предыдущего запроса
		Если Объект.ИменаПолей.Количество() > 0 Тогда
			РезультатЗапроса.Очистить();
			//Удалим элементы формы
			Для каждого П из Объект.ИменаПолей Цикл
				Эл = Элементы.Найти(П.Поле);
				Если НЕ Эл = Неопределено Тогда
					Элементы.Удалить(Эл);
				КонецЕсли;
			КонецЦикла;
			//Удалим реквизиты ТЧ РезультатЗапроса
			УдаляемыеРеквизиты = Новый Массив;
			Для каждого П из Объект.ИменаПолей Цикл
				УдаляемыеРеквизиты.Добавить("РезультатЗапроса."+П.Поле);
			КонецЦикла;
			ИзменитьРеквизиты(,УдаляемыеРеквизиты);
		КонецЕсли;
		//Удалим список полей из результата предыдущего запроса
		Объект.ИменаПолей.Очистить();

После того как мы получили результат в виде таблицы значений выводим его аналогично тому как мы очищали перед выполнением запроса. Только на этот раз сначала заводим реквизиты, а затем добавляем элементы.

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

На этом все.

См. также

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

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

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

84000 руб.

19.08.2020    22460    19    1    

22

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

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

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

60000 руб.

05.10.2022    9217    9    8    

10

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

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

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

24000 руб.

24.04.2017    48712    96    163    

86

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

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

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

15600 руб.

08.12.2011    81580    128    123    

147

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

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

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

24000 руб.

16.11.2018    30006    20    31    

21

Загрузка спецификаций в УНФ из системы Базис-мебельщик

Производство готовой продукции (работ, услуг) Внешние источники данных Платформа 1С v8.3 1С:Управление нашей фирмой 1.6 Лесное и деревообрабатывающее хозяйство Россия Управленческий учет Платные (руб)

Обработка предназначена для загрузки файлов, выгруженных из системы Базис-мебельщик, в справочник "Спецификации" для последующих процессов учета и диспетчирования полуфабрикатов и изделий.

7200 руб.

24.06.2021    19134    52    50    

29
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. fishca 1254 14.06.17 09:07 Сейчас в теме
2. yak127 38 14.06.17 13:13 Сейчас в теме
(1) На самом деле, все что нужно описано в публикации и на мой взгляд, прочитав ее, вполне можно самому сделать и даже развить функционал. Скачивать нет необходимости :-)
user712426; +1 Ответить
3. spezc 782 14.06.17 13:44 Сейчас в теме
Оставьте свое сообщение