Is it me? Base firebird weird edit table behaviour

[lucky67]

Trying to edit tables in base using firebird is driving me insane.
I just wanted to make a few changes to an existing database. Will contain all new data so thought that wouldn’t be a problem. Copy the database, delete all the records, add in a few fields to existing tables. Import new data from calc, new form, add in extra data. Done. Not so.
First I know how to work round the error message that the field can’t be changed and should it be replaced glitch. (Click yes and nothing seems to happen, close the table and reopen and it will have been deleted) Then you need to add in a new field. (And if you want a field to be a specific place, not at the end for importing data etc you have to use SQL to move the column/field. In the edit table view it doesn’t appear to have moved. If you close it and open the table as a data view it has moved,close and open in edit view and it has now moved.
Create primary key when copying a table definition creates a primary key that isn’t autogenerated. To get it to be you have to go through the delete and replace the field performance. And sometimes you have exit the file/database all together to see that the changes have been made.
This is something I find that I have to keep doing as you can’t rename tables in firebird and trying to edit my tables is causing me so much grief I want to retain ‘backup’ versions and try and retain the table names (have queries set up using them).
I did get to a point where I thought I could import the data but then couldn’t reinstate my relationships (think because the primary IDS in some of the tables were no longer autogenerated). Then had problems because I was trying to edit tables containing data, so I’ve had to delete it and go back.
But what is really driving me insane is in edit table view if you select a field and use ‘delete’ in the box on the left hand side (where you can set a primary key) it deletes the field beneath the one highlighted. At first I didn’t realise this was happening - hence me needing to add in more new fields. You can delete by selecting the field text, deleting which seems to leave an empty field - until you close and reopen and it has gone.
Now I’ve tried to set two adjoining fields as primary keys and one of the fields moved down the field list.
Which is going to cause me problems when I try and import the data …so use SQL to move it back, close the table, reopen etc etc .
Please tell me there is an easier way… thinking maybe using HSQLDB then exporting back to firebird??? or???

Update - having closed down the db and reopened (after this rant) to try and sort out the moved primary key field found it hadn’t actually moved …I think the biggest problem is that what you see in edit tables doesn’t reflect the actual situation. Probably the reason I get so many error codes as I am trying to change what I see rather than the underlying situation. I think I need to close and reopen after every change… far from ideal

Using LO 6.4.4.2. set to automatically update - apparently 6.4.6 is available, but not for automatic download, OS Windows 10.

The wrong field deleted - I just tried to reproduce this and at first couldn’t but then worked out what was happening. So in the edit table dialogue.

right click on left hand side button, row highlighted, options are cut, copy, delete, insert rows, primary key - select delete - correct field deleted

cursor in field name, right click on left hand button field name is not highlighted but rest of row is, options are now delete, insert rows, primary key. Click delete and the row/field below is deleted. (did think about doing comparative screen shots for this but don’t think I can post them here)

Apart from the random field appearing to jump 4 rows down after making it a primary key, which didn’t appear to have moved in the table ‘data’ view (there is no data in the tables at the moment) and when I went back to edit hadn’t moved.

The main problem is when I duplicate a table using copy, paste, definition, whether I select create a primary key on the first screen or when I save and it says I need a primary key, should it create one - the key created is set to autovalue =NO.

When I try to change it to autovalue it says it will have to be deleted and recreated - I have to go through exit and reopen faff, then add a primary key but that is at the bottom of the table and ideally I want it at the top (I know not essential but easier/cleaner if it is) In future I’m going to say don’t create a primary key and just add one myself…

Actually just reproducing it for this I got this error message
firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE CURRENT3 failed
*SQL error code = -607
*Invalid command
*Specified domain or source column SQL_LONG does not exist
caused by
‘ALTER TABLE “CURRENT3” ADD “ID” SQL_LONG NOT NULL’
Not sure if you can tell me what that is all about!

Relationships - for some reason I am having real problems getting this to work (it did in the original database), getting constant errors. And as I try to resolve them - making changes to the tables, primary keys, I find myself on this round of change, close (or exit) and open, insert new field, move field etc etc. Getting more frustrated and therefore making more mistakes …

So my main question is really is there a way of avoiding the glitches? Not using the gui to set up the tables? or anything …

Thanks again for your time -and sorry for the rant/essay.
(In my defence - I am also having problems opening some but not all doc (and docx) files in LO writer that I emailed out as doc attachments and have been edited and returned, they cause LO to crash, but open no problem in google docs and Word -so all in all not having a happy computer time!)

[Ratslinger]

Hello,

Have done some current testing with LO v7.0.0.3 on Ubuntu 20.04. Yes, there are still open issues but do not see the problems you seem to be stating.

Did get one added field out of sequence (added to beginning of table) but can not duplicate. Also do not see problems with wrong field deleted. Did not look at relationships but strongly disagree it would have anything to do with a field being auto-increment or not.

Don’t know your problem with sequence of fields with data import. It is just a matter of insuring the data is aligned with the correct field. Field types are always a concern and need careful attention.

Regardless of database type, find that creating the table definition first provides much less problems when importing data is involved.

Have also done table backups for security without a concern. Copy Table1 as Table1BU. Then if a problem can restore data from the backed up table or simply delete Table1 then copy the backup table as a new Table1 table. Fairly simple.

A concern with Firebird embedded is the different approach to committed data. You must save at the .odb. Not consistent with other connections/databases.

Don’t think this actually answers any question, but it is also not clear as to what specific question(s) you are actually asking.

Edit 2020-08-16:

It appears you may not understand what you are seeing and this possibly is adding to the confusion. I will explain with you field deletion in table editing. So here is a starting table edit:

Now will proceed as you noted:

cursor in field name, right click on left hand button field name is not highlighted but rest of row is, options are now delete, insert rows, primary key. Click delete and the row/field below is deleted.

Here is my result:

Now on the surface it appears what you state is true. However, upon a closer look, the field type is actually the one from below. Now if you click anywhere else or simply save the table all is OK:

Now please don’t misunderstand, this is wrong that the incorrect field name is displayed. It needs to be reported as a bug. Bugs do happen but the end result does not reflect your statement of:

The wrong field deleted

Now I am not certain about your firebird_sdbc error because you did not give details on how you incurred this and I was no
about to start trying various things looking for the same error.

Relationship errors are usually because of incorrect definitions.

So my main question is really is there a way of avoiding the glitches?

These will most likely always be present in any software. You can’t avoid them until you know they exist. You do not need to use the GUI for table creation or modifications. All can be done using SQL. See the Firebird documentation → Firebird 2.5 Language Reference (English).

Version 2.5 is the latest available.

[lucky67]

I’ll add a further update to my first post to explain a bit better - I was really at the end of my patience - hence the rant - sorry.

[lucky67]

Thanks again for your answer. Not sure about what you are saying about the wrong field being deleted - although when I did mine the fields had the same properties (integers) so not obvious. Shall I report the bug or will you?
I’ll look at the firebird link -direct sql might be a good idea. I do have a query about something else but I’ll do a new (less ranty ) post about it - so it might be more helpful for others! thanks again

[Ratslinger]

Put another way, the wrong field is not being deleted. It is simply retaining the display of the deleted field name (only under your circumstance of cursor in field name & then delete) - that is the bug you need to report. The correct field is actually deleted.

Edit:

And after deleting, if you click anywhere else or save the table, the name being displayed in error is corrected. It is simply a display problem.

[lucky67]

Thanks! I’ll report it as a bug…using your description above. (I think most of my problems are with the display not accurately representing changes unless you close and reopen. I guess I should close and reopen after every change)

[Ratslinger]

Not my method of thinking. In this case just clicking elsewhere fixes the problem. Better to figure out what is happening than to arbitrarily open & close something and hope the problem goes away.