Hello,
I have been using this small database for several years now. I designed this database 3 or 4 years ago and during those years I used it without problem. The database is very simple: a table and a form. To fill out the table, I use the form. Yesterday I noticed that the width of a ‘text’ type field is not sufficient to receive the new data. I didn’t find how to modify its properties on the “form” object, so I tried on the ‘table’ object: I selected the table, then clicked on “Edit” (in the toolbar). In the window that opened displaying the table properties, I selected the desired field and modified the maximum length (150 instead of 100). Then recorded. When I reopened the database, I discovered to my surprise that the maximum language for the field in question is still 100.
Any comment?
Thanks in advance.
You are using an internal database, HSQLDB? When changing tables and it won’t show the changed properties:
Change the property, safe the database, close the database and reopen the database. Might help.
This database is actually a local .odb file, so using your terminology, i.e. internal and (maybe) external, it is internal.
I did exactly what you suggested…as I explained in my original post.
Could you send me the database per private mail. I will have a look to the table and the field, which should have a length of 150 characters.
Try menu:Tools>SQL…
ALTER TABLE "table name" ALTER COLUMN "column name" VARCHAR(150)
fill in the right names between the double-quotes and append NOT NULL if the field is meant to be mandatory.
Click [Excecute]
After closing the dialog, call menu View>Refresh Tables.
Well… I prefer to avoid this if possible because the database contains private data.
I can share with you some screenshots.
Maybe you could suggest me something from screenshots:
Upon opening .odt file it looks like this. I select table Annonces, then click on Edit
… will be continued
Then I change max length in the field link (150 instead of 100), then save it
After closing .odt and reopening it I see that length of link is still 100
You are using Firebid, not internal HSQLDB. But it woks here without any problems through GUI.
Code for changing it through SQL for Firebird will be:
ALTER TABLE "Annonces" ALTER COLUMN "Link" TYPE VARCHAR(150)
Which version of LibreOffice do you use? Hope it isn’t LO 7.6.0 or 7.6.1. It has a bug for saving data in Firebird. There has been tried to do the saving automatically like HSQLDB does, but it went wrong …
This command worked (previous version was without TYPE).
Then upon refreshing table, tool crashed:
… to be continued
Which version of LO do you use? When LO crashes there is no chance to write back to the *.odb-file. Changing of the field will be lost. Better save Base, close Base and reopen Base if refreshing the tbale crashes.
Hope the table has been closed, also the form has been closed before changing the table.
I can only recommend to abstain from embedded Firebird. FB has too many idiosyncrasies for anyone not familiar with this particular database engine.
Embedded HSQL (version 1.8) may be outdated, but the whole Base component is tailored around this database engine and HSQL follows SQL standards. If you are missing certain features in embedded HSQL, it is very easy to extract the database and use it with a recent HSQL2 driver.
I forgot to mention that in SQL command I used 300 instead of 150.
Did as you suggested. Upon reopening the length is still 100.
LO version: 7.6.2.1
Ok, but what can I do now to work around the problem?
I would try the following
- Create a new, empty embedded HSQLDB.
- Copy or drag the tables from the old database to the new one and import structure with data.
- In HSQL you should be able to change almost everything in the table design view. Table design view can NOT turn a primary ID into an incrementing auto-ID. This is how it works:
ALTER TABLE "TABLE NAME" ALTER COLUMN "COLUMN NAME" SET IDENTITY
- Add relations in the relation design window.
- Copy over all the queries forms and reports. If the names of columns and tables are the same, these objects should simply work.
@Pavel_167 : Have tried with LO 7.6.2.1 (OpenSUSE 15.4 64bit rpm Linux) and an internal Firebird database. Couldn’t find any buggy behavior you described here. Field could set to any other length by GUI.
You only use one table and one form? Create a new internal database, copy the table into this database and define the length in the wizard for pasting table and data. Then copy the form and all will be there.
A general hint: You may reduce the size of the window to have less white space in the middle, and you should always include the bottom status-bar. In this thread, Robert could have skipped all hints to HSQLDB if he could read Firebird at the bottom.
