Использование процедур SQL при обращении к внешним источникам данных

Для иллюстрации работы технологии приведем пример решения типовой задачи:

1. Начальные условия.

    Существует база сигналов [DB_AGK] и хранится на [AGK1] — MS SQL Server 2014 Standart, где значение каждого сигнала за период опроса хранится в отдельной таблице (количество сигналов в БД более 300). При превышении заданных пороговых значений в базу [MFSB] записывается событие с максимальным значением сигнала. Структура таблиц [MFSB] приведена на прикрепленном рисунке.

Замечание: Сервер [AGK1], содержащий таблицы сигналов, должен быть целевым сервером или быть подключенным к целевому серверу как связанный сервер.

    Например, таблица [SA_SIG_0_16922_5_4] (название её совпадает с именем сигнала) содержит следующие поля:

       [PK_ID_SIG] int — (первичный ключ) 
       [VALUE] float — (значение сигнала)
       [TIME] int — (время в формате Unix — кол-во секунд с 01.01.1970 г.)
       [MSEC] int — (количество микросекунд с начала секунды времени измерения)

2. Постановка задачи.

   Необходимо за заданный период найти максимальное значение сигнала и вывести в 1С.  

3.  Решение.

3.1 Код для SQL — стандартное решение для таких задач.

Нам приходится использовать процедуру с возвращаемым значением вместо функции, так как в функции нельзя использовать динамический SQL.

Код процедуры создаем в базе [MFSB]. Исходник приведен ниже:

--1. Код процедуры MAXEVENT
USE [mfsb]
GO
/****** Object:  StoredProcedure [dbo].[MaxEvent]    Script Date: 19.03.2024 11:03:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[MaxEvent]
-- Add the parameters for the stored procedure here
@Signal        varchar(100),    -- имя таблицы
@TimeBegin    varchar(100),     -- начало периода в формате времени 1С
@TimeEnd    varchar(100),       -- конец  периода в формате времени 1С
@Result        float OUT        -- результат
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @t table (MaxVal float)                             -- временная таблица значений
declare @var_databegin     As int;                          -- время в формате UNIX
declare @var_dataend        As int;                         -- время в формате UNIX
DECLARE @SQL                As varchar(max);                -- строка для сборки SQL-выражений

-- преобразование даты: см. код функции UNIX_TIMESTAMP() ниже, convert() - встроенная функция
select @var_databegin  = dbo.UNIX_TIMESTAMP(convert(datetime, @TimeBegin,120)); -- преобразуем время
select @var_dataend    = dbo.UNIX_TIMESTAMP(convert(datetime, @TimeEnd  ,120));

--Обработка ошибок ()
if @var_dataend > @var_databegin
Begin
@Result  =-3.0; -- значение, если результат не получен.
Return;
End

--Формируем строку SQL для запроса максимального результата:
Set @SQL ='SELECT max([VALUE])
FROM [AGK1].[DB_AGK].[dbo].'+@Signal+' WHERE ([TIME]>='+convert(varchar(20),@var_databegin)+') and
([TIME]<='+Convert(varchar(20),@var_dataend)+')'

Insert into @t Exec(@Sql) -- выполняем запрос и складываем в таблицу
Select top 1 @Result = MaxVal From @t -- выборка значимого результата
if (@Result is Null) set @Result = -3.0 -- значение, если результат не получен.
END

--2. Код функции UNIX_TIMESTAMP() --
USE [mfsb]
GO
/****** Object:  UserDefinedFunction [dbo].[UNIX_TIMESTAMP]    Script Date: 19.03.2024 12:37:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date, ,>
-- Description:    <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[UNIX_TIMESTAMP] (
@ctimestamp datetime
)
RETURNS integer
AS
BEGIN
/* Function body */
declare @return int

--SELECT @return = DATEDIFF(SECOND,{d '1970-01-01'}, @ctimestamp) -- как вариант

-- Внимание!!! Если у вас есть часовой пояс отличный от Гринвича то вместо '1970-01-01 00:00:00' необходимо указать '1970-01-01 04:00:00' (для Москвы, то есть +4 часа)
SELECT @return = DATEDIFF(SECOND,Convert(datetime,'1970-01-01 00:00:00',120), @ctimestamp)

return @return
END

--3. Код функции UNIX_TO_DATETIME() --
-- Приведу еще одну функцию, обратную UNIX_TIMESTAMP, может быть пригодится.

