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

Программирование - Практика программирования

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

На многих предприятиях возникает необходимость "подружить" между собой различные системы учета. В моем случае это были 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 И ТекущийИтог <> Неопределено Тогда
				П.ТекстПодвала = ТР.Итог(П.Имя);
				П.ГоризонтальноеПоложениеВПодвале = ГоризонтальноеПоложениеЭлемента.Право;
			КонецЕсли;
		КонецЦикла;

На этом все.

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

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

См. также

Комментарии
1. Сергей Рудаков (fishca) 1074 14.06.17 09:07 Сейчас в теме
2. Юрий Кирпичиков (yak127) 32 14.06.17 13:13 Сейчас в теме
(1) На самом деле, все что нужно описано в публикации и на мой взгляд, прочитав ее, вполне можно самому сделать и даже развить функционал. Скачивать нет необходимости :-)
3. Ildar Gabdrakhmanov (spezc) 324 14.06.17 13:44 Сейчас в теме
Оставьте свое сообщение