Alpha.Community

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

Получение данных из БД SQL

Для выполнения SQL-запросов используйте набор компонентов из юнита SQL:

  • Источник SQL — управляет подключением к базе данных: устанавливает и разрывает соединение.
  • Запрос источника SQL — выполняет SQL-запросы к базе данных.

Компоненты Источник SQL и Запрос источника SQL работают через ODBC-драйверы и совместимы с популярными системами управления базами данных (СУБД):

  • PostgreSQL
  • MySQL
  • Microsoft SQL Server
  • Oracle Database
  • Другие СУБД с поддержкой ODBC

Результаты SQL-запросов могут быть представлены в проекте только в виде таблицы, так как таблицы являются основным способом организации и хранения данных в SQL базах данных. Чтобы отобразить результаты SQL-запросов, подключите компонент Запрос источника SQL к компоненту Таблица. Подробный пример настройки подключения и выполнения запросов рассмотрен ниже.

Как запросить данные из БД (на примере PostgreSQL)

В этом разделе рассмотрен демонстрационный проект (скачать его можно здесь ), в котором в качестве примера базы данных используется PostgreSQL, но компоненты Источник SQL и Запрос источника SQL универсальны и поддерживают большинство популярных СУБД (PostgreSQL, MySQL, Microsoft SQL Server и другие).

В проекте показано как:

  • Настроить источник подключения к базе данных.

  • Подготовить таблицу для вывода результатов SQL-запросов.
  • Выводить информацию в текстовое поле о количестве затронутых изменениями строк в базе данных.
  • Выполнить SQL-запрос из редактора текста по нажатию кнопки и отобразить результаты в таблице.

  • Разорвать соединение с базой данных после завершения всех операций с ней.

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

Настройка подключения к базе данных

  1. Добавьте на экранную форму компонент Источник SQL.
  2. В свойстве Строка подключения укажите параметры подключения к вашей базе данных. Можно использовать один из следующих форматов:
    • Через системный источник данных ODBC (DSN):

      DSN — это предопределённый набор настроек подключения, сохранённый в системе, который можно использовать вместо явного указания всех параметров в строке подключения.

      DSN=ИмяВашегоDSN
    • Явная строка подключения (без DSN):

      Driver={PostgreSQL};
      Server=localhost;
      Port=5434;
      Database=postgres;
      Uid=postgres;
      Pwd=password

  3. В свойстве Состояние соединения с БД укажите объект Состояние соединения SQL для отслеживания состояния подключения. Для этого дважды щелкните левой кнопкой мыши по свойству.

Подготовка таблицы для отображения результатов

  1. Добавьте на экранную форму компонент Таблица, который будет отображать результаты запроса.

  2. Добавьте компонент Таблица: модель данных дочерним компоненту Таблица.

  3. Укажите ссылку на добавленный компонент Таблица: модель данных. Для этого нажмите правой кнопкой мыши по свойству Модель данных → Сослаться → TableModel_1.

  4. Добавьте компонент Запрос источника SQL дочерним компоненту Таблица: модель данных. В свойстве Источник SQL укажите ссылку на добавленный ранее Источник SQL. Для этого нажмите правой кнопкой мыши по свойству Источник SQL → Сослаться → SqlSource_1.

  5. Добавьте к компоненту Таблица дочерние компоненты Таблица: столбец. Вы можете настроить отображение в таблице на форме всех столбцов из базы данных либо только отдельных столбцов. Для каждого объекта Таблица: столбец укажите идентификатор столбца в свойстве Идентификатор, который соответствует идентификатору (имени) столбцов в базе данных.

Установка соединения с базой данных

  1. Добавьте на экранную форму компонент Кнопка, при нажатии на которую будет устанавливаться соединение с базой данных.
  2. Добавьте на экранную форму компонент Текст, в который будет выводиться статус подключения (успех или ошибка).

  3. Настройте обработчик ButtonPressed для кнопки:

    // Установить соединение с базой данных
    SqlSource_1.Connect();
  4. Для отслеживания состояния подключения настройте обработчик события OnConnectionStateChanged у компонента Источник SQL:

    // Проверяем код состояния (0 - не подключено, 1 - подключение, 2 - подключено, 3 - отключение)
    if (state.Code == 2) {
        // Соединение успешно установлено
        Text_1.Text = "Соединение установлено\n";
    } else if (state.Code == 0) {
        // Соединение не установлено
        Text_1.Text = "Соединение не установлено\n";
    }

