Консоль запросов EX (внешние данные)

Публикация № 558547 30.10.16

Разработка - Инструментарий разработчика - Консоль запросов

Работа с внешними данными в консоли запросов (1С 7.7, 8.x, XBase, Excel, ADO).

Тексты всех модулей открыты, автором не предполагается в дальнейших версиях изменять это обстоятельство. Тем не менее, рекомендуется проверить отсутствие ВРЕДОНОСНОГО и ШПИОНСКОГО кода техническими специалистами, штатными или с высоким уровнем доверия, в связи с работой с учетными данными (паролями).

Выполнение запроса будет сопровождено предварительным выполнением запросов, описанных как внешние и являющиеся подчиненными этого запроса в дереве запросов консоли.
Результаты выполнения внешних запросов будут помещены в менеджер временных таблиц запроса с именами, соответствующими именам внешних запросов в дереве консоли, становясь, таким образом, доступными для запроса.

Внешний запрос может быть выполнен отдельно, либо в составе запроса; в любом случае подключение будет внесено в пул подключений консоли, доступный всем запросам консоли.

В случае, если запросы обращаются к копиям баз или совпадающим по GUID данным баз 1С, возможна работа с типами данных, отличных от примитивных; остальные запросы будут работать только с примитивными типами, и извлекать данные следует в строковых представлениях.
Отличные от примитивных данные передаются по GUID, поэтому вместо данных источника будут отображены совпадающие по GUID. Поэтому, например, документ копии с одинаковым GUID с измененной датой, будет отображен с датой документа текущей базы.

Параметры запроса 7.7 следует вносить вручную, перед выполнением запроса 7.7 в тексте запроса имена параметров заменяются их значениями, поэтому имена параметров не должны совпадать с лексемами текста запроса.
Значения параметров следует указывать по шаблону: например, стандартный шаблон кода счета ##:#:#:#, поэтому счет 205 должен выглядеть: "20.5    ".

v1.01b
Добавлена функция GetEX, позволяющая обращаться к отдельным запросам файла запроса из приложений 1С:

Функция GetEX(ФайлSel = Неопределено, СтрокаSel = Неопределено, Параметры = Неопределено) Экспорт

Функция возвращает выборку из результата запроса, в случае ошибки – строку с описанием ошибки.
Если не указано имя файла в первом параметре, будет открыто окно основной формы в режиме диалога выбора, остальные параметры будут игнорированы.
Приложения 1С могут получать данные запросов, в т.ч. "внешних", компонентным использованием консоли:

Перем ОбработкаEX Экспорт;
...

ИмяФайла = d:\Консоль запросов EX.epf";
Параметры = Новый Структура; // часть параметров запроса может быть переопределена без повторных сохранений файла запроса
Параметры.Вставить("ДатаС", '20160101');
Параметры.Вставить("ДатаПо", '20161231');

Если ОбработкаEX = Неопределенно Тогда
	ОбработкаEX = ВнешниеОбработки.Создать(ИмяФайла);
