How to reproduce : 1) Start LO Base 2) Choose to create a new embedded Firebird database. 3) Start the table creation wizard 4) Choose Invoices from the Business category of proposed default tables 5) Click Next at each step of the wizard, accepting defaults for field types. 6) At the Set Primary Key dialog, select Create Primary Key - Automatically Add Primary Key - Autovalue 7) Click on Next 8) Click on Finish (accept the defaults) ERROR : table creation will not complete, and displays following error message : Error code: 1 firebird_sdbc error: *Dynamic SQL Error *SQL error code = -842 *Short integer expected caused by 'isc_dsql_prepare' Alex
This is a blocker if the user can't create via the UI from the default table choices provided by the application. The SQL error message is somewhat too sybilline for a normal user who will have no hope of understanding what is going wrong. Alex
The problem still occurs even if the user does not choose to set a primary key at step 6 outlined above, however, the error message is different : Error code: 1 firebird_sdbc error: *Dynamic SQL Error *SQL error code = -204 *Data type unknown *Implementation limit exceeded *COLUMN Notes caused by 'isc_dsql_prepare' Alex
The problem occurs with the majority of the default tables proposed by LO in the Business category. If you choose the Transactions table, the wizard doesn't even get to the end, try clicking on "Finish" and nothing happens, clicking on Next several times eventually gets you stuck on the Primary Key definition page of the dialog, and then you can go no further - there seems to be some focus issues here with the wizard and event firing/handling. I guess that this particular behaviour is a separate issue though. Alex
I could confirm this behavior. Seems to be a problem with the datatypes. *Short integer expected : There is no "Short inter" as type of a field in Base and isn't in Firebird. Could be Bug67179 or Bug71256. *Implementation limit exceeded *COLUMN Notes : There is defined a length of 32767 chars, but Firebird sets a limit to 32767 bytes. UTF-8, for example, allows 4byte for one character. So this must be limited to 8191 characters, when using UTF-8. For more content Firebird uses BLOB (BLOB SUB_TYPE 1). Don't know if we should report a bug for every fieldtype, which is chosen wrong.
(In reply to comment #3) > If you choose the Transactions table, the wizard doesn't even get to the > end, try clicking on "Finish" and nothing happens, clicking on Next several > times eventually gets you stuck on the Primary Key definition page of the > dialog, and then you can go no further - there seems to be some focus issues > here with the wizard and event firing/handling. I guess that this particular > behaviour is a separate issue though. The wizard doesn't allow to set types and format for this special table. Could be it is a problem with the Bool-field inside of this table. There is no special Bool-field in Firebird.
(In reply to comment #5) > Could be it is a problem with the Bool-field inside of this table. There is > no special Bool-field in Firebird. Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if that's the problem). Maybe we could just do it like MySQL and at table *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And then, the table has a TINYINT(1) field, not a BOOLEAN field.) (note: firebird 3 has/will have BOOLEAN datatype)
(In reply to comment #6) > Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if > that's the problem). Maybe we could just do it like MySQL and at table > *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And > then, the table has a TINYINT(1) field, not a BOOLEAN field.)\ Maybe add a CHECK clause "check (A=0 or A=1)" as suggested by http://tracker.firebirdsql.org/browse/CORE-726?focusedCommentId=20162&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_20162
(In reply to comment #6) > (In reply to comment #5) > > Could be it is a problem with the Bool-field inside of this table. There is > > no special Bool-field in Firebird. > > Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if > that's the problem). Maybe we could just do it like MySQL and at table > *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And > then, the table has a TINYINT(1) field, not a BOOLEAN field.) > > (note: firebird 3 has/will have BOOLEAN datatype) It shouldn't be that hard to fake it at creation time, and reading the field as a boolean would also work fine, however there is still the issue that when reading it won't present itself as a boolean, i.e. we wouldn't have the checkboxes etc. in the UI when editing table data directly (because Base thinks it's just an integer). However we could simply read whether there is a CHECK constraint in place every time we read a SMALLINT (that's the smallest datatype in firebird -- there's no tinyint). (CHECK constraints (and not-nulls) are in RDB$CHECK_CONSTRAINTS, mapping to RDB$TRIGGERS, still need to figure out how to detect this specific check once there though.)
(In reply to comment #8) > (In reply to comment #6) >> Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if >> that's the problem). Maybe we could just do it like MySQL and at table >> *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And >> then, the table has a TINYINT(1) field, not a BOOLEAN field.) > It shouldn't be that hard to fake it at creation time, and reading the field > as a boolean would also work fine, however there is still the issue that > when reading it won't present itself as a boolean, i.e. we wouldn't have the > checkboxes etc. in the UI when editing table data directly (because Base > thinks it's just an integer). I think that's OK, although not optimal. > However we could simply read whether there is a CHECK constraint in place > every time we read a SMALLINT (that's the smallest datatype in firebird -- > there's no tinyint). That would be even better. We can check whether the CHECK constraint is the one that LibreOffice puts when faking BOOLEAN.
I can confirm this bug on LO 4.2 RC2. I get the same error message as reported in the bug description. Version: 4.2.0.2, Build-ID: 601a398b803303d1a40a3299729531824fe0db56 Ubuntu 13.04
(This is an automated message.) LibreOffice development currently prioritizes bugs with the so called MAB (most annoying bugs) -- as this bug has not run through that process (including writing a short rationale for this bug being a candidate and other who are watching the tracker bug silently approving that rationale etc.) its priority is set to high. Note this is effectively no change in the urgency assigned to this bug, as we are currently not making a difference between high and highest and severity is untouched. You can find out more about MABs and how the process works by contacting libreoffice qa on irc: http://webchat.freenode.net/?channels=libreoffice-qa The QA wiki page also gives you hints on how to get in contact with the team (if IRC fails you, your next best choice is the mailing list): https://wiki.documentfoundation.org/QA
Adding as blocker for making Firebird the default
Set platform to ALL, as I can also reproduce on Windows XP SP1, version 4.3.0.0 beta2.
Adding self to CC if not already on
Can confirm even in LibO 5.0.0. RC1 - on windows 8.1 Version: 5.0.0.1 Build-ID: 9a0b23dd0ab9652e0965484934309f2d49a7758e Gebietsschema: de-DE (de_DE)
Dropping Severity -> critical (we've deprecated the 'blocker' value) https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Severity#Severity_Levels (In reply to Lionel Elie Mamane from comment #6) > > (note: firebird 3 has/will have BOOLEAN datatype) Lionel: Looks like Firebird 3 beta 2 is available --> does it fix this issue? http://www.firebirdsql.org/en/firebird-3-0-0-beta2/
Hi, (In reply to Robinson Tryon (qubit) from comment #16) > > (In reply to Lionel Elie Mamane from comment #6) > > > > (note: firebird 3 has/will have BOOLEAN datatype) > Read in Firebird 3 Release Notes : New Data Type Support : A true BOOLEAN type (True/False/Unknown), complete with support for logical predicates Bernard
The problem with BOOLEAN will be made obsolete by the upgrade to Firebird 3, so most of the discussion in this bug is moot. If there are other things that keep the default wizard option from succeeding, then they need to be fixed separately. I suggest to use this bug as a tracker bug and to open new bugs for each issue, and add them to this bug's "depends on".
With master sources updated today, I had another error: warn:connectivity.firebird:4079:1:connectivity/source/drivers/firebird/Statement.cxx:126: isc_dsql_execute failed warn:connectivity.firebird:4079:1:connectivity/source/drivers/firebird/Util.cxx:52: firebird_sdbc error: *unsuccessful metadata update *CREATE TABLE Invoices failed *Dynamic SQL Error *SQL error code = -204 *Data type unknown *Implementation limit exceeded *COLUMN <Missing arg #1 - possibly status vector overflow> caused by 'CREATE TABLE "Invoices" ("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY,"Notes" VARCHAR(32767),"Status" VARCHAR(50),"ShipDate" DATE,"InvoiceID" INTEGER,"ShippedTo" VARCHAR(50),"CustomerID" INTEGER,"DeliveryID" INTEGER,"EmployeeID" INTEGER,"ShippedVia" VARCHAR(50),"InvoiceDate" DATE,"Salesperson" VARCHAR(50),"ShippingCost" DOUBLE PRECISION, PRIMARY KEY ("ID"))' There's a pop with a stacktrace but I can't retrieve its content.
(In reply to Julien Nabet from comment #19) > "Notes" VARCHAR(32767) The maximum length of a VARCHAR in Firebird is 32,765. That's likely the problem. What can we do about that? - The firebird-sdbc driver caps the length to 32765, even if the calling code asks for bigger. - We change the wizard to ask fro 32700 or 32000 or 32500, that is not to try to go to the extreme limit of what "most databases" support for VARCHAR (that is 32676) which will increase compatibility in general, not only for Firebird.
Following the description of Alex for this bug I tested it with Firebird 3.0. When accepting the defaults for the table of the wizard the SQL-code won't work: The field "Note" is defined as VARCHAR(32767) Limit for VARCHAR and Firebird should be 32767 *bytes*, but it works if I set 32767 to 16383. 32767 bytes aren't the same as 32767 characters - specially for UTF8. Version: 5.3.0.0.alpha0+ Build ID: e7324c5705eaa38a2c9aa0636f01a73f033ba4d6 CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; VCL: kde4; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-10-20_06:12:58 Locale: de-DE (de_DE.UTF-8); Calc: group
I submitted a patch here: https://gerrit.libreoffice.org/#/c/30783/ Max length is 32765 not 32767 (see http://www.firebirdsql.org/en/firebird-technical-specifications/)
(In reply to Julien Nabet from comment #22) > > Max length is 32765 not 32767 (see > http://www.firebirdsql.org/en/firebird-technical-specifications/) You are right. Seems it has been changed with Firebird 2.0. Have tested the insert with 32765 - works right.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ba8a41bac3765f7b3562d357aeb13f752ea14ba4 tdf#71251: VARCHAR max length is 32765 for Firebird It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Backport for 5.2 branch waiting for reviewing: https://gerrit.libreoffice.org/#/c/30786/
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=489914a676ae5aadc68063d6d41de111728f2eb4&h=libreoffice-5-2 tdf#71251: VARCHAR max length is 32765 for Firebird It will be available in 5.2.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.