Bug 53027 - EDITING: dBase-Import-Wizard fails when trying to append DECIMAL(NUMERIC) and BOOLEAN data types
Summary: EDITING: dBase-Import-Wizard fails when trying to append DECIMAL(NUMERIC) and...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: high major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Import
  Show dependency treegraph
 
Reported: 2012-08-01 07:19 UTC by Robert Großkopf
Modified: 2020-08-26 15:57 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Little dBase-file for testing (942 bytes, application/x-dbf)
2012-08-01 07:19 UTC, Robert Großkopf
Details
Description how to reproduce wrong formatting of dBase-import-wizard (131.95 KB, application/pdf)
2012-08-01 07:21 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2012-08-01 07:19:58 UTC
Created attachment 65020 [details]
Little dBase-file for testing

I tried to import some dBase-tables into Base. The wizard shows me the types of the fields. So I only declared a primary-key.
Message from the wizard: "A error occured".
Then I had a look at the tables with Calc. The following description of the header were not read correct by the dBase-wizard:
FIELDNAME,N,5,0 → Wizard will create Decimal,4,0 - should be Decimal,5,0
FIELDNAME,L → Wizard will create Text(1) - should be Boolean
FIELDNAME,N,8,2 → Wizard will create Decimal,6,2 - should be Decimal,8,2

When I get the direct contact from Base to a *.dbf-file it works correct. But with this direct contact I could not create a query over more than one table.
Comment 1 Robert Großkopf 2012-08-01 07:21:07 UTC
Created attachment 65021 [details]
Description how to reproduce wrong formatting of dBase-import-wizard
Comment 2 Robert Großkopf 2012-08-02 05:41:48 UTC
Try to get a direct contact with Base to the dBase-file. When I open the table for editing the field-propertis I noticed, that the field with N,5,0 is shown as Decimal,4,0. But there are values in the table like "98765" - and if you are opening the table for seeing the data you could have a look: a number with fife-digit is shown in a field, that seems to be four-digit.
The field with "L" is shown correct as Boolean in the properties of the table.

So this isn't only a problem of the wizard. It's a problem, that Base doesent know, how to interpret the field-types of dBase.
Comment 3 Jochen 2012-09-01 17:50:28 UTC
@Robert,
I want to reproduce the bug but I´m not able to do this. Please list the single first steps concerning import dBase-tables into Base.

In comment 2 you write "When I open the table". What and how do you open? Please list the file.

In your description you write "This must be corrected" and "I have to change this". Are this two points/example the bugs?

In your description shows the first picture the settings of the dBase-file. Right?
Is there any doubt that the shown data (e.g. "SPECIES_NR,N,0" or "SYNONYM,L") can be wrong or a "fake"?
I´m asking because I will test without availability of dBase and set status to "NEW" if I can reproduce the steps and the behavior. I must trust that the information concerning dBase are right.

PS: another person (with dBase) obviously do not have time or inclination to test.
Comment 4 Robert Großkopf 2012-09-02 08:10:16 UTC
@Jochen,

before you could import a dBase table into Base you have to create a new database in Base. The wizard asks, when creating a new connection to a dBase-Database, for the folder, where to look for the dBase-files. Formerly all dBase-files were shown as tables in this new Base-Database.

Then create a new standalone Base-file: 
Database → Create a new database → Open the database for editing ..

Now you have two databases: 
The database, which connects to the dBase-files and shows all the tables inside a folder ending with *.dbf
The new database for importing the tables form the dBase-Database.

With a right-click of the mousebutton over a dBase-Tabele from your Base-file, which connects to the dBase-files, you could copy this table. After this action go to the new database, whre the file should be imported. Now you have to paste the file. The import-wizard appears as shown in the description https://bugs.freedesktop.org/attachment.cgi?id=65021 .

When you will import the table without changing the filed-types a popup appears: "A error occured". This happens, because there are values in the table, which could not be shown with the filed-types the wizard will create. And the field-types, the wizard will create, are not the same which were shown, when opening the *.dbf-file with LO-Calc. When you change the filed-types and length, the wizard creates, to the field-types and length, which are shown in Calc, the import works.
Comment 5 Rainer Bielefeld Retired 2012-09-02 09:43:31 UTC
Hi Robert,
your explanation starts far behind the point interesting here. We need the few mouse clicks required to reproduce the effect. Something like "Then create a new standalone Base-file: "Database → Create a new database → Open the database for editing .." does not exist in my LibO, or I can't find it, and be sure, I will not look for it, I have to do enough. It would really help if you could respect the "Step by step instruction with every mouse click and every key press" hint. Bug 43431 shows how that should be done.
Comment 6 Robert Großkopf 2012-09-02 10:37:04 UTC
@Rainer

