При работе с внешними базами данных или при выполнении прямых запросов к СУБД из среды 1С:Предприятие разработчики часто сталкиваются со специфическими синтаксическими ошибками. Одна из распространенных проблем — ошибка синтаксиса СУБД около ключевого слова "ON". Проанализируем причины этой ошибки, выясним, почему код мог работать ранее, и детально разберем корректные способы решения проблемы.
Ошибки подобного рода возникают из-за путаницы в диалектах SQL. Конструкция вида INSERT ... ON DUPLICATE KEY UPDATE является уникальной особенностью СУБД MySQL (и MariaDB). Она позволяет вставить строку в таблицу, а в случае совпадения уникального ключа — обновить существующую запись (операция, известная как UPSERT).
Если база данных 1С:Предприятие или внешняя база, к которой обращается обработка, работает под управлением Microsoft SQL Server (MS SQL), то СУБД использует диалект T-SQL (Transact-SQL). В стандарте T-SQL синтаксис ON DUPLICATE KEY попросту отсутствует. Попытка выполнить такой запрос приведет к синтаксической ошибке на стороне SQL-сервера.
Ситуация, когда «раньше все работало, а после обновления платформы перестало», обычно связана с изменением окружения. Например, мог измениться используемый драйвер соединения (переход с MySQL ODBC на SQL Server Native Client), изменилась строка подключения в коде внешней обработки, либо сама база данных была мигрирована на другую СУБД.
Рассмотрим четыре надежных паттерна реализации логики UPSERT в MS SQL Server, а также разберем, как правильно организовать работу на стороне платформы 1С.
Самый очевидный способ заменить отсутствующую конструкцию — сначала попытаться обновить запись, а если строк для обновления не нашлось (запись еще не существует), выполнить добавление. Однако в высоконагруженных системах без специальных подсказок блокировок этот подход подвержен «состоянию гонки» (race condition), когда два параллельных потока могут одновременно попытаться вставить одну и ту же запись.
Рассмотрим правильный и безопасный шаблон транзакции на T-SQL:
BEGIN TRANSACTION;
UPDATE ИмяТаблицы WITH (UPDLOCK, HOLDLOCK)
SET Поле1 = @Значение1, Поле2 = @Значение2
WHERE КлючевоеПоле = @Ключ;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO ИмяТаблицы (КлючевоеПоле, Поле1, Поле2)
VALUES (@Ключ, @Значение1, @Значение2);
END;
COMMIT TRANSACTION;
Разберем подробнее используемые подсказки (хинты) блокировок:
В современных версиях MS SQL Server для синхронизации таблиц предназначен мощный оператор MERGE. Он позволяет объединить проверку, вставку и обновление в одну инструкцию.
Посмотрим на пример использования оператора MERGE для одиночной записи:
MERGE ИмяТаблицы WITH (HOLDLOCK) AS target
USING (SELECT @Ключ, @Значение1, @Значение2) AS source (Ключ, Поле1, Поле2)
ON (target.КлючевоеПоле = source.Ключ)
WHEN MATCHED THEN
UPDATE SET target.Поле1 = source.Поле1, target.Поле2 = source.Поле2
WHEN NOT MATCHED THEN
INSERT (КлючевоеПоле, Поле1, Поле2)
VALUES (source.Ключ, source.Поле1, source.Поле2);
Важное предостережение: Вопреки распространенному заблуждению, оператор MERGE не является абсолютно атомарным «из коробки». При очень высокой интенсивности параллельных запросов без подсказки WITH (HOLDLOCK) он может приводить к ошибкам нарушения уникальности первичного ключа (Primary Key Violation) и взаимоблокировкам (deadlocks). Всегда используйте хинты блокировок при работе с высоконагруженными таблицами.
Если специфика вашего бизнес-процесса такова, что в 90% случаев происходит именно вставка новых записей, а обновление существующих требуется крайне редко, наиболее производительным будет оптимистичный подход с перехватом ошибок.
Рассмотрим структуру такого запроса:
BEGIN TRY
INSERT INTO Таблица (КлючевоеПоле, Поле1)
VALUES (@Ключ, @Значение);
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627 -- Код ошибки нарушения Unique Constraint / Primary Key в MS SQL
BEGIN
UPDATE Таблица
SET Поле1 = @Значение
WHERE КлючевоеПоле = @Ключ;
END
ELSE
BEGIN
THROW; -- Перенаправляем любые другие непредвиденные ошибки
END
END CATCH
Этот способ работает максимально быстро при редких коллизиях, поскольку СУБД не тратит ресурсы на предварительную проверку существования записи и накладывание избыточных блокировок чтения.
При написании внешних обработок 1С настоятельно рекомендуется избегать ручного формирования прямых SQL-запросов к базам данных. Платформа предоставляет мощные абстракции, которые самостоятельно транслируют действия разработчика в оптимальный SQL-код, подходящий под текущую СУБД.
Сценарий А: Работа со сторонней базой данных
Если ваша внешняя обработка обменивается данными с внешней СУБД (например, сайтом или сторонней ERP), используйте объект метаданных ВнешниеИсточникиДанных. Начиная с версии платформы 8.3.5, внешние источники поддерживают полноценную объектную технику работы:
НоваяСтрока = ВнешниеИсточникиДанных.ИмяИсточника.Таблицы.ИмяТаблицы.СоздатьОбъект();
НоваяСтрока.КлючевоеПоле = Ключ;
НоваяСтрока.Поле1 = Значение;
НоваяСтрока.Записать(); // Платформа сама сгенерирует нужный UPSERT-запрос под диалект СУБД
Сценарий Б: Работа с собственной базой 1С
Если вы пытаетесь через ADO напрямую писать данные в физические таблицы самой базы данных 1С (например, таблицы регистров сведений _InfoRegXXXX или справочников _ReferenceXXXX) — немедленно прекратите эту практику. Прямая запись данных в СУБД мимо объектной модели 1С:
Для корректного добавления или обновления данных всегда используйте стандартные объекты платформы. Например, для записи в регистр сведений воспользуйтесь менеджером записи:
МенеджерЗаписи = РегистрыСведений.ИмяРегистра.СоздатьМенеджерЗаписи();
МенеджерЗаписи.Измерение1 = Ключ;
МенеджерЗаписи.Прочитать(); // Проверяем наличие записи
МенеджерЗаписи.Ресурс1 = Значение; // Если запись была, она обновится, если нет — запишется новая
МенеджерЗаписи.Записать(Истина);
Использование встроенного языка 1С гарантирует абсолютную безопасность ваших данных и полную независимость кода от того, на какой СУБД развернута информационная база (MS SQL, PostgreSQL, IBM DB2 или Oracle Database).