Description: Any table containing a numeric or decimal data field creates an error and the data is not transferred. This happens whether data originates from HSQL embedded, split, MySQL and other DB's. Will work if just copying data within the same .odb. Steps to Reproduce: 1.Copy source table 2.Try to paste table in Firebird 3. Actual Results: Message stating error. Expected Results: Data is copied Reproducible: Always User Profile Reset: Yes Additional Info: User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36
Confirming with Version: 6.0.3.2 Build ID: 8f48d515416608e3a835360314dac7e47fd0b821 Threads CPU : 4; OS : Mac OS X 10.13.4; UI Render : par défaut; Locale : fr-FR (fr_FR.UTF-8); Calc: group The copy table wizard displays an error message that it can't interpret some fields (which are NUMERIC in the original tables) and then assigns these fields a DECIMAL field type. The attempt by the wizard to populate the tables with such field definitions fails and no data is copied. If the user adapts the field types in the copy wizard dialog before final creation, and changes the field types to NUMERIC, an error message is displayed : Error message : Incorrect type for SetString and the user asked if the wizard should continue. If the user accepts to continue, a table is created with no data.
@Tamas : one for you ?
Or, is this an error in Calc's number converter ? ( which would more likely be of interest to Eike ?)
I don't understand copy from where to where? Hsqldb->Firebird? Calc->Firebird? Firebird->Calc? Other?
Copy table from .odb using HSQLDB embedded or split (so v1.8 thru 2.x) OR Copy table from .odb using MySQL OR Copy table from .odb using Firebird embedded TO Another .odb using Firebird embedded
Thank you Stang for your feedback, so the pb is when one's tries to copy a table with decimal/numeric from a database X to a database Y (X different from Y) Alex: so why did you mention Calc? I created a table on a brand new file with embedded hsqldb id integer, testnum numeric, testdec decimal and filled 2 lines: 1, 1, 1 2, 2, 2 Then I tried to copy the table on a brand new Firebird embedded file and indeed got an error: Incorrect type for setString
code pointer: https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/PreparedStatement.cxx#226
Created attachment 141273 [details] bt with debug symbols Here's a bt from setString (whereas I filled the origin table with non decimal values, I see "1.00"!)
(In reply to Julien Nabet from comment #6) > Thank you Stang for your feedback, so the pb is when one's tries to copy a > table with decimal/numeric from a database X to a database Y (X different > from Y) > Alex: so why did you mention Calc? > Hi Julien, I mentioned the Calc number interpreter because it was my understanding that this is used by the data copy assistant to map the data on import.
(In reply to Julien Nabet from comment #7) > code pointer: > https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/ > firebird/PreparedStatement.cxx#226 That select/case statement will need to be extended with new case clause(s) for DECIMAL and NUMERIC. OPreparedStatement::setObjectWithInfo around line 603 contains code that deals with DECIMAL/NUMERIC values.
*** Bug 120237 has been marked as a duplicate of this bug. ***
Seems that bug 53027 is also relevant here. @Julien, you looked into some of the problems with the code there at the time.
*** Bug 120187 has been marked as a duplicate of this bug. ***
(In reply to Lionel Elie Mamane from comment #10) > ... > That select/case statement will need to be extended with new case clause(s) > for DECIMAL and NUMERIC. OPreparedStatement::setObjectWithInfo around line > 603 contains code that deals with DECIMAL/NUMERIC values. The main pb is we don't have sqlType here so "if(sqlType == DataType::DECIMAL || sqlType == DataType::NUMERIC)" isn't possible Then which scale value should we use here (since there's no "scale" arg) for this line: for(sal_Int32 i=sValue.copy(sValue.indexOf('.')+1).getLength(); i<scale;i++)
(In reply to Julien Nabet from comment #14) > (In reply to Lionel Elie Mamane from comment #10) > > ... > > That select/case statement will need to be extended with new case clause(s) > > for DECIMAL and NUMERIC. OPreparedStatement::setObjectWithInfo around line > > 603 contains code that deals with DECIMAL/NUMERIC values. > > The main pb is we don't have sqlType here so "if(sqlType == > DataType::DECIMAL || sqlType == DataType::NUMERIC)" isn't possible You probably have to add cases for SQL_SHORT SQL_LONG SQL_FLOAT SQL_DOUBLE SQL_D_DOUBLE SQL_INT64 > Then which scale value should we use here (since there's no "scale" arg) > for this line: > for(sal_Int32 i=sValue.copy(sValue.indexOf('.')+1).getLength(); i<scale;i++) Err... Just convert the string to a number (integer or float, depending on the SQL_foo type) and call the corresponding setFloat/setDouble/setLong/...? In these cases, the if (str.getLength() > pVar->sqllen) str = str.copy(0, pVar->sqllen); will (I think) also do the wrong thing.
*** Bug 120693 has been marked as a duplicate of this bug. ***
Created attachment 147342 [details] tdf121921_decimal1000.odb: HSQLDB decimal numbers migrate incorrectly. (In reply to Alex Thurgood from comment #16) > *** Bug 120693 has been marked as a duplicate of this bug. *** I was not able to reproduce problems with the numbers 3,4,5, but in my "Main" table, the DECIMAL field "pledgeAmount" migrates values 655.4 thru 1310 as negative numbers (-167116.76 thru -166462.16).
Created attachment 147393 [details] Results of importing data from Calc intro firebird Today on the QA irc channel a report was made concerning importing data from Calc into Base with firebird db. The results of doing this using drag drop from calc to base, using the data from the users report as decimal, then double, then numeric datatypes on the Base side are shown in the graphic. The data in the calc sheet is: 9000.00 0.00 9000.00 200.00 0.00 9200.00 0.00 100.00 9100.00 If the auto detect function is used in the import wizard DOUBLE is the selected datatype for the new table columns and in that case all the data comes in properly. For DECIMAL and NUMERIC the data does not come in properly, but is consistent in both cases. 900.00 0.00 900.00 20.00 0.00 920.00 0.00 10.00 910.00
Moving my comments from bug Bug 120693 to this thread since it is the active bug thread. Tested this conversion behavior again in just released 6.2.0 and NUMERIC data is still corrupted. Description: In testing a copy of my existing database that has been migrated to Firebird, I've noticed some apparently random Number [ NUMERIC ] field data being corrupted. Typical entries for my Table would look something like (0.50, 3.00, 10.00, 55.00 or similar). Post migration all entries with the previous value of 3.00 are showing -652.36, previous values of 4.00 are showing -651.36, and previous values of 5.00 are showing -650.36. All other numeric entries appear to be correct. Steps to Reproduce: 1. Convert existing HSQLDB to Firebird 2. Open data Table 3. View NUMERIC field data and errors Actual Results: NUMERIC Table fields showed errors Expected Results: NUMERIC Table fields should show correct NUMERIC data. Reproducible: Always
I can verify sdritchey@gmail.com, comment #19. Migration of any table containing numeric/decimal data gets corrupted. I would consider this a VERY CRITICAL problem since it makes using Firebird migration totally unusable and loss of confidence in the whole process.
From LO 6.0.7 stable we tried to go to 6.1.5 stable and turned on "experimental features" in order to try conversion from HSQLDB embedded to Firebird db embedded. Unfortunately most of our DECIMAL data got corrupted and turned into -16xxxx.xx values. This is highly critical for us since 6.1.5 was considered stable release. It is understandable that Firebird is still experimental feature. We also tried the migration wizard in an 6.2.x portable version of LO but the problem was reproduced again and the conversion was erroneous.
(In reply to Drew Jensen from comment #18) > Created attachment 147393 [details] > Results of importing data from Calc intro firebird > > Today on the QA irc channel a report was made concerning importing data from > Calc into Base with firebird db. The results of doing this using drag drop > from calc to base, using the data from the users report as decimal, then > double, then numeric datatypes on the Base side are shown in the graphic. > > The data in the calc sheet is: > 9000.00 0.00 9000.00 > 200.00 0.00 9200.00 > 0.00 100.00 9100.00 > > If the auto detect function is used in the import wizard DOUBLE is the > selected datatype for the new table columns and in that case all the data > comes in properly. > > For DECIMAL and NUMERIC the data does not come in properly, but is > consistent in both cases. > 900.00 0.00 900.00 > 20.00 0.00 920.00 > 0.00 10.00 910.00 This is fixed in bug 123591. Regarding the DECIMAL migration, this is already covered by bug 126268. Closing as RESOLVED DUPLICATED *** This bug has been marked as a duplicate of bug 126268 ***