Как прочитать значение поля со свойством IDENTITY

Что такое IDENTITY
Недостаток использования полей со свойством IDENTITY как первичного ключа
Как определить значение поля со свойством IDENTITY в новой записи
Как работать с полями со свойством IDENTITY в FoxPro
  Прямое использование Pass-Through технологии через функцию SQLEXEC()
  Использование Remote View
  Использование Cursor Adapter

Что такое IDENTITY

IDENTITY - это не тип данных. Это некоторое дополнительное свойство, ограничение, накладываемое на целочисленные типы данных в MS SQL сервере. Т.е. это свойство может быть применено к полям следующего типа: tinyint, smallint, int, bigint, decimal(p,0), или numeric(p,0)

Аналогом этого свойства в FoxPro является тип данных Integer-AutoIncrement. Только не надо считать, что Integer-AutoIncrement это и есть поле со свойством Identity. Вовсе нет. Это именно аналог. В основном они похожи, но имеют ряд отличий. В данной статье речь пойдет о свойстве IDENTITY в MS SQL сервере.

Поля со свойством IDENTITY обладают следующими особенностями:

В одной таблице допустимо существование только одного поля со свойством IDENTITY
Поле со свойством IDENTITY нельзя редактировать. Они имеет свойство "только для чтения".
Значение полю со свойством IDENTITY присваивается автоматически в момент создания новой записи.

Есть еще некоторые особенности, но они уже являются следствием перечисленных особенностей

Новое значение - это последнее использованное значение плюс некоторая фиксированная величина. Обратите внимание, новое значение опирается не на максимальное значение в существующих записях, а на последнее использованное значение. Это значит, что записи с последним использованным значением физически может не существовать, тем не менее, это значение будет использовано.

Другими словами, в последовательности значений поля со свойством IDENTITY вполне допустимы "дыры". Список значение вовсе не непрерывный

Как правило, в качестве шага приращения указывают 1, но это может быть и любое целое число. В том числе и отрицательное.

В связи с такими особенностями полей со свойством IDENTITY такие поля часто используют в качестве первичных ключей. Другими словами, в качестве полей, по значению которых всегда можно однозначно идентифицировать запись таблицы.

Следует иметь в виду, что свойство IDENTITY никак не контролирует уникальность данных. Например, если изначально поле имело тип INTEGER, и в него были введены ряд значений. А затем была изменена структура таблицы, и на данное поле было наложено свойство IDENTITY, то новые записи вполне могут иметь те же данные, что уже были введены ранее в эту таблицу. Поэтому, если поле со свойством IDENTITY используется в качестве первичного ключа, то на это поле следует наложить дополнительное ограничение по уникальности.

Недостаток использования полей со свойством IDENTITY как первичного ключа

Однако, несмотря на явные достоинства использования полей со свойством IDENTITY в качестве первичного ключа, они имеют и серьезный недостаток.

Значение полей со свойством IDENTITY невозможно узнать до того, как запись будет физически создана.

Ну и что? Какие проблемы-то? Создадим запись и узнаем ее новое значение.

Проблема заключается в том, что для того, чтобы узнать значение поля какой-либо записи эту запись надо сначала найти. А поиск записи как раз и осуществляется по значению первичного ключа. Того самого, значение которого необходимо определить. Замкнутый круг: чтобы прочитать значение это значение надо знать!

Структура хранения данных в MS SQL сервере принципиально отличается от структуры хранения данных в DBF-файлах. В нем нет таких понятий как "физический номер записи", "следующая запись", "последняя запись" и т.п. Т.е. невозможно перейти к "последней записи" чтобы прочитать значение ее первичного ключа.

Более того, хотя новое значение поля со свойством IDENTITY всегда больше любого из существующих значений (если шаг приращения - положительное число), но определить это новое значение, просто вычислив максимальное из существующих значений, тоже нельзя. Нет, само максимальное значение, разумеется, будет получено. Просто нет никакой гарантии, что полученное значение - это значение именно той записи, которая и была создана.

Дело тут в том, что, как правило, MS SQL сервер используется в многопользовательских приложениях. Это значит, что сразу несколько пользователей, одновременно, могут создавать новые записи. Получается, что один пользователь создал новую запись, затем начал вычислять максимальное значение и в этот момент другой пользователь также создал новую запись. В результате, первый пользователь в качестве максимального значения получит значение записи созданной вторым пользователем.

Так что же, отказаться от использования полей со свойством IDENTITY в качестве первичного ключа? Вовсе нет. Все-таки существуют способы определения значения поля со свойством IDENTITY у новой записи.

