Проблема возврата автоинкрементного ключа при вставке данных в SQL Server 2019 с использованием TADOQuery в Delphi 10.4 Update 2

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

Разработчики, работающие с базами данных и языками программирования вроде Object Pascal, часто сталкиваются с необходимостью выполнения операций обновления или вставки записей в одной транзакции. В частности, задача может состоять в обновлении записи, если она уже существует, и вставке новой, если обновление не требуется. В SQL Server 2019 такая операция может быть выполнена с помощью одного запроса, который включает в себя инструкции UPDATE и INSERT.

Однако, при использовании TADOQuery в Delphi 10.4 Update 2, разработчик столкнулся с проблемой возврата значения автоинкрементного ключа при вставке данных. В его SQL-запросе использовался параметр OUTPUT для получения значения ключа, но при выполнении второго подраздела (INSERT) результат не возвращался.

Вот структура таблицы Artikel, используемой в запросе:

CREATE TABLE [dbo].[Artikel]
(
    [SuchBeg] [varchar](25) NULL,
    [ArtNr] [varchar](25) NULL,
    [IndexField] [bigint] IDENTITY(1,1) NOT NULL,
    PRIMARY KEY CLUSTERED ([IndexField] ASC)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

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

UPDATE Artikel
SET [SuchBeg] = 'TEST', [ArtNr] = '19904.S'
OUTPUT INSERTED.[IndexField]
WHERE [ArtNr] = '19904.S'
IF @@ROWCOUNT = 0
INSERT INTO Artikel ([SuchBeg], [ArtNr])
OUTPUT Inserted.[IndexField]
VALUES ('TEST', '19904.S');

При выполнении данного запроса через TADOQuery в Delphi, результат второго подраздела (INSERT) не возвращался, и разработчик не мог получить значение автоинкрементного ключа.

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

Для решения данной проблемы было предложено использовать табличную переменную для хранения результатов запроса OUTPUT. В SQL-запросе это выглядело бы следующим образом:

DECLARE @Output TABLE (IndexField BIGINT);
UPDATE Artikel SET [SuchBeg] = 'TEST', [ArtNr] = '19904.S' OUTPUT INSERTED.[IndexField] INTO @Output WHERE [ArtNr] = '19904.S'
IF @@ROWCOUNT = 0
INSERT INTO Artikel ([SuchBeg], [ArtNr]) Output Inserted.[IndexField] INTO @Output VALUES ('TEST', '19904.S');
SELECT * FROM @Output;

Также было предложено использовать параметр OUTPUT напрямую в запросе с последующим присвоением значения локальной переменной:

Declare @IndexField Bigint;
UPDATE Artikel
SET [SuchBeg] = 'TEST', @IndexField = [IndexField]
WHERE [ArtNr] = '19904.5'
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Artikel ([SuchBeg], [ArtNr])
    VALUES ('TEST', '19904.5');

    SET @IndexField = SCOPE_IDENTITY();
END;
SELECT @IndexField;

Для обеспечения ACID-совместимости транзакции, запросы можно обернуть в транзакцию с использованием hint-ов:

SET XACT_ABORT ON;
BEGIN TRAN;
UPDATE Artikel WITH (UPDLOCK, HOLDLOCK)
SET [SuchBeg] = 'TEST', @IndexField = [IndexField]
WHERE [ArtNr] = '19904.S'
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Artikel ([SuchBeg], [ArtNr])
    VALUES ('TEST', '19904.S');

    SET @IndexField = SCOPE_IDENTITY();
END;
COMMIT;
SELECT @IndexField;

Выводы

При работе с TADOQuery в Delphi для выполнения операций обновления или вставки данных в SQL Server 2019, необходимо учитывать особенности работы с параметром OUTPUT. Использование табличных переменных или прямых параметров OUTPUT может помочь в решении проблемы возврата автоинкрементного ключа. Также важно обеспечить корректность транзакций, особенно при работе с данными, которые должны быть согласованы по правилам ACID.

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

Разработчик столкнулся с проблемой в Delphi 10.4 Update 2 при возврате автоинкрементного ключа после вставки данных в SQL Server 2019 через TADOQuery, требуя решения для корректной работы параметра OUTPUT в транзакции обновления/вставки.

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

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

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