При интеграции платформы 1С:Предприятие с внешними базами данных посредством механизма «Внешние источники данных» (ВИД) разработчики часто сталкиваются со специфическими ошибками СУБД. Особенно остро эта проблема проявляется при попытке построения отчетов с использованием Системы компоновки данных (СКД) по таблицам из MySQL или MariaDB. В простых списках данные могут отображаться и редактироваться корректно, однако при выполнении запроса СКД система выдает синтаксическую ошибку СУБД (например, SQLSTATE: 42000, код ошибки: 1064).
В этой статье мы подробно разберем, почему возникают сбои при обращении к полям со знаком подчеркивания (например, shop_id, r_user_id) и логическим типам данных, проанализируем причины такого поведения транслятора запросов 1С и рассмотрим несколько эффективных способов решения проблемы.
Давайте разберем по шагам, что происходит «под капотом» платформы 1С при выполнении запроса к внешнему источнику данных через СКД. Проблема кроется в несовместимости стандартов трансляции SQL-запросов 1С и особенностей СУБД MySQL/MariaDB.
Причина №1. Попытка неявного приведения типов к BOOLEAN. При формировании схемы компоновки данных СКД опрашивает метаданные таблиц. Если в таблице MySQL присутствуют колонки типов TINYINT(1) или BIT, драйвер ODBC и платформа 1С автоматически распознают их как логический тип (Булево). При генерации итогового SQL-запроса 1С пытается выполнить валидацию типов, добавляя в запрос конструкцию приведения типа CAST(поле AS BOOLEAN). Однако СУБД MySQL/MariaDB не поддерживает приведение типов к BOOLEAN через функцию CAST(). Это и вызывает ошибку синтаксиса:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BOOLEAN)
Причина №2. Некорректное экранирование полей со знаком подчеркивания. Символ подчеркивания «_» является служебным для транслятора 1С. Чтобы передать имя поля в СУБД в исходном виде, 1С оборачивает его в стандартные двойные кавычки (например, "shop_id"). По умолчанию в СУБД MySQL двойные кавычки служат для обозначения строковых литералов (обычного текста), а для идентификаторов таблиц и полей используются обратные апострофы (бэктэки). СУБД воспринимает имя поля как строку текста, из-за чего выполнение запроса падает с ошибкой.
Ниже рассмотрим пять надежных способов решения этих проблем.
Первое, что необходимо проверить — это настройки подключения к внешнему источнику данных непосредственно в режиме пользователя. Очень часто по умолчанию для параметра типа СУБД устанавливается значение «Прочее», что заставляет платформу использовать обобщенные и менее совместимые SQL-конструкции.
Если ошибка синтаксиса вызвана попыткой приведения числовых полей к логическому типу BOOLEAN, мы можем скорректировать поведение самого драйвера ODBC на сервере или рабочем компьютере.
Для этого в строке подключения (или в настройках системного DSN в администраторе источников данных ODBC) необходимо отключить интерпретацию однобайтовых целых чисел как логических значений. Добавьте в строку подключения следующий параметр:
tinyInt1isBit=false
Если вы используете графический интерфейс настройки ODBC-драйвера MySQL (ODBC Data Source Administrator):
После этого драйвер будет возвращать такие поля как обычные числа. В метаданных 1С тип поля изменится с Булево на Число, и генератор запросов СКД перестанет использовать ошибочную конструкцию CAST AS BOOLEAN.
Если у вас нет возможности перенастраивать ODBC-драйвер на всех клиентских машинах, мы можем переопределить типы данных непосредственно в свойствах внешнего источника в конфигураторе.
is_credits) и откройте список ее полей.Булево.Число (длина 1, точность 0) или Строка.Чтобы заставить MySQL корректно воспринимать двойные кавычки (в которые 1С оборачивает поля со знаками подчеркивания "shop_id") как имена колонок, а не как строки, необходимо изменить режим работы СУБД.
Для этого включите режим ANSI_QUOTES в настройках вашего SQL-сервера. Вы можете сделать это временно для тестирования, выполнив SQL-запрос в консоли СУБД:
SET GLOBAL sql_mode = 'ANSI_QUOTES';
Для постоянного применения этой настройки откройте конфигурационный файл сервера MySQL/MariaDB (обычно это my.cnf или my.ini) и добавьте значение в директиву sql-mode:
[mysqld]
sql-mode="ANSI_QUOTES"
После перезапуска СУБД MySQL начнет распознавать стандартное для ANSI SQL экранирование идентификаторов двойными кавычками, и запросы СКД 1С к полям с подчеркиваниями начнут выполняться без ошибок.
Если структуру оригинальных таблиц внешней базы данных менять категорически запрещено, а изменять настройки всего SQL-сервера нет возможности, наиболее надежным и профессиональным решением будет создание «прослойки» в виде SQL-представления (View).
Мы можем создать представление на стороне MySQL, в котором переименуем все поля со знаками подчеркивания в монолитные имена (или CamelCase), а также принудительно преобразуем проблемные типы данных:
CREATE VIEW v_is_credits AS
SELECT
f,
auto,
payment,
first_payment AS firstpayment,
bank_id AS bankid,
a_date AS adate,
comment,
shop_id AS shopid,
r_user_id AS ruserid,
CAST(active_flag AS SIGNED) AS active_flag
FROM is_credits;
После создания представления выполните следующие шаги в 1С:
v_is_credits.shopid) не содержат знаков подчеркивания и не будут вызывать ошибок трансляции.Использование представлений гарантирует стабильную работу СКД при любых обновлениях платформы 1С и исключает возникновение непредвиденных ошибок синтаксиса СУБД.