Разработчики на платформе 1С:Предприятие нередко сталкиваются с необходимостью выполнять прямые запросы к базам данных MS SQL Server. Самым распространенным инструментом для решения этой задачи является COM-объект ADODB.Connection. Однако при работе со сложными SQL-скриптами, содержащими несколько последовательных команд (например, смену контекста базы данных USE, создание временных таблиц или резервное копирование), возникает неприятная проблема: ошибки на стороне SQL Server перестают вызывать исключения в 1С. Стандартная конструкция Попытка...Исключение просто игнорирует сбои СУБД, а приложение продолжает работу так, будто всё прошло успешно.
В этой статье мы подробно разберем, почему возникает такое поведение, как устроено сетевое взаимодействие между ADODB и SQL Server и как гарантированно отловить любую ошибку на стороне базы данных.
Давайте проанализируем ситуацию, которая часто приводит разработчиков в замешательство. Мы пишем SQL-запрос, состоящий из нескольких шагов, например:
USE [ИмяБазы Данных];
EXEC sp_rename 'v8users', 'v8users_old';
UPDATE dbo.Params SET FileName = 'users.usr_old' WHERE FileName = 'users.usr';
Если запустить этот скрипт напрямую в SQL Server Management Studio (SSMS), при отсутствии таблицы v8users мы получим явное сообщение об ошибке: Msg 15225, Level 11, State 1... No item by the name of 'v8users' could be found.... Но если запустить его из 1С через метод Execute() объекта ADODB.Command, то код спокойно проскакивает блок исключения и продолжает выполнение.
Причина кроется в особенностях работы сетевого протокола TDS (Tabular Data Stream), по которому общаются драйвер ADODB и SQL Server, а также в архитектуре самого ADO. Любая инструкция в скрипте (изменение контекста базы данных, вывод информационных сообщений СУБД, выборка строк или возникновение ошибки) возвращает на клиент отдельный пакет данных. В терминах ADO каждый такой пакет преобразуется в самостоятельный набор данных — объект Recordset.
Когда мы вызываем метод Execute(), драйвер ADO обрабатывает и возвращает только самый первый набор данных, полученный от сервера. В нашем примере первой идет команда USE [ИмяБазы]. Она выполняется успешно и возвращает информационное сообщение «Changed database context...». ADO считает, что первая операция выполнена успешно, и возвращает управление в 1С. Все последующие результаты выполнения (включая критические ошибки во второй и третьей строках скрипта) остаются ожидать в буфере сетевого сокета. Драйвер на клиенте физически не считывает их до тех пор, пока разработчик сам не попросит об этом.
Чтобы заставить драйвер прочитать весь буфер ответов от SQL Server и «вытащить» скрытые ошибки, мы должны использовать метод NextRecordset() объекта Recordset. Этот метод последовательно переключает объект на следующий набор результатов в пакете.
Разберем готовый шаблон кода для выполнения SQL-скриптов из 1С с гарантированной обработкой ошибок:
// 1. Инициализируем соединение с СУБД
ADOСоединение = Новый COMОбъект("ADODB.Connection");
СтрокаПодключения = "Provider=sqloledb;Server=ИмяСервера;Database=ИмяБазы;Trusted_Connection=Yes;";
ADOСоединение.ConnectionTimeOut = 30;
ADOСоединение.CommandTimeOut = 60;
Попытка
ADOСоединение.Open(СтрокаПодключения);
Исключение
ЗаписьЖурналаРегистрации("SQL.ОшибкаСоединения", УровеньЖурналаРегистрации.Ошибка,,, ОписаниеОшибки());
ВызватьИсключение "Не удалось подключиться к серверу SQL!";
КонецПопытки;
// 2. Создаем команду и передаем наш пакетный скрипт
ТекстКоманды = "
| USE [ИмяБазыДанных];
| EXEC sp_rename 'v8users', 'v8users_old';
| UPDATE dbo.Params SET FileName = 'users.usr_old' WHERE FileName = 'users.usr';
|";
Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = ADOСоединение;
Команда.CommandType = 1; // adCmdText
Команда.CommandTimeout = ADOСоединение.CommandTimeOut;
Команда.CommandText = ТекстКоманды;
Попытка
// Выполняем команду и получаем первый рекордсет
Рекордсет = Команда.Execute();
// 3. Пробегаем по всем последующим рекордсетам, чтобы спровоцировать генерацию исключения
Пока Рекордсет <> Неопределено Цикл
// Метод NextRecordset возвратит Неопределено, когда результаты закончатся
Рекордсет = Рекордсет.NextRecordset();
КонецЦикла;
Сообщить("Скрипт успешно выполнен!");
Исключение
// 4. Если на любом из шагов произошла ошибка, мы попадем сюда
Сообщить("В процессе выполнения SQL возникла критическая ошибка!", СтатусСообщения.Важное);
Сообщить(ОписаниеОшибки());
// Дополнительно анализируем коллекцию Errors самого соединения
Для Каждого ОшибкаИзКоллекции Из ADOСоединение.Errors Цикл
Сообщить("Код SQL-ошибки: " + ОшибкаИзКоллекции.NativeError + " | Описание: " + ОшибкаИзКоллекции.Description);
КонецЦикла;
КонецПопытки;
При таком подходе, как только метод NextRecordset() дойдет до шага, на котором произошла ошибка (например, переименование несуществующей таблицы), платформа 1С мгновенно прервет выполнение блока Попытка и передаст управление в блок Исключение, где вы сможете детально залогировать проблему.
При работе с базами данных важно учитывать еще один нюанс поведения MS SQL Server. По умолчанию после выполнения операторов INSERT, UPDATE, DELETE или MERGE СУБД возвращает клиенту служебное сообщение о количестве затронутых строк (например, «1 row(s) affected»). Для ADO каждое такое сообщение является отдельным пустым рекордсетом.
Если ваш скрипт выполняет массовые изменения, вам придется вызывать метод NextRecordset() огромное количество раз. Чтобы избежать лишней нагрузки на сеть и избавиться от генерации сотен пустых наборов данных, всегда добавляйте в самое начало вашего SQL-запроса команду SET NOCOUNT ON;. Она отключает отправку этих сообщений СУБД, делая выполнение скрипта более быстрым и чистым.
ТекстКоманды = "
| SET NOCOUNT ON;
| USE [ИмяБазыДанных];
| -- Теперь SQL пришлет только реальные данные или ошибки!
|";
Если вы не хотите усложнять код на стороне 1С постоянными вызовами NextRecordset(), можно переложить задачу отслеживания ошибок на плечи самого SQL Server. Для этого оберните тело вашего скрипта в конструкцию BEGIN TRY ... END TRY, а в блоке BEGIN CATCH ... END CATCH верните управление в 1С в виде структурированной таблицы с описанием ошибки.
Посмотрим на пример такого SQL-запроса:
SET NOCOUNT ON;
BEGIN TRY
USE [ИмяБазыДанных];
EXEC sp_rename 'v8users', 'v8users_old';
-- Если все шаги успешны, возвращаем признак успеха
SELECT 0 AS ErrorCode, 'Success' AS ErrorMessage;
END TRY
BEGIN CATCH
-- Если произошла ошибка, возвращаем информацию о ней первым же набором данных
SELECT
ERROR_NUMBER() AS ErrorCode,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
При таком подходе SQL Server перехватит ошибку внутри себя и вернет её вам в качестве обычного результата выборки (строки таблицы). В 1С вам останется лишь прочитать значения первой строки рекордсета:
Рекордсет = Команда.Execute();
Если Не Рекордсет.EOF Тогда
КодОшибки = Рекордсет.Fields("ErrorCode").Value;
СообщениеОбОшибке = Рекордсет.Fields("ErrorMessage").Value;
Если КодОшибки <> 0 Тогда
ВызватьИсключение "Ошибка СУБД (" + КодОшибки + "): " + СообщениеОбОшибке;
КонецЕсли;
КонецЕсли;
Этот метод считается наиболее надежным в промышленной разработке, так как гарантирует возврат структуры ошибки в 1С без необходимости глубокого анализа сетевых буферов драйвера ADO.