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
Message stating error.
Data is copied
User Profile Reset: Yes
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36
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?
Copy table from .odb using HSQLDB embedded or split (so v1.8 thru 2.x)
Copy table from .odb using MySQL
Copy table from .odb using Firebird embedded
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
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?
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:
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
> 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.
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
NUMERIC Table fields showed errors
NUMERIC Table fields should show correct NUMERIC data.
I can verify email@example.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.