Исправление проблемы обновления записей через представления в Delphi и ADO: изолированные таблицы и разрешения

Delphi , Базы данных , ADO

В этой статье мы рассмотрим проблему обновления записей через представления в Delphi с использованием ADO компонентов, а также предложим различные подходы к её решению. Мы будем работать с Delphi 10.1 (Berlin), MS SQL Server 2019 и изолированными таблицами, доступными только через представления.

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

У вас есть приложение на VCL, написанное с использованием Delphi 10.1 (Berlin), которое использует ADO компоненты для работы с MS SQL Server 2019. В SQL Server у вас есть база данных (MY_DB), в которой есть таблица (MY_TABLE) и на основе этой таблицы создана VIEW (MY_VIEW). Эта view просто отображает все столбцы из таблицы. SQL Server login (и пользователь MY_DB) который подключается к приложению, имеет разрешение на SELECT только на представлении. В приложении используется TADOQuery (ADOQuery1) для поиска записей, отображаемых в DBGrid, и любая запись открывается на отдельной форме (Form2) для просмотра и обновления. SQL запрос ADOQuery1 простой: SELECT * FROM MY_VIEW. Вы не сталкиваетесь с проблемами при выполнении ADOQuery1.Open в приложении во время выполнения. Однако, когда пользователь открывает Form2 для обновления записи, вы получаете следующую ошибку:

The SELECT permission was denied on the object '<MY_TABLE>', database 'MY_DB', schema 'MY_SCHEMA'.

и выбранная запись не обновляется. Если вы предоставите SELECT разрешение на таблицу MY_TABLE для приложения, то всё будет работать корректно. Однако вы хотите избежать предоставления SELECT разрешения на таблицу, чтобы изолировать её.

Попытки решения

Вы уже пробовали использовать методы для обновления записи, такие как UpdateCursorPos, Resync и другие. Вот пример вашего кода:

form1.ADOQuery1.UpdateCursorPos;
form1.ADOQuery1.Recordset.Resync(adAffectCurrent, adResyncAllValues);
form1.ADOQuery1.Resync([rmExact, rmCenter]);

Эти методы не работают из-за отсутствия разрешения на таблицу MY_TABLE. Решение проблемы заключается в том, чтобы избежать необходимости предоставления разрешения на таблицу MY_TABLE, сохраняя её изоляцию.

Решение проблемы

Для решения этой проблемы можно использовать процедуры или функции, которые будут обновлять запись напрямую в представлении. Это позволит избежать необходимости предоставления разрешения на таблицу MY_TABLE. Мы можем создать хранимую процедуру, которая будет обновлять запись в представлении MY_VIEW.

Создание хранимой процедуры для обновления записи

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

CREATE PROCEDURE UpdateMyViewRecord
    @POLICY_ID INT,
    @POLICY_NAME NVARCHAR(200),
    @POLICY_DESC NVARCHAR(1000),
    @STATUS_ID INT,
    @RULE_EVAL INT,
    @AUDIT_OPTION INT,
    @FORMULA NVARCHAR(2000),
    @DEBUG_LOG INT,
    @USER_APPLY INT,
    @USE_CACHE INT,
    @DATE_UPD DATETIME,
    @USER_UPD NVARCHAR(200)
AS
BEGIN
    UPDATE MY_TABLE
    SET POLICY_NAME = @POLICY_NAME,
        POLICY_DESC = @POLICY_DESC,
        STATUS_ID = @STATUS_ID,
        RULE_EVAL = @RULE_EVAL,
        AUDIT_OPTION = @AUDIT_OPTION,
        FORMULA = @FORMULA,
        DEBUG_LOG = @DEBUG_LOG,
        USER_APPLY = @USER_APPLY,
        USE_CACHE = @USE_CACHE,
        DATE_UPD = @DATE_UPD,
        USER_UPD = @USER_UPD
    WHERE POLICY_ID = @POLICY_ID;
END;
GO

Обновление записи в Delphi через хранимую процедуру

Теперь мы можем использовать эту хранимую процедуру в Delphi для обновления записи. Мы будем использовать TADOQuery для выполнения хранимой процедуры с параметрами.

