Created attachment 65021 [details]
Description how to reproduce wrong formatting of dBase-import-wizard

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.

@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.

@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.

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.

@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 ...

@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

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

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

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

Also reproducible in Version 3.6.0.4 (Build ID: 932b512)

@Lionel : care to take a look ?

Alex

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.

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

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

Edited title to reflect situation.

@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.

Changed title again to include Boolean field types.

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 }

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.

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);

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

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?

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.

(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 ?

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?

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.

(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.

Please note: This happens when copying data from ANY other source (not just the server) See Bug 116893

I should also make mention that the Ask LO reference was regarding Firebird server & Bug 116893 is related to Firebird embedded.

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)

@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.

(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?

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