bg

Экспорт данных в базу данных SQL с помощью хранимой процедуры

Описание проблемы:

Мне нужно записывать данные в базу данных SQL с использованием хранимой процедуры, поскольку мне нужно выполнять дополнительные проверки перед записью данных.

Что такое хранимая процедура:

Хранимая процедура (stored procedure или stored proc) - это функция на сервере, которая обеспечивает особый доступ для приложений к базе данных SQL. Такие процедуры выполняются и обрабатывают данные на стороне сервера.

Когда может потребовать хранимая процедура:

  • Обработка данных на стороне сервера.
  • Запись данных в несколько таблиц.
  • Добавление первичных или дополнительных ключей к записываемым данным.
  • Обновление или запись данных по дополнительным условиям.

Требования:

  • USB HID Logger Professional, Enterprise, или пробная версия;
  • SQL Database Professional

Подразумевается что:

У вас уже настроен парсер и подготовлены переменные парсера для экспорта.

Для этого примера переменные парсера взяты из предыдущего примера.

Также вы можете просмотреть другие примеры парсинга данных (разные типы парсеров)

Решение:

Замечание: Вам потребуются знания языка программирования SQL для вашей базы данных, поскольку хранимая процедура это код программы для сервера.

Будем считать, что у вас есть доступ к базе данных и права для создания объектов в ней. Прежде всего, создайте таблицу в базе данных для сохранения входящих данных. Выберите существующую базу данных, или создайте новую для тестирования. Самый простой способ - использовать инструменты разработчика, предоставляемые производителем базы:

  • SQL Server Management Studio для MSSQL.
  • MySQL Workbench для MySQL.
  • pgAdmin для PostgreSQL.

Обратитесь к документации на соответствующий инструмент для дополнительной информации.

Мы подготовили пример таблицы (рис. 1). Это прототип. Вы можете изменить его по своим требованиям или использовать вашу существующую таблицу.

