I want to generate unique keys for a table in a PostgreSQL DB that looks
like this:
CREATE TABLE tkt (
id SERIAL NOT NULL PRIMARY KEY,
username VARCHAR(16) REFERENCES userlist(username),
techname VARCHAR(16) REFERENCES techlist(username),
problem VARCHAR(128) NOT NULL,
opendate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
closedate TIMESTAMP
);
PostgreSQL reports the "serial" type as "integer" via ODBC. Since OOo does
not think this is an auto_increment column, it doesn't know that it can
send a NULL and allow the DB backend to generate a key (I've invested too
much time in PostgreSQL to switch to MySQL over this).
In the wizard-generated OOo form, the id column is bound to a formatted
input field.
If I enter a number in the id field on the form, OOo will crash if only a
few records are in the table. If there are lots of records in the table,
it won't crash, but the cursor is in an invalid state, requiring a DB
refresh before the new record can be found and displayed. I haven't played
with it enough to know how many "lots" is. I suspect it's more than 11,
which is the typical number of records OOo shows as available when
browsing the table using a form. Even though it crashes, the record is
successfully inserted into the DB.
If I change the id column from SERIAL or INTEGER to VARCHAR or CHAR, I can
then enter a value in the form and the record will be saved and displayed
properly (as long as the value is not a duplicate key). A dupe key entry
only displays an error dialog -- no crash.
I'm willing to live with a VARCHAR column as a primary key, but I still
want to auto-generate the value, preferably using the sequence function in
PostgreSQL. I can assign a macro to the "before submitting" event of the
form that calls a Shell script to get the next value in the sequence, but
I don't know how to capture the output of the Shell command into a
variable in the macro (write/read/delete a file? set/read clipboard?).
Alternatively, I could have the macro run a SELECT query on the database
to get the next sequence value, but I'm just starting to learn OOBasic and
would appreciate help with this (assuming it's even possible).
I'm running OOo 2.02 on Linux (FC5, latest RPM updates), and PostgreSQL
server 8.1.4 with postgresql-odbc-08.01.0200-1.2. Suggestions, pointers,
and especially examples would be appreciated.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