procedure TForm1.UpdateRecord;
var
  Params: TADOParameters;
  Param: TADOParameter;
  i: Integer;
begin
  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'EXEC UpdateMyViewRecord ';
  Params := ADOQuery1.Parameters;
  for i := 0 to Params.Count - 1 do
  begin
    Param := Params.ParamByName(Params.ParamNames[i]);
    Param.Value := Param.Value; // Просто для обновления значения параметра
  end;
  ADOQuery1.ExecProc;
  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'SELECT * FROM MY_VIEW';
  ADOQuery1.Open;
end;

Альтернативное решение: использование триггеров

Если вы хотите избежать использования хранимых процедур, вы можете использовать триггеры для обновления записи. Триггер будет автоматически обновлять запись в таблице MY_TABLE при обновлении записи в представлении MY_VIEW.

CREATE TRIGGER trg_UpdateMyViewRecord
ON MY_VIEW
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE MY_TABLE
    SET POLICY_NAME = i.POLICY_NAME,
        POLICY_DESC = i.POLICY_DESC,
        STATUS_ID = i.STATUS_ID,
        RULE_EVAL = i.RULE_EVAL,
        AUDIT_OPTION = i.AUDIT_OPTION,
        FORMULA = i.FORMULA,
        DEBUG_LOG = i.DEBUG_LOG,
        USER_APPLY = i.USER_APPLY,
        USE_CACHE = i.USE_CACHE,
        DATE_UPD = i.DATE_UPD,
        USER_UPD = i.USER_UPD
    FROM INSERTED i
    WHERE MY_TABLE.POLICY_ID = i.POLICY_ID;
END;
GO

Обновление записи в Delphi через триггер

Теперь мы можем использовать TADOQuery для обновления записи в представлении MY_VIEW, и триггер автоматически обновит запись в таблице MY_TABLE.

procedure TForm1.UpdateRecord;
begin
  ADOQuery1.SQL.Text := 'UPDATE MY_VIEW SET 
POLICY_NAME = :POLICY_NAME, 
POLICY_DESC = :POLICY_DESC, 
STATUS_ID = :STATUS_ID, 
RULE_EVAL = :RULE_EVAL, 
AUDIT_OPTION = :AUDIT_OPTION, 
FORMULA = :FORMULA, 
DEBUG_LOG = :DEBUG_LOG, 
USER_APPLY = :USER_APPLY, 
USE_CACHE = :USE_CACHE, 
DATE_UPD = :DATE_UPD, 
USER_UPD = :USER_UPD 
WHERE POLICY_ID = :POLICY_ID';
  ADOQuery1.ParamByName('POLICY_NAME').Value := 'Новое имя';
  ADOQuery1.ParamByName('POLICY_DESC').Value := 'Новое описание';
  ADOQuery1.ParamByName('STATUS_ID').Value := 1;
  ADOQuery1.ParamByName('RULE_EVAL').Value := 2;
  ADOQuery1.ParamByName('AUDIT_OPTION').Value := 1;
  ADOQuery1.ParamByName('FORMULA').Value := 'Новый формула';
  ADOQuery1.ParamByName('DEBUG_LOG').Value := 1;
  ADOQuery1.ParamByName('USER_APPLY').Value := 1;
  ADOQuery1.ParamByName('USE_CACHE').Value := 1;
  ADOQuery1.ParamByName('DATE_UPD').Value := Now;
  ADOQuery1.ParamByName('USER_UPD').Value := 'Новый пользователь';
  ADOQuery1.ParamByName('POLICY_ID').Value := 1;
  ADOQuery1.ExecSQL;
  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'SELECT * FROM MY_VIEW';
  ADOQuery1.Open;
end;

Заключение

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

Создано по материалам из источника по ссылке.

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

Комментарии и вопросы

Получайте свежие новости и обновления по Object Pascal, Delphi и Lazarus прямо в свой смартфон. Подпишитесь на наш Telegram-канал delphi_kansoftware и будьте в курсе последних тенденций в разработке под Linux, Windows, Android и iOS

:: Главная :: ADO ::