Base confused by compound primary key

English
, ,
#1

I am using a table in which the first two fields constitute the primary key, and of these the second may be blank, thus:

Boston      Centre       Some other info
Boston      Suburbs     Some other info
Cambridge               Info on whole of Cambridge

The table is stored in SQLite, which has no objection to this structure, provided that the empty key field is blank ("") and not NULL. However, my front-end in LibreOffice Base (version 7.2.5.2) seems unable to cope with this, whether I open the table directly or display it in a table-grid on a form. It is confused about record numbers (claiming e.g. that there are 129 records, when there are 141) and, while I can get correctly from the beginning to the end by scrolling through the table, if I click on the ‘Last record’ button I get an error message ‘Failed to refetch row’.

Has anyone else come across this and, if so, is there a solution?

One fetch is not getting all of the data and LO does not give clean splice when appending fetch 2 to fetch 1. Is this a bug or are we always supposed to know the required [fetch limit] to get ALL of the data in one fetch?
#2

Be careful with blank fields in LO. If you are creating a form this could be send as NULL from the form (no entry, NULL) and you will get an error.

Which connection to SQLite do you use?

#3

Hello,

This is a problem. Test was using SQLite3 and ODBC - only connector I know that works on Linux at least.

While I can go to the LAST record and get the correct count (only on first opening a table or Form), if I go to a NEW record instead it will be erroneous. Going back, the tables with the empty fields are missing. Even with single scrolling, the first fetch to get more records will result in the records not being displayed. This is the same in table or form.

And while I can enter new records with SQL having an empty field in the key, have not been able to do so otherwise (although didn’t try a lot).

Would seem this should be reported.

#4

Thank you both. This is for me a legacy problem, since the db was created many years ago in Paradox, which had no objection to nulls in a compound primary key. It was not difficult to turn these into blanks in order to satisfy SQLite3. The db structure defines the default value in the (potentially) untouched field as “”, and this is so also on the LO form that displays the table. But as Ratslinger has found, LO really does not cope with this situation: I am very nervous of editing entries with LO, in case it makes changes to a record other than the one I think I am looking at.

Shall report!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.