...
- 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
- 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
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
...
- 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:
...
- 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 Clarion doesn't currently support Unicode), but I've been told that the Unicode version works fine as well. The curly braces can be omitted. 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 ANSIUnicode};Server=closet;Port=5432;Database=test;Uid=usr;Pwd=pwd;...