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.
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.
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.
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.
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.
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.
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.
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 ( ?,?,?,?,?,?,?,?,?)]
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.
Also reproducible in Version 18.104.22.168 (Build ID: 932b512)
@Lionel : care to take a look ?
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.
Tested also in master build on Mac OSX :
Version 22.214.171.124.alpha0+ (Build ID: 4deb9d4)
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.
Edited title to reflect situation.
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.
Adding self to CC if not already on
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;
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 ....
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)
24 return _nScale ? _nLen +2 : _nLen +1;
26 sal_Int32 SvDbaseConverter::ConvertPrecisionToOdbc(sal_Int32 _nLen, sal_Int32 _nScale)
28 return _nScale ? _nLen -2 : _nLen -1;
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:
1437 TOTypeInfoSP OCopyTableWizard::convertType(const TOTypeInfoSP& _pType, bool& _bNotConvert)
1439 if ( !m_bInterConnectionCopy )
1440 // no need to convert if the source and destination connection are the same
1441 return _pType;
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.
2) Just searching in the whole _rTypeInfo seems to work better than just the range.
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 126.96.36.199, 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 188.8.131.52 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?
** Please read this message in its entirety before responding **
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.
Please DO NOT
Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword
Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!
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 184.108.40.206 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 220.127.116.11 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:
Author: Niklas Nebel <firstname.lastname@example.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"
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.
I'm not sure, must we still able to open sdb files or deal with macros using the old mechanism?