Faking an identity column: Difference between revisions
(New) |
(+commitment control) |
||
| (One intermediate revision by the same user not shown) | |||
| Line 8: | Line 8: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
If there is a certain chance that another process is concurrently active, it will derive the same ID and those <tt>INSERT</tt>s will fail with a duplicate key error. As long as this ID is not referenced by other tables as a primary key value, the easiest way to counter this is to programmatically try to insert the record in a loop<ref>You should ensure that only a duplicate key error forces another iteration. Other errors can happen.</ref> until the <tt>INSERT</tt> eventually succeeds. Note that using ''commitment control'' adds another layer of possible pitfalls here. | |||
Similar ways can be done with classic positional API calls, and a LF defined only for the ID column.<ref>An example can be found in the [https://github.com/PoC-dev/as400-sfltemplates/blob/main/v_lodallpg.rpgle v_lodallpg.rpgle] I wrote. Look for subroutine <code>INCLASTID</code>.</ref> | |||
Other ways of obtaining IDs are pointed out briefly in ''Sequence Objects and Identity Columns''. | Other ways of obtaining IDs are pointed out briefly in ''Sequence Objects and Identity Columns''. | ||
Latest revision as of 19:26, 20 April 2025
Older versions of OS/400 lack an automatically maintained identity column, to uniquely refer to a given record in a table. Depending on use case, it might be programmatically easier to pick a certain record by a a single value, instead of e. g. a multi-column key.
The most easy way to fake this is to define a column as numeric and derive a new value at insert time.
INSERT INTO mytable (id, field1, field2, field3)
SELECT MAX(id)+1, 'foo', 'bar', 'baz' FROM mytable
If there is a certain chance that another process is concurrently active, it will derive the same ID and those INSERTs will fail with a duplicate key error. As long as this ID is not referenced by other tables as a primary key value, the easiest way to counter this is to programmatically try to insert the record in a loop[1] until the INSERT eventually succeeds. Note that using commitment control adds another layer of possible pitfalls here.
Similar ways can be done with classic positional API calls, and a LF defined only for the ID column.[2]
Other ways of obtaining IDs are pointed out briefly in Sequence Objects and Identity Columns.
Weblinks
- Sequence Objects and Identity Columns, datavail.com
- Subfile Templates, GitHub
Footnotes
- ↑ You should ensure that only a duplicate key error forces another iteration. Other errors can happen.
- ↑ An example can be found in the v_lodallpg.rpgle I wrote. Look for subroutine
INCLASTID.