Как определить значение поля со свойством IDENTITY в новой записи

Собственно, есть три принципиальные стратегии определения значения поля со свойством IDENTITY в новой, только что созданной, записи

Значение, возвращаемое системной переменной @@IDENTITY
Значение, возвращаемое функцией SCOPE_IDENTITY()
Нахождение новой записи по значению других полей

Теперь рассмотрим более подробно достоинства и недостатки каждой стратегии

Значение, возвращаемое системной переменной @@IDENTITY

В MS SQL сервере есть ряд системных переменных, значение которых изменяется автоматически при наступлении определенных событий. В частности, значение системной переменной @@IDENTITY автоматически устанавливается равным значению поля со свойством IDENTITY последней созданной записи в текущем соединении. Т.е. создание новых записей в другом соединении (другим пользователем) никак не повлияет на ее значение в данном соединении.

Ну, так вот оно, решение. Просто после создания новой записи читаем значение системной переменной @@IDENTITY и имеем искомое значение.

В целом, верно. Проблема только в том, что системная переменная @@IDENTITY меняет свое значение при создании записи в любой таблице.

На практике, это означает, что если на таблицу установлен триггер на вставку, в теле которого дается команда INSERT на создание записи в другой таблице, которая, в свою очередь, также имеет поле со свойством IDENTITY, то системная переменная @@IDENTITY получит значение поля из этой второй таблицы.

Другими словами, опираться на значение системной переменной @@IDENTITY можно, но, помня о том, что эта переменная не привязана к значению поля одной таблицы.

Значение, возвращаемое функцией SCOPE_IDENTITY()

В версии MS SQL 2000 была введена системная функция SCOPE_IDENTITY(). Эта функция также возвращает значение поля со свойством IDENTITY последней созданной записи, но созданной в пределах текущего SCOPE.

Адекватно перевести термин SCOPE на русский язык достаточно сложно. Но, приблизительно, можно сказать так: SCOPE - это одна процедура или функция. Другими словами, SCOPE_IDENTITY() вернет значение поля со свойством IDENTITY последней записи созданной в пределах той процедуры, где эта функция была вызвана.

Триггер - это уже другой SCOPE (другая функция), поэтому он никак не повлияет на значение возвращаемое SCOPE_IDENTITY().

Даже если два пользователя одновременно вызвали одну и ту же процедуру, но каждый вызвал процедуру в своем SCOPE. Т.е. опять-таки нет конфликта.

К недостаткам этой функции относится то, что ее следует вызывать в пределах того SCOPE, где и была создана новая запись интересующей нас таблицы. А это не всегда возможно.

Другими словами, для корректного использования SCOPE_IDENTITY() необходимо всегда следить за областью действия SCOPE. Зачастую, создавая специальные процедуры.

Нахождение новой записи по значению других полей

Если помните, то основная проблема с определением значения поля со свойством IDENTITY заключается в том, что данное поле используется в качестве первичного ключа. Т.е. по его значению как раз и находят нужную запись.

Однако, зачастую, таблицы имеют поле или набор полей, по которым также можно однозначно определить запись. Например, если речь идет о справочнике, то, разумеется, справочник имеет поле "Название". Также очевидно, что это поле должно быть уникально в пределах справочника. Иначе просто теряется смысл использования самого справочника. Зачем вводить в справочник записи с одинаковым значением?

Почему же не использовать в качестве первичного ключа это самое "Название"? Зачем вообще нужно поле со свойством IDENTITY? Это тема отдельного разговора. Вкратце, "Название" - это для пользователя (внешние данные), а IDENTITY - это для обеспечения ссылочной целостности базы данных (внутренние данные).

Значение поля со свойством IDENTITY в новой записи не известно. Но значение поля "Название" в этой новой записи вполне известно. Пользователь сам же его и ввел! Значит, после создания новой записи можно найти эту новую запись по значению поля "Название" и прочитать значение поля со свойством IDENTITY.

Проблема только в том, что далеко не всегда существует такое поле или набор полей для однозначной идентификации записи. Это, кстати, одна из причин ввода, так называемых, суррогатных ключей. Тех самых полей со свойством IDENTITY.

Если, тем не менее, Вы решите использовать эту стратегию для поиска новой записи, то обязательно наложите на поле "Название" (или выбранный Вами набор полей) ограничение по уникальности. Т.е., чтобы случайно не оказалось двух записей с одинаковым значением в этом поле.

Как работать с полями со свойством IDENTITY в FoxPro

