Решение проблемы с получением идентификатора после INSERT в Delphi XE с использованием DBExpress

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

Разработчики, работающие с компонентами баз данных в Delphi, иногда сталкиваются с проблемой получения идентификатора (ID) записи после выполнения операции INSERT в базу данных. Это может быть связано с особенностями использования компонентов TSQLQuery и ExecSQL. В данной статье мы рассмотрим, почему может возникать ошибка "cursor not returned query" и как её можно решить.

Проблема

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

function TServerDBUtils.ExecuteQueryWithIdentity(ASQLConn: TSQLConnection): Integer;
var
  newSQLQuery: TSQLQuery;
begin
  Result := -1;
  newSQLQuery := TSQLQuery.Create(nil);
  try
    with newSQLQuery do
    begin
      SQLConnection := ASQLConn;
      SQL.Clear;
      SQL.Add('Insert into SampleTable(uomname) values(' + QuotedStr('bag') + ')');
      SQL.Add('Select Scope_Identity()');
      Open;
      Result := Fields[0].AsInteger;
    end;
  finally
    FreeAndNil(newSQLQuery);
  end;
end;

При выполнении данного кода возникла ошибка "cursor not returned query", что указывает на проблему с использованием метода Open после выполнения операции INSERT.

Альтернативный способ получения идентификатора

Пользователь также попытался использовать метод ExecSQL для выполнения INSERT, а затем Open для получения идентификатора, но результаты были null, возможно, из-за разных сессий.

Решение с использованием @@Identity

Оказалось, что использование @@Identity позволяет получить корректный идентификатор, так как оно не зависит от сессии:

SQL.Add('Insert into SampleTable(uomname) values(' + QuotedStr('bag') + ')');
ExecSQL;

SQL.Clear;
SQL.Add('Select @@Identity');
Open;
Result := Fields[0].AsInteger;

Однако, есть нюанс: если на таблице установлен триггер, который вставляет записи в другую таблицу, @@Identity вернёт идентификатор последней вставленной записи триггером.

Подтвержденное решение

Для более элегантного решения на SQL Server можно использовать OUTPUT Clause, который позволяет возвращать не только один ID, но и все новые идентификаторы в случае мульти-вставки:

INSERT into aTable (aField)
OUTPUT Inserted.ID
Values ('SomeValue')

Если на таблице установлен триггер, необходимо определить целевую таблицу для OUTPUT:

DECLARE @tmp table (ID int)
INSERT into aTable (aField)
OUTPUT Inserted.ID into @tmp
Values ('SomeValue')
Select * from @tmp

Также рекомендуется использовать параметризованные запросы вместо жёстко закодированных значений.

С помощью TSQLQuery добавление SET NOCOUNT ON перед запросом предотвратит ошибку "cursor not returned query" и позволит получить ожидаемый результат:

begin
  SQLQuery1.SQL.Text := 'SET NOCOUNT ON' + #13#10 +
                         'DECLARE @tmp table (ID int)' + #13#10 +
                         'INSERT into aTable (aField)' + #13#10 +
                         'OUTPUT Inserted.ID into @tmp' + #13#10 +
                         'Values (:P)' + #13#10 +
                         'Select * from @tmp';
  SQLQuery1.Params.ParamByName('P').Value := 'SomeText';
  SQLQuery1.Open;
  ShowMessage(SQLQuery1.Fields[0].AsString);
end;

Альтернативное решение

Другой способ заключается в использовании ExecSQL для выполнения INSERT, а затем Open для запроса Scope_Identity, используя алиас для поля, чтобы убедиться, что вы работаете с правильным идентификатором:

with newSQLQuery do
begin
  SQLConnection := ASQLConn;
  SQL.Clear;
  SQL.Add('Insert into SampleTable(uomname) values(' + QuotedStr('bag') + ')');
  ExecSQL;

  SQL.Clear;
  SQL.Add('Select Scope_Identity() as id');
  Open;

  if not Eof then
    Result := FieldByName('id').AsInteger;
end;

Заключение

При работе с TSQLQuery и ExecSQL в Delphi XE с использованием DBExpress важно помнить, что Open предназначен для запросов, возвращающих наборы данных, а ExecSQL — для операций вставки, обновления и удаления. Использование SET NOCOUNT ON и OUTPUT Clause может помочь решить проблему с получением идентификатора после INSERT. Параметризация запросов и использование алиасов для полей также могут быть полезными практиками.

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

Разработчики в Delphi XE столкнулись с проблемой получения идентификатора записи после операции `INSERT` с использованием компонентов `TSQLQuery` и `DBExpress` и рассматривают различные способы решения этой проблемы.

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

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

:: Главная :: SQL ::