Issue 125949 - Connecting to an existing dBase database defines incorrect field widths
Summary: Connecting to an existing dBase database defines incorrect field widths
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: 4.1.1
Hardware: All All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
: 127301 (view as issue list)
Depends on:
Reported: 2014-12-17 15:04 UTC by rosspjohnson
Modified: 2017-01-29 10:27 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 4.1.3
Developer Difficulty: ---

Example DBF and Screen Shots (267.95 KB, application/zip)
2014-12-17 15:04 UTC, rosspjohnson
no flags Details
Examplr DBF and screenshots updated for version 4.1.3 (337.28 KB, application/x-zip-compressed)
2017-01-29 01:02 UTC, rosspjohnson
no flags Details
a test database with different numeric fields (305 bytes, application/x-dbf)
2017-01-29 10:27 UTC, mroe
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description rosspjohnson 2014-12-17 15:04:05 UTC
Created attachment 84301 [details]
Example DBF and Screen Shots

I have a set of DBF tables in a directory that I connect Base to as a new database. I can view the data etc. in Base and it seems normal but if I copy a table to, for example, another newly created Base HSQL database I get an error "SQL STATUS: 22001 - Error code: -124" "Value too long in statement ..." (see Base_DBF_Table_Copy.png inside the ZIP file attachment).

I checked the source table definition and found that the fields for all the DECIMAL[NUMERIC] fields are shorter than defined in the DBF file.

I confirmed the DBF field widths by opening the DBF table in Calc (see Calc_DBF_Field_Widths.png inside the ZIP file attachment).

In the example DBF table inside the attachment the fields with shortened field lengths in Base are:

  Length 7
  Decimals 0
  Length 3
  Decimals 1
  Length 3
  Decimals 1
  Length 3
  Decimals 1

(see Base_DBF_ANT_ID_Field_Width.png and Base_DBF_ANT_BW_Field_width.png in the ZIP file attachment)

In Calc the column headers display the correct definitions:

(see Calc_DBF_Field_Widths.png inside the ZIP file attachment).

In the example DBF table "antenna.dbf" the first value that I determined produces the error on copy is in the row where ANT_ID = 46, in the column "ANT_BW" with value "136".
This was identified by:
1) observing that the copy succeeds if only the ANT_BW column is omitted from the copy,
2) including only the ANT_BW in the copy and stopping the copy at the error then viewing the incomplete copy of the table in the destination Base database, where the last row copied was the row immediately before this row.

However, this should not be the first point of failure for the copy, so there appear to be other problems around enforcing field lengths. For example, why doesn't the copy fail at the row where ANT_ID = 3 and the ANT_GAIN = 12.2? This value exceeds the field length (3).
Comment 1 rosspjohnson 2014-12-19 07:00:47 UTC
Re why the value ANT_GAIN=12.2 did not cause a failure. The non-failure suggests that the field length value of 3 is being interpreted as what is called the maximum "precision" of the field, i.e. the maximum number of digits that the field will allow rather than the field length. The DBF field definition is N,5,1 indicating a numeric field with length 5 and 1 decimal place.

Experimenting further I tried copying the exmaple table within the same Base database as the source table and this did not fail. I tried modifying values in the table with the following results:

1) change the value ANT_GAIN=12.2 to -12.2. Result: accepted (does not exceed 5 characters total, does not exceed 3 digits total).
2) change the value ANT_BW=136.0 to -136.0. Result: error updating (exceeds the field length and 5 characters total).

I'm guessing that the field length values seen in the Base table edit screens are the result of deducting the sign and decimal point characters from the total. That would explain why:

N,8,0 (DBF) becomes Decimal 7,0 (Base), i.e. an integer with a sign character deducted.

N,5,1 (DBF) becomes Decimal 3,1 (Base), i.e. a float with sign and point characters deducted.

But I can't see why this would be done as both the DBF format and Base define these attributes as total field length and decimals, not precision and scale.
Comment 2 oooforum (fr) 2017-01-23 13:39:14 UTC
Try to test with lastest build AOO 4.1.3
If you have same issue, please attach DBF file to reproduce.
Comment 3 rosspjohnson 2017-01-29 01:02:58 UTC
Created attachment 85938 [details]
Examplr DBF and screenshots updated for version 4.1.3

Confirming this issue is NOT resolved.
Comment 4 rosspjohnson 2017-01-29 01:09:38 UTC
This issue is NOT RESOLVED in 4.1.3.

Confirmed as still an issue in 4.1.3 using the same DBF file and process as before. See the new ZIP attachment for updated screenshots.

As this ticket is marked CLOSED OBSOLETE we will open a new ticket refering to this ticket.
Comment 5 mroe 2017-01-29 10:19:54 UTC
*** Issue 127301 has been marked as a duplicate of this issue. ***
Comment 6 mroe 2017-01-29 10:20:41 UTC
There are some issues with dBase in AOO Base.
Comment 7 mroe 2017-01-29 10:27:50 UTC
Created attachment 85940 [details]
a test database with different numeric fields

Here is a sample dbase file with fields that are defined differently.
1. Connect with AOO Base to that file.
2. Look at the definitions of the fields.
3. Open the table and enter some values which exceeds the field length an look at the error message.

For the fields with length 7 the error message show the right length. But not for the other fields.