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 

2013-09-30_-_Table_Properties.png

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

 

2.  Modify the SysID "External Name" attribute:

2013-09-30_-_SysID_Attributes.png

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:

2013-09-30_-_SysID_Options.png

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

 

4.  Add primary key:

2013-09-30_-_Primary_Key_Attrributes.png

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

Have more questions? Submit a request

5 Comments

  • 0
    Avatar
    Hyrum W Tatton

    Arnor,

    Thanks for the information.

    Hyrum

  • 0
    Avatar
    Johan van Zyl

    Will this work/is it required for SERIAL in PostGreSQL?

  • 0
    Avatar
    Arnor Baldvinsson

    Hi Johan,

    I'm not sure. If SERIAL is an autonumbered column on the server then I would expect it to work. I don't have any experience with PostGreSQL so I can't give you a definite answer.

  • 0
    Avatar
    Jacek Kosinski

    Hi Arnor
    The second metod is set Options Isidentity = TRUE
    in this case you dont need to set extra read only atribute

  • 0
    Avatar
    Arnor Baldvinsson

    Hi Jacek,

    Thanks for that!

    I like to use the READONLY as it shows in the generated source, like:

    EBEmailFormatID LONG,NAME('EBEmailFormatID | READONLY')

    Which makes it immediately visible in the source:)

Please sign in to leave a comment.
Powered by Zendesk