...
The most important part of this is the definition of mytableid:
- serial tells it isn't a real datatype. It's just a signal to do this:
- 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
- associate 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 only component
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
...
- Change the Label to the mixed case MyTableID.
- Note the Data Type should be LONG.
- On the Attributes tab, set the External Name to mytableid | READONLY.
- The first part is the lowercase name of the column in your PostgreSQL table.
- READONLY 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!
- 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.
...
- I usually make the Table's Prefix match its Label, so that Customer:Name and Customer.Name are more easily recognized as the same thing. The prefix size is limited, though, so sometimes you're forced to abbreviate it.
- The Driver is ODBC. (There is no native driver for PostgreSQL.)
- The Driver Options need an /AUTOINC attribute. This specifies 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 is not clear the official Clarion docs:. If you happen to use Clarion's dialog in the dictionary editor, it will punctuate it like the second example (and get really weird looking in your generated source). Both work.
/AUTOINC=SELECT currval(<39>public.mytable_mytableid_seq<39>)(pg_get_serial_sequence('mytable','mytableid'))
/AUTOINC='SELECT currval(pg_get_serial_sequence(''mytable'',''mytableid''))'
- There are several additional driver options that you can add. They don't specifically related to auto-numbering, but you should add them nonetheless. You can look up each of these in the Clarion help:
/BUSYHANDLING=2 /NESTING=1 /USEDECLAREFETCH=1- The Owner Name contains the SQL Connection String. In my example I hard coded it, but usually I use a global variable that's used for all tables. The value is set once when the program begins. I use the ANSI driver (versus Unicode, as Although Clarion doesn't currently support Unicode, it's recommended that you use the Unicode driver. (If you're using UTF-8 encoding, then most of the western character set is the same as with ANSI.) The curly braces can be omitted. The server in this case is called closet (which is also its address), the default port for PostgreSQL is 5432, the database is called test, the user is usr, and the password is pwd:
Driver={PostgreSQL ANSIUNICODE};Server=closet;Port=5432;Database=test;Uid=usr;Pwd=pwd;...