Created attachment 146837 [details] Test file The Firbird support in Base added CLOB to the list recently. Using the table editor to create a CLOB field in a table works initially. However if you attempt to alter the table structure subsequently by adding a new column then during the ALTER TABLE process the CLOB field is first erroneously reported as needing to be dropped and added to the back of the table columns, but will change the type of field from CLOB to BLOB. Steps to reproduce: 1 - download and open the attached test file. 2 - Open the table TABLE2 with the Table Editor 3 - Add a new column after Notes of any name and type 4 - Click on save changes. Result: User is prompted (see second attachment for screen shot) that the Notes field can not be changed (OK) and instead should it be dropped and appended with new format. This is because what the application really wants to do is change the column type to BLOB (a type that was supported with older versions of LO/Firebird when it did not support CLOB), so if you the user answers yes it does just that (which looses any data currently in the column for all records) and the user is now unable to edit, insert, etc any data in the changed field. If the user is sharp and answers no there is no harm. Expected result: The application adds the new column silently. Setting this to priority 'High' because of the data loss potential.
Created attachment 146838 [details] Screen shot of erroneous error message screen shot of error box message.
I have tested it with both tables: The "notes"-field, defined as BLOB[BLOB], needs an <OBJECT> for input. The "notes"-field, defined as CLOB[BLOB], could contain text. Both fields show the same irritating and wrong errormessage. You have to switch to "No" to prevent dataloss. By the way: What is the real type of the fields? With HSQLDB the real type is described in square brackets, and this is the same here ([BLOB]). Tested with LO 6.1.3.2, OpenSUSE 15, 64bit rpm Linux
Real type. BLOB and CLOB are two types of LOB, to be AR about it. From Firebird perspective a CLOB is a specialized sub type of BLOB with the C in CLOB standing for Character and the BLOB being a generic binary encoded large object. CLOB signifies to client software that the binary data may be retrieved as character data. Same is true of the Image field type in Base, which is another BLOB sub type. So, it is accurate in the table editor to, as it does, display the types as: BLOB [BLOB] CLOB [BLOB] IMAGE [BLOB] The actual equivalent to the CLOB [BLOB] field in the HSQLdb SDBC was the field type MEMO [BLOB].
This bug is also in LibreOffice Version: 6.1.5.2 ("Stable Version")
Lionel: I tried 2 ways to fix this: 1) In ODatabaseMetaData::getTypeInfo(), replace: aRow[1] = new ORowSetValueDecorator(OUString("BLOB")); // BLOB, with subtype 1 by aRow[1] = new ORowSetValueDecorator(OUString("CLOB")); // BLOB, with subtype 1 See https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/DatabaseMetaData.cxx?r=1daeea3a#885 2) In firebird::ColumnTypeInfo::getColumnTypeName() Replace: case DataType::CLOB: return "CLOB"; by case DataType::CLOB: return "BLOB"; See connectivity/source/drivers/firebird/Util.cxx (not present in Opengrok yet since the patch is recent). But considering http://www.firebirdfaq.org/faq48/, if we want to stick to Firebird naming, I think 2) should be chosen. Now I must recognize it's not easy to think the impacts of each way. Any thoughts?
Of course, related with tdf#137801
Try: // Clob (SQL_BLOB SUBTYPE TEXT) - aRow[1] = new ORowSetValueDecorator(OUString("BLOB")); // BLOB, with subtype 1 + aRow[1] = new ORowSetValueDecorator(OUString("BLOB SUBTYPE TEXT")); // BLOB, with subtype 1 aRow[2] = new ORowSetValueDecorator(DataType::CLOB); aRow[3] = new ORowSetValueDecorator(sal_Int32(2147483647)); // Precision = max length aRow[6] = new ORowSetValueDecorator(); // Create Params aRow[9] = new ORowSetValueDecorator( sal_Int16(ColumnSearch::FULL)); // Searchable aRow[12] = new ORowSetValueDecorator(false); // Autoincrement aRow[14] = ODatabaseMetaDataResultSet::get0Value(); // Minimum scale aRow[15] = ODatabaseMetaDataResultSet::get0Value(); // Max scale tmp.push_back(aRow); // Longvarbinary (SQL_BLOB SUBTYPE BINARY) // Distinguished from simple blob with a user-defined subtype. + aRow[1] = aRow[2] = new ORowSetValueDecorator(DataType::LONGVARBINARY); aRow[2] = new ORowSetValueDecorator(DataType::LONGVARBINARY); tmp.push_back(aRow); and In firebird::ColumnTypeInfo::getColumnTypeName() Replace: case DataType::CLOB: return "CLOB"; by case DataType::CLOB: return "BLOB SUBTYPE TEXT"; and replace case DataType::BLOB: return "BLOB"; by case DataType::CLOB: return "BLOB SUBTYPE BINARY";
(In reply to Lionel Elie Mamane from comment #7) > Try: > > // Clob (SQL_BLOB SUBTYPE TEXT) > - aRow[1] = new ORowSetValueDecorator(OUString("BLOB")); // BLOB, with > subtype 1 > + aRow[1] = new ORowSetValueDecorator(OUString("BLOB SUBTYPE TEXT")); > // BLOB, with subtype 1 > aRow[2] = new ORowSetValueDecorator(DataType::CLOB); > aRow[3] = new ORowSetValueDecorator(sal_Int32(2147483647)); // > Precision = max length > aRow[6] = new ORowSetValueDecorator(); // Create Params > aRow[9] = new ORowSetValueDecorator( > sal_Int16(ColumnSearch::FULL)); // Searchable > aRow[12] = new ORowSetValueDecorator(false); // Autoincrement > aRow[14] = ODatabaseMetaDataResultSet::get0Value(); // Minimum scale > aRow[15] = ODatabaseMetaDataResultSet::get0Value(); // Max scale > tmp.push_back(aRow); > > // Longvarbinary (SQL_BLOB SUBTYPE BINARY) > // Distinguished from simple blob with a user-defined subtype. > + aRow[1] = aRow[2] = new > ORowSetValueDecorator(DataType::LONGVARBINARY); > aRow[2] = new ORowSetValueDecorator(DataType::LONGVARBINARY); > tmp.push_back(aRow); > > and > > In firebird::ColumnTypeInfo::getColumnTypeName() > Replace: > case DataType::CLOB: > return "CLOB"; > by > case DataType::CLOB: > return "BLOB SUBTYPE TEXT"; > and replace > case DataType::BLOB: > return "BLOB"; > by > case DataType::CLOB: > return "BLOB SUBTYPE BINARY"; Remark I suppose the last one is: > and replace > case DataType::BLOB: > return "BLOB"; > by > case DataType::BLOB: > return "BLOB SUBTYPE BINARY"; Anyway, it'll work for this case but it'll regress for "tdf#137801" since I got: sTypeName=SMALLINT pField->GetTypeName()=BLOB SUBTYPE BINARY In connectivity/source/drivers/firebird/Util.cxx, I used: case DataType::BLOB: - return "BLOB"; + return "BLOB SUBTYPE BINARY"; *Dynamic SQL Error *SQL error code = -104 *Token unknown - line 1, column 36 *SUBTYPE caused by 'isc_dsql_prepare' It seems we can modify table containing a CLOB (and I suppose a BLOB) but we can't create a brand new table when adding a BLOB or CLOB because of the "SUBTYPE ..."
(In reply to Julien Nabet from comment #8) > (In reply to Lionel Elie Mamane from comment #7) > Remark I suppose the last one is: > > and replace > > case DataType::BLOB: > > return "BLOB"; > > by > > case DataType::BLOB: > > return "BLOB SUBTYPE BINARY"; Yes. > Anyway, it'll work for this case but it'll regress for "tdf#137801" > since I got: > sTypeName=SMALLINT pField->GetTypeName()=BLOB SUBTYPE BINARY Sorry, the correct token is SUB_TYPE, not SUBTYPE. Replace everywhere. See https://firebirdsql.org/file/documentation/pdf/en/refdocs/fblangref25/firebird-25-language-reference.pdf section 3.6 page 38.
(In reply to Lionel Elie Mamane from comment #9) > ... > Sorry, the correct token is SUB_TYPE, not SUBTYPE. Replace everywhere. See > https://firebirdsql.org/file/documentation/pdf/en/refdocs/fblangref25/ > firebird-25-language-reference.pdf section 3.6 page 38. I can create a BLOB field but not a CLOB field. warn:connectivity.firebird:3720:3720:connectivity/source/drivers/firebird/Util.cxx:57: firebird_sdbc error: *Dynamic SQL Error *SQL error code = -104 *Token unknown - line 1, column 70 *SUB_TYPE caused by 'isc_dsql_prepare'
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/aba06f2c3a39f33007a8f4e6e234254f42e01f0d tdf#121553: Firebird, fix datatypes management It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Backport for 7.0 waiting for review here: https://gerrit.libreoffice.org/c/core/+/105050
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-7-0": https://git.libreoffice.org/core/commit/101647617f38a40dabbe9fb66b71ee9b2db5f2ae tdf#121553: Firebird, fix datatypes management It will be available in 7.0.4. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Great to hear this issue is getting fixes:) To add a little note: CLOB fields which are larger than standard VARCHAR max do not display correctly in LO - they appear to be truncated to VARCHAR max, eg in form text-fields, and (unsurprisingly) in calc cells.... ...since there is also an issue exporting .csv directly from Base+Firebird (oops-lost link) this becomes a potentially big issue>> ie: CLOB fields larger than VARCHAR max may become truncated and irrecoverable by 'normal' means !!! :O (perhaps a LO project could be fully reconstructed in external DB...but not exactly for the standard user)
(In reply to trowelandmattock from comment #14) > ... > CLOB fields which are larger than standard VARCHAR max do not display > correctly in LO - they appear to be truncated to VARCHAR max, eg in form > text-fields, and (unsurprisingly) in calc cells.... > ...since there is also an issue exporting .csv directly from Base+Firebird > (oops-lost link) this becomes a potentially big issue>> > ... Don't hesitate to submit a new bugtracker about this if there's none which corresponds.