Uniqid

uniqid allows you to generate a unique series of long integer for every database table.

Syntax

 uniqid (CLASS)

CLASS is the abbreviation of a table that must be opened with the syntax [ABBR] or [F:ABBR].

Examples

 # Let's assign a unique id to a line in my table[F:MYT]ID = uniqid([MYT])Write [MYT]

Description

uniqid returns a unique long integer value for a given database table. These numbers are attributed sequentially and can therefore by used as an access key.

The next number to be assigned can be transferred by copy of the table, by backup and restoration, or by revalidation of the table. If a number is assigned in a transaction and a Rollback is performed, the number will never be recovered. Thus, lines numbered by a sequence may not be consecutive.

The implementation of this function is based on a database sequence created for every table.

The type of result is integer.

Comments

For a newly created table, the number sequence starts at 1. A RTZ on the table will also restart the sequence from 1.

As holes can be present in the sequence when Rollback is performed, this function is not suitable if lines need to have a sequential numbering with no lost number. Other supervisor resources (counters) are available to assign document numbers to database records.

The uniqid function executes even if the table is locked.

uniqid does not update [S]fstat.

UUID handling functions have been added in the SAFE X3 languages; therefore, using UUIDs to manage unique identifiers for database lines is preferred.

Troubleshooting with unique ID keys

A number is used in some database tables to get a unique key. If for any reason the sequence has been recreated independently from the table, errors with duplicate keys will happen when attempts are done to create new lines.

A script like the example below can be executed to restore the situation:

 # MYTABLE uses uniqid to assign a property MY_ID that is the component of a unique key called MY_KEYLocal File MYTABLE [MYT]# The last line in MY_KEY order gives the biggest value assigned with uniqidRead [MYT]MY_KEY Last# If there is a sequence issue, loop until the sequence becomes equal to the last valueIf uniqid([MYT])<=[MYT]MY_IDWhile uniqid([MYT])<[MYT]MY_IDEndif

A SQL request can also be used to know the last sequence number used for 'MYTABLE' (on the FOLDER folder):

On the Oracle database:

Select last_number from user_sequences where sequence_name =’SEQ_MYTABLE’;

On the SQL server database:

select value from SS_SEQUENCE where usr='FOLDER' and name='MYTABLE'

You can also change the sequence number through a database connection, but you must be connected as a single user:

On Oracle database:

Drop sequence SEQ_MYTABLE;Create sequence SEQ_MYTABLE increment by 1 start with <MAX+1> ;Grant select on SEQ_MYTABLE to USER_ADONIX;Grant select on SEQ_MYTABLE to ADMIN_ADONIX;

On the SQL server database:

update SS_SEQUENCE set value=<MAX> where name='MYTABLE' and usr='FOLDER'

Associated errors