При интеграции 1С с внешними базами данных MS SQL Server через COM-объект ADO (ActiveX Data Objects) программисты часто сталкиваются с ошибками синтаксиса или несоответствия количества параметров. Одной из самых коварных ошибок является ситуация, когда количество полей в таблице не совпадает с количеством передаваемых значений, или когда типы данных 1С некорректно интерпретируются драйвером OLE DB. В этой статье мы подробно разберем, как построить надежный запрос на добавление данных, избежать «магических чисел» в индексах параметров и обеспечить стабильную работу обмена.
Рассмотрим типичную ситуацию: при попытке выполнить команду Cmd.Execute() вылетает исключение «Microsoft OLE DB Provider for SQL Server: Ошибка синтаксиса или нарушение прав доступа». Чаще всего это происходит по следующим причинам:
INSERT INTO Table VALUES (?, ?, ...), SQL-сервер ожидает значения для всех колонок таблицы в том порядке, в котором они созданы в БД. Если в таблицу добавили хотя бы одно поле (например, для аудита или технический флаг), старый код без перечисления полей мгновенно сломается.? параметры являются позиционными. Это значит, что Parameters(0) всегда соответствует первому вопросу, Parameters(1) — второму и так далее. Малейшая ошибка в расчетах или пропущенная запятая ведет к смещению данных.Name, Order, Key, User являются зарезервированными в T-SQL. Без использования квадратных скобок [] запрос будет возвращать ошибку синтаксиса.Для того чтобы сделать запрос устойчивым к изменениям структуры таблицы, мы всегда должны явно указывать имена колонок. Разберем правильную структуру запроса на примере добавления контрагента.
ТекстЗапроса = "
|INSERT INTO [gtw_Accounts] (
| [UI1C],
| [FromBPM],
| [From1C],
| [Name],
| [INN],
| [Email]
|)
|VALUES (?, ?, ?, ?, ?, ?)";
Cmd = Новый COMОбъект("ADODB.Command");
Cmd.ActiveConnection = Соединение; // Установленное ранее ADODB.Connection
Cmd.CommandText = ТекстЗапроса;
Cmd.CommandType = 1; // adCmdText
// Заполняем параметры строго по порядку
Cmd.Parameters(0).Value = СтрокаКонтрагента.УИД;
Cmd.Parameters(1).Value = Ложь;
Cmd.Parameters(2).Value = Истина;
Cmd.Parameters(3).Value = СтрокаКонтрагента.Наименование;
Cmd.Parameters(4).Value = СтрокаКонтрагента.ИНН;
Cmd.Parameters(5).Value = СтрокаКонтрагента.Почта;
Cmd.Execute();
Важный момент: Обратите внимание на использование квадратных скобок [] вокруг имен таблицы и полей. Это «золотой стандарт» при написании прямых запросов, который страхует от конфликтов с именами встроенных функций SQL.
Проанализируем ситуацию, когда поле в SQL разрешает значение NULL, и нам нужно передать именно его. В 1С простое присвоение Null параметру ADO может не сработать корректно в зависимости от версии провайдера. Рассмотрим, как гарантированно передать пустое значение или NULL:
Если вам нужно оставить поле пустым на стороне сервера, и оно поддерживает NULL, лучше всего передать его явно в тексте запроса, если значение не меняется, либо использовать специальное присвоение:
// Если значение всегда NULL для этого запроса:
ТекстЗапроса = "INSERT INTO [gtw_Accounts] ([Name], [ParentId]) VALUES (?, NULL)";
// Если значение зависит от условия в 1С:
Если ЗначениеЗаполнено(РодительСсылка) Тогда
Cmd.Parameters(1).Value = РодительСсылка.УникальныйИдентификатор();
Иначе
// Для некоторых провайдеров помогает передача пустого значения
// или явное указание типа через CreateParameter
Cmd.Parameters(1).Value = null;
КонецЕсли;
Многие программисты полагаются на автоматическое создание коллекции Parameters. Однако более надежным и производительным способом (особенно при работе с типами String большой длины или DateTime) является использование метода CreateParameter. Это позволяет явно указать тип данных SQL, что исключает ошибки приведения типов на стороне сервера.
Разберем пример создания параметра вручную:
// Типы данных ADO:
// 200 = adVarChar (строка),
// 135 = adDBTimeStamp (дата/время),
// 3 = adInteger (целое число)
// 11 = adBoolean (булево)
// Пример добавления строкового параметра
ПараметрИмя = Cmd.CreateParameter("Name", 200, 1, 150, СтрокаКонтрагента.Наименование);
Cmd.Parameters.Append(ПараметрИмя);
// Пример добавления булева параметра
ПараметрФлаг = Cmd.CreateParameter("From1C", 11, 1, , Истина);
Cmd.Parameters.Append(ПараметрФлаг);
Этот метод позволяет не зависеть от того, смог ли драйвер OLE DB правильно «прочитать» тип данных из переменной 1С. Кроме того, это ускоряет выполнение, так как SQL Server не тратит ресурсы на неявное преобразование типов (Implicit Conversion).
Часто требуется сначала проверить наличие записи, а затем либо вставить новую, либо обновить существующую. Рассмотрим синтаксис запроса UPDATE, который работает по тем же принципам позиционных параметров:
ТекстЗапроса = "
|UPDATE [gtw_Accounts]
|SET [Name] = ?,
| [Email] = ?,
| [DoDelete] = ?
|WHERE [UI1C] = ?";
Cmd.CommandText = ТекстЗапроса;
Cmd.Parameters(0).Value = СтрокаКонтрагента.Наименование;
Cmd.Parameters(1).Value = СтрокаКонтрагента.Почта;
Cmd.Parameters(2).Value = Ложь;
Cmd.Parameters(3).Value = СтрокаКонтрагента.УИД;
Cmd.Execute();
Важно: В блоке WHERE также используются знаки вопроса. Порядок присвоения Parameters(N).Value должен строго соответствовать порядку появления ? в тексте запроса сверху вниз и слева направо.
Если вы получаете ошибку «Имя столбца или число предоставленных значений не соответствует определению таблицы», воспользуйтесь следующим алгоритмом:
Script Table as -> INSERT To -> New Query Editor Window. Вы получите идеальный шаблон запроса со всеми именами полей, который точно понимает сервер.Cmd.Prepared = Истина; перед выполнением. Это заставит сервер заранее скомпилировать план запроса, что поможет выявить ошибки синтаксиса еще на этапе подготовки, а не в момент передачи данных.INSERT INTO и количество знаков вопроса после VALUES. Они должны быть строго равны.Следуя этим правилам и используя явное перечисление полей, вы создадите устойчивый программный код, который не потребует переписывания при каждом мелком изменении структуры внешней базы данных.