CREATE FUNCTION [dbo].[UNIX_TO_DATETIME] (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN

-- закомментированный текст содержит автоматическое определение часового пояса:

-- для этого в выражении RETURN переменную @Datetime замените на @AdjustedLocalDatetime

--DECLARE @LocalTimeOffset BIGINT;
--DECLARE @AdjustedLocalDatetime BIGINT;

--SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
--SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset

RETURN (SELECT DATEADD(second, @Datetime, CAST('1970-01-01 00:00:00' AS datetime)))

END;

3.2 Теперь рассмотрим код для 1С.

Пример приведен в виде внешней обработки с одной формой, где в поле "Источник" необходимо задать имя таблицы (сигнала).

Также необходимо выбрать период, заполним поля "Дата начала" и "Дата окончания".

При нажатии кнопки "Расчет" в поле "Результат" будет помещено максимальное значение за заданный период выбранного сигнала.

(естественно, если у вас есть эти данные :-).

Ниже помещен модуль формы.

&НаСервере
Процедура Команда1НаСервере()

Результат = 0;
СигналТекст = Источник;
Если ЗначениеЗаполнено(СигналТекст) Тогда
Тест = ВыполнитьЗапросSQL(СигналТекст, ДатаНачала, ДатаОкончания);
Результат = Тест;
КонецЕсли;

КонецПроцедуры

&НаКлиенте
Процедура Команда1(Команда)

Команда1НаСервере();

КонецПроцедуры

&НаСервереБезКонтекста
Функция  ВыполнитьЗапросSQL(Источник, ДатаНачала, ДатаОкончания)

Тест1 = 0; //Инициализация переменной

//Задаем параметры подключения к MS SQL Server
Сервер           = "10.10.10.11"; //ip или имя вашего сервера
База             = "mfsb";
Пользователь     = "user"; // пользователь или "sa" ;-) - права пользователя нужны на исполнение
Пароль           = "123456"; //или пароль "fgR678uЕЕyr#" - например...

СтрокаСоединения = "Provider=SQLOLEDB.1; Trusted_Connection=no; Initial Catalog ="+База
+"; Data Source="+Сервер
+";User ID="+Пользователь
+";Password="+Пароль;

Connection = Новый COMОбъект("ADODB.Connection");
//Попытка //Для отладки
Connection.Open(СтрокаСоединения);
//Исключение
//    Возврат Неопределено;
//    ОписаниеОшибки();
//КонецПопытки;

Command = Новый COMОбъект("ADODB.Command");
Command.CommandTimeout = 100;
Command.ActiveConnection = Connection;

Command.CommandText ="dbo.MaxEvent";
Command.CommandType=4;

Param0 = Command.CreateParameter("@Signal", 130, 1, 100);
Command.Parameters.Append (Param0);
Command.Parameters(0).value = Источник;

Param1 = Command.CreateParameter("@TimeBegin", 130, 1, 100);
Command.Parameters.Append (Param1);
Command.Parameters(1).value = Строка(Формат(ДатаНачала,"ДФ='гггг-ММ-дд чч:мм'"));

Param2 = Command.CreateParameter("@TimeEnd", 130, 1, 100);
Command.Parameters.Append (Param2);
Command.Parameters(2).value = Строка(Формат(ДатаОкончания,"ДФ='гггг-ММ-дд чч:мм'"));

Param3 = Command.CreateParameter("@Result", 5 , 3, 20);
Command.Parameters.Append (Param3); ;
Command.Parameters(3).value = Тест1;

Command.Prepared = true;
Command.Execute();

Тест1 =  Command.Parameters(3).value;

Connection.Close();
//Сообщить(Тест1);
Возврат Тест1;
КонецФункции

  Описание: 

Имеет смысл разобрать только функцию "ВыполнитьЗапросSQL". Она состоит из 4 частей:

 — в первой части формируется строка подключения.

 — во второй части создается COM-объект и выполняется открытие соединения.

 — в третьей части формируются структура Command c 4-мя параметрами и происходит вызов метода Exicute (выполнение)

(Обратите внимание — число параметров должно быть указано как в вашей процедуре на SQL!!!)

 — в четвертой части происходит возврат значения 4-го параметра (Parameters(3) — счет от нуля) и закрытие соединения. 

 

Для более полного ознакомления с темой [Внешние источники данных] рекомендую статью: 

"[ Внешние источники данных ] Пример интеграции базы 1С и внешней СУБД"

 

Ещё замечание (отсюда):

Чтобы правильно установить ADO на вашем компьютере, необходимо установить MDAC. При этом устанавливаются необходимые базовые компоненты ADO и ADO. OLE DB также требует компонентов ODBC версии 3.0 или более поздней.
Компоненты MDAC предоставляется бесплатно загрузить с веб-узла по АДРЕСУ:

http://msdn.microsoft.com/en-us/data/aa937729.aspx

Благодарю за внимание.