I set up a simple table. One field is datatype VARCHAR. I set the length property as 8.
And then I started using the table.
It quite happily accepts data for that field that consists of more than 8 characters. How can this be? How can I stop it?
Windows 10/ Libre Office 7.0.5.2 / Embedded Firebird
Hello,
How can this be? How can I stop it?
Use a different database. It is a bug and should be reported (have not checked if already is).
Firebird embedded:
HSQLDB embedded:
PostgreSQL:
Edit:
Here is the same file on a Win 10 system It accepts 9 characters in an 8 character field:
Here is setting a for control to limit character entry:
Here is the form with trying to enter more than 8 characters but not accepting:
You can try for yourself.
Edit 2:
To go one step further, here is the same test on Win 10 using HSQLDB embedded:
Varchar was set to 8 in ALL these tests. Firebird embedded version is a Bug!
Edit 3:
This is an open item - tdf#108082
Here is test using LO v7.2.x:
As in the example captured above, I (who raised the question) didn’t even get the “error inserting record”. It inserted just fine, in spite of being longer than the table design mandated. If I entered “123456789” into a VARCHAR that was set for length 4 (say), (and the entered a second record, just to be sure the too-long datum had been posted to the database), closed the file, reopened it, looked at the record: There it was! “123456789” in a “4 character” field.
I probably did all of this working directly with the table, not through a form. (Though I am fairly certain I’ve seen the same behavior working with such a table through a form.)
Could another Windows 10 user… the more experienced the better… give this a try too? (via embedded Firebird) It’s a quick, easy path to the unexpected behavior. (Or is this behavior NOT surprising??) I don’t want to trouble the bug teams with something that may not be a bug, or at least not a commonly present one. See Q for my OS, etc.
Could you please explain what you mean by:
As in the example captured above, I (who raised the question) didn’t even get the “error inserting record”. It inserted just fine, in spite of being longer than the table design mandated.
as that is just what the example shows - NO ERROR. And that is the problem. It is a bug. The other two examples DO show an error - HSQLDB embedded and PostgreSQL. This was to show it is isolated to the Firebird embedded database.
As for a form, you can, in a control, limit entry with number of characters. This is separate from the database limitations.
As for Windows experience, I was using Windows since DOS days. I have a copy now of Win 10 (for testing) but only rarely use it because I think it is pathetic - my opinion.
Have added to the answer - should not have needed to. Can’t understand why commented as you did. That will be all from me.
tdf#108082, It happens with 7.1.3, but the error message it’s showed with master Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 5d7251c7121cee8885fa9f2387c4a0625dd4ecee CPU threads: 4; OS: Windows 10.0 Build 21376.
Thank you. Yes did find just prior to your post & tested with v7.2.x and error was generated. Posting result in answer.
Nutshell… Prior to ver 7.2.x, the embedded Firebird database engine will, without any complaint, post to the database VARCHAR values which are longer than the “length” property set for the field. This is has BEEN FIXED in versions that are in the development pipeline. The correct behavior is part of ver 7.2.x.
A workaround is to limit the number of characters which can be entered in any VARCHAR field via the associated forms. This is probably good practice anyway (for those who don’t go beyond the version recommended for “technology enthusiasts, early adopters and power users”). Until ver 7.2.x is ready for ordinary users, it will be an extra reason to always use forms to enter data.
Additional information… Other answers contain the story of making sure this really is a fault, not just a novice failing to understand how the database works. It was confirmed to exist by three users and probably arises regardless of which OS you are using. All of this is when you use the embedded Firebird engine, which is still a work in progress, but I have concluded that embedded HSQL’s end is in sight, and prefer to suffer the pangs of “youth” instead of those of migration.
