При интеграции 1С с внешними базами данных Microsoft SQL Server часто возникает задача выполнения хранимых процедур или пользовательских функций, которые принимают диапазон дат и возвращают сформированный XML-документ. При реализации такого обмена разработчики сталкиваются с рядом специфических ошибок: драйвер сообщает о нереализованных функциях, механизм внешних источников данных не видит объект SQL, а полученное через COM-объект значение внезапно сбрасывается в NULL. Разберем подробно, почему возникают эти проблемы и как правильно организовать получение XML из SQL Server в среде 1С:Предприятие.
Рассмотрим, почему стандартные подходы могут завершаться ошибками:
XML. Если хранимая функция SQL возвращает тип xml, сопоставление типов в конфигураторе дает сбой, а драйвер при попытке прямого вызова возвращает ошибку о том, что столбец или функция не найдены. Для использования ВИД на стороне SQL требуется создавать обертку с явным приведением к строке: CAST(dbo.procOrderGetXML(...) AS NVARCHAR(MAX)).ADODB.Command для SQL Server нельзя использовать устаревший тип adDBDate (код типа 7), так как системный драйвер ODBC его не поддерживает. Вместо него необходимо указывать тип adDBTimeStamp (код типа 135).NULL при отладке. Тип данных XML и длинные строки передаются через ADO потоком. При первом чтении свойства Fields(0).Value (в том числе при вычислении выражения в окне отладки или табло) драйвер вычитывает буфер. При повторном обращении к полю из кода указатель потока уже смещен в конец, и ADO возвращает NULL. Значение необходимо сразу считывать в переменную за один проход.Разберем классический способ вызова процедуры с использованием параметров команды. Этот метод безопасен с точки зрения передачи спецсимволов и не зависит от региональных настроек дат на сервере.
Посмотрим на пример реализации серверной функции:
&НаСервереБезКонтекста
Функция ПолучитьXMLЗаказовЧерезПараметры(ДатаНачала, ДатаОкончания)
СтрокаПодключения = "Provider=SQLOLEDB.1;"
+ "Data Source=ИмяСервера;"
+ "Initial Catalog=ИмяБазы;"
+ "User ID=Логин;"
+ "Password=Пароль;";
Соединение = Новый COMОбъект("ADODB.Connection");
Попытка
Соединение.Open(СтрокаПодключения);
Исключение
ВызватьИсключение "Не удалось подключиться к SQL Server: " + ОписаниеОшибки();
КонецПопытки;
Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandType = 4; // 4 = adCmdStoredProc (хранимая процедура)
Команда.CommandText = "dbo.procOrderGetXML";
Команда.CommandTimeout = 120; // Увеличиваем таймаут под длительные выборки
// Тип adDBTimeStamp (135), направление adParamInput (1)
ПараметрДатаНач = Команда.CreateParameter("dateB", 135, 1, , ДатаНачала);
Команда.Parameters.Append(ПараметрДатаНач);
ПараметрДатаКон = Команда.CreateParameter("dateE", 135, 1, , ДатаОкончания);
Команда.Parameters.Append(ПараметрДатаКон);
Рекордсет = Команда.Execute();
ТелоXML = Неопределено;
Если Рекордсет.Fields.Count > 0 Тогда
// Считываем значение ровно один раз в переменную во избежание сброса потока
ЗначениеПоля = Рекордсет.Fields(0).Value;
Если ЗначениеПоля <> Null Тогда
ТелоXML = Строка(ЗначениеПоля);
КонецЕсли;
КонецЕсли;
Рекордсет.Close();
Соединение.Close();
Возврат ТелоXML;
КонецФункции
Если на стороне SQL Server создана не хранимая процедура, а скалярная или табличная функция (User-Defined Function), вызывать её через CommandType = 4 нельзя. В этом случае выполним прямой текстовый запрос через оператор SELECT, передав даты строковыми литералами в каноническом формате yyyy-MM-dd hh:mm:ss либо yyyymmdd.
Проанализируем пример функции:
&НаСервереБезКонтекста
Функция ВыполнитьЗапросSQL(ДатаНачала, ДатаОкончания)
СтрокаСоединения = "Provider=SQLOLEDB.1;"
+ "Persist Security Info=False;"
+ "User ID=Логин;"
+ "Password=Пароль;"
+ "Initial Catalog=ИмяБазы;"
+ "Data Source=ИмяСервера;";
Попытка
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open(СтрокаСоединения);
Исключение
Возврат Неопределено;
КонецПопытки;
ТекстДатыНачала = Формат(ДатаНачала, "ДФ='yyyy-MM-dd HH:mm:ss'");
ТекстДатыОкончания = Формат(ДатаОкончания, "ДФ='yyyy-MM-dd HH:mm:ss'");
Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandTimeout = 100;
Команда.CommandType = 1; // 1 = adCmdText (текстовый SQL-запрос)
Команда.CommandText = "SELECT dbo.procOrderGetXML(CAST('" + ТекстДатыНачала + "' AS DATETIME), CAST('" + ТекстДатыОкончания + "' AS DATETIME))";
Рекордсет = Команда.Execute();
РезультатXML = Неопределено;
Если Рекордсет.Fields.Count > 0 Тогда
РезультатXML = Рекордсет.Fields(0).Value;
КонецЕсли;
Возврат РезультатXML;
КонецФункции
Выясним тонкости, которые предотвращают трудноуловимые ошибки:
SET NOCOUNT ON. Если хранимая процедура выполняет промежуточные инструкции (создание временных таблиц, вставки данных), SQL Server передает клиенту системные сообщения о количестве обработанных строк. В ADO каждое такое сообщение создает пустой вспомогательный Recordset, из-за чего в коде 1С при первом чтении возвращается пустота. Администратору БД необходимо добавить в начало процедуры SET NOCOUNT ON;, либо на стороне 1С перебирать наборы методом Рекордсет = Рекордсет.NextRecordset();.ADODB функционируют исключительно под управлением ОС Windows. Если серверный кластер 1С развернут на Linux, вызов Новый COMОбъект("ADODB.Connection") вызовет фатальное исключение. Для Linux-серверов обращение к MS SQL выполняется через внешние источники данных и драйвер unixODBC, либо через публикацию веб-сервисов.SQLOLEDB устарел и может усекать длинные XML-структуры. Для современных версий Microsoft SQL Server рекомендуется использовать провайдер MSOLEDBSQL (Microsoft OLE DB Driver for SQL Server).
// Пример дальнейшей обработки полученной строки
Если РезультатXML <> Неопределено Тогда
Чтение = Новый ЧтениеXML;
Чтение.УстановитьСтроку(РезультатXML);
// Чтение через объект модели DOM или ФабрикуXDTO
Пока Чтение.Прочитать() Цикл
// Обработка узлов документа
КонецЦикла;
Чтение.Закрыть();
КонецЕсли;