При интеграции 1С со сторонними базами данных Microsoft SQL Server нередко возникает задача пакетного чтения информации из однотипных таблиц. Например, когда для каждого узла учета, насосной станции (ВНС) или филиала создается отдельная таблица с одинаковым набором колонок. Разберем, как подключиться к СУБД через COM-технологию ADODB, динамически получить список нужных таблиц по шаблону имени и прочитать из них данные.
Рассмотрим структуру типовой задачи. В базе данных MSSQL существует множество таблиц, имена которых содержат определенный паттерн (например, VNS: dbo.[VNS.Krasina], dbo.[VNS.Chkalova]). Нам необходимо:
ADODB.Connection.ТаблицаЗначений платформы 1С.Решить эту задачу можно двумя способами: последовательным обходом каждой таблицы в цикле или формированием единого пакета запросов через UNION ALL. Разберем по шагам оба варианта.
Для выборки метаданных в MSSQL можно обратиться к стандартному представлению INFORMATION_SCHEMA.TABLES или системной таблице sys.sysobjects (или sys.objects). Запрос с фильтром TABLE_NAME LIKE '%VNS%' возвращает только интересующие нас таблицы.
Посмотрим на полный пример программного кода на стороне 1С:
Процедура ВыгрузитьДанныеИзТаблицПоШаблону() Экспорт
// 1. Инициализация и открытие соединения
Соединение = Новый COMОбъект("ADODB.Connection");
СтрокаСоединения = "Provider=SQLOLEDB;Data Source=ИмяСервера;Initial Catalog=ИмяБазы;User ID=Логин;Password=Пароль;";
Попытка
Соединение.Open(СокрЛП(СтрокаСоединения));
Исключение
Сообщить("Ошибка подключения к MSSQL: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// 2. Получение списка таблиц по маске имени
ТекстЗапросаМетаданных =
"SELECT TABLE_SCHEMA, TABLE_NAME
|FROM INFORMATION_SCHEMA.TABLES
|WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE '%VNS%'";
RsТаблицы = Новый COMОбъект("ADODB.Recordset");
RsТаблицы.Open(ТекстЗапросаМетаданных, Соединение);
СписокТаблиц = Новый Массив;
Пока RsТаблицы.EOF = 0 Цикл
Схема = RsТаблицы.Fields("TABLE_SCHEMA").Value;
ИмяТаблицы = RsТаблицы.Fields("TABLE_NAME").Value;
// Формируем безопасное полное имя с экранированием квадратными скобками
ПолноеИмя = "[" + Схема + "].[" + ИмяТаблицы + "]";
СписокТаблиц.Добавить(ПолноеИмя);
RsТаблицы.MoveNext();
КонецЦикла;
RsТаблицы.Close();
RsТаблицы = Неопределено;
// 3. Подготовка результирующей таблицы 1С
ТаблицаЗнач = Новый ТаблицаЗначений;
ТаблицаЗнач.Колонки.Добавить("ВНС");
ТаблицаЗнач.Колонки.Добавить("Водомер");
НачалоПериода = Формат(НачалоДня(ТекущаяДата()), "ДФ=""гггг-ММ-дд HH:mm:ss""");
КонецПериода = Формат(КонецДня(ТекущаяДата()), "ДФ=""гггг-ММ-дд HH:mm:ss""");
// 4. Последовательный опрос каждой таблицы
Для Каждого ПолноеИмяТаблицы Из СписокТаблиц Цикл
ТекстВыборки =
"SELECT Rashod_Sum, Rashod_Sum1, control
|FROM " + ПолноеИмяТаблицы + "
|WHERE calldate BETWEEN '" + НачалоПериода + "' AND '" + КонецПериода + "'";
RsДанные = Новый COMОбъект("ADODB.Recordset");
RsДанные.Open(ТекстВыборки, Соединение);
Пока RsДанные.EOF = 0 Цикл
ЗначениеControl = RsДанные.Fields("control").Value;
Если ЗначениеControl <> Null И ЗначениеControl > 0 Тогда
Стр = ТаблицаЗнач.Добавить();
Стр.ВНС = ПолноеИмяТаблицы;
Водомер = 0;
ЗначениеRashod = RsДанные.Fields("Rashod_Sum").Value;
Если ЗначениеRashod <> Null Тогда
Водомер = Водомер + ЗначениеRashod;
КонецЕсли;
ЗначениеRashod1 = RsДанные.Fields("Rashod_Sum1").Value;
Если ЗначениеRashod1 <> Null Тогда
Водомер = Водомер + ЗначениеRashod1;
КонецЕсли;
Стр.Водомер = Водомер;
КонецЕсли;
RsДанные.MoveNext();
КонецЦикла;
RsДанные.Close();
RsДанные = Неопределено;
КонецЦикла;
// 5. Корректное закрытие соединения
Соединение.Close();
Соединение = Неопределено;
КонецПроцедуры
Если таблиц много (десятки или сотни), последовательное выполнение десятков запросов создает существенные накладные расходы на сетевое взаимодействие между 1С и СУБД. Проанализируем более производительный вариант: на стороне 1С динамически собирается один общий SQL-запрос с оператором UNION ALL.
Посмотрим, как выглядит оптимизированный алгоритм:
Процедура ВыгрузитьДанныеОптимизированно() Экспорт
Соединение = Новый COMОбъект("ADODB.Connection");
СтрокаСоединения = "Provider=SQLOLEDB;Data Source=ИмяСервера;Initial Catalog=ИмяБазы;User ID=Логин;Password=Пароль;";
Попытка
Соединение.Open(СокрЛП(СтрокаСоединения));
Исключение
Сообщить("Ошибка подключения: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// Получаем список таблиц
RsТаблицы = Новый COMОбъект("ADODB.Recordset");
RsТаблицы.Open("SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE '%VNS%'", Соединение);
МассивЗапросов = Новый Массив;
НачалоПериода = Формат(НачалоДня(ТекущаяДата()), "ДФ=""гггг-ММ-дд HH:mm:ss""");
КонецПериода = Формат(КонецДня(ТекущаяДата()), "ДФ=""гггг-ММ-дд HH:mm:ss""");
Пока RsТаблицы.EOF = 0 Цикл
Схема = RsТаблицы.Fields("TABLE_SCHEMA").Value;
ИмяТаблицы = RsТаблицы.Fields("TABLE_NAME").Value;
ТекстВетки =
"SELECT '" + ИмяТаблицы + "' AS VNS_Name, Rashod_Sum, Rashod_Sum1, control " +
"FROM [" + Схема + "].[" + ИмяТаблицы + "] " +
"WHERE calldate BETWEEN '" + НачалоПериода + "' AND '" + КонецПериода + "'";
МассивЗапросов.Добавить(ТекстВетки);
RsТаблицы.MoveNext();
КонецЦикла;
RsТаблицы.Close();
RsТаблицы = Неопределено;
Если МассивЗапросов.Количество() = 0 Тогда
Соединение.Close();
Возврат;
КонецЕсли;
// Объединяем все подзапросы через UNION ALL
ОбщийЗапрос = СтрСоединить(МассивЗапросов, " UNION ALL ");
RsДанные = Новый COMОбъект("ADODB.Recordset");
RsДанные.Open(ОбщийЗапрос, Соединение);
ТаблицаЗнач = Новый ТаблицаЗначений;
ТаблицаЗнач.Колонки.Добавить("ВНС");
ТаблицаЗнач.Колонки.Добавить("Водомер");
Пока RsДанные.EOF = 0 Цикл
ЗначениеControl = RsДанные.Fields("control").Value;
Если ЗначениеControl <> Null И ЗначениеControl > 0 Тогда
Стр = ТаблицаЗнач.Добавить();
Стр.ВНС = RsДанные.Fields("VNS_Name").Value;
Водомер = 0;
ЗначениеRashod = RsДанные.Fields("Rashod_Sum").Value;
Если ЗначениеRashod <> Null Тогда
Водомер = Водомер + ЗначениеRashod;
КонецЕсли;
ЗначениеRashod1 = RsДанные.Fields("Rashod_Sum1").Value;
Если ЗначениеRashod1 <> Null Тогда
Водомер = Водомер + ЗначениеRashod1;
КонецЕсли;
Стр.Водомер = Водомер;
КонецЕсли;
RsДанные.MoveNext();
КонецЦикла;
RsДанные.Close();
RsДанные = Неопределено;
Соединение.Close();
Соединение = Неопределено;
КонецПроцедуры
NULL: При получении пустых значений из базы данных SQL в 1С они приходят как тип Null, а не Неопределено. Кроме того, проверять нужно значение поля, обращаясь к свойству .Value: Rs.Fields("Поле").Value <> Null. Сравнение самого объекта поля с Неопределено приведет к логической ошибке.dbo.VNS.Krasina), MSSQL без квадратных скобок посчитает точку разделителем схемы и таблицы. Обязательно экранируйте имена: [dbo].[VNS.Krasina].Recordset.Close() и Connection.Close(), а затем присваивайте переменным значение Неопределено. Это предотвращает утечки памяти и блокировки соединений на стороне СУБД.Новый COMОбъект("ADODB.Connection") завершится аварийно. Для кроссплатформенной работы рекомендуется использовать механизм Внешние источники данных платформы 1С:Предприятие через драйверы ODBC.