Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a PostgreSQL database, with data from some 8 tables (not counting enums implemented as a table referenced by foreign key) summarized in a view. I made an INSTEAD OF trigger, so the view is in fact completely updateable. I connected the database to OpenOffice Base through jdbc so that I could make forms for it in OpenOffice. There is an option to connect a form to the view as though it was a table, but since the view has no primary key (it can't in PostgreSQL), the view is read-only.

Is there any way to make the form active for the view? Or do I have to decompose the form into one primary and seven sub-forms, so that I could use it to insert data? Or is there any other solution? Or is it not possible to make such a complex form in OOo Base at all?

share|improve this question

1 Answer 1

According to this bug report, there is currently no way to insert data into updatable views through SDBC in LibreOffice Base (I assume it's the same about OpenOffice Base). I tested JDBC and ODBC as well, and no luck. There is no way how to point the Base to a (fake) primary key in the Base, and Base won't insert data into tables without primary key. The author of the report promised to solve this issue himself, but this project looks dead now.

However, the post pointed me to a workaround: when you add a serial-like column to the view (like ROW_NUMBER() OVER (ORDER BY table.id ASC) AS oid), you can assing it as the primary key while importing the view to MS Access through ODBC. This works with MS Access 2003 for me - the data can be inserted into database, with only minor bugs. Access is as good at making forms as OpenOffice base, so this is a good workaround for anyone who have the Access.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.