Выполнение SQL-запроса

  1. Добавьте на экранную форму два компонента:

    1. Редактор текста — в него будет вводиться текст SQL-запроса.

    2. Кнопка — при нажатии на неё будет выполняться запрос и обновляться таблица.

  2. Настройте обработчик ButtonPressed у кнопки:

    // Проверка состояния соединения (2 - Connected)
    if (SqlSource_1.ConnectionState.Code == 2) {
       // Получаем текст запроса из текстового поля
        sqlQuery: var = TextEdit_1.Text;
        
        // Выполнить SQL запрос напрямую
        Table_1.TableModel_1.SqlSourceQuery_1.ExecuteDirectly(sqlQuery);
        
        // Заполнить таблицу полученными данными из БД
        Table_1.TableModel_1.BeginReadAsync();
    } else {
        Text_1.Text = "Ошибка: соединение не установлено\n";
    }

    После запуска в рантайме введите SQL-запрос в редактор текста и нажмите кнопку для его выполнения. Результаты запроса отобразятся в таблице.

    Пример SQL-запроса:

    SELECT user_id, product_name, price, status FROM orders

Отображение статуса операций

Чтобы в процессе выполнения SQL-запросов к базе данных получать информацию о результатах операций, подготовьте текстовое поле и настройте событие OnRequestStatusChanged компонента Запрос источника SQL. Пример кода JavaScript:

// Проверяем, что запрос завершён (код статуса 4 - Finished)
if (requestStatusCode == 4) {
    // Парсим JSON стандартной функцией JavaScript
    var statusData = JSON.parse(requestStatusData);
    
    // Получаем код результата (0 - успех, 1 - успех с предупреждениями, 2 - ошибка)
    var resultCode = statusData.result_code;
    
    if (resultCode == 0) {
        // Успешное выполнение
        var rowCount = statusData.result_description.row_count;
        Text_2.Text = "Успех: обработано строк - " + rowCount + "\n";
    } else if (resultCode == 1) {
        // Выполнено с предупреждениями
        Text_2.Text = "Выполнено с предупреждениями\n";
    } else {
        // Ошибка выполнения
        Text_2.Text = "Ошибка выполнения запроса\n";
    }
}

Этот код обрабатывает событие завершения запроса и выводит информацию о результате выполнения. Параметр requestStatusCode содержит код статуса (4 означает завершение операции), а requestStatusData содержит детальную информацию в формате JSON.

Разрыв соединения с базой данных

После завершения всех операций с базой данных рекомендуется разрывать соединение с ней. Это действие позволяет эффективно освободить системные ресурсы и избежать потенциальных утечек памяти.

Добавьте на экранную форму компонент Кнопка, при нажатии на которую будет разрываться соединение с базой данных. В обработчике ButtonPressed добавьте следующий код:

// Разорвать соединение с базой данных
SqlSource_1.Disconnect();

Этот код вызывает метод Disconnect() у экземпляра Источник SQL, который разрывает соединение.

Примеры выполнения запросов к базе данных

Ниже рассмотрены несколько примеров выполнения базовых SQL-запросов для вставки, изменения, чтения и удаления данных из БД.

Для избежания возможных проблем, связанных с дублированием идентификаторов столбцов в результирующей выборке SQL-запроса, рекомендуется использовать уникальные идентификаторы столбцов при их выборе. Если в SQL-запросе присутствуют операции объединения таблиц или другие ситуации, когда имена столбцов могут дублироваться, то рекомендуется использовать SQL псевдонимы (временные имена) для столбцов, чтобы обеспечить уникальность идентификаторов.

Пример правильного использования SQL псевдонимов:

Вместо:

SELECT * FROM nodes_history h JOIN nodes n ON n.NodeId=h.NodeId WHERE n.TagName ='Test.Parameter' AND
(h.time BETWEEN '2023-03-02 07:39:00' AND '2023-03-02 07:39:01')

Рекомендуется:

SELECT n.nodeid, h.valint, h.quality, h.actualtime FROM nodes_history h JOIN nodes n ON
n.NodeId=h.NodeId WHERE n.TagName ='Test.Parameter' AND
(h.time BETWEEN '2023-03-02 07:39:00' AND '2023-03-02 07:39:01')

Перед выполнением приведённых ниже примеров убедитесь, что соединение с базой данных уже установлено методом:

SqlSource_1.Connect();

Не вызывайте Connect() и Disconnect() для каждого запроса.

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

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

SqlSource_1.Disconnect();

Как добавить данные в БД (INSERT)

В этом примере добавляется новая запись в таблицу testtables для столбцов Id, Value, Category, Text с указанными значениями.

Добавить в таблицу testtables новую запись для столбцов Id = 1, Value = 6, Category = Even, Text = Six

