Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

By Mike Hanson

In my opening article, Auto-Numbering in SQL – An Overview, I touched upon many of the various issues and decisions that you must tackle when addressing the challenge of 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 Server

Each flavors of SQL handles auto-numbering with it's 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 one, and you can continually ask it for the next number.  You can create a sequence at any time, and it doesn't have to be related to anything else.  There are many optional parameter, but the simplest form is:

CREATE SEQUENCE mysequence;

This creates a basic counter.  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 applies in the way that we'll be using it.

The SEQUENCE doesn't handle everything, of course.  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, but using 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:

  • serial tells it:
    • use an integer for storage
    • create a SEQUENCE called mytable_mytableid_seq
    • set the default value for the field as nextval('mytable_mytableid_seq')
    • set the sequence attribute of the field to match
    • associates the SEQUENCE with the TABLE (so it's dropped if the table is dropped)
  • PRIMARY KEY tells it to create a primary key constraint called mytable_pkey with mytableid as the column

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 case 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

Note that the auto-numbering is done only as a default.  If you have existing data to store in the table, you can do it by specifying the existing values for mytableid in the INSERT statement.  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 desired value:

ALTER SEQUENCE mytable_myfield_seq RESTART WITH 1234;

The next value requested will be 1234 (so it assumes your pre-existing values ended with 1233).

In Your Dictionary

If you're creating an app from scratch, you could import that definition into your dictionary, or you can define it in Clarion from scratch.  One thing you don't want to do is let Clarion's CREATE command create the 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.

Note that the PostgreSQL labels are lowercase, but I definitely 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 in MixedCase as you're used to inside Clarion.

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

Auto-Numbered Field

In the table creation script above, the field name is mytableid.  I want to adjust that, and tell it a bit more about it's auto-numbering duty:

  1. Change the Label to MyTableID.
  2. Note the Data Type should be LONG.
  3. On the Attributes tab, set the External Name to mytableid | READONLY.
    • The first part is the name of the column in your PostgreSQL.
    • The READONLY segment tells Clarion that the field will always be set in the back-end, so never attempt to assign values to it directly.  Note the space on either side of the pipe character is required.
  4. On the Options tab, add a new property IsIdentity.  It's a Boolean, with a value of TRUE.

Primary Key

You may have heard that keys defined on the Clarion side are helps for use with the template, but they need not be defined as-is on the SQL side.  Clarion will use the key definition in the dictionary to determine the ORDER BY clause sent to SQL.  Keys on the SQL side are for efficient queries.

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.

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 backend, but it's definitely should match.  In addition, check the following settings:

  • Require Unique Value is ON
  • Primary Key is ON
  • Auto-Number is OFF(That's right!)
  • Exclude Empty Keys is OFF
  • Case Sensitive is ON
  • The single Column within the key is MyTableID

Table

Although they aren't all related to auto-numbering, I'll cover various additional aspects of the Table settings, to help clarify ongoing confusion:

  • I usually try to make the Table's Prefix match its Label, so that Customer:Name and Customer.Name are more easily recognized as the same thing.
  • The Driver is ODBC.
  • The Driver Options need an AUTOINC attribute.  This is the SQL command that's executed after the INSERT statement, to determine the new sequence value that was just assigned.  Note the punctuation, which differs from the official Clarion docs:
        /AUTOINC=SELECT currval(<39>public.mytable_mytableid_seq<39>)
  • The Owner Name contains the SQL Connection String.  I use the ANSI driver (versus Unicode, as Clarion doesn't current support Unicode).  The server in this case is called closet, the default port for PostgreSQL is 5432, the database is called test, the user is usr, and the password is pwd:
        Driver={PostgreSQL ANSI};Server=closet;Port=5432;Database=test;Uid=usr;Pwd=pwd;
  • The Full Path Name is public.mytable.  Note that as with the sequence name above, it's all lowercase, and I specify the Schema.  In PostgreSQL the default is schema is public, in contrast with Microsoft SQL Server where it's dbo.
  • The Create attribute is OFF.

Conclusion

As you can see, there are many crucial steps to get this to work, compared to the 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, you should do things they way they do, and each flavor of SQL does it differently.  Clarion provides all the pieces to make this work, but it's certainly harder overall.  SQL is faster, more robust, and more flexible than ISAM.  It's a trade-off, but 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 more complicated than good old TPS or DAT files.

  • No labels