When I open LO there is a welcomescreen with all the sub-programms of LO. There you have to click on "Database".
When you have started LO directly, for example with the sub-program writer, you could find the same with 
File → New → Database ...
Comment 7 Alex Thurgood 2012-09-02 11:32:45 UTC
@ALL : Robert's report is understandable, at least for me. I'm pretty certain I saw this reported on the English or French user list quite a while ago with a previous version of LO, but I will check whether I can reproduce it in a recent(ish) master build.

Alex
Comment 8 Alex Thurgood 2012-09-02 11:39:31 UTC
Ok, so if you want instructions, try this :

1) Create a first ODB database file that links to one or more DBF files in a folder.

2) Create a second database file, which is a native HSQLDB ODB container. Put nothing in it for the time being.

3) Open both ODB files.

4) In the ODB file that links to DBF tables, click on a Table, and then Ctrl-C, or right mouse button click and choose "Copy".

5) Now click on the Tables button of your second ODB file (the native HSQLDB one), and in the bottom central window, press Ctrl-V, or right mouse button click and choose "Paste"

6) As an alternative to steps 4 and 5, you should simply be able to select a table from the DBF-ODB and drag and drop it in the "Tables" lower central window of the HSQLDB-ODB.

7) The import wizard should start.
8) Accept the defaults, and allow the wizard to add a primary key ID field.

9) See whether the operation completes without errors, and if so, whether the imported data and field type definitions correspond to those defined in the DBF table.


Alex
Comment 9 Alex Thurgood 2012-09-02 11:47:16 UTC
I can confirm on Linux Mint 12 64bit, with LO 3.5.4
The error message is :

SQL Status: 22001
Error code: -124

Value too long in statement [INSERT INTO "speciesshort" ( "ID","SPECIES_NR","LETTERCODE","SHORTNAME","ABBREVIAT","NATIVENAME","AUTHOR","SYNONYM","VALID_NR") VALUES ( ?,?,?,?,?,?,?,?,?)]



Alex
Comment 10 Alex Thurgood 2012-09-02 11:50:03 UTC
If the user continues to press "Yes" in answer to the error message, it will eventually go away (once it has reached the number of parameters required), and the table will be created, but no data is inserted.


Alex
Comment 11 Alex Thurgood 2012-09-02 11:54:21 UTC
Also reproducible in Version 3.6.0.4 (Build ID: 932b512)
Comment 12 Alex Thurgood 2012-09-02 13:03:57 UTC
@Lionel : care to take a look ?

Alex
Comment 13 Rainer Bielefeld Retired 2012-09-02 14:26:37 UTC
I don't want to disturb experts' talks with such childish nonsense like a step by step instruction understandable for people who do not know yet what a HSQLDB ODB container is, but have bibisect installed on their PC ...

When I remove my name from CC that does not mean that I want to be added again.

I do not want to be added to CC of database bugs by others.
Comment 14 Alex Thurgood 2012-09-02 14:33:09 UTC
After transferring several different types of DBF table, I noticed that some succeeded and some failed. 

The problem lies in the copy of tables containing DECIMAL(NUMERIC) fields. This is what causes the error message to occur and the data append to fail, even if the table structure is created.


Alex
Comment 15 Alex Thurgood 2012-09-02 14:37:09 UTC
Tested also in master build on Mac OSX :
Version 3.7.0.0.alpha0+ (Build ID: 4deb9d4)

Reproducible.

I even managed to get LO to crash on OSX during the drag n drop maneuver, but as I couldn't then reproduce the crash, I've put that down to general instability on Mac. If I do manage to crash it again with the same thing, I'll post a different report with a trace.


Alex
Comment 16 Alex Thurgood 2012-09-02 14:40:10 UTC
Edited title to reflect situation.
Comment 17 Robert Großkopf 2012-09-02 15:09:25 UTC
@Alex

