By Mike Hanson

In my opening article, Auto-Numbering in SQL – An Overview, I touched upon many of the issues and decisions that you must tackle to adopt SQL server-side auto-numbering with Clarion.  Much of this involved broader concepts, leaving the practical examples for later.  In this installment, I'll discuss how this plays out with PostgreSQL.

On the PostgreSQL Server

Each flavor of SQL handles auto-numbering with its own quirks.  In the case of PostgreSQL, you'll be dealing with something called a SEQUENCE.  There's nothing too mysterious: it's just a counter.  It usually starts at 1, and you repeatedly ask it for the next number.  The simplest way to create one is:

CREATE SEQUENCE mysequence;

To fetch the next number (and increment the internal counter for the following call), just issue this command:

SELECT nextval('mysequence');

If you want to check which number was just assigned, you can use this:

SELECT currval('mysequence');

The call to currval must be executed in the same context as the call to nextval, which is the way that we'll be using it.

The SEQUENCE doesn't handle everything.  It must be used in conjunction with your TABLE, a primary key constraint, and a primary key field.  You also should also tell PostgreSQL that your SEQUENCE is owned by the TABLE, so that dropping the TABLE implicitly tells it to drop the SEQUENCE too.

That sounds like a lot of work, but fortunately there's a way to define all of this in one fairly simple step with a table creation script like this:

CREATE TABLE mytable
(
  mytableid serial PRIMARY KEY,
  somefield varchar(100)
);

The most important part of this is the definition of mytableid:

Here are the resulting attributes of mytableid inside pgAdmin:

The helpful thing here is that the connection between the field and the sequence provides an more descriptive syntax for retrieving the recently assigned sequence (rather than remembering that it's "tablename_columnname_seq", which could change in the future):

SELECT currval(pg_get_serial_sequence('mytable','mytableid'))

The Case of PostgreSQL Labels

You may have noticed that all of the labels I used in PostgreSQL were lowercase.  As long as you initially define everything in lowercase, you can mix and match uppercase and lowercase in your scripts later.  If, however, you create them with mixed case, then you're going to run into trouble.  Just use lowercase and avoid troubles.

Importing Existing Data into PostgreSQL

The auto-numbering is done only as a default value.  If you need to transfer existing data into the table, you can do it by specifying the existing values for mytableid in the INSERT statement.  It sees the explicit value, so doesn't bother to apply a default.  As long as it doesn't request the nextval, then it won't increment the SEQUENCE.

Once you're done importing your data, you can use the ALTER SEQUENCE command to set the counter to the next higher value.  For example:

ALTER SEQUENCE mytable_myfield_seq RESTART WITH 1234;

In this case, the next value requested will be 1234 (i.e. your pre-existing values ended with 1233).

In Your Clarion Dictionary

If you're creating an app from scratch, you could import that definition into your dictionary, or you could define it in Clarion from scratch.  One thing you don't want to do is let Clarion's CREATE command create the actual table in your SQL database.  It will give you rather undesirable results.  Instead, use a tool like DMC, FM3 or DCT2SQL, all of which will use a CREATE TABLE script on the server side.

For the sake of this article, I imported the definition from PostgreSQL into the dictionary.

PostgreSQL labels are lowercase, but most of us don't want to use lowercase labels inside Clarion.  Fortunately, you can use set the External Names within the dictionary, so that Clarion always talks to PostgreSQL with lowercase names, but lets you see your labels inside Clarion as MixedCase.

To get the auto-numbering to work, you have to visit three main areas: the auto-numbered field, the primary key, and the file definition.

Auto-Numbered Field

In the SQL table creation script above, the field name is mytableid.  You can adjust that, and tell it a bit more about its auto-numbering duty:

  1. Change the Label to the mixed case MyTableID.
  2. Note the Data Type should be LONG.
  3. On the Attributes tab, set the External Name to mytableid | READONLY.
  4. On the Options tab, add a new property IsIdentity.  It's a Boolean, with a value of TRUE.

If you are using FM3, you can add the ForceSQLDataType Option and set it to serial, so that FM3 automatically uses this type when it creates your SQL table.  I've not had a chance to experiment with this myself, and I'll report back here if I run into problems with it.

Primary Key

You may have learned that keys defined on the Clarion side are there primarily for template use, and they need not be defined as-is on the SQL side.  Clarion will use the key definition in the dictionary to build the ORDER BY clause sent to SQL.  Keys on the SQL side are for efficient queries, and need not be the same in Clarion.  In summary, the keys defined in Clarion don't have to be defined in SQL, and vice versa.

The primary key is an exception to this.  It's critical that you define a primary key on the Clarion side that matches the basic structure of the primary key on the SQL side.  It need not be given the same name (although it doesn't hurt).  It's critical, though, that it contain the same fields/columns, and be marked as PRIMARY on both sides.  (On the SQL side it's actually called a "Constraint".)

For the sake of my example dictionary, the primary key's Label will be PK_MyTable.  Just for consistency, I set the External Name to match the label on the PostgreSQL side, mtable_pkey.  Note that it won't be used in any query statement to the back end.  In addition, check the following settings for the key:

Table

Although they aren't all related to auto-numbering, I'll describe most of the Table settings, to help clarify ongoing confusion:

        /AUTOINC=SELECT currval(pg_get_serial_sequence('mytable','mytableid'))
        /AUTOINC='SELECT currval(pg_get_serial_sequence(''mytable'',''mytableid''))'
        /BUSYHANDLING=2 /NESTING=1 /USEDECLAREFETCH=1
        Driver={PostgreSQL UNICODE};Server=closet;Port=5432;Database=test;Uid=usr;Pwd=pwd;

Conclusion

As you can see, there are many crucial steps to get this to work – much more complicated than relative simplicity of Clarion's "Auto Number" attribute on its keys.  If you miss one piece, it's not going to work.  You're probably asking why it needs to be this complicated, and the answer is the age old conundrum of flexibility breeding complexity.

When Clarion controls everything (including the data in a DAT or TPS file), then it can provide a simple approach.  Once you move to SQL, it's recommended that you do things they way they do, and each flavor of SQL is a little different.  Clarion provides all the pieces to make this work, but it's certainly harder overall.  SQL makes up for it with speed, robustness, and flexibility, as well as no more TPS corruptions, etc.  It's a trade-off, and overall SQL is worth the trouble.

3rd-party tools like DMC, FM3 and DCT2SQL help to simplify your job, and you can turn to consulting firms like Mitten to help with your conversion.  Even so, life with SQL is definitely more complicated than good old TPS or DAT files (with all of their warts).