Возвращение ID при вставке

Обсуждение возникших проблем, предложений и ошибок SDAC компонентов
Ответить
vso
Сообщения: 39
Зарегистрирован: Чт 24 янв 2013 11:08

Возвращение ID при вставке

Сообщение vso » Чт 27 мар 2014 14:23

Всем добрый день!

есть такой SQL:

Код: Выделить всё

SELECT Lv.ID_LISTVOLUME,
       Lv.NAME,
       Lv.PAGE,
       Lv.TYPEPAGE,
       Lv.NOTICE,
       Lv.REF_REV AS ID_REVISIONS
FROM dbo.LISTVOLUME Lv
WHERE Lv.REF_REV = :ID_REVISIONS
ORDER BY PAGE
и есть такой код в SQLInsert:

Код: Выделить всё

INSERT INTO LISTVOLUME (NAME, NOTICE, REF_REV)
VALUES (:Name, :NOTICE, :ID_REVISIONS)
SET :ID_LISTVOLUME = scope_identity()
так же в документации написано
Use the ReturnParams property to return the new values of fields to dataset after insert or update.When ReturnParams is True, OUT parameters of the SQLInsert and SQLUpdate statements is assigned to corresponding fields. The default value is False.
Но суди по мониторингу (DBMonitor) ID_LISTVOLUME = NULL, подскажите как заставить возвращать только что вставленный ID

vso
Сообщения: 39
Зарегистрирован: Чт 24 янв 2013 11:08

Re: Возвращение ID при вставке

Сообщение vso » Чт 27 мар 2014 15:51

Разобрался, это шутил триггер ISTEAD OF INSERT, почему то если есть такой триггер функция scope_identity() не работает, пришлось воспользоваться вместо нее - IDENT_CURRENT('LISTVOLUME').

Но это не хорошее решение

Alexp
Devart Team
Сообщения: 349
Зарегистрирован: Пн 27 дек 2010 10:34

Re: Возвращение ID при вставке

Сообщение Alexp » Пт 28 мар 2014 09:53

Добрый день,

Это поведение обусловлено спецификой работы SQL Server и мы не можем повлиять на него. Более подробную информацию вы можете в MSDN.

vso
Сообщения: 39
Зарегистрирован: Чт 24 янв 2013 11:08

Re: Возвращение ID при вставке

Сообщение vso » Ср 02 апр 2014 08:30

На просторах интернета, я нашел как решить проблему
http://wiki.alphasoftware.com/Scope_Ide ... F+triggers

Правда она позволяет только в инструкции OUTPUT получить значение не равное NULL. Scope_Identity продолжает возвращать NULL.

Но когда я делаю такой запрос

Код: Выделить всё

DECLARE @Return TABLE (id int);

INSERT INTO LISTVOLUME (NAME, NOTICE, REF_REV)
OUTPUT INSERTED.ID_LISTVOLUME INTO @Return
  VALUES (:Name, :NOTICE, :ID_REVISIONS);

SELECT :ID_LISTVOLUME = id FROM @Return;
в студии (предварительно подправив синтаксис и вставив реальные значения) то результат возвращается, а TMSQuery возвращается 0?

Alexp
Devart Team
Сообщения: 349
Зарегистрирован: Пн 27 дек 2010 10:34

Re: Возвращение ID при вставке

Сообщение Alexp » Ср 02 апр 2014 10:23

Для того чтобы приведенный вами запрос возвращал значение, Вам необходимо установить
свойство ReturnParams в True, а также в событии BeforeUpdateExecute установить ParamType для параметра ID_LISTVOLUME в ptOutput.

Код: Выделить всё

  MSQuery1.Options.ReturnParams := True;

Код: Выделить всё

procedure TForm1.MSQuery1BeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  Params.ParamByName('ID_LISTVOLUME').ParamType := ptOutput;
end;

vso
Сообщения: 39
Зарегистрирован: Чт 24 янв 2013 11:08

Re: Возвращение ID при вставке

Сообщение vso » Чт 03 апр 2014 14:54

Alexp писал(а):

Код: Выделить всё