It isn't only a problem with DECIMAL(NUMERIC) data types. The wizard couldn't create Boolean also. The Type "L" is boolean in dBase. DBase-tables, shown in Base, would show such a boolean field. Imported tables in Base haven't any boolean-field.
Comment 18 Alex Thurgood 2012-09-02 16:26:24 UTC
Changed title again to include Boolean field types.
Comment 19 Alex Thurgood 2015-01-03 17:39:42 UTC Comment hidden (no-value)
Comment 20 Julien Nabet 2015-03-12 22:14:42 UTC
About Decimal, on pc Debian x86-64 with master sources updated today,  the value is also at 4 on dbase odb file.
after some gdb, it's because of this (see http://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/dbase/DTable.cxx#359)
    359         case 'N':
    360             aTypeName = "DECIMAL";
    361             if ( aDBFColumn.db_typ == 'N' )
    362                 aTypeName = "NUMERIC";
    363             eType = DataType::DECIMAL;
    364 
    365             // for numeric fields two characters more are written, than the precision of the column description predescribes,
    366             // to keep room for the possible sign and the comma. This has to be considered...
    367             nPrecision = SvDbaseConverter::ConvertPrecisionToOdbc(nPrecision,aDBFColumn.db_dez);
    368             // This is not true for older versions ....
    369             break;

If removing line 367, the value is at 5 in table edition + when copying table.

Here are the other lines:
     22 sal_Int32 SvDbaseConverter::ConvertPrecisionToDbase(sal_Int32 _nLen, sal_Int32 _nScale)
     23 {
     24     return _nScale ? _nLen +2 : _nLen +1;
     25 }
     26 sal_Int32 SvDbaseConverter::ConvertPrecisionToOdbc(sal_Int32 _nLen, sal_Int32 _nScale)
     27 {
     28     return _nScale ? _nLen -2 : _nLen -1;
     29 }
Comment 21 Julien Nabet 2015-03-13 06:45:35 UTC
It's been like this since initial import in 2000!
I must recognize I don't understand why using a function about odbc (or which indicates it's about odbc) whereas we're not in odbc part.
Comment 22 Julien Nabet 2015-03-13 23:31:21 UTC
About BOOLEAN changed into VARCHAR, here's a codepointer:

http://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/misc/WCopyTable.cxx#1444
   1437 TOTypeInfoSP OCopyTableWizard::convertType(const TOTypeInfoSP& _pType, bool& _bNotConvert)
   1438 {
   1439     if ( !m_bInterConnectionCopy )
   1440         // no need to convert if the source and destination connection are the same
   1441         return _pType;
   1442 
   1443     bool bForce;
   1444     TOTypeInfoSP pType = ::dbaui::getTypeInfoFromType(m_aDestTypeInfo,_pType->nType,_pType->aTypeName,_pType->aCreateParams,_pType->nPrecision,_pType->nMaximumScale,_pType->bAutoIncrement,bForce);
Comment 23 Julien Nabet 2015-03-14 01:08:24 UTC
I've succeeded to retrieve Boolean but I don't know if that's the good way.
I noticed that:
1) OCopyTableWizard::convertType didn't manage DataType::BOOLEAN case.
See http://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/misc/WCopyTable.cxx#1445

2) Just searching in the whole _rTypeInfo seems to work better than just the range.

See http://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/misc/UITools.cxx#289
Comment 24 Julien Nabet 2015-03-14 01:12:10 UTC
Forgot to say that each comparison is like this:
for(;aIter != aPair.second;++aIter)
aPair.second is not considered as the match since each time we've got this kind of code:
        if (aIter == aPair.second)
        {
            for(aIter = aPair.first; aIter != aPair.second; ++aIter)

IMHO, there's really something wrong in this method.

Lionel: any thoughts?
Comment 25 Robert Großkopf 2016-03-06 16:05:55 UTC
Bug appears also in the first available LO-version (LO 3.3.0.4, OpenSUSE 42.1 64bit rpm Linux). So I set this bug as "Inherited From OOo".

Two problems reported here: 
1. Database connecting to dBase-files.
- look at the table-properties
- DECIMAL for 4 numbers is created and contains 5 numbers
- BOOLEAN field has right properties
2. New Database, connecting to HSQLDB
- Importwizard will create DECIMAL for 4 numbers and fails
- Importwizard will create VARCHAR-field for 1 character instead of BOOLEAN-field.

With LO 3.3.0.4 this behavior leads to a totally crash of LO.
Comment 26 Alex Thurgood 2016-12-13 09:18:16 UTC
(In reply to Julien Nabet from comment #21)
> It's been like this since initial import in 2000!
> I must recognize I don't understand why using a function about odbc (or
> which indicates it's about odbc) whereas we're not in odbc part.

I imagine that the thinking at the time was to produce ODBC-compliant strings ?
Comment 27 Julien Nabet 2017-06-23 22:45:53 UTC
Lionel: I noticed this comment from http://opengrok.libreoffice.org/xref/core/sc/source/ui/docshell/docsh8.cxx#708
    708             // 538 MUST: Sdb internal representation adds 2 to the field length!
    709             // To give the user what he wants we must subtract it here.
    710              //! CAVEAT! There is no way to define a numeric field with a length
    711              //! of 1 and no decimals!
(been there since initial commit)

Indeed, I can't create a simple odb file HSQL Embedded with 2 fields:
"id" INTEGER (primary key)
"test" NUMERIC (length = 1 + no decimals)
in this case, length value is changed to 10

If I put length = 2, the value stays unchanged.

Do you know the reasons for this?
Comment 28 QA Administrators 2018-08-15 02:33:33 UTC Comment hidden (obsolete)
Comment 29 Robert Großkopf 2018-08-15 06:23:52 UTC
Created a database for the dBase-file. 
Copied the table from the dBase-database to an HSQLDB-database.
Fails with NUMERIC and BOOLEAN fields.

It hasn't changed with LO 6.1.0.3 on OpenSUSE 15, 64bit rpm Linux.
Comment 30 Drew Jensen 2018-08-15 11:57:08 UTC
(In reply to robert from comment #29)
> Created a database for the dBase-file. 
> Copied the table from the dBase-database to an HSQLDB-database.
> Fails with NUMERIC and BOOLEAN fields.
> 
> It hasn't changed with LO 6.1.0.3 on OpenSUSE 15, 64bit rpm Linux.

There was a fellow talking about this (though he didn't mention this issue) over on ask libreoffice. Just this week.

It was in the context of why he uses a macro to run Firebird in the same convoluted split way people started using HSQL embedded Base files.

I mentioned that the old workaround was not needed with Firebird, but he reminded me that there was no other way to reliably move data from his server bases database to the file based database - guess where the major problem is in that process, right here.
Comment 31 Stang 2018-08-15 20:58:06 UTC
Please note: This happens when copying data from ANY other source (not just the server) See Bug 116893
Comment 32 Stang 2018-08-15 21:05:49 UTC
I should also make mention that the Ask LO reference was regarding Firebird server & Bug 116893 is related to Firebird embedded.
Comment 33 Julien Nabet 2018-10-10 19:58:18 UTC
I tried to find the origin of the CAVEAT, the oldest commit was:
54adddb5a9da7124cc41c75b3ede595ae28f20cb
Author: Niklas Nebel <nn@openoffice.org>
Date:   Thu Oct 26 18:07:53 2000 +0000

    DBaseImport/Export (uses api) instead of SbaSdbImport/Export

but it was added before this.

Would anyone has info about "Sdb internal representation"
What's sdb?
Idem, what's sba?
Are these notions still present? (In Opengrok, I saw "com::sun::star::sdb" or sba resource without anything else)
Comment 34 Alex Thurgood 2018-10-11 09:28:46 UTC
@Julien : the SDB file format was the old StarOffice database format, which was also carried over to OpenOffice.org for a while (version 1.0 ?) before being abandoned, perhaps this is what is referred to by the comments in the code ?

dBase III+ integration was slightly different then than it is today, I seem to recall. Sun changed all that when it introduced the ODB container.
Comment 35 Julien Nabet 2018-10-11 09:38:06 UTC
(In reply to Alex Thurgood from comment #34)
> @Julien : the SDB file format was the old StarOffice database format, which
> was also carried over to OpenOffice.org for a while (version 1.0 ?) before
> being abandoned, perhaps this is what is referred to by the comments in the
> code ?
So perhaps "MUST: Sdb internal representation adds 2 to the field length!" may be not relevant anymore!

> dBase III+ integration was slightly different then than it is today, I seem
> to recall. Sun changed all that when it introduced the ODB container.
ok

I'm not sure, must we still able to open sdb files or deal with macros using the old mechanism?
Comment 36 geekydanny123 2020-07-09 08:04:45 UTC Comment hidden (spam)
Comment 37 Questions Hub 2020-08-26 15:57:11 UTC Comment hidden (spam)