Для иллюстрации работы технологии приведем пример решения типовой задачи:
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.2019 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.2019 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 предоставляется бесплатно загрузить с веб-узла по АДРЕСУ:
Благодарю за внимание.