CREATE TABLE dbo.[DATA] (
   [ID] decimal(10,0)  IDENTITY(1,1) NOT NULL,
   [DATE_TIME_STAMP] datetime NULL,
   [DATA_SOURCE_NAME] nvarchar(32) NULL,
   [DEVICE_ID] nvarchar(32) NULL,
   [V1] real NULL,
   [V2] real NULL,
   [V3] real NULL,
CONSTRAINT [PK_DATA] PRIMARY KEY ([ID] ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE `DATA` (
   `ID` int(11) NOT NULL auto_increment,
   `DATE_TIME_STAMP` DATETIME NULL,
   `DATA_SOURCE_NAME` VARCHAR(32) NULL,
   `DEVICE_ID` VARCHAR(32) NULL,
   `V1` DOUBLE NULL,
   `V2` DOUBLE NULL,
   `V3` DOUBLE NULL,
PRIMARY KEY  (`ID`)
) ENGINE=InnoDB;


CREATE TABLE "DATA" (
   "ID" SERIAL,
   "DATE_TIME_STAMP" timestamp DEFAULT NULL,
   "DATA_SOURCE_NAME" varchar(32) DEFAULT NULL,
   "DEVICE_ID" varchar(32) DEFAULT NULL,
   "V1" real DEFAULT NULL,
   "V2" real DEFAULT NULL,
   "V3" real DEFAULT NULL,
PRIMARY KEY (ID)
);

Подготовьте хранимую процедуру в базе данных и выдайте права "Execute" для пользователя. Следующий пример демонстрирует хранимую процедуру, которая проверяет входящие данные и записывает их в таблицу.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS(SELECT 1 FROM   INFORMATION_SCHEMA.ROUTINES WHERE  ROUTINE_NAME = 'data_insert')
 DROP PROCEDURE data_insert
GO

CREATE PROCEDURE [dbo].[data_insert]
  @DATE_TIME_STAMP datetime,
  @DATA_SOURCE_NAME nvarchar(32),
  @DEVICE_ID nvarchar(32),
  @V1 float,
  @V2 float,
  @V3 float
WITH EXECUTE AS OWNER
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 IF (@V1 > 100) OR (@V2 > 100) OR (@V3 > 100) BEGIN
   RETURN;
 END;

 UPDATE DATA SET
    DATE_TIME_STAMP = @DATE_TIME_STAMP,
    V1 = @V1,
    V2 = @V2,
    V3 = @V3
 WHERE
   DATA_SOURCE_NAME = @DATA_SOURCE_NAME AND DEVICE_ID = @DEVICE_ID;

END;
GO

DROP PROCEDURE IF EXISTS `data_insert`;


CREATE PROCEDURE `data_insert`(
        IN `DATE_TIME_STAMP` DATETIME,
        IN `DATA_SOURCE_NAME` VARCHAR(32),
        IN `DEVICE_ID` VARCHAR(32),
        IN `V1` DOUBLE,
        IN `V2` DOUBLE,
        IN `V3` DOUBLE)
BEGIN
 SET @DATE_TIME_STAMP = DATE_TIME_STAMP;
 SET @DATA_SOURCE_NAME = DATA_SOURCE_NAME;
 SET @DEVICE_ID = DEVICE_ID;
 SET @V1 = V1;
 SET @V2 = V2;
 SET @V3 = V3;

 IF (@V1 <= 100) AND (@V2 <= 100) AND (@V3 <= 100) THEN
  
    UPDATE `DATA` SET
     `DATE_TIME_STAMP` = IFNULL(@DATE_TIME_STAMP, NOW()),
     `V1` = @V1,
     `V2` = @V2,
     `V3` = @V3
    WHERE
     `DATA_SOURCE_NAME` = @DATA_SOURCE_NAME AND `DEVICE_ID` = @DEVICE_ID AND;

 END IF;

END;
-- split line


CREATE OR REPLACE FUNCTION data_insert(
  IN DATE_TIME_STAMP_1 timestamp,
  IN DATA_SOURCE_NAME_1 varchar(32),
  IN DEVICE_ID_1 varchar(32),
  IN V1_1 real,
  IN V2_1 real,
  IN V3_1 real)
RETURNS void SECURITY DEFINER AS $$

BEGIN
 IF (V1_1 > 100) OR (V2_1 > 100) OR (V3_1 > 100) THEN
  RETURN;
 END IF;

 UPDATE DATA SET
   DATE_TIME_STAMP = DATE_TIME_STAMP_1,
   V1 = V1_1,
   V2 = V2_1,
   V3 = V3_1
 WHERE
   DATA_SOURCE_NAME = DATA_SOURCE_NAME_1 AND DEVICE_ID = DEVICE_ID_1;

END;
$$ LANGUAGE plpgsql;
-- split line

Выберите и настройте модуль экспорта данных "SQL Database Professional" для вызова хранимой процедуры на вашем сервере (рис. 1).

Выбор модуля SQL Database
Рис. 1. Выбор модуля SQL Database.

Перейдите в окно настройки модуля "SQL Database Professional" и выберите страницу "Режим соединения" на левой панели (рис. 2)

Активация подключения
Рис. 2. Модуль SQL Database. Активация подключения.

  1. Отключите опцию "Временно отключено" (поз. 1).
  2. Опция №2 указывает программе всегда поддерживать активное соединение с базой данных. Это увеличивает производительность при большом потоке данных.
  3. Опция №3 активирует режим автоматического переподключения к базе при потере связи с ней.

На второй странице "Параметры соединения" (рис. 3) вы можете выбрать тип базы данных и настроить параметры подключения к ней. Обратитесь к руководству по модулю SQL Database для подробных разъяснений. Вы должны успешно протестировать подключение к базе кликнув на кнопке "Проверить связь" (поз. 2), перед тем как переходить к следующему шагу.

Параметры соединения
Рис. 3. Параметры соединения.

На третьей странице "Обработка ошибок" (рис. 4) выберите как программа должна реагировать на ошибки, возникающие при записи данных в БД.

Обработка ошибок
Рис. 5. Обработка ошибок.

Опция №2 позволяет активировать запись во временный файл, когда база данных не доступна. А затем восстановить данные из файла, когда удастся подключиться к базе данных снова.

7. Последняя страница "Очередь SQL" (рис. 6) очень важна. Вы должны добавить SQL запрос и связать параметры SQL запроса и переменные парсера.

Очередь SQL
Рис. 7. Очередь SQL.

Для добавления SQL запроса в очередь кликните на кнопке "Действие → Добавить SQL в очередь" (рис. 7, поз. #2), а затем укажите текст SQL запроса в SQL редакторе (рис. 8) кликнув на поле "Текст SQL", а затем на кнопке с точками в нем (рис. 7).

SQL редактор
Рис. 8. SQL редактор.

Укажите текст SQL запроса для вызова хранимой процедуры в соответствии с синтаксисом языка SQL вашей базы данных. Параметры вида ":TIMESTAMP" или ":V1" позволяют передать значение переменной парсера в SQL запрос, и далее в базу. Каждый параметр затем появляется в очереди (рис. 7), где вы должны указать тип данных (поле Тип данных) и привязать переменную парсера (поле Имя переменной парсера). Тип данных должен соответствовать типу данных соответствующего параметра вашей хранимой процедуры. Оба типа данных должны соответствовать типу данных значения переменной парсера.

exec data_insert :TIMESTAMP, :DS_NAME, :DEVICE_ID, :V1, :V2, :V3

call data_insert(:timestamp, :ds_name, :device_id, :v1, :v2, :v3);

select data_insert(:timestamp, :ds_name, :device_id, :v1, :v2, :v3);

Кликните на кнопке "OK" для того чтобы закрыть все диалоговые окна и сохранить все настройки.

Если модуль SQL Database Professional настроен правильно, то в главном окне программы будут отображаться сообщения об успешной записи данных в БД.

Частые вопросы

Как много параметров может быть у хранимой процедуры?

Количество зависит от типа и версии базы данных SQL. Оптимальный максимум - это 64 параметра.

Тип данных параметра моей хранимой процедуры не точно совпадает с типом данных значения. Именно такого типа данных нет в вашем списке. Что делать?

Модуль может автоматически преобразовывать близкие типы данных. Например, целые числа с разной точностью и разрядностью (возможна потеря точности), целые числа в вещественные, любые числа в строку.

Сопутствующие статьи:

USB HID Logger. Дополнительные материалы:

Мониторинг USB порта Мониторинг COM порта