// Добавляет запись в таблицу testtables
if (SqlSource_1.ConnectionState.Code == 2) {
    Table_1.TableModel_1.SqlSourceQuery_1.ExecuteDirectly(
        "INSERT INTO testtables (Id, Value, Category, Text) VALUES (1, 6, 'Even', 'Six')"
    );
    
    // Заполнить таблицу данными, полученными из БД
    Table_1.TableModel_1.BeginReadAsync();
} else {
    Text_1.Text = "Ошибка: соединение не установлено";
}

Как обновить данные в БД (UPDATE)

В этом примере обновляется значение столбца Value в строке, где значение столбца Id равно 2.

Обновить в таблице testtables значение столбца Value на 343, где Id = 2.

// Проверка состояния соединения с БД (2 - подключено)
if (SqlSource_1.ConnectionState.Code == 2) {
    // Выполнить запрос UPDATE
    Table_1.TableModel_1.SqlSourceQuery_1.ExecuteDirectly(
        "UPDATE testtables SET Value = 343 WHERE Id = 2"
    );
    
     // Заполнить таблицу данными, полученными из БД
    Table_1.TableModel_1.BeginReadAsync();
} else {
    Text_1.Text = "Ошибка: соединение не установлено";
}

Как получить данные из БД (SELECT)

В этом примере получены все данные из таблицы testtables.

Получить значения всех столбцов таблицы testtables.

// Проверка состояния соединения с БД (2 - подключено)
if (SqlSource_1.ConnectionState.Code == 2) {
    // Выполнить запрос SELECT
    Table_1.TableModel_1.SqlSourceQuery_1.ExecuteDirectly(
        "SELECT * FROM testtables"
    );
    
    // Заполнить таблицу данными, полученными из БД
    Table_1.TableModel_1.BeginReadAsync();
} else {
    Text_1.Text = "Ошибка: соединение не установлено";
}

В этом примере получены только записи, где Category = 'Even'.

Получить только записи, где Category = 'Even'

// Проверка состояния соединения с БД (2 - подключено)
if (SqlSource_1.ConnectionState.Code == 2) {
    Table_1.TableModel_1.SqlSourceQuery_1.ExecuteDirectly(
        "SELECT * FROM testtables WHERE Category = 'Even'"
    );

// Заполнить таблицу данными, полученными из БД Table_1.TableModel_1.BeginReadAsync(); } else { Text_1.Text = "Ошибка: соединение не установлено"; }

Как удалить данные из БД (DELETE)

В этом примере удаляются все строки из таблицы testtables, где значение столбца Category равно Even.

Удалить все строки из таблицы testtables, где значение столбца Category равно Even.

// Проверка состояния соединения с БД (2 - подключено)
if (SqlSource_1.ConnectionState.Code == 2) {
    // Выполнить запрос DELETE
    Table_1.TableModel_1.SqlSourceQuery_1.ExecuteDirectly(
        "DELETE FROM testtables WHERE Category = 'Even'"
    );
    
    // Заполнить таблицу данными, полученными из БД
    Table_1.TableModel_1.BeginReadAsync();
} else {
    Text_1.Text = "Ошибка: соединение не установлено";
}

Команда DELETE без условия WHERE удалит все строки из таблицы. Всегда проверяйте условия перед выполнением.

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

Настройка переменных окружения для взаимодействия с SQL базой данных на Linux

При взаимодействии с SQL базой данных через Alpha.HMI на операционных системах семейства Linux необходимо предварительно настроить переменные окружения ODBCSYSINI и LD_LIBRARY_PATH. Эти переменные окружения необходимы для того, чтобы система знала, где искать конфигурационные файлы для корректной работы с SQL базой данных через ODBC (интерфейс между базой данных и приложением, взаимодействующим с ней).

Самый распространенный способ настройки переменных окружения на Linux- это добавить переменные в системный файл .bashrc. Для этого:

  1. Откройте файл .bashrc для редактирования в текстовом редакторе (например, nano).

    nano ~/.bashrc
  2. Добавьте в файл следующие строки:

    # Эта строка устанавливает переменную ODBCSYSINI в значение "/etc"
    export ODBCSYSINI="/etc"
    # Эта строка устанавливает переменную LD_LIBRARY_PATH в значение "/usr/lib/x86_64-linux-gnu/odbc"
    export LD_LIBRARY_PATH="/usr/lib/x86_64-linux-gnu/odbc"
  3. Сохраните файл, нажав Ctrl + X, затем Y, затем Enter.
  4. Обновите текущий сеанс оболочки, чтобы изменения вступили в силу:

    source ~/.bashrc