AUTONUMBER MALFUNCTION FOR BASE WITH UN-EMBEDDED FILES

[gareth27]

I have just set up base with an UN-EMBEDDED FILE structure. All seems fine - except that when I create a table with an autonumbered key field the autonumber selection malfunctions. To clarify:-

1. create table with a key field with autonumber selected (YES), and save it.

2. now select edit of the same table, an examination of the key field shows that autonumber has changed to (NO) without my intervention. (why has the program done this?)

I have not had this problem with the EMBEDDED version of base so I think I know what I am talking about! This problem renders base un-embedded base completely unusable.

A definative answer would be appreciated.... I cannot trust base with my data without
an understanding that it will never happen again.

Thanks
_________________
G JONES

[dacm]

The Base Graphical User Interface (GUI) works well when managing Field Properties within the context of an "Embedded database" configuration. However, this configuration is prone to data loss due to zip (.odb) file corruption upon OOo crash, so it's best avoided for any serious database work beyond prototyping.

The immediate solution configures Base for external database access through the built-in HyperSQL database engine (tutorial). So fortunately, Base supports alternate database configurations through compatible drivers (JDBC, ODBC, etc.). Unfortunately, many of these drivers do not integrate with the 'Field Properties' aspect of the Base GUI very well. Or at least not as well as you might expect.

In reality, the Field Properties only appear frozen. You can effectively access most Field Properties by other means through the GUI. For instance, you can Cut, Copy, Paste, Delete, Insert Rows [database columns] which then grants access to the column's Field Properties. You can also apply a Primary Key(s). Or better yet, you can Right-click > Copy > then Paste the entire table in order to access most exisiting Field Properties (perhaps repeating this twice to re-use the original table name; then issue CHECKPOINT DEFRAG to recover the minimum database size using Tools > SQL...). This last option is outlined step-by-step with graphics at in this tutorial link.

But we still need a workaround for: AutoValue and Default Value. These workarounds (and others) require SQL as outlined below. The last remaining field property, Format Example, is apparently hard-frozen because there's no known workaround.

AutoValue

If you try to set the AutoValue field, it will always flip to "No" -- but this can be set to 'Yes' using SQL as outlined below (keeping in mind I'm not the resident SQL expert so backup your database folder first):

With an existing table:

use Tools > SQL... to add the AutoValue function to your Primary Key column as shown below...
replacing the orange-colored Table and Column names with your own:

ALTER TABLE "TableName" ALTER COLUMN "ColumnName" INTEGER IDENTITY;

This will update the table but you'll need to click: View > Refresh Tables to see the change in the Base GUI.

Note: You'll get an error if the specified column doesn't exist OR is not already a Primary Key column.

To add a new column as the Primary Key with AutoValue:
ALTER TABLE "TableName" ADD COLUMN "ColumnName" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1)
or
To add the Primary Key constraint to an existing column with unique values:
ALTER TABLE "TableName" ADD PRIMARY KEY ("ColumnName")

Note: INTEGER IDENTITY provides up to a couple billion (2^31) records per table. This generous limit equates to generating 100,000 records, per table, per day, for 60 years. But these values are generated in sequence without re-use so if you commonly generate millions of records per week then consider BIGINT IDENTITY which allows nine-quintillion (2^63) unique values -- which equates to generating 1 billion rows per second for 300 years.

Note: GENERATED {BY DEFAULT | ALWAYS} AS IDENTITY syntax is supported by HSQLDB 2.x. The two options {BY DEFAULT | ALWAYS} are very similar when applied to a Primary Key or other Unique Constraint column. Prior to HSQLDB 2.2 the IDENTITY sequence generator could only be applied to the Primary Key column.


When creating a new table: (with the Base wizard)

Select an ID field and AutoValue = Yes,
then type IDENTITY in the resulting 'Auto-increment statement' box.
Then de-select Create a primary key on the Next page of the Table Wizard.

Default Value

A Column's Default Value can also be setup using SQL as outlined here. Or, if you have the know-how, you can use a text editor to make the changes directly in your .script file.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Migrating 'Embedded databases'

[gareth27]

AUTONUMBER MALFUNCTION WITH UNEMBEDDED BASE

THANKS TO DCAM, FOR ANSWERS. MUCH APPRECIATED, WILL TRY DIRECTLY.

I ASK THE BASIC QUESTION, SHOULD A MATURE PIECE OF SOFTWARE HAVE
A PROBLEM LIKE THIS IN 2010? I CAN UNDERSTAND GLITCHES IN PROGRAM CODE
PERHAPS, BUT A FUNDAMENTAL FAILURE OF THE SOFTWARE?

BEFORE COMMITTING TO BASE I ASK JUST HOW MANY PROBLEMS WILL I HAVE TO
ENCOUNTER AND RESOLVE BEFORE ACHIEVING RELIABLE USAGE? IT WAS EASY FOR ME TO TELL THE DIFFERENCE BETWEEN SOFTWARE BUG AND MY LACK OF KNOWLEDGE THIS TIME - BUT THIS IS NO ALWAYS SO.

USING COMPUTERS IS ABOUT JUST THAT, USING THEM, OPEN OFFICES ADMITTEDLY EXCELLENT KNOWLEDGE WEB SITE DOES GIVE THE IMPRESSION HOWEVER THAT
USING BASE IS ONE PROBLEM AFTER ANOTHER. PLEASE CAN SOMEONE GIVE REASSURANCE BASED UPON WIDE KNOWLEDGE AND EXPERIENCE THAT THIS IS NOT SO?

