Bg SoftFactory Website

So, how do I make this feature work with PostgreSQL? I have to admit it took me a little while to figure this out, as it's quite counter intuitive, in a way. The function for auto-incrementing, in PostgreSQL is nextval('sequence'). It would seems logical, then, to tell Clarion to use nextval here. BUT, if you read SoftVelocity help carefully, and not way too quickly like I did the first time, you realize this feature "now support the ability to RETRIEVE values set by the server when an insert is issued", not SET values.... In other words, this is not going to execute some hidden Prop:SQL statement to actually DO the autoincrement, but just find what value the server just set. So, we need to use currval('sequence') for Clarion to retrieve the correct value.

My driver string looks like this: /AUTOINC='SELECT currval(''people_id_seq'')'

Remember that the sequence name, as generated by PostgreSQL, will always be "tablename_columnname_seq ". While you are on the table definition, select the options tab, insert a option named EMULATEAUTONUMKEY, and give it the value "True".

Now, for the field which is the primary key (which I generally name ID or SYsID), go to the option tab also, insert an option named IsIdentity ( yeah, I know, that's SQL Server terminology, but that's what we have to use here....), and give it also the value True.

That's it! Don't forget that your primary key is unique, primary, exclude nulls, BUT NOT AUTO INCREMENTED, not on the Clarion side anymore, at last....

I am very happy to see SoftVelocity make efforts to give a better support to SQL from within Clarion. I feel it could be made simpler, but at least it's there, now, we will be able to stop calling manually nextval and currval, via Prop:SQL.

DISCUSSION

After publishing this in the NewsLetter, I got a couple of questions wondering how this work. So, what happens is that, the EMULATEAUTONUMKEY option set to true indicate to the template to insert a "blank" record, just like Clarion does with the standard AutoInc feature. The record will not be REALLY blank, as PostgreSQL will use the default value for your primary key, that is nextval('sequence').

Assuming you are on an Invoice Form, with the Invoice Line Items being inserted BEFORE the user validate the form, and the header, you will now be able to prime the line items with the correct header value. Note that, for the Line Items themselves, there is no need to use EMULATEAUTONUMKEY, and to insert a blank record, as you are not inserting childs here.

You can download this example I made to test this new feature. SQL calls generated by Clarion are traced for each file, so that you can see what is actually happening.

Download Server Side Autoinc Test

File Title: Server Side Autoinc Test (Details) File Type: zip File Size: 280.90 Kb License: N/A File Author: Bernard Grosperrin