Ross Johnson wrote:
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 workaround:
My apologies as this is perhaps more appropriate to the users list, but
since it's a followup ... another workaround that I've just found is to
put the auto field in a subform bound either to the same table, or a
leaner SQL query that includes just the autofield column, then link the
two forms via what is effectively the same column. At least that get's
me out of having to write any code and, in particular, avoids having to
write code to update the field as I view different records in the form,
because I couldn't find a convenient event to hang the macro on to do that.
Ross
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