procedure TForm1.MSQuery1BeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  Params.ParamByName('ID_LISTVOLUME').ParamType := ptOutput;
end;
к сожалению это не помогло возвращается 0 и соответсвенно не работает Refresh после вставки. :(

Alexp
Devart Team
Сообщения: 349
Зарегистрирован: Пн 27 дек 2010 10:34

Re: Возвращение ID при вставке

Сообщение Alexp » Чт 03 апр 2014 15:07

проверьте что вы установили опцию

Код: Выделить всё

MSQuery1.Options.ReturnParams := True;

vso
Сообщения: 39
Зарегистрирован: Чт 24 янв 2013 11:08

Re: Возвращение ID при вставке

Сообщение vso » Чт 03 апр 2014 16:12

у меня стоит это в инспекторе объектов, не программно...

сейчас еще раз сделал запрос в MSSMS

Код: Выделить всё

DECLARE @Return TABLE (
  ID int
);

INSERT INTO LISTVOLUME ( NAME, TYPEPAGE, NOTICE, REF_REV)
OUTPUT INSERTED.ID_LISTVOLUME INTO @Return
  VALUES ('Тест', NULL, NULL, 117);

SELECT ID FROM @Return
P.S. у меня sdac 6.7.13

результат возвращает

Alexp
Devart Team
Сообщения: 349
Зарегистрирован: Пн 27 дек 2010 10:34

Re: Возвращение ID при вставке

Сообщение Alexp » Пт 04 апр 2014 07:36

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

vso
Сообщения: 39
Зарегистрирован: Чт 24 янв 2013 11:08

Re: Возвращение ID при вставке

Сообщение vso » Сб 05 апр 2014 19:11

Вот таблица:

Код: Выделить всё

CREATE TABLE dbo.LISTVOLUME (
  ID_LISTVOLUME int IDENTITY,
  NAME varchar(510) NOT NULL,
  PAGE int NOT NULL,
  TYPEPAGE int NULL,
  NOTICE varchar(255) NULL,
  REF_REV int NOT NULL,
  CONSTRAINT PK_LISTVOLUME PRIMARY KEY (ID_LISTVOLUME),
  CONSTRAINT KEY_LISTVOLUME UNIQUE (REF_REV, PAGE),
  CONSTRAINT FK_LISTVOLUME_1 FOREIGN KEY (REF_REV) REFERENCES dbo.REVISIONS (ID_REVISIONS),
  CONSTRAINT FK_LISTVOLUME_2 FOREIGN KEY (TYPEPAGE) REFERENCES dbo.TYPEPAGE (ID_PAGE_TYP)
) ON Data
это ее триггер:

Код: Выделить всё

CREATE TRIGGER trListVolume_Insert
ON dbo.[LISTVOLUME] WITH EXECUTE AS CALLER
INSTEAD OF INSERT
AS
BEGIN
  DECLARE @MPage int;

  SET NOCOUNT ON;
  
  SELECT @MPage = ISNULL(MAX(l.PAGE), 1)
  FROM LISTVOLUME l, INSERTED i
  WHERE i.REF_REV = l.REF_REV
  
  INSERT INTO LISTVOLUME (NAME, PAGE, TYPEPAGE, NOTICE, REF_REV)
    SELECT i.NAME,
          @MPage + ROW_NUMBER() OVER (ORDER BY i.PAGE),
          i.TYPEPAGE,
          i.NOTICE,
          i.REF_REV
    FROM INSERTED i;
  
  SELECT SCOPE_IDENTITY();
END
На самом деле я еще раз взглянул на то что получается в MSSMS и поня что это не результат действий OUTPUT, а результат работу выражения

Код: Выделить всё

SELECT SCOPE_IDENTITY();
в конце триггера. И он выглядит как результат работы запроса, т.е. MSSMS отображает возвращаемые данные оператором SELECT.

Раз MSSMS может работать с возвращенными данными, может есть возможность то, что возвращается вернуть и в TMSQuery???

Alexp
Devart Team
Сообщения: 349
Зарегистрирован: Пн 27 дек 2010 10:34

Re: Возвращение ID при вставке

Сообщение Alexp » Пн 07 апр 2014 09:20

Действительно при использовании триггера значения параметра после вставки равно 0. И мы не можем повлиять на это поведения. Если изменить ваш INSERT запрос то сервер вернет DataSet который был сформирован в тригере. Поэтому В вашем случае для получения вставленного значения Вам необходимо использовать дополнительный MSQuery, в котором указать INSERT запрос в свойстве

Код: Выделить всё

MSQuery.SQL.Text := 'INSERT INTO LISTVOLUME (NAME, NOTICE, REF_REV)  VALUES (:Name, :NOTICE, :ID_REVISIONS)';
и для ставки данных заполнить параметры и вызвать метод Execute, после выполнения результат можно получить из DataSet

Код: Выделить всё

MSQuery.Fields[0].AsInteger

vso
Сообщения: 39
Зарегистрирован: Чт 24 янв 2013 11:08

Re: Возвращение ID при вставке

Сообщение vso » Пн 07 апр 2014 11:36

Ok, получается что, если вставку/обновление осущиствить в стороннем компоненте, то основной датасет останится в состоянии вставки/обновления. Как сделать грамотно, чтобы он вышел из этого состояния и обновил даные/обновленную строчку, т.е. сымитировать свойство RefreshOptions = [roAfterInsert, roAfterUpdate]?

Alexp
Devart Team
Сообщения: 349
Зарегистрирован: Пн 27 дек 2010 10:34

Re: Возвращение ID при вставке

Сообщение Alexp » Пт 11 апр 2014 10:37

К сожалению в этом случае придется обновлять DataSet полностью.

Ответить