С теоретической частью закончили, теперь "попробуем со всем этим добром взлететь". Т.е. определимся, как же воспользоваться всеми этими знаниями в FoxPro. Еще раз уточним задачу, которую необходимо решить.

Добавляется запись в таблицу MS SQL сервера имеющую поле со свойством IDENTITY. Необходимо сразу после создания новой записи получить значение поля со свойством IDENTITY на стороне FoxPro.

У FoxPro есть три принципиальные возможности организации работы с MS SQL сервером

Прямое использование Pass-Through технологии через функцию SQLEXEC()
Использование Remote View
Использование Cursor Adapter

Тут следует остановиться на том, какое именно событие собственно создает запись на MS SQL сервер. Ну, с Pass-Trough все ясно. Это собственно прямая команда серверу создать новую запись. А вот с Remote View и Cursor Adapter несколько иначе.

Результатом работы как Remote View, так и Cursor Adapter является курсор. Т.е. некая временная таблица, физически расположенная на машине клиента. По умолчанию, этот курсор автоматически открывается в режиме оптимистической буферизации строк (3) и может быть переключен только в режим оптимистической буферизации таблиц (5). Переключиться в режим пессимистической буферизации или отключить буферизацию совсем для этого курсора невозможно

Следовательно, новая запись сначала физически будет создана именно на клиентской машине в этом самом курсоре. Точнее, в буфере этого курсора. Физическое создание записи на MS SQL сервере произойдет только после сброса буфера.

Для строковой буферизации сброс буфера может произойти автоматически при выполнении одного из следующих действий:

Переход (или попытка перехода) на другую запись
Закрытие курсора
Переключение в режим табличной буферизации
По команде TableUpdate()

Для табличной буферизации сброс буфера может произойти только по команде TableUpdate() и никак иначе.

Никакие другие действия и операции ни с Remote View, ни с Cursor Adapter не приведут к созданию новой записи на MS SQL сервере. Если при выполнении какой-либо операции оказалось, что на MS SQL сервере создалась новая запись, это означает, что курсор находился в режиме строковой буферизации, и произошло одно из событий вызвавших автоматический сброс буфера.

Например, такое может произойти по команде Requery() для Remote View. Но это вовсе не означает, что команда Requery() сбрасывает буфер. Вовсе нет. Просто одним из условий выполнения команды Requery() является закрытие ранее существовавшего курсора. А вот это событие как раз и вызовет автоматический сброс буфера, если курсор находится в режиме строковой буферизации.

Чтобы избежать подобных недоразумений, переключайте курсор в режим табличной буферизации (5). В этом случае Вы всегда сможете контролировать процесс сброса буфера.

Однако следует понимать, что даже если Вы установите режим табличной буферизации, измените несколько записей в Remote View или Cursor Adapter, а потом дадите команду TableUpdate() все равно сброс буфера будет происходить по одной записи за раз. Т.е. на сервер будет послана не одна команда, например, на модификацию, а набор команд по модификации каждой записи в отдельности.

Применительно к операциям создания новой записи из этого следует вывод, что во всех событиях объекта Cursor Adapter всегда происходит вставка только одной записи за раз.

Прямое использование Pass-Through технологии через функцию SQLEXEC()

При таком способе работы программист как бы напрямую работает с MS SQL сервером. Сам формирует все команды, отсылаемые на сервер, получает результат, и сам же его обрабатывает. В этом случае не составляет никакого труда послать дополнительный запрос серверу на значение функции SCOPE_IDENTITY

   LOCAL lcNewValue, lnResut  
  lcNewValue = "Новое значение"  
  lnResut = SQLExec(m.lnConnectHandle,"INSERT INTO MyTab (Field1) VALUES (?m.lcNewValue)")  
  IF m.lnResut>0  
  	SQLExec(m.lnConnectHandle,"SELECT NewIdent=SCOPE_IDENTITY()","NewIdent")  
  	?NewIdent.NewIdent  
  ELSE  
  	LOCAL laError(1)  
  	=AERROR(laError)  
 	* Анализ массива laError для уточнения причины ошибки  
  ENDIF

В данном примере m.lnConnectHandle - это число, номер соединения с MS SQL сервером, которое настраивается раньше. MyTab - это таблица, которая имеет поле со свойством IDENTITY.

После выполнения второго запроса в результирующем курсоре NewIdent в поле NewIdent первой записи и получим искомое значение. В данном синтаксисе и команда вставки и вызов функции SCOPE_IDENTITY() происходят в одном SCOPE. Поэтому, получаем нужное значение.

Использование Remote View

Remote View - это некая "надстройка" над технологией Pass-Through. По сути, при создании новой записи выполняется та же команда INSERT INTO. Однако проблема в том, что даже если прочитать номер соединения, в котором работает Remote View, а потом выполнить запрос для определения значения, возвращаемого SCOPE_IDENTITY(), то получим NULL, поскольку в этом случае команда вставки и SCOPE_IDENTITY() выполняются в разных SCOPE. Следовательно, остается только два способа определения значения поля со свойством IDENTITY.

  * Определение значения системной переменной @@IDENTITY  
  LOCAL lnConnectHandle  
  lnConnectHandle = CursorGetProp("ConnectHandle","MyRemoteView")  
  SQLExec(m.lnConnectHandle,"SELECT NewIdent=@@IDENTITY","NewIdent")  
  ?NewIdent.NewIdent  
    
 * Определение по значению другого поля  
  LOCAL lnConnectHandle, lcNickName  
  lnConnectHandle = CursorGetProp("ConnectHandle","MyRemoteView")  
  lcNickName = MyRemoteView.NickName  
  SQLExec(m.lnConnectHandle,"SELECT TabId FROM MyTab WHERE NickName=?lcNickName","NewIdent")  
  ?NewIdent.TabId

В обоих примерах MyRemoteView - это имя Вашего Remote View. NickName - это имя поля в Remote View по значению которого выполняется поиск новой записи, а TabID - это то самое поле со свойством IDENTITY значение которого и надо определить

Предполагается, что создание новой записи уже произошло. Либо явно была дана команда TableUpdate(), либо Remote View находится в режиме строковой буферизации, и была попытка перехода на другую запись.

А почему во втором случае не было использовано, казалось бы, более очевидное решение поиска после Requery()? Что-то вроде

   =REQUERY("MyRemoteView")  
  SELECT MyRemoteView  
  LOCATE FOR NickName = "Новое значение"  
  ?MyRemoteView.TabID

На это есть несколько причин.

Во-первых, Requery() предполагает выполнение запроса заново. Т.е. предполагается, что произошел сброс буфера всех записей Remote View. Но ведь это может быть не так. Например, сбрасывают буфер по одной записи за раз в цикле.

Во-вторых, обычно Remote View содержит в себе дополнительные условия отбора записей, и только что созданная запись может вообще не попасть в условия отбора. Т.е. после Requery() запись, хотя и будет физически создана на сервере, но в самом Remote View отображена не будет.

Использование Cursor Adapter

Объект Cursor Adapter был введен в FoxPro, начиная с версии Visual FoxPro 8. Он призван облегчить работу с удаленными данными при выполнении некоторых стандартных операций. В частности, операции создания новой записи. Через Cursor Adapter можно реализовать все три варианта получения значения поля со свойством IDENTITY в новой записи.

Значение, возвращаемое системной переменной @@IDENTITY

После того, как будет выполнен сброс буфера и произойдет физическая создание новой записи на MS SQL сервере, в объекте Cursor Adapter сработает событие AfterInsert. Вот в нем как раз и надо выполнить дополнительный запрос на сервер и прочитать значение системной переменной @@IDENTITY

   PROCEDURE AfterInsert  
  LPARAMETERS cFldState, lForce, cInsertCmd, lResult  
  IF lResult=.T. && вставка произошла успешно. Надо получить значение ID  
  	LOCAL currentArea  
  	currentArea=SELECT()  
    
  	TRY  
  		IF 1=SQLEXEC(this.DataSource,"SELECT NewIdent=@@IDENTITY","NewIdent")  
  			REPLACE TabId WITH NewIdent.NewIdent IN (This.Alias)  
  			USE IN IDRes  
  		ELSE  
  			LOCAL laError(1)  
  			=AERROR(laError)  
 			* Анализ массива laError для уточнения причины ошибки  
  		ENDIF  
  	FINALLY  
  		SELECT (currentArea)  
  	ENDTRY  
  ENDIF  
  ENDPROC

Думаю, код достаточно понятный и не нуждается в пояснениях. После успешного создания новой записи на сервере при помощи SQLExec() для того же самого соединения определили значение @@IDENTITY и записали это значение в текущую запись курсора в ключевое поле.

В версии Visual FoxPro 9 в объект Cursor Adapter было добавлено свойство InsertCmdRefreshCmd. Это команда, которая посылается на сервер только после успешной вставки новой записи. Т.е. здесь нет необходимости убеждаться в самом факте создания новой записи.

В паре с другим новым свойством InsertCmdRefreshFieldList можно выполнить обновление, опираясь на значение системной переменной @@IDENTITY проще. Для этого всего лишь надо сделать следующие настройки объекта CursorAdapter

   CursorAdapter.InsertCmdRefreshCmd       = "SELECT @@IDENTITY"  
  CursorAdapter.InsertCmdRefreshFieldList = "TabId"

Здесь TabId - это не имя поля со свойством IDENTITY в таблице собственно на MS SQL сервере, а имя поля курсора, полученного на стороне клиента в котором и отображается содержимое поля со свойством IDENTITY. Как правило, эти имена совпадают, но, в общем случае, могут и отличаться. Более подробно о свойстве InsertCmdRefreshFieldList читайте ниже. В разделе, посвященном нахождению новой записи по значению других полей.

Значение, возвращаемое функцией SCOPE_IDENTITY()

Здесь все несколько сложнее. Дело в том, что действовать, так же как и в случае определения системной переменной @@IDENTITY нельзя. SQLExec() и Cursor Adapter работают в разных SCOPE. Поэтому при подобном подходе SCOPE_IDENTITY() всегда будет возвращать NULL. Чтобы преодолеть это противоречие используют специальные процедуры.

Для начала, надо создать на самом MS SQL сервере вот такую хранимую процедуру

   CREATE PROCEDURE Get_ValueInt  
  		@ValueIn Int,  
  		@ValueOut Int OUTPUT  
  AS  
  SET NOCOUNT ON  
  SELECT @ValueOut=@ValueIn

Как видите, цель данной процедуры просто присвоить значение входного параметра выходному параметру. Теперь можно использовать эту процедуру для определения значения SCOPE_IDENTITY() в Cursor Adapter. Для этого в событии BeforeInsert делается такая подмена

   PROCEDURE BeforeInsert  
  LPARAMETERS cFldState, lForce, cInsertCmd  
  cInsertCmd = cInsertCmd + ;  
  		"; DECLARE @id int" + ;  
  		"; SELECT @id=SCOPE_IDENTITY()" + ;  
  		"; EXEC Get_ValueInt @id, ?@MyTab.TabId"  
  ENDPROC

Только следует иметь в виду, что в данной подмене MyTab - это уже не имя таблицы на MS SQL сервере, а имя курсора создаваемого у клиента. Точнее, то имя, которое записано в свойстве Alias собственно Cursor Adapter. Соответственно, "TabId" - это имя поля курсора, созданного у клиента и содержащего значение поля со свойством IDENTITY

В данном случае, по сути, формируется динамическая хранимая процедура. Т.е. не просто одна команда INSERT, а последовательность команд. Команды отделяются друг от друга символом точки с запятой, хотя это вовсе не обязательно. Достаточно отделять команды друг от друга простым пробелом.

Нахождение новой записи по значению других полей

Если у Вас Visual FoxPro 8, то Вам остается использовать метод аналогичный методу, примененному для поиска значения системной переменной @@IDENTITY. Т.е. в методе AfterInsert объекта Cursor Adapter выполнить дополнительный запрос через SQLExec()

Начиная с версии Visual FoxPro 9, в объекте Cursor Adapter появились дополнительные свойства, позволяющие автоматизировать эту процедуру без написания дополнительного кода

InsertCmdRefreshFieldList - список полей, значение которых будет обновлено после Insert
InsertCmdRefreshKeyFieldList - поле, или набор НЕ ключевых полей также однозначно идентифицирующих запись, по которым и будет осуществлен поиск новой записи

Следует отметить, что в этих свойствах указываются не имена полей таблицы собственно MS SQL сервера, а соответствующие им имена полей курсора, полученного на стороне клиента. Применительно к тому же примеру это будет примерно так:

   InsertCmdRefreshFieldList 	= "TabID"  
  InsertCmdRefreshKeyFieldList 	= "NickName"

Другими словами, опираясь на значение поля NickName, будет найдена запись в таблице на MS SQL сервере и прочитано значение поля TabID, после чего это значение будет записано в новую запись на стороне клиента.

Более подробно как обсуждение данной темы, так и примеры использования Вы можете посмотреть здесь

CursorAdapter: Авто-рефреш полей во время TABLEUPDATE
Как автомат-ки получать автоинкриментное поле SQL сервера в КА
CursorAdapter, событие AfterInsert и поле сервера типа IDENTITY

Владимир Максимов
Последнее обновление: 01.05.06

Автор публикации

не в сети 24 года

Владимир Максимов

Комментарии: 0Публикации: 65Регистрация: 02-09-2000

Статьи