При разработке прямых запросов к базе данных 1С:Предприятия 7.7 с помощью внешней компоненты 1С++ разработчики часто используют механизм метаимен. Одной из самых востребованных и одновременно сложных виртуальных таблиц является $РегистрОстаткиОбороты. Однако при попытке рассчитать остатки и обороты по дням с конструкцией ДвиженияИГраницыПериода можно столкнуться с неприятной ошибкой парсера метаимен:
Meta name parser error: это метаимя разрешить невозможно "$РегистрОстаткиОбороты.ВзаиморасчетыПокупателей"State 42000, native 137, message [Microsoft][ODBC SQL Server Driver][SQL Server]Необходимо объявить скалярную переменную "@_CP_2".
Кроме того, при запуске на старых версиях СУБД (например, MS SQL Server 7.0) может проявиться сопутствующая ошибка:
Specified column precision 38 is greater than the maximum precision of 28.
Разберем детально, почему возникают эти сбои и какими способами можно построить стабильный и быстрый прямой запрос.
Проанализируем механизм обработки текста запроса компонентой 1С++. По умолчанию класс ODBCRecordset отправляет запросы на MS SQL Server через системную процедуру sp_executesql (RPC-режим). Чтобы оптимизировать выполнение и повторно использовать планы запросов, внутренний парсер пытается параметризовать константы, литералы и даты, автоматически подставляя переменные вида @_CP_1, @_CP_2 (Constant Parameters).
Когда мы задействуем виртуальную таблицу $РегистрОстаткиОбороты с параметром периодичности ДЕНЬ,ДвиженияИГраницыПериода, парсер разворачивает макрос в огромный SQL-код, состоящий из нескольких подзапросов, объединений (UNION ALL) таблиц итогов (_1SRG) и движений (_1SREG). Если внутри параметров макроса присутствуют сложные условия фильтрации, например подзапросы вида (Контрагент IN (SELECT val FROM #Klienti)), препроцессор 1С++ допускает ошибку: константы заменяются на @_CP_x внутри тела сгенерированного подзапроса, но в результирующий заголовок параметров вызова sp_executesql они не попадают. В итоге Microsoft SQL Server получает код с переменной, которая не была объявлена, и аварийно завершает выполнение.
Рассмотрим первый и самый надежный способ решения проблемы. Нам потребуется отказаться от RPC-параметризации запроса на уровне объекта ODBCRecordset и вынести фильтры из внутренних параметров виртуальной таблицы в явные соединения INNER JOIN.
Разберем по шагам, что нужно изменить:
РежимRPC = 0. Это заставит компоненту отправлять SQL-текст через прямой вызов SQLExecDirect без автоматического создания переменных @_CP_x.IN (SELECT ...) из параметров виртуальной таблицы.УложитьСписокОбъектов) во внешнем запросе либо передать их в параметры соединений метаимени.ORDER BY безопаснее указывать порядковые номера полей вместо псевдонимов.Посмотрим на пример исправленного и работающего кода:
ТекстЗапроса = "
|SELECT
| Рег.Фирма AS [Фирма $Справочник.Фирмы]
| , Рег.Период AS Период
| , Рег.Контрагент AS [Контрагент $Справочник.Контрагенты]
| , Sum(Рег.ДолгНачальныйОстаток) AS НачТов
| , Sum(Рег.ДолгПриход) AS ПрихТов
| , Sum(Рег.ДолгРасход) AS РасхТов
| , Sum(Рег.ДолгКонечныйОстаток) AS КонТов
|FROM $РегистрОстаткиОбороты.ВзаиморасчетыПокупателей(:ДатаНачала, :ДатаКонца~, ДЕНЬ, ДвиженияИГраницыПериода, , , (Фирма, Контрагент), Долг) AS Рег
|INNER JOIN #Klienti ON #Klienti.Val = Рег.Контрагент
|INNER JOIN #Firma ON #Firma.Val = Рег.Фирма
|GROUP BY Рег.Фирма, Рег.Период, Рег.Контрагент
|ORDER BY 1, 2, 3
|";
RS = СоздатьОбъект("ODBCRecordset");
RS.РежимRPC = 0;
RS.SetQueryTimeout(0);
RS.УложитьСписокОбъектов(спсКлиенты, "#Klienti", "Контрагенты");
RS.УложитьСписокОбъектов(спсФирмы, "#Firma", "Фирмы");
RS.SetTextParam("ДатаНачала", Дата1);
RS.SetTextParam("ДатаКонца", Дата2);
табЗапрос = RS.ВыполнитьИнструкцию(ТекстЗапроса);
RS.Закрыть();
Рассмотрим альтернативную возможность синтаксиса виртуальной таблицы. В 1С++ пятый параметр макроса $РегистрОстаткиОбороты предназначен для передачи условий соединения таблиц (JOIN). Если передать соединение с временной таблицей непосредственно в тело виртуальной таблицы, отбор данных произойдет до построения движений и расчета промежуточных итогов, что существенно ускорит выполнение.
Посмотрим на фрагмент описания метаимени с использованием 5-го параметра:
|FROM $РегистрОстаткиОбороты.ВзаиморасчетыПокупателей(
| :ДатаНачала,
| :ДатаКонца~,
| ДЕНЬ,
| ДвиженияИГраницыПериода,
| INNER JOIN #Klienti ON #Klienti.Val = Контрагент,
| ,
| Контрагент,
| Долг
|) AS Рег
Такой подход исключает появление лишних условий во внутреннем фильтре и обходит проблему с подстановкой необъявленных параметров.
Проанализируем вторую проблему, которая возникает при запуске подобных запросов на старых серверах MS SQL Server 7.0. По умолчанию в этой версии максимальная разрядность числовых типов DECIMAL и NUMERIC составляет 28 знаков. Генератор виртуальных таблиц 1С++ при агрегации полей ресурсов и расчете начальных и конечных остатков выполняет расчеты с расширенной точностью до 38 знаков. Когда SQL Server встречает такую точность, он выдает ошибку с кодом 2750.
Разберем пути устранения этой ошибки:
-p (или /p). Данный флаг переводит MS SQL Server 7.0 в режим поддержки 38-значной точности (Precision 38), снимая ограничение в 28 знаков. Параметр задается в строке запуска исполняемого файла sqlservr.exe через свойства службы или Enterprise Manager с последующим перезапуском инстанса.$Регистр.ИмяРегистра и $РегистрИтоги.ИмяРегистра с явным приведением суммируемых полей через конструкцию CAST(Сумма AS NUMERIC(20, 2)), которая гарантированно укладывается в базовый лимит точности.