15 окт. 2019 г.

Получение в программе значения первичного ключа после INSERT

    Часто бывает, что после добавления строки в таблицу необходимо получить сгенерированное в СУБД значение первичного ключа. Например, это необходимо для последующей вставки detail-данных. Самый "оригинальный" способ, что я видел: стартуем транзакцию, вызываем INSERT, делаем запрос "select MAX(ID) from table", вставляем detail-данные, коммитим... Но мы пойдем другим путем – без дополнительных запросов к базе данных. Мы воспользуемся вызовом оператора INSERT с модификатором RETURNING. Вопрос только в том, как получить в программе значение из RETURNING?
    Первый вариант – это делать добавление строки в таблицу используя функцию или процедуру, и возвращать программе значение первичного ключа вставленной строки в качестве результата функции или параметра процедуры. Мне такой вариант нравится – напоминает инкапсуляцию в ООП, когда все данные доступны пользователю только через методы объекта, а пользователь даже не догадывается о реальной структуре хранения данных.
    Второй вариант – воспользоваться возможностями библиотеки для доступа к базам данных. Рассмотрим этот вариант на примере Devart Universal Data Access Components (UniDAC).

1. Oracle
Создаем тестовую таблицу и сиквенс (не у всех есть Oracle 12c с IDENTITY) для генерации уникальных значений первичного ключа:
create table test 
(
  id number(10),
  t  varchar(100)
);
create sequence test_id;
Так как в Oracle при использовании "RETURNING" указывается куда поместить результат, то логично поместить его в параметр:
q := TUniQuery.Create(nil);
q.Connection := dbOra;
q.SQL.Text := 'insert into test(id, t) ' +
              'values (test_id.nextval, ''test'') ' +
              'returning id into :id';
q.Params.Add;
q.Params[0].Name := 'id';
q.Params[0].ParamType := ptResult;
q.Params[0].DataType  := ftInteger;
q.Execute;
Writeln(q.Params[0].Name, ' ', q.Params[0].AsString);

2. PostgreSQL
Создаем тестовую таблицу со столбцом типа SERIAL, который будет генерировать нам уникальные идентификаторы для первичного ключа:
create table public.test 
(
  id serial,
  t  varchar(100)
);
В предыдущем примере меняем connection на подключение к PostgreSQL, а из теста запроса для вставки убираем ID и получение значения из сиквенса:
q.SQL.Text := 'insert into test(t) values (''test'') returning id into :id';
Запускаем программу и получаем: "Project XYZ.exe raised exception class EPgError with message 'syntax error at or near "INTO"'". Т.е. как с Oracle сделать не получилось, т.к. в синтаксисе RETURNING у PostgreSQL нет INTO, и он просто возвращает поля аналогично SELECT.
Среди свойств TUniQuery я заметил свойство LastInsertId. Звучит многообещающе. Убираем из запроса "id into :id" и запускаем:
q := TUniQuery.Create(nil);
q.Connection := dbPG;
q.SQL.Text := 'insert into test(t) ' +
              'values (''test'') returning id';
q.Execute;
Writeln(q. LastInsertId);
В результате вместо 1 получаю 0. Глюк? Читаю справку, и понимаю, что LastInsertId мне не поможет. Оказалось, что для PostgreSQL он возвращает значение OID (Object Identifier) если при создании для таблицы указывается "WITH OIDS" или включён параметр конфигурации "default_with_oids".
Что же делать? Мысль о том, что в PostgreSQL RETURNING возвращает поля аналогично SELECT, наводит на решение – проверить наличие полей после выполнения TUniQuery:
q := TUniQuery.Create(nil);
q.Connection := dbPG;
q.SQL.Text := 'insert into test(t) ' +
              'values (''test'') returning id';
q.Execute;
Writeln(q.Fields[0].FieldName, ' ', q.Fields[0].AsString);
В результате FieldByName('id') после запуска TUniQuery содержит значение ID вставленной строки.

3. Firebird/InterBase
Создаем тестовую таблицу и генератор (или сиквенс, как у Oracle) для генерации значения первичного ключа:
create table test 
(
  id integer,
  t  varchar(100)
);
create generator test_id;
В синтаксисе RETURNING у Firebird, как у PostgreSQL, нет INTO, поэтому по аналогии с PostgreSQL после запуска запроса на вставку проверяем нулевое поле:
q := TUniQuery.Create(nil);
q.Connection := dbFB;
q.SQL.Text := 'insert into test(id, t) ' +
              'values (gen_id(test_id, 1), ''test'') ' +
              'returning id';
q.Execute;
Writeln(q.Fields[0].FieldName, ' ', q.Fields[0].AsString);
и вместо результата получаем ошибку: "Project XYZ.exe raised exception class EDatabaseError with message 'List index out of bounds (0)'". Оказывается, что UniDAC для InterBase /Firebird, как для Oracle, возвращает результат RETURNING в параметры TUniQuery:
q := TUniQuery.Create(nil);
q.Connection := dbFB;
q.SQL.Text := 'insert into test(id, t) ' +
              'values (gen_id(test_id, 1), ''test'') ' +
              'returning id';
q.Execute;
Writeln(q.Params[0].Name, ' ', q.Params[0].AsString);
UniDAC даже сам создает результирующий параметр.

4. MS SQLServer
Создаем тестовую таблицу со столбцом типа IDENTITY, который будет генерировать нам уникальные идентификаторы для первичного ключа:
create table test 
(
  id integer identity,
  t  varchar(100)
);
У MS SQLServer нет RETURNING! Что делать? Классическое решение – это после INSERT запросом "SELECT SCOPE_IDENTITY()" получить значение последнего введенного идентификатора в данной сессии. Но мы ищем решение без дополнительных запросов, поэтому, как я писал выше, мы пойдем другим путем. При операциях DML SQLServer автоматически создает временные таблицы INSERTED и DELETED, которые используются обычно в триггерах. Попробуем использовать временную таблицу INSERTED и мы:
q := TUniQuery.Create(nil);
q.Connection := dbMS;
q.SQL.Text := 'insert into test(t) output INSERTED.ID '+
              'values (''test'')';
q.Execute;
Writeln(q.Fields[0].FieldName, ' ', q.Fields[0].AsString);
И снова верный результат. TUniQuery, как для PostgreSQL, вернул значение ID вставленной строки в виде поля.

Других СУБД у меня сейчас под рукой нет, но я думаю, что в TUniQuery для любой СУБД можно получить значение первичного ключа после вставки в таблицу новой строки – или как поле или как параметр.

1 комментарий:

Константин комментирует...

При использовании ODAC очень удобны свойства KeySequence и SequenceMode. В TOraQuery описан только обычный select, остальные датасет генерирует сам. Значение ПК можно брать из поля либо после Post либо после Insert.

Но, конечно, это подойдёт только для Oracle. И, если я правильно помню, при использовании этих свойств, Query отправляет два запроса: cначала на выборку из последовательности, а потом уже Insert, но это не точно.

Про "select MAX(ID) from table" встречается еще худший вариант, когда не просто так получают значение, сам ПК генерируется таким образом "select MAX(ID) + 1 from table"