При организации обмена данными между платформой 1С:Предприятие и внешними базами данных на СУБД MS SQL (например, через прямое подключение по технологии ADO) разработчики часто сталкиваются со специфическими ошибками приведения типов. Одна из наиболее распространенных и неочевидных проблем — ошибка синтаксиса или сбой преобразования строкового значения в целочисленное.
В этой статье мы подробно разберем, почему возникает системное сообщение "Syntax error converting the varchar value... to a column of data type int" (или его русскоязычный аналог "Ошибка преобразования значения varchar в тип данных int"), проанализируем внутренние механизмы работы платформы 1С и СУБД MS SQL, а также рассмотрим несколько надежных практических способов решения этой проблемы.
Давайте разберем по шагам природу возникновения этой ошибки и выясним, как правильно передавать числовые параметры в SQL-запросы без риска получить аварийное завершение работы программы.
Рассмотрим классическую ситуацию. У нас есть числовое поле в таблице MS SQL с именем TECHYear, имеющее тип данных int (целое число). Нам необходимо выполнить запрос к этой таблице из кода 1С, отфильтровав данные по конкретному году. Разработчик пытается динамически собрать строку запроса:
AND TECHYear = '" + НаборЗаписей.Fields("TECHYear").Value + "'";
На первый взгляд, код выглядит логично. Но при его выполнении система выдает ошибку:
Ошибка преобразования значения varchar "2 017" в тип данных int
Давайте проанализируем ситуацию. Откуда в строковом представлении года взялcя символ пробела между двойкой и нулем? Ведь в базе данных СУБД хранится чистое число 2017.
Все дело в особенностях работы платформы 1С:Предприятие с региональными установками. Когда мы выполняем неявную конвертацию числа в строку (а в коде выше это происходит автоматически в момент сложения строки с числовым значением свойства Value через оператор +), 1С применяет к числу стандартные правила форматирования текущей локали (в данном случае — русской). По умолчанию для разделения триад чисел используется пробел.
При этом платформа 1С вставляет не обычный пробел (код символа 32), а специальный неразрывный пробел (код символа 160, или 0xA0 в шестнадцатеричной системе кодирования). Сделано это для того, чтобы при выводе чисел в текстовые поля или печатные формы числа не разрывались на разные строки.
Когда сформированная строка запроса отправляется в MS SQL Server, СУБД пытается выполнить операцию неявного приведения типов (Implicit Conversion). SQL Server умеет автоматически преобразовывать строки вида '2017' в целые числа. Он также умеет отсекать обычные пробелы по краям строки. Но встретив неразрывный пробел (символ с кодом 160) внутри строки, стандартный парсер СУБД признает строку некорректной для преобразования в число, прерывает выполнение запроса и выбрасывает ошибку конвертации.
Самый простой и быстрый способ победить неразрывный пробел при сборке динамического SQL-запроса — использовать встроенную функцию 1С Формат() с явным указанием параметров форматирования чисел.
Параметр ЧГ=0 (Чтение Групп = 0) отключает группировку разрядов числа, тем самым полностью исключая появление неразрывного пробела из результирующей строки. Давайте посмотрим на практический пример исправления строки запроса:
// Неправильно:
ЗапросSQL = "SELECT * FROM MyTable WHERE TECHYear >= '" + Формат(ДатаНачалаРабочегоГода, "ДФ=гггг") + "'";
// В данном случае вернется дата, отформатированная как строка, но нам нужен именно числовой год
// Правильно:
ГодЧислом = Год(ДатаНачалаРабочегоГода);
ЗапросSQL = "SELECT * FROM MyTable WHERE TECHYear >= " + Формат(ГодЧислом, "ЧГ=0");
Обратите внимание на важную деталь: если поле TECHYear в базе данных имеет тип int, нам больше не нужно оборачивать передаваемое значение в одиночные кавычки '...' на стороне SQL. Передавая число напрямую без кавычек (как 2017 вместо '2017'), мы избавляем оптимизатор СУБД от необходимости делать лишнюю работу по неявному приведению типов данных.
Хотя динамическая сборка SQL-запросов через склеивание строк и проста в реализации, этот подход признан небезопасным и неэффективным в профессиональной разработке. Склеивание строк порождает уязвимости к SQL-инъекциям и заставляет СУБД каждый раз заново компилировать план выполнения запроса.
Наиболее правильным, надежным и современным решением является использование параметризованных запросов через объект ADODB.Command. При использовании параметров драйвер СУБД передает данные в бинарном виде, минуя стадию текстового преобразования в 1С. В этом случае ошибки форматирования чисел и дат физически исключены.
Рассмотрим подробный пример реализации параметризованного запроса на языке 1С:
// Инициализируем ADO-соединение и команду
Connection = Новый COMОбъект("ADODB.Connection");
Connection.Open("Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DBName;User ID=sa;Password=pass;");
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;
// Задаем текст запроса с использованием знаков вопроса (?) в качестве параметров
Command.CommandText = "SELECT * FROM MyTable WHERE TECHYear >= ? AND TECHYear = ?";
// Создаем и добавляем первый параметр (Год начала рабочего года)
// Обозначения типов: 3 - adInteger (целое число), 1 - adParamInput (входной параметр)
ParamStartYear = Command.CreateParameter("StartYear", 3, 1, , Год(ДатаНачалаРабочегоГода));
Command.Parameters.Append(ParamStartYear);
// Создаем и добавляем второй параметр (Точное значение года из выборки ADO)
ТекущийГод = НаборЗаписей.Fields("TECHYear").Value; // Извлекаем чистое число напрямую
ParamExactYear = Command.CreateParameter("ExactYear", 3, 1, , ТекущийГод);
Command.Parameters.Append(ParamExactYear);
// Выполняем запрос
Recordset = Command.Execute();
При использовании этого метода вам вообще не нужно думать о региональных настройках 1С, формате дат или наличии неразрывных пробелов — типы данных будут строго сопоставлены на уровне интерфейсов COM-соединения.
Если по каким-то причинам вам необходимо получить именно строковое представление числа без лишних символов, и вы хотите подстраховаться от любых неожиданностей, можно применить принудительную замену символов в 1С.
Для этого мы воспользуемся встроенной функцией СтрЗаменить() и системным свойством Символы.НПП (Неразрывный Пробел Полуширины):
ИсходноеЧисло = НаборЗаписей.Fields("TECHYear").Value;
СтрокаБезПробелов = СтрЗаменить(Строка(ИсходноеЧисло), Символы.НПП, "");
// Теперь переменную СтрокаБезПробелов можно безопасно встраивать в запрос
ЗапросSQL = "SELECT * FROM MyTable WHERE TECHYear = " + СтрокаБезПробелов;
Этот способ гарантированно вырезает неразрывный пробел из любой строки, полученной стандартными методами приведения типов в 1С.
Иногда возникают ситуации, когда данные с неразрывным пробелом уже записаны во временную таблицу СУБД или передаются из внешней системы, логику которой мы не можем изменить. В этом случае очистку строкового представления года можно произвести непосредственно внутри SQL-запроса или хранимой процедуры на стороне MS SQL Server.
Для этого мы можем использовать встроенную T-SQL функцию REPLACE, указав в качестве заменяемого символа ASCII-код неразрывного пробела, который равен 160. Для получения символа по его коду в SQL используется функция CHAR().
Посмотрим на пример SQL-кода:
-- Демонстрация очистки строки и приведения типов в T-SQL
DECLARE @TechYearString varchar(20);
SET @TechYearString = '2' + CHAR(160) + '017'; -- Имитируем строку с неразрывным пробелом из 1С
-- Очищаем строку от неразрывного пробела и безопасно конвертируем в INT
SELECT CAST(REPLACE(@TechYearString, CHAR(160), '') AS INT) AS CleanedYear;
Этот запрос успешно вернет число 2017 в формате int, предотвратив возникновение системной ошибки на сервере базы данных.
Подведем итоги. При возникновении ошибок приведения типов varchar к int при обмене данными между 1С и MS SQL:
'...', если поле в таблице БД имеет тип int.Формат(ИмяПеременной, "ЧГ=0") для удаления группировки триад.ADODB.Command.Применение этих простых правил сделает вашу интеграцию надежной, безопасной и защищенной от региональных настроек конкретного компьютера или сервера.