LASTLY - IF EMBEDDED USAGE IS GENERALLY REGARDED AS UNRELIABLE WHY
DOES OPEN OFFICE SUPPLY BASE THAT WAY AS STANDARD?


THANK YOU

G JONES
_________________
G JONES

[dacm]

G JONES,

If you can't handle bugs and workarounds, you won't like Base. With respect to stability, Base crashes on me at least once an hour during heavy development -- especially of macros -- but things stabilize to acceptable levels after development including daily-use by the end-user. Base/OOo crashes DO NOT lead to data-loss when using a proper database configuration perhaps like the Quick Fix (see sig links below). As I've mentioned before in this forum, Base is the buggiest software I've ever used twice. But if you, or your customers, need a FLOSS, cross-platform, desktop solution including Unix, then Base rises to the top of your choices rather quickly.

Concerning the default database product created by Base, it makes some sense to attempt a single-file database. OpenOffice.org simply did it wrong. There should be a way to replace only the front-end components without affecting the latest data. And the data-loss issue associated with the single-file database is simply a design flaw. A single-file database is difficult to design, but the durability of user-data should never rely on the stability of the front-end GUI. I'm guessing the culprit (zip-translation processing and file input/output) could be off-loaded to a separate process to gain the necessary stability. My guess is this could be coded in a day using built-in Java (zip) classes since Java is required for the single-file database otherwise. But I digress.

Otherwise I prefer MS Office. I mean for $80 online with MS Access if you have a student or military in the house, I see no reason to fret with OOo. It simply lags in stability and user-friendliness, not to mention features. I carry OpenOffice Portable on my USB flash drive and I've grown to like Calc and Impress but absolutely hate Writer due to it's non-standard handling of end-of-line spaces (fixed in LibO 3.5). This issue too often results in an unknown number of spaces, which then cause problems if the text reflows with format changes, etc. I now refuse to collaborate with co-workers using Writer because it always results in hours of tedious work fixing the spacing issue because find&replace also removes intentional spacing in long, complex documents. I'll accept AbiWord products anyday. Impress is fairly good but inconsistent across forks such as Go-oo, which doesn't render my animations [correctly] the same as OOo. Again, this becomes a collaboration issue, but differing versions of PowerPoint can exhibit the same limitations, but various compatibility filters help across MS Office versions. Calc is really mature, but complex spreadsheets seem to require extra re-calcs to get the correct answers which I've rarely seen in Excel.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Migrating 'Embedded databases'

[flyingfox777]

Not sure if this a appropriate location but I have had a problem with autonumbers between Calc and Base which I have now manually solved but I am now curious as to why it occurred in the first place.


History of error and work around used:- (might help someone else who is briefly stuck with the same problem)

Viewing table of external registered database in Calc (using F4) I created a new record.
When the record was created the Autonumber ID field changed to 66666612 for the new record number - the table previously had 18 entries!!

When I refreshed the table the initial record in the table's ID had been renumbered from 0 to 66666613.

Concern here is that the ID field is used/will be used as a foreign key on another table.

When I access the database through Calc the following error messages were displayed:-
-Cursor updatability mismatch
-- Warnings were encountered while connecting to the data source. Press "More" to view them.
-- SQL Status: 36502
-- Error code: -4712
-- cursor updatability mismatch

Is this information related to the autonumber error?
Just tried opening the table with Base closed and the error messages do not appear.

Although I can manually edit the autonumbers to recreate the correct sequence (which surprises me - I thought they would be locked) the autonumber sequence continued with the last generated number. I have now edited the Script file so the sequence restarts as it should.
End history


Questions:-
1) Should I avoid using autonumbers that start with 0 and force them to start with 1 instead.
2) Does Calc use older HSQLDB version1.8 supplied with the install rather the recent version that I have downloaded for Base and does this cause the errors I have seen?
3) Did I make a mistake by assuming that I could open Calc and Base with the same database at the same time?
4) Does the JRE have anything to do with this (using 1.6.24 but not yet able to change to 1.6.22)?

Sorry for the long post but I hope that this information is of use to somebody but also if anyone can shed a little light on the possible cause I would appreciate the extra insight.

Many thanks in advance.
_________________
Always in the proverbial - only the depth varies.
Running LibreOffice 3.5.4.2, HSQLDB 2.2.9 with JDBC (separate database from frontend) on Ubuntu 12.04 X86-64 all on one machine.

[dacm]

I'm presuming you're using a 'file: mode' configuration using HSQLDB 2.x...

[flyingfox777]

Hi dacm,
Thanks once again for a great response.
With regards to mode of use (still learning a lot) but I believe that your assumption is correct (so much extra reading is required to be certain but I will get there - eventually)

Just to clarify I wasn't adding the field to the table just creating a new record in an existing table (I think that we are both considering the same sequence of events).

The word of warning is appreciated and I will now endeavour to create my backups more frequently (before a problem occurs).

The query about the use of zero was due to that identity value being rewritten by Calc when it created the other high value autonumber.

When this database is finished it will be single user with copies pasted between machines so the requirement to explore the server version will be left for a while. Calc was only being used to check out file structures for updating my personal documentation of the database.
All the best.
_________________
Always in the proverbial - only the depth varies.
Running LibreOffice 3.5.4.2, HSQLDB 2.2.9 with JDBC (separate database from frontend) on Ubuntu 12.04 X86-64 all on one machine.