Почему прямой запрос к MS SQL через ADODB возвращает Неопределено и как это исправить?

← На главную

При работе с внешними базами данных через COM-объект ADODB.Connection разработчики 1С часто сталкиваются с парадоксальной ситуацией: один и тот же SQL-запрос идеально выполняется в консоли SQL Server Management Studio (SSMS), но в коде 1С возвращает пустые значения или Неопределено для некоторых колонок. Проанализируем причины этой проблемы и разберем по шагам методы её решения.

Причина 1. Расположение курсора (CursorLocation)

Одной из самых частых причин "исчезновения" данных является использование серверного курсора по умолчанию. В этом режиме 1С может некорректно считывать данные через мост COM, особенно если запрос содержит сложные соединения (JOIN). Рассмотрим, как изменить настройки соединения для стабильной работы.

По умолчанию свойство CursorLocation равно 2 (adUseServer). Для того чтобы данные гарантированно попадали в RecordSet в понятном для 1С виде, необходимо переключить его на клиентскую сторону (значение 3 — adUseClient). Это заставляет драйвер выкачать данные на сторону клиента перед их обработкой.


// Перед открытием соединения устанавливаем тип курсора
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.CursorLocation = 3; // adUseClient
Соединение.Open(СтрокаПодключения);

Причина 2. Использование хинтов на блокировки (NOLOCK)

Разберем ситуацию, когда данные есть, но 1С их "не видит" из-за активных транзакций в SQL Server. Если в таблицу Location постоянно пишутся данные от мобильных агентов, SQL Server может блокировать чтение для внешних запросов, чтобы обеспечить целостность данных.

Для чтения оперативных данных, где допустимо "грязное чтение" (чтение незафиксированных данных), необходимо использовать хинт WITH (NOLOCK). Это значительно ускоряет работу и предотвращает ситуацию, когда запрос возвращает пустую выборку из-за ожидания освобождения ресурсов. Проанализируем, как изменить текст запроса:


ТекстИнструкции = "
|SELECT 
|   agents.Name as agentName, 
|   location.MobAgentID as agentID
|FROM dbo.MobAgent WITH (NOLOCK)
|LEFT JOIN dbo.Location AS location WITH (NOLOCK) 
|ON dbo.MobAgent.Id = location.MobAgentID";

Причина 3. Специфика типов данных (Numeric и Decimal)

Посмотрим на поля Latitude (Широта) и Longitude (Долгота). В SQL они часто имеют тип numeric или decimal. COM-интерфейс 1С не всегда может автоматически преобразовать эти типы, что приводит к появлению Неопределено в объекте Fields.

Выясним, как гарантировать получение числовых значений. Самый надежный способ — явное приведение типов (CAST) к формату float прямо в тексте SQL-запроса. Тип float (число с плавающей точкой) отлично понимается платформой 1С.


// Вместо прямого обращения к полю используем CAST
ТекстИнструкции = "
|SELECT 
|   CAST(location.Latitude AS FLOAT) as latitude,
|   CAST(location.Longitude AS FLOAT) as longitude
|FROM dbo.Location WITH (NOLOCK)";

Причина 4. Отключение вспомогательных сообщений (SET NOCOUNT ON)

Проанализируем техническую особенность взаимодействия ADODB и SQL. При выполнении команд SQL Server часто возвращает информационные сообщения (например, "1 row(s) affected"). ADODB может интерпретировать это сообщение как закрытие основного набора данных (Recordset).

Чтобы избежать этой ошибки, всегда добавляйте команду SET NOCOUNT ON; в самое начало вашего SQL-текста. Это подавит выдачу служебной информации и оставит в потоке только сами данные.


ТекстИнструкции = "SET NOCOUNT ON;
|SELECT * FROM dbo.MobAgent";

Причина 5. Оптимизация запроса через CTE (Common Table Expressions)

Рассмотрим более современный и читаемый подход к написанию запроса. Вместо вложенных подзапросов, в которых легко запутаться и получить NULL, используем обобщенные табличные выражения. Это сделает логику получения последних координат агента прозрачной.


ТекстИнструкции = "
|WITH LastLocations AS (
|   SELECT 
|       MobAgentID, 
|       MAX(Date) as MaxDate
|   FROM dbo.Location WITH (NOLOCK)
|   WHERE Date >= DATEADD(day, -1, GETDATE())
|   GROUP BY MobAgentID
|)
|SELECT 
|   A.Name as agentName,
|   L.Date,
|   CAST(L.Latitude AS FLOAT) as latitude,
|   CAST(L.Longitude AS FLOAT) as longitude
|FROM LastLocations LL
|JOIN dbo.Location L WITH (NOLOCK) ON LL.MobAgentID = L.MobAgentID AND LL.MaxDate = L.Date
|JOIN dbo.MobAgent A WITH (NOLOCK) ON LL.MobAgentID = A.Id";

Причина 6. Обработка NULL значений

В языке 1С значение NULL из SQL-базы через ADODB преобразуется в тип Null. Однако при попытке записи в таблицу значений 1С или при выводе в отладчик это может выглядеть как пустое значение. Проанализируем, как обезопасить себя от этого с помощью функции ISNULL в SQL.

Рекомендуется заменять потенциальные пустоты на значения по умолчанию:

Для строк: ISNULL(Name, '')
Для чисел: ISNULL(Latitude, 0)

Причина 7. Формат даты в параметрах

Выясним причину, по которой запрос может не находить данные за период. В коде автора используется формат dd/MM/yyyy. Этот формат зависит от настроек языка (Locale) конкретного SQL-сервера. Если на сервере установлена английская локаль, он будет ждать дату в формате MM/dd/yyyy, и ваш запрос либо выдаст ошибку, либо вернет пустой результат.

Рассмотрим правильный подход: всегда используйте универсальный (ISO) формат даты YYYYMMDD или YYYY-MM-DDTHH:mm:ss. В 1С это реализуется следующим образом:


// Формируем дату, которую поймет любой SQL Server
СтрокаДата = Формат(ТекущаяДата(), "ДФ=yyyyMMddHHmmss");
// В запросе это будет выглядеть как '20231025143000'

Итоговый пример надежного подключения

Соберем все рекомендации вместе и посмотрим на пример кода, который минимизирует риски получения пустых данных:


Попытка
    Соединение = Новый COMОбъект("ADODB.Connection");
    Соединение.ConnectionString = "Provider=SQLOLEDB.1;Data Source=" + ИмяСервера + ";Initial Catalog=" + БазаДанных + ";User ID=" + Пользователь + ";Password=" + Пароль + ";";
    Соединение.CursorLocation = 3; // Клиентский курсор
    Соединение.Open();
    
    Команда = Новый COMОбъект("ADODB.Command");
    Команда.ActiveConnection = Соединение;
    
    // Используем SET NOCOUNT ON и явное приведение типов
    Команда.CommandText = "SET NOCOUNT ON; SELECT CAST(Latitude AS FLOAT) as lat FROM Location WITH (NOLOCK)";
    
    Выборка = Команда.Execute();
    
    Пока Выборка.EOF = Ложь Цикл
        // Обращаемся к полю безопасно
        Широта = Выборка.Fields("lat").Value;
        Сообщить(Широта);
        Выборка.MoveNext();
    КонецЦикла;
    
    Соединение.Close();
Исключение
    Сообщить("Ошибка: " + ОписаниеОшибки());
КонецПопытки;

Соблюдение этих правил: установка CursorLocation = 3, использование хинтов NOLOCK и явное приведение типов к FLOAT — позволит вам избежать большинства проблем при интеграции 1С и MS SQL через ADODB.