[Solved] Updateable select query

[gpe]

Hi, I use ODBC to access my tables in PostgreSQL and create data entry forms in Base. I would like to create a form based on a select query that uses all fields from two tables, including, of course, the primary keys. In the query design view, the relationship between the two tables that I have set in PostgreSQL is visible as "inner join". When I save and run the query, it is not updateable; I cannot add records. When I edit the relationship and change it into Left Join, it becomes updateable: I can add records, however, whenever I add a record, Base crashes. After recovering, I see that the record has been added correctly.

Is this an issue with the ODBC driver? I have tried JDBC, but that doesn't work any better.

[gpe]

I will try to explain better. I have a lot of paper files for which I want to create a metadatabase. The general information of the file (file_nr, start_date, closed_date, shelf_location) are stored in table tbl_file. Each file has a number of documents of different types: letters, receipts, contracts etc for which I created the tables tbl_letter, tbl_receipt, tbl_contract. The documents have some general attributes in common, which go in table tbl_document: doc_nr, doc_date, doc_type, file_nr (which is a FK to the tbl_file) and doc_path, which stores the path to the scanned image of the document.
Now, I would like to create data entry forms, one form for each type of document. To combine the attributes from tbl_document and (let’s start with the receipts) tbl_receipt, I created a select query that includes all of the attributes of the two tables:

SELECT "tbl_document"."doc_nr", "tbl_document"."doc_date", "tbl_document"."doc_type", "tbl_document"."doc_path", "tbl_document"."file_nr", "tbl_receipt"."doc_nr", "tbl_receipt"."receipt_type", "tbl_receipt"."receipt_amount" FROM "filedb"."public"."tbl_receipt" AS "tbl_receipt", "filedb"."public"."tbl_document" AS "tbl_document" WHERE "tbl_receipt"."doc_nr" = "tbl_document"."doc_nr" AND "tbl_document"."doc_type" = 1

When I run the query, it is not updateable, I cannot add records. Changing the relationship to a left or right join will make it updateable:

SELECT "tbl_document"."doc_nr", "tbl_document"."doc_date", "tbl_document"."doc_type", "tbl_document"."doc_path", "tbl_document"."file_nr", "tbl_receipt"."doc_nr", "tbl_receipt"."receipt_type", "tbl_receipt"."receipt_amount" FROM { OJ "filedb"."public"."tbl_receipt" AS "tbl_receipt" RIGHT OUTER JOIN "filedb"."public"."tbl_document" AS "tbl_document" ON "tbl_receipt"."doc_nr" = "tbl_document"."doc_nr" } WHERE "tbl_document"."doc_type" = 1

Now, when I add record to the query resultset, filling all the attributes and making sure that tbl_document.doc_nr = tbl_receipt.doc_nr, and then save the record, Base crashes. After recovering the odb file and opening the query, I can see that the records were added correctly in both tables.

Any ideas what I can do to avoid Base from crashing with every added record?

[Villeroy]

Record sets are editable in Base when:
1) The database engine allows editing the record set in question by driver, connection type, user/group privileges.
2) The record set includes the primary key
3) The record set consists of one table only.
3a) In some cases you may be able to edit record sets from more than one table but only if the primary key of each table is included in your record set otherwise Base can not put a "finger" on the distinct records. Since I am always unsure about the circumstances when and why this works or fails, I use the form/subform mechanism to edit multiple record sets in one go. It is possible to build arbitrary complex form hierarchies where each editable form and subform is based on one table's record set.

[gpe]

Thanks for looking at this. I did try to create a form with a subform, where the mainform is based on tbl_document and the subform on tbl_receipt, manually selecting the joined fields (tbl_document.doc_nr and tbl_receipt.doc_nr), choosing the "labels on top" arrangement for both mainform and subform. When I open the form to enter data, it gives an error message "The data content could not be loaded. Invalid descriptor index." The form does actually let me enter the records correctly, but that error message comes up every time when opening the form.

I suppose I could move the general attributes from tbl_document into tbl_receipt (and also in tbl_letter, tbl_contract etc.) since they basically have a 1-1 relationship, so that each form takes attributes from one table only. However, this is not a nice solution and I would really like to keep things orderly...

[Villeroy]

"The data content could not be loaded. Invalid descriptor index."

I've never seen that error. Could be Postgre specific.

With your 1-1 relation try a query like:

Code: Select all   Expand viewCollapse view

SELECT "A".*, "B".* FROM "A", "B" WHERE "A"."X" = "B"."Y"

See if this record set is editable and if the shared key is updated in the dependent table. When I keep both sides separated in a form/subform, the dependent subform automatically inherits the key value from its parent.

Oh, I forgot a fourth point:
Record sets are editable in Base when:
1) The database engine allows editing the record set in question by driver, connection type, user/group privileges.
2) The record set includes the primary key
3) The record set consists of one table only.
4) Views and queries in direct SQL mode which are parsed by the backend engine are not editable at all, not even a most simple SELECT * FROM X is editable in direct SQL mode.

[gpe]

A select query with only the shared/joined attributes in a 1-1 relationship is also not editable, but it becomes editable, again, if I change it into a right join:

SELECT "tbl_document"."doc_nr", "tbl_receipt"."doc_nr" FROM { OJ "filedb"."public"."tbl_receipt" AS "tbl_receipt" RIGHT OUTER JOIN "filedb"."public"."tbl_document" AS "tbl_document" ON "tbl_receipt"."doc_nr" = "tbl_document"."doc_nr" }

The shared key is indeed updated in the dependent table, but Base crashes with every added record.

[Villeroy]

All I can suggest is to try LibreOffice. It comes with a built-in Postgre driver.

[gpe]

Did as you suggested and tried it in LibreOffice. Works fine there without errors, without crashes, so I'll use that.
Thanks, and have a good day!