При работе с внешними базами данных из 1С через механизм ActiveX Data Objects (ADO) часто возникает необходимость отфильтровать выборку по списку значений. Например, выбрать данные по массиву артикулов, кодов или идентификаторов. Первая логичная идея, которая приходит разработчику — передать массив 1С в качестве параметра SQL-запроса с оператором IN. Однако при попытке реализовать это напрямую мы неизбежно сталкиваемся со специфическими ошибками ADODB и систем управления базами данных (СУБД).
В этой статье мы подробно разберем, почему стандартный подход не работает, как правильно настроить объект ADODB.Parameter для передачи массива (если это поддерживается внешним драйвером) и рассмотрим четыре надежных альтернативных решения этой задачи.
Давайте проанализируем типичный код, который часто пытаются использовать разработчики:
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open(СтрокаСоединения);
Cmd = Новый COMОбъект("ADODB.Command");
Cmd.ActiveConnection = Соединение;
Cmd.CommandText = "SELECT p.group_id, p.art FROM ns.products p WHERE p.art IN (@TMC)";
МТМЦ = Новый Массив;
МТМЦ.Добавить("ТМЦ001");
МТМЦ.Добавить("ТМЦ002");
Парам = Новый COMОбъект("ADODB.Parameter");
Парам.Name = "@TMC";
Парам.Type = "0x2000"; // Попытка указать массив
Парам.Size = МТМЦ.Количество();
Парам.Direction = 1;
Парам.Value = МТМЦ; // Здесь падает первая ошибка
Cmd.Parameters.Append(Парам);
При выполнении этого кода мы сразу сталкиваемся с целым рядом проблем:
Value объекта ADODB.Parameter не умеет работать со стандартным типом Массив платформы 1С. Для передачи массивов через COM-соединение необходимо использовать специальный объект-обертку — COMSafeArray."0x2000" приводит к ошибке инициализации типа. Флаг adArray (который равен 0x2000 в шестнадцатеричном представлении или 8192 в десятичном) не может использоваться самостоятельно. Он должен комбинироваться с базовым типом элементов массива с помощью побитового сложения. Например, для массива строк Юникод (adBSTR, код 8) итоговый тип будет равен 8192 + 8 = 8200.adArray в ADO, при выполнении метода Cmd.Execute() база данных (MS SQL Server, MySQL, Oracle) выдаст ошибку вроде "Неизвестное имя типа". Классический оператор SQL IN (@Параметр) ожидает перечисление литералов через запятую или подзапрос, но он не умеет на лету разбирать бинарную структуру переданного массива.Выяснив причины ошибок, рассмотрим рабочие способы реализации этой задачи.
---
Если количество элементов в вашем массиве относительно невелико (до нескольких сотен), самый простой путь — отказаться от передачи параметра и встроить список значений непосредственно в текст SQL-запроса. СУБД отлично понимает конструкцию вида IN ('Значение1', 'Значение2').
Разберем по шагам, как безопасно сформировать такой запрос на стороне 1С:
// Очищаем и подготавливаем строки, экранируя одиночные кавычки для защиты от SQL-инъекций
МассивСтрокДляЗапроса = Новый Массив;
Для Каждого Элемент Из МТМЦ Цикл
БезопасноеЗначение = СтрЗаменить(СокрЛП(Элемент), "'", "''");
МассивСтрокДляЗапроса.Добавить("'" + БезопасноеЗначение + "'");
КонецЦикла;
// Объединяем элементы массива в строку через запятую
СтрокаСписок = СтрСоединить(МассивСтрокДляЗапроса, ", ");
// Формируем текст запроса динамически
ТекстЗапроса = "SELECT p.group_id, p.art
|FROM ns.products p
|WHERE p.art IN (" + СтрокаСписок + ")";
Cmd.CommandText = ТекстЗапроса;
RecordSet = Cmd.Execute();
Преимущества метода: Работает в абсолютно любой СУБД, прост в реализации, не требует сложных преобразований типов.
Важное ограничение: Помните, что у многих СУБД есть жесткие лимиты на максимальное количество элементов в секции IN (например, в Oracle это 1000 элементов) и на максимальную длину строки SQL-запроса. Также этот метод потенциально уязвим к SQL-инъекциям, если входящие данные не проходят валидацию.
---
Если вы работаете с Microsoft SQL Server, лучшим решением для передачи больших массивов является использование формата XML. Мы передаем всю структуру данных как один длинный строковый параметр, а на стороне SQL Server быстро разбираем его во временную таблицу с помощью встроенных возможностей XQuery.
Рассмотрим, как выглядит этот процесс на стороне 1С:
// Шаг 1. Формируем XML-строку из элементов нашего массива
СтрокаXML = "<items>";
Для Каждого Элемент Из МТМЦ Цикл
СтрокаXML = СтрокаXML + "<i v=""" + СтрЗаменить(Элемент, """", """) + """/>";
КонецЦикла;
СтрокаXML = СтрокаXML + "</items>";
// Шаг 2. Создаем стандартный строковый параметр (adVarWChar = 202)
ПарамXML = Cmd.CreateParameter("@TMC_XML", 202, 1, СтрДлина(СтрокаXML), СтрокаXML);
Cmd.Parameters.Append(ПарамXML);
// Шаг 3. Пишем запрос, который на лету преобразует XML в таблицу
ТекстЗапроса = "DECLARE @xmlData XML = CAST(@TMC_XML AS XML);
|
|SELECT p.group_id, p.art
|FROM ns.products p
|WHERE p.art IN (
| SELECT x.v.value('@v', 'varchar(40)')
| FROM @xmlData.nodes('/items/i') AS x(v)
|)";
Cmd.CommandText = ТекстЗапроса;
RecordSet = Cmd.Execute();
Почему это удобно: Нам не нужно создавать никаких дополнительных функций или хранимых процедур в базе данных. Весь разбор XML происходит "на лету" прямо в теле SQL-запроса с помощью метода .nodes().
---
Современные версии СУБД (MS SQL Server 2016+, PostgreSQL 9.4+, MySQL 8.0+) поддерживают эффективную работу с форматом JSON. Это делает передачу массивов еще проще, ведь сериализация в JSON на стороне 1С выполняется встроенными механизмами платформы буквально в три строки.
Разберем пример реализации для MS SQL Server:
// Шаг 1. Сериализуем массив 1С в строку JSON
ЗаписьJSON = Новый ЗаписьJSON;
ЗаписьJSON.УстановитьСтроку();
ЗаписатьJSON(ЗаписьJSON, МТМЦ);
СтрокаJSON = ЗаписьJSON.Закрыть(); // Получим что-то вроде: ["ТМЦ001","ТМЦ002"]
// Шаг 2. Передаем JSON-строку как обычный строковый параметр
ПарамJSON = Cmd.CreateParameter("@TMC_JSON", 202, 1, СтрДлина(СтрокаJSON), СтрокаJSON);
Cmd.Parameters.Append(ПарамJSON);
// Шаг 3. Разбираем JSON на стороне SQL-сервера с помощью OPENJSON
ТекстЗапроса = "SELECT p.group_id, p.art
|FROM ns.products p
|WHERE p.art IN (
| SELECT value FROM OPENJSON(@TMC_JSON)
|)";
Cmd.CommandText = ТекстЗапроса;
RecordSet = Cmd.Execute();
Если ваша внешняя база данных работает под управлением PostgreSQL, то разбор параметра в запросе будет выглядеть следующим образом:
SELECT p.group_id, p.art
FROM ns.products p
WHERE p.art IN (
SELECT json_array_elements_text(@TMC_JSON::json)
)
---
Для СУБД, которые не поддерживают ни XML, ни JSON (или если вы работаете через устаревшие версии СУБД), самым надежным и производительным решением будет загрузка элементов массива во временную таблицу с ее последующим связыванием (JOIN) в основном запросе.
Алгоритм действий следующий:
Посмотрим на практическую реализацию этого сценария:
// Шаг 1. Создаем временную таблицу в рамках текущей сессии подключения
Cmd.CommandText = "CREATE TABLE #TempTMC (ArtId VARCHAR(40) PRIMARY KEY)";
Cmd.Execute();
// Шаг 2. Подготавливаем команду для быстрой вставки элементов
CmdInsert = Новый COMОбъект("ADODB.Command");
CmdInsert.ActiveConnection = Соединение;
CmdInsert.CommandText = "INSERT INTO #TempTMC (ArtId) VALUES (?)";
ПарамДобавления = CmdInsert.CreateParameter("ArtId", 202, 1, 40);
CmdInsert.Parameters.Append(ПарамДобавления);
// Заполняем временную таблицу в транзакции для максимального ускорения
Соединение.BeginTrans();
Попытка
Для Каждого Элемент Из МТМЦ Цикл
CmdInsert.Parameters("ArtId").Value = СокрЛП(Элемент);
CmdInsert.Execute();
КонецЦикла;
Соединение.CommitTrans();
Исключение
Соединение.RollbackTrans();
ВызватьИсключение "Не удалось заполнить временную таблицу: " + ОписаниеОшибки();
КонецПопытки;
// Шаг 3. Выполняем основной запрос с использованием связывания (INNER JOIN)
Cmd.CommandText = "SELECT p.group_id, p.art
|FROM ns.products p
|INNER JOIN #TempTMC t ON p.art = t.ArtId";
RecordSet = Cmd.Execute();
// Шаг 4. Очищаем за собой временные объекты
Cmd.CommandText = "DROP TABLE #TempTMC";
Cmd.Execute();
Преимущества работы через временные таблицы: Максимальное быстродействие на огромных объемах данных (тысячи и десятки тысяч элементов). Наличие первичного ключа на временной таблице гарантирует СУБД возможность построить оптимальный план выполнения запроса, в отличие от использования динамических списков внутри оператора IN.
---
Подводя итоги, дадим краткие рекомендации по выбору архитектурного решения: