I'm investigating a problem in an OOo-based database application and I
think I've located the cause. Also, I suspect that it's in the OOo Form
handling code rather than in the sdbc connection driver.
My environment is:
FC3 Linux
OOo 2.0.3
postgresql sdbc driver (version 0.7.2)
PostgreSQL 8.1.
The problem:
The application uses OOo Form controls and connects to a PostgreSQL
server. The table that the form binds to has an autosequence column. The
problem is that the form doesn't insert a correct auto value when saving
a new record. It inserts a value of 0 (zero) and then returns the wrong
record to the record set, which is displayed by the form on refresh. In
comparison, adding a new record via the OOo data explorer table view
works fine.
The Cause:
Using tcpdump to compare the SQL commands sent from OOo to PostgreSQL, I
can see where the problem is occuring. When adding a new record via the
form, the INSERT includes the autosequencing column (sampleid), which
inserts a value of 0 (zero). When adding a new record via the OOo data
explorer, the sql INSERT command doesn't include the autosequencing
sampleid column. In both cases, they perform a SELECT MAX(sampleid)
followed by a SELECT * where sampleid equals the value returned by the
earlier select max. For the form, this obviously selects the wrong record.
The workaround and possible solution:
The only workaround I've found thus far is to remove the autosequencing
sampleid control from the form (which was set up as a read-only control
for reference only). I can get the value directly from the row set and
display it as a label control on the form instead of binding it directly
to a control. That way it doesn't get included in the SQL insert
statement, and this works. However, I think a simple general solution
would be to exclude columns that are bound to either autosequencing or
read-only form controls from the SQL insert statements when records are
saved.
Another concern I have about the save record process involving auto
sequence fields: the SQL "insert" and "select max" statements don't
appear to be performed as a single transaction. At least, I didn't see
any SQL begin/commit statements (or equivalent).
Thanks.
Ross
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