КонецЕсли;
Результат = ОбработкаEX.GetEX(d:\Мои запросы.sel", "Анализ_счета_7_7", Параметры);
Если ТипЗнч(Результат) = Тип("Строка") Тогда
	Сообщить(Результат); // сообщение об ошибке извлечения данных
	Возврат;
КонецЕсли;
...

ОбработкаEX.Закрыть(); // Очистка пула подключений

Разработчик может хранить в двоичных данных как саму консоль, так и файл запросов.

Для пользователя модифицирована универсальная обработка ВыгрузкаЗагрузкаДанныхXML.epf.

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

Обращаю Ваше внимание: вопрос обеспечения безопасности данных аутентификации открыт.
Если при определении типа запроса не указывать данные аутентификации, пароли в файлах *.sel не будут сохранены, предусматривая необходимость первичной аутентификации.
Предлагаю защищать паролями тексты модулей как внешней обработки, так и приведенных примеров, ограничивать права записи двоичных данных, хранящих файл консоли, для предотвращения подмены файла консоли.

v1.02b
Исправления в интерфейсе.

v1.03b
Добавлены новые типы внешних данных: Excel и ADO.
Excel
Решена задача об извлечении данных, не содержащих структуры, промежуточных итогов по группировкам.
Определена методика автоопределения многострочного заголовка таблицы, результаты автоопределения могут быть пересмотрены и сохранены.
ADO
При трудностях определения типа значения параметра DataTypeEnum предлагаю использовать следующее: измените текст запроса, исключив из инструкции SQL параметры (возможно, добавив фразу TOP 1) и выполните запрос. Как правило, параметры представляют собой ограничения на извлечение полей отдельных таблиц.
Выполнив запрос, провайдер предоставит коллекцию Fields, содержащую тип DataTypeEnum каждого поля.
Эту коллекцию можно отобразить: Тип запроса –> Структура. Используя эти данные, присвойте параметру соответствующий тип.
К области применения
Крылья для 1С 7.7

Представленный инструмент позволяет работать с данными 1С 7.7 средствами запросов SQL.
Обращаясь из 7.7 к 8.x, можно выполнять полноценные запросы SQL, и наоборот, из 8.x можно работать с данными 7.7, используя интерфейсные преимущества.
Сравнение таблиц.
При использовании инструкции ПОЛНОЕ СОЕДИНЕНИЕ (OUTER JOIN) можно сравнить остатки/обороты/результаты выполнения запроса к рабочей БД и копии БД, для РИБ сравнивать можно результат, сохраненный в Excel.
Средствами Excel можно сравнить две таблицы несколькими способами.
Сравнить таблицы Excel представленным инструментом имеет, в сущности, технологическое преимущество: 1С поддерживает OUTER JOIN, и пользовательское преимущество: чтобы сравнить что-нибудь, пользователю достаточно поменять в параметрах даты и, может быть, обновить файл Excel.
В прилагаемом файле запросов приводятся примеры запросов сравнения остатков и оборотов, в описаниях внешних типов источников установите Ваши настройки. Требуется следить за тем, чтобы параметры запросов совпадали, иначе можно столкнуться с ситуацией, когда сравниваются остатки различных баз на различные периоды.
Установив период, в котором остатки совпадают, можно приступать к сравнению оборотов с точностью до регистратора. Путем эксперимента обнаружено, что сравнение представлений в инструкции ПО (ON) не вызывает ошибки сравнения полей неограниченных типов, иначе сравнивать следует результаты запросов, выгруженных в Excel. Здесь также есть подводные камни: это ограничение количества строк. Столкнувшись с этим ограничением, выбирайте данные с меньшим диапазоном в случае оборотов по регистраторам, или с отборами по счетам/субконто/измерениям.

Привожу схематично решение автоматизации задачи периодического сравнения и дальнейшей синхронизации данных в базах с использованием Конвертации данных как пример использования GetEX:

Процедура Выгрузка() Экспорт
	//...
	ВыгрузитьМакет("Перенос_данных",				  Каталог + "Перенос данных.sel");
	ВыгрузитьМакет("V8Exchan82",					  Каталог + "V8Exchan82.epf");
	ВыгрузитьМакет("ПравилаОбмена_УТП",				  Каталог + "ПравилаОбмена.xml");
	ВыгрузитьМакет("КонсольЗапросовEX",				  Каталог + "Консоль запросов EX.epf");

	Обработка = Каталог + "V8Exchan82.epf";
	Обработка = ВнешниеОбработки.Создать(Обработка);
	Обработка.ИмяФайлаПравилОбмена					= Каталог + "ПравилаОбмена.xml";
	Обработка.ИмяФайлаОбмена						= Каталог + "ФайлДанных.xml";
	Обработка.ЗагрузитьПравилаОбмена();
	Обработка.ИмяФайлаПротоколаОбмена				= Каталог + "Протокол выгрузки.txt";
	Обработка.ДописыватьДанныеВПротоколОбмена		= Истина;
	Обработка.ДатаНачала							= ДатаНач;
	Обработка.ДатаОкончания							= ДатаКон;
	Обработка.ИспользоватьОтборПоДатеДляВсехОбъектов= Истина;
	УстановитьТаблицыПравилВыгрузки(Обработка.ТаблицаПравилВыгрузки, Обработка.ДатаНачала, Обработка.ДатаОкончания);
	Обработка.ВыполнитьВыгрузку();
	//...
КонецПроцедуры

Процедура Загрузка() Экспорт
	//...
	ВыгрузитьМакет("V8Exchan82",						  Каталог + "V8Exchan82.epf");
	Обработка = Каталог + "V8Exchan82.epf";
	Обработка = ВнешниеОбработки.Создать(Обработка);
	Обработка.ИмяФайлаОбмена							= Каталог + "ФайлДанных.xml";
	Обработка.ИмяФайлаПротоколаОбмена					= Каталог + "Протокол загрузки.txt";
	Обработка.ДописыватьДанныеВПротоколОбмена			= Истина;
	Обработка.РежимОбмена								= "Загрузка";
	Обработка.ЗагружатьДанныеВРежимеОбмена				= Истина;
	Обработка.ВыполнитьЗагрузку();
	//...	
КонецПроцедуры

Процедура ВыгрузитьМакет(ИмяМакета, ИмяФайла)
	ПолучитьМакет(ИмяМакета).Записать(ИмяФайла); // см. https://forum.infostart.ru/forum15/topic228623/
КонецПроцедуры

Процедура УстановитьТаблицыПравилВыгрузки(ТаблицаПравилВыгрузки, ДатаНачала, ДатаОкончания)
	Для Каждого Строка Из ТаблицаПравилВыгрузки.Строки Цикл
		Если Не Строка.Имя = "Документы" Тогда
			Строка.Включить = 0;
		КонецЕсли;
		Для Каждого Подстрока Из Строка.Строки Цикл
			Построитель = Новый ПостроительОтчета;
			Если Подстрока.Имя = "ОтчетОРозничныхПродажах" Тогда
				Построитель.Текст = "ВЫБРАТЬ Разрешенные _.* ИЗ Документ.ОтчетОРозничныхПродажах КАК _ {ГДЕ _.Ссылка.* КАК Документ_ОтчетОРозничныхПродажах}";
				
				ЭлементОтбора = Построитель.Отбор.Добавить("Документ_ОтчетОРозничныхПродажах.Проведен");
				ЭлементОтбора.Использование = Истина;
				ЭлементОтбора.ВидСравнения = ВидСравнения.Равно;
				ЭлементОтбора.Значение = Истина;
				
				ЭлементОтбора = Построитель.Отбор.Добавить("Документ_ОтчетОРозничныхПродажах.Номер");
				ЭлементОтбора.Использование = Истина;
				ЭлементОтбора.ВидСравнения = ВидСравнения.ВСписке;
				Список = Новый СписокЗначений;
				Список.ЗагрузитьЗначения(ПолучитьМассивДокументов("Документы_ОРП", ДатаНачала, ДатаОкончания));
				ЭлементОтбора.Значение = Список;
			Иначе
				Подстрока.Включить = 0;
				Продолжить;
			КонецЕсли;
			Подстрока.НастройкиПостроителя = Построитель.ПолучитьНастройки();
			Подстрока.ИспользоватьОтбор = Истина;
		КонецЦикла;
	КонецЦикла;
КонецПроцедуры

Функция ПолучитьМассивДокументов(ТипДокументов, ДатаНач, ДатаКон)
	
	ИмяФайлаEX = Каталог + "Консоль запросов EX.epf";
	Параметры = Новый Структура; // часть параметров запроса может быть переопределена без повторных сохранений файла запроса
	Параметры.Вставить("ДатаНач", ДатаНач);
	Параметры.Вставить("ДатаКон", ДатаКон);

	Если ОбработкаEX = Неопределено Тогда
		ОбработкаEX = ВнешниеОбработки.Создать(ИмяФайлаEX);
	КонецЕсли;
	Результат = ОбработкаEX.GetEX(Каталог + "Перенос данных.sel", ТипДокументов, Параметры);
	Если ТипЗнч(Результат) = Тип("Строка") Тогда
		Возврат Новый Массив; // ошибка извлечения данных
	Иначе
		Возврат Результат.ВыгрузитьКолонку("Р_Номер")
	КонецЕсли;
	ОбработкаEX.Закрыть(); // Очистка пула подключений
КонецФункции

v1.04
Для внешних источников обработка формировала таблицу значений и помещала ее во временную таблицу (ВТ) запроса, при выводе запрос извлекал ВТ; при этом для запросов, содержащих в качестве подчиненных строки дерева запросов, становились доступными ВТ, сформированные на уровне подчиненных строк.
Данные различных источников, в т.ч. различных БД/серверов, оказывались доступными для работы на уровне одного запроса (иначе говоря, в менеджере ВТ оказывались все ВТ запросов подчиненных строк TreeTempTablesManager). Для запроса, результат которого выводился, данные извлекались из ВТ: происходила промежуточная выгрузка/загрузка ВТ. Для больших объемов были отмечены нарушения сортировки: источник возвращал результат с правильной сортировкой, он помещался в ВТ и далее на стороне обработки извлекался без инструкций ORDER BY, с возможными нарушениями сортировки (TempDB, как другие, отрабатывает по https://habr.com/ru/company/otus/blog/504144/ (Почему SQL Server не гарантирует сортировку результатов без ORDER BY)).
Для полноты описания в обработке 1С с базой на SQL создадим заведомо упорядоченную таблицу значений (ТЗ), поместим ее в ВТ и извлечем - у меня проблемы сортировки начались на первом десятке тысяч строк:

	ВТ = Новый ТаблицаЗначений();	
	КвалификаторТипа = Новый КвалификаторыЧисла(10000, 0, ДопустимыйЗнак.Любой);
	ОписаниеТипов = Новый ОписаниеТипов("Число", КвалификаторТипа);
	ВТ.Колонки.Добавить("Номер", ОписаниеТипов);

	КвалификаторТипа = Новый КвалификаторыСтроки(10);
	ОписаниеТипов = Новый ОписаниеТипов("Строка", , КвалификаторТипа);
	ВТ.Колонки.Добавить("Имя", ОписаниеТипов);
	
	Для Счетчик = 1 По 10000 Цикл
		СтрокаВТ = ВТ.Добавить();
		СтрокаВТ.Номер = Счетчик;
		СтрокаВТ.Имя = " " + Формат(Счетчик, "ЧГ=") + "+";
	КонецЦикла;
	ОбъектЗапрос = Новый Запрос;
	Менеджер = Новый МенеджерВременныхТаблиц;
	ОбъектЗапрос.МенеджерВременныхТаблиц = Менеджер;
	ОбъектЗапрос.Текст = "ВЫБРАТЬ * ПОМЕСТИТЬ ТаблицаВТ ИЗ &ВТ КАК ТаблицаВТ";
	ОбъектЗапрос.УстановитьПараметр("ВТ", ВТ);
	ОбъектЗапрос.Выполнить();
	ОбъектЗапрос.Текст = "ВЫБРАТЬ * ИЗ ТаблицаВТ";
	//ОбъектЗапрос.Текст = "ВЫБРАТЬ * ИЗ ТаблицаВТ УПОРЯДОЧИТЬ ПО Номер";
	Результат = ОбъектЗапрос.Выполнить().Выгрузить();
	ЭлементыФормы.Результат.СоздатьКолонки();

Эта проблема решена заменой загрузки из ВТ на прямую загрузку результатом - ТЗ, полученной из внешнего источника, выполнение TreeTempTablesManager не изменилось.
Для ADODB (в частности, для прямых запросов 1С++) техника TreeTempTablesManager может быть излишней и заменена использованием ВТ SQL:

SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tmpAccs') IS NOT NULL BEGIN DROP TABLE #tmpAccs; END

SELECT *
INTO #tmpAccs
FROM _1SACCS AS БухСчета;

SELECT *
FROM #tmpAccs

Основным преимуществом этой консоли запросов вижу в технике TreeTempTablesManager - это возможность сопоставлять данные различных БД на этапе разработки запроса.

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

Наименование Файл Версия Размер
Консоль запросов EX.epf

.epf 109,96Kb
25
.epf v1.03b 109,96Kb 25 Скачать бесплатно
Сравнение данных.sel

.sel 11,16Kb
8
.sel 11,16Kb 8 Скачать бесплатно

Специальные предложения

Оставьте свое сообщение

См. также

Экспорт запроса из отладки в консоль запросов

Консоль запросов v8 Платформа 1C v8.2 1cv8.cf Россия Бесплатно (free)

Есть множество вариантов, как выгрузить запрос в файл в момент отладки, для дальнейшей быстрой загрузки этого файла в консоль запросов (включая временные таблицы). Я просто опишу код, который можно добавить в консоль запросов управляемого приложения, скачанную с сайта ИТС для 8.2 и 8.3. В самом коде/заголовках процедур все описание. Тестировал на платформе 1С:Предприятие 8.3 (8.3.18.1334).

сегодня в 07:00    263    5    prohorp    3    

Консоль HTTP запросов

Консоль запросов v8 v8::УФ 1cv8.cf Бесплатно (free)

Консоль HTTP запросов для управляемых форм.

05.10.2020    5930    385    zarankony    4    

Универсальные инструменты 1С

Универсальные обработки Прочие инструменты разработчика Консоль запросов v8 v8::УФ 1cv8.cf Бесплатно (free)

Свободно распространяемый набор универсальных обработок и отчетов в виде расширения для разработки и поддержки, которое работает во ВСЕХ видах клиентских приложений и во всех операционных системах, которые поддерживает платформа 1С:Предприятие, кроме мобильных. Консоль запросов - консоль отчетов - консоль кода - редактор объектов базы данных - удаление помеченных объектов - поиск и удаление дублей - редактор констант - консоль заданий - групповая обработка справочников и документов - динамический список - поиск ссылок на объект - регистрация изменений для обмена данными - структура хранения базы - консоль HTTP запросов-консоль вебсервисов- консоль сравнения данных- информация о лицензиях- загрузка из табличного документа-файловый менеджер-все функции- навигатор по конфигурации-конструктор регулярных выражений-Выгрузка загрузка XML с фильтрами

21.01.2020    52721    4521    cprit    173    

Полезные вкусняшки: Консоль запросов от GROOVY

Консоль запросов v8 1cv8.cf Россия Бесплатно (free)

Практически типовая 1Совская консоль слегка адаптированная для повседневных нужд. Поддерживаются пакетные запросы и просмотр временных таблиц.

19.09.2007    30101    2667    GROOVY    17