Ocke,

please have a look at this. Normally, I would expect OOo to behave
like for other databases with auto-increment fields. Would be
interesting where exactly this bug here happens, and if we can easily
fix it to at least a state where we can successfully insert records,
though not necessarily correctly re-fetch them (which is the current
state with auto-increment fields in other databases at the moment).

On the medium run, we should re-design the auto-increment handling
with drivers knowing theirself how to refetch.

wanted to assign this to Ocke ....

seems I'm drunk .... oj, not fs .....

Hi garf,
the problem here is that we don't get any information about which
column is an auto_increment field. If this is fixed, we could use the
currval('tablename_columname_seq') function to reftech the newly
inserted value.

Best regards,
Ocke
PS: I'll start to implement some general stuff like to use
$table_$column_seq as argument for the currval function.

targeting to "OOo Later"
As long as the PostgreSQL doesn't return the information 'bout
auto-increment columns, there isn't much of a chance for OOo ....

"As long as the PostgreSQL doesn't return the information 'bout
auto-increment columns, there isn't much of a chance for OOo ...."

That could be difficult.  PostgreSQL doesn't have columns that are
marked as "auto-increment".  Instead, the approach that PostgreSQL
takes is to have "Sequence generator" objects that are standalone (not
tied to a table) and give back "the next number in the sequence" when
something asks for one.   When the field definition of a table has
it's default value set to "nextval('some_sequence_generator')" you get
something very close to what would be called an auto-increment field.
 An advantage to this approach is that several fields from different
tables can all tie to the same sequence generator and have shared
sequence counting between them.

It should be pretty simple to have OOo not mark such fields as "Entry
Required", because this would then let OOo pass the inserted row data
to the PostgreSQL backend and it would generate the correct number
in-place?

Ocke, can you change the "OS" from "Linux" to "All" for this issue as
well?  It affects Windows PC's as well, and probably everything that
accesses PostgreSQL through ODBC.

Can we please bump the priority for this issue to P2?  This issue
appears to be serious enough that it will stop OOo from being used as
an alternative interface to all decently established PostgreSQL
applications (the use of serial/sequence types like this in PostgreSQL
is *very* common).

Would like to change the target milestone to be "OOo 1.1 Beta" as
well, because this really should be fixed.  PostgreSQL is widely used
in fairly complex environments.

Justin, one problem I see here is to reliably detect fields which are
"bound" to such a sequence (or however we call it). Do you see a
possibility except that we parse the default value string? Once we
have this, we could refetch an inserted row with the curval.

But: I would have a little headache doing this in a generic ODBC
driver, as it is way too specific to PostgreSQL (this would be a
perfect opportunity for a dedicated PostgreSQL driver :).
The more as I suppose that a proper implementation requires some
guessing - for instance, what to do with a SELECT statement spanning
two tables? We would need to parse the statement an assign the match
the resultset columns with table columns. This is somewhat error-prone
(as an example only: there are ADO drivers out there where this
appraoch is known to fail), thus, in my opinion, misplaced in a
generic driver.

Thus I would prefer a solution where we fix this problem more precise,
to have a cleaner code and to not brake anything else.

> Trying to update a row in a table with a sequence field causes the
> error ...

Garf, can you please confirm that this happens not only with
insertions, but also with updates? Normally, we shouldn't have a
problem with updates of sequence fields, and in fact we did not
encounter them here ...

Hi, i'm ralf with the same problems as garf.
The problem is not where the default is come from (sequence or constant).
If I set a default-value with openoffice, then the value is in a new
row, if the dafault is set in the DB, it will be inserted when I left
the new row.
In 2. case OO tries to insert a NULL-value and make problems if NULL
is not allowed.   
But the problems comes not from the DB. The DB uses the default.
I think OO or the odbc-driver checks, wether NULL is allowed or not
without checking for a default-value.
Possibly it is better making an insert and wait for an error from the DB.

ralf, thanks for explaining this! This indeed sheds a different light
on the problem. But I would like to be sure that this is really what
garf was talking about here originally - we should open another issue
otherwise, to not mix things up. Garf?

Hi Frank,

Just thought about your sentence "Do you see a possibility except that
we parse the default value string?"

With this, you probably don't need to parse the default value string
at all.  If there is _any_ default value string present at all, then
OOo should ensure it doesn't try to make the field NOT NULL.  This
will automatically let PostgreSQL do whatever needs to be done for
generatig a default value.

The problem really seems to be that OOo assumes the NOT NULL attribute
on primary key fields or something, thereby never letting the backend
even get the INSERT.

If a finger-pointing comparison is in order, MS Access 2000 works
perfectly fine with the same PostgreSQL database tables that OOo
chokes on due to this problem, working from the same ODBC drivers and
data source.

Hi Justin,

I think we indeed have two problems here - the sentence you cited was
referring more to the serial-problem with PostgreSQL in general :)

For the NOT-NULL problem:

I just tried it again to confirm it: The PostgreSQL-ODBC driver
returns inconsistent information about sequence fields: When asking
with SQLColumns, it claims that the column is _not_ nullable. When
creating a statement "SELECT * from <table>", and describing the cols
of the result set, then the driver claims that the column indeed _is_
nullable.

Well, for some reasons it is sometimes necessary to rely on the
information as returned by SQLColumns, instead of the meta information
of the result set. There are numerous other drivers, noticably some MS
stuff, which are inconsistent there, too, and OOo simply has to decide
which information it should declare as reliable.

Is there a reason why the ODBC driver is inconsistent, or is this
simply a bug?

Hello,
As a humble suggestion, you could take a look at how Metabase php db
abstraction layer solved this auto_increment / sequences issue.
It is hosted at http://www.phpclasses.org
It has a pg driver that works well at this.
Then, maybe some idea exchanging with psqlodbc crew could lead to a
good solution.
Regards.
Andre Felipe

Hello,
I found an interesting article that could help.
http://www.developer.com/open/print.php/10930_631251_2
Regards.
Andre Felipe Machado

Andre, thanks for these pointers.

for the records here: A completely new option may be possible with the
native PostgreSQL-SDBC driver which is currently developed by an
external guy - see dev@dba.openoffice.org

Hi Jörg,

this may be possible with your new driver. So I think that this issue
is best palced at your side.

Best regards,

Ocke

well, then let's give it to Jörg :)

Though I don't know yet how to deal with it, I'am certainly the one to
solve it.

Hi,
this problem still exist in oo11rc3 so its still a bug in oo.
Its not odbc, problem not exist when using access.
any news about this ?

Hi,

it will be added to the native postgresql driver implementation,

see 

http://dba.openoffice.org/drivers/postgresql

.

However, I don't want to say a date when this will be fixed.
(and actually, I still don't know, how to fix it completely
unfortunately up to now).


Bye, Joerg

> this problem still exist in oo11rc3 so its still a bug in oo.
> Its not odbc, problem not exist when using access

Well, I did some ODBC traces, and Access uses some completely
different approach for this. Without going into details: When OOo
would use the same appraoch, then due to some specialities of OOo,
this would have a non-negligible chance of data corruption. In Access,
this chance seems to be present, too (at least I didn't find out yet
how they prevent it, but also couldn't trigger it), but is much smaller.
Changing OOo's architecture in this respect would be possible (and not
only PostgreSQL would benefit from it, basically we could drop the
"you need a primary key for editing records" restriction), but pretty
expensive in terms of workload.

change subcomponent to 'none'

This limitation of OOo with PosgreSQL is pretty much a show stopper for its use
with any complex database application with OOo as a client. No one wants to have
to manually increment a primary key, let alone keep track of manually generating
keys in related tables with a foreign key. This is not a problem for a database
with really simple unrelated tables that have some sort of natural primary key,
but for anything else where autoincrement is being used, using OOo as a client
with PostgreSQL is not feasible. This limitation does not seem to depend on the
drivers one would commonly rely on in the form of JDBC or ODBC, but rather this
seems to be an issue related to OOo itself. The OOo SDBC PostgreSQL driver is an
alpha project and lacks essential capabilities at this point in time.

I have not tried to duplicate this problem with an ODBC driver using OOo as a
client with PostgreSQL, but I tried several different JDBC PostgreSQL drivers
from different sources over a period of a couple of days while I tried to figure
out why I was getting an error in OOo while attempting to insert a new record
using OOo. Yet the record was actually inserted into the database table with the
autoincrement column properly incremented as advertised. I had to use PgAccess
to view the data to see this. This means that the database is doing what it is
supposed to do. I tried MS Access 2002 with the PostgreSQL database tables set
up as external links and the autoincrement works on the serial primary key
field. So this cannot be brushed off merely as a PostgreSQL idiosyncracy.

I made the assumption that using the OOo recommended JDBC "database independent"
connection using the PostgreSQL pg73jdbc3.jar file for the JDBC Driver class and
org.postgresql.Driver in the JDBC class path of the JDBC tab in the OOo Database
Administration somehow would be the place where basic issues like returning the
proper values back from an inserted record would be implemented properly without
the user having to do much more than have a correct databse set up and to make
the connection properly, which by the way are not trivial prerequisities. This
assumption cost me more hours than I choose to say trying to find out what was
causing this error. 

Frankly, I hesitate to be critical here because it smacks of a lack of
appreciation for what is clearly a fine effort by others and OOo has some very
nice features, but the DB area is really rough around the edges and not all too
well-documented in too many areas. 

There is way too much language in the various souces of information like "a
typical connection would be as follows" and there is a dbase or mySQL example. I
had to scour source READMEs and source  INSTALL files, etc before I found what
to use in the dialog for the JDBC class path for PostgreSQL. Same for the JDBC
Driver Class over in the OOo Options under the "Security" category. 

There is a generic reference to "Retrieve generated values", "Auto-increment
statement" and "Query of generated values" of the JDBC tab of the Database
Administration in the OOo Help system:

"Retrieve generated values
Enables OpenOffice.org support of auto-incremented data fields for the current
ODBC or JDBC data source. Select this check box if the database does not support
the auto-increment feature in its SDBCX layer. In general, the auto-increment is
selected for the primary key field.

Auto-increment statement
Enter the SQL command specifier that instructs the data source to auto-increment
a specified Integer data field. For example, a typical SQL statement to create a
data field is: 
CREATE TABLE "table1" ("id" INTEGER)
To auto-increment the "id" data field in a MySQL database, change the statement to:
CREATE TABLE "table1" ("id" INTEGER AUTO_INCREMENT)
In other words, enter AUTO_INCREMENT into Auto-increment statement box.

Query of generated values
Enter an SQL statement that returns the last auto-incremented value for the
primary key data field. For example:
SELECT LAST_INSERT_D();"

What does this mean? Will this fix the problem of this issue? How does it work
with PostgreSQL? This is great if you already know what this means and what it
is supposed to be used for and when, and if you are using mySQL. It is not much
help otherwise. And I can't find out what it means by searching around for
references or find any real examples.

I have two suggestions:

First, on the OOo web site for the database project (http://dba.openoffice.org)
there needs to be a compatibility table listing the most commom databases that
OOo is known to work with and with what connection methods, along with any known
bugs or limitations (this issue would be one of them for PostgreSQL). 

Second there needs to be a page with the details of the specific required
entries for each database that OOo claims is compatible for JDBC, ODBC, SDBC or
what ever else applies for a connection. General connection documention that
says "your connection for PostgreSQL" should look something like the following
example for mySQL" is why there are so many connection problem threads in my
opinion.

In my case I have two choices. Give up on using PostgreSQL with OOo and switch
to mySQL or use PostgreSQL with another client (TBD) and give up on using OOo
database features. I guess I will switch to mySQL since it seems this is the
defacto "supported" database under OOo, and I would really like to use OOo.

Hi,

with the latest version of the postgresql-driver

http://dba.openoffice.org/drivers/postgresql/index.html ,

you can create tables with serial columns (by checking the auto increment flag)
and you can insert data in the beamer window as long as the table has oids.

Bye,

Joerg

Hi,

Also, this is very similar (probably the same) error that occurs with Oracle 9i
and 10g when inserting a new row if one of the columns has a value that's
supposed to be filled in by the database.  (i.e. using Oracle sequences,
triggers, whatever)

Like with PostgreSQL, it gives the same "[DriverManager] Invalid curor state",
and also like PostgreSQL this is the one big thing that renders the OOo as a
non-option.

If this _one_ issue is fixed on the OOo side of things, it'll massively open up
options for end users.  Really! :)

Regards and best wishes,

Justin Clift

Quick and dirty workaround thought...

Would it be possible to check for error 24000 from the ODBC driver (prior to the
error being displayed to the user) and if it's this error then just reread the
row that was written instead.

It's a workaround, but it'd probably be reliable and it'd make PostgreSQL,
Oracle, and possibly SQL Server work _usably_ through OOo.

?

Regards and best wishes,

Justin Clift

Hi,

just for the records,

> it'd make PostgreSQL, Oracle, and possibly SQL 
> Server work _usably_ through OOo
the current version of the native postgresql driver supports defaults as long as
your table has oids. It uses a feature of the postgresql libpq, which returns
the oid after an insert.

Bye,

Joerg

Hi Joerg,

Sure, but that only helps in a very small set of environments.

 + Production quality releases of PostgreSQL haven't required OID's on tables
for nearly a year and a half, so there's quite a decent userbase in existance
who's tables couldn't be used.

 + Your SDBC driver is marked as _alpha_.  Only environments where _alpha_
quality drivers are acceptable will be used.

 + This problem in OOo seems to be common across more than PostgreSQL.  It
includes at least both PostgreSQL (all recent versions) and Oracle (all recent
versions), and other reports indicate it also includes SQL Server (unsure which
versions).  It seems like a fairly generic problem in the way OOo is handling
things.

If your SDBC driver was released as production quality code, it would have more
users.  If it also no longer required OID's, then it would be usable by the
majority of PostgreSQL installations.

I hope that helps.

Regards and best wishes,

Justin Clift

Hmm. With OOo 2.0 Beta Candidate (and also with the latest developer snapshots),
I don't have any problem with SERIALs.

- PostgreSQL 8.0.1 running on WindowsXP
- PostgreSQL ODBC driver 7.03.02.00 (also WinXP)
- OOo's default ODBC connection, not Joerg's driver
- table created with explicit or implicit SERIAL field
- open the table data view in OOo, enter some data in a new record, but leave the
  SERIAL field empty
=> everything works fine, OOo properly refetches the automatically generated values
- Also updating existing records works like a charm
- (deleting records in the table data view leaves some garbage in the UI, will
submit
  another issue for it)

Admittedly, we didn't do anything *explicitly* for this issue, so I'm not sure
what change fixed it, but it seems it *does* work ... Justin, where's my error
in reasoning?

Thanks Frank.

One thought: Was the table you're testing with created in OOo, or a table
created externally that OOo has been hooked up to?

If you can give me the table structure to replicate with, I'll test in my end
with the same drivers and such and see if I get the same problem.

:)

Regards and best wishes,

Justin Clift

created via Tools|SQL in OOo :). Executed the following:
  CREATE TABLE customer ( customer_id SERIAL, name CHAR(30) )

Declaring customer_id as PRIMARY KEY (either directly in the statement, or later
on via OOo's table design) didn't change anything - it worked with and without
the PK.

Of course the above means that psql implicitly used oids with the table.

If I add an WITHOUT OIDS to the CREATE statements, then OOo fails to even *open
the table. The error message then is
  ERROR: column "oid" does not exist.
Well, that's right, but funnily this happens when we try to execute the statement
  SELECT * from "public"."customer"
I looked into the ODBC trace - we do *nowhere* refer to an oid column,
nevertheless the SELECT fails.
Though, it still seems to be an OOo problem: If I use ODBCTest with the above
SELECT statement, this works like a charm. Hmm. /me is confused.

Heh Heh Heh

Yeah, I know the feeling with this stuff. :)

It's almost 3am here, and my brain is too foggy to test this properly, so I'll
go through it in the morning after sleeping.

Meanwhile, are you ok to fully document the settings in each of the pages of the
PG ODBC driver you're using?  I'm thinking that screenshots will save typing,
because from memory there's quite a few options... :-/

In the morning, we'll compares versions and options and stuff, and that might
help with the OID problem you're getting.

Regards and best wishes,

Justin Clift

@justin: I submitted issue 44050 for discussion things which speaking strictly
do not belong into this issue here :)

*** Issue 63529 has been marked as a duplicate of this issue. ***

I have installed the postgres jdbc0.7.2 and I have connected to my database
using base sucessfully.

I have tried setting my serial (autoincrement) field in postgres to allow null
values so ooo base can insert the record and postgres to add the serial field.
However my serial field is the primary key and there is no other field that can
could be a natural pk in the table, ooo base will not allow me to add records to
a table with no primary key.

ps. using ooo and postgres on win

Rgds
Ben

The Issue you raised has been marked as 'Resolved' and not updated within the
last 1 year+. I am therefore setting this issue to 'Verified' as the first step
towards Closing it. If you feel this is incorrect, please re-open the issue and
add any comments.

Many thanks,
Andrew
 
Cleaning-up and Closing old Issues
~ The Grand Bug Squash, pre v3 ~
http://marketing.openoffice.org/3.0/announcementbeta.html

As per previous posting: Verified -> Closed.
A Closed Issue is a Happy Issue (TM).

Regards,
Andrew