Using IDENTITY columns with Clarion files

It is customary in SQL tables to use an IDENTITY column to create a unique identity number or SysID for each row.  This is all handled on the server side so the front end doesn't need to do anything to make it happen.

In some cases you do need to know the SysID in a Clarion program for example when adding child records to a parent record on the parent record form.  Fortunately this is very simple to do and it can all done in the dictionary, same setup for every table and once done, you never have to mess with it again.

It requires 4 changes to the table and ID field:

1.  Modify the File Options 

Add a new EMULATEAUTONUMKEY option and set it to 1 (one)  not "True", just the number 1)

2.  Modify the SysID "External Name" attribute:

It must be set to the name of the IDENTITY column in the database, a space, a pipe character (|), another space and then the word "READONLY"  The spaces around the pipe characer are absolutely mandatory.  If they are missing the read-only attribute is not recognized and you may get errors when you try to insert or update a record.  "FieldName | READONLY"

3.  Modify the SysID Options:

You need to add IsIdentity option and set the value to True - not 1, just "True"

4.  Add primary key:

You should create a primary key on the SysID field in the dictionary if you don't already have one.  Make sure you do NOT check the "Auto Number"  If you are converting from Topspeed files, you must make sure that you uncheck the "Auto Number" check-box.

That's it.  As soon as the parent record is inserted into the database, the SysID will now contain the value from the IDENTITY column in the data table.  

By doing this, you will have the value of the SysID immediately after you insert a record.  So you can do something like this:

 !MYF:SysID =
 MYF:Name = 'Arnor'
 Add(MyFile)
 If Not ErrorCode()
   Message('ID = ' & MYF:SysID)
 End

and it would show you the SysID of the newly inserted record.  Same with using ABC:

 !MYF:SysID =
 MYF:Name = 'Arnor'
 If Access:MyFile.Insert() = LEVEL:Benign
   Message('ID = ' & MYF:SysID)
 End

Arnor Baldvinsson