ports/150903: databases/dbf: options --sql / --csv does produce crap on floats/doubles

Fri Sep 24 08:50:02 UTC 2010

>Number:         150903
>Category:       ports
>Synopsis:       databases/dbf: options --sql / --csv does produce crap on floats/doubles
>Confidential:   no
>Severity:       serious
>Priority:       medium
>Responsible:    freebsd-ports-bugs
>State:          open
>Quarter:        
>Keywords:       
>Date-Required:
>Class:          sw-bug
>Submitter-Id:   current-users
>Arrival-Date:   Fri Sep 24 08:50:01 UTC 2010
>Closed-Date:
>Last-Modified:
>Originator:     Hartmann, O.
>Release:        FreeBSD 8.1-STABLE/amd64
>Organization:
FU Berlin
>Environment:
>Description:
Using the tool databases/dbf on an extract of a GIS DBF4 database with the option '--sql output.sql' to produce SQL input, results in useless output. Floating point numbers resulting in zeros. A check with a normal text output (--csv) of 'dbf' shows correct values for fields containing dobles/floats, but switching to --sql output turns results into crap. Below a fragment of both outputs.

# dbf --view-info test.dbf

dBase Reader and Converter V. 0.8.3.1, (c) 2002 - 2004 by Bjoern Berg

-- File statistics
dBase version.........:          FoxBase+/dBASE III+ (without memo)
Date of last update...:          2010-09-16
Number of records.....:          1790816 (001b5360d)
Length of header......:          225 (00e1d)
Record length.........:          75 (004bd)
Columns in file.......:          6 
Rows in file..........:          1790816

+---------------+-------+---------------+---------------+---------------+
| field name    | type  | field adress  | length        | field dec.    |
+---------------+-------+---------------+---------------+---------------+
|     OBJECTID  |   N   |        0      |   9           |   0           |
|      POINTID  |   N   |        0      |   9           |   0           |
|    GRID_CODE  |   F   |        0      |  19           |  11           |
|   OBJECTID_1  |   N   |        0      |   9           |   0           |
|    POINTID_1  |   N   |        0      |   9           |   0           |
|   GRID_COD_1  |   F   |        0      |  19           |  11           |
+---------------+-------+---------------+---------------+---------------+

------

The dump to the console without any options looks like this (and lokks good):

# dbf test.dbf | more

dBase Reader and Converter V. 0.8.3.1, (c) 2002 - 2004 by Bjoern Berg
Export from test.dbf to stdout
   OBJECTID:         1
    POINTID:         1
  GRID_CODE:  1.54900000000e+000
 OBJECTID_1:         1
  POINTID_1:         1
 GRID_COD_1: -2.07200000000e+003
   OBJECTID:         2
    POINTID:         2
  GRID_CODE:  1.89150000000e+000
 OBJECTID_1:         2
  POINTID_1:         2
 GRID_COD_1: -2.06800000000e+003
   OBJECTID:         3
    POINTID:         3
  GRID_CODE:  1.95540000000e+000
 OBJECTID_1:         3
  POINTID_1:         3
 GRID_COD_1: -2.06400000000e+003
   OBJECTID:         4
    POINTID:         4
  GRID_CODE:  1.37740000000e+000
 OBJECTID_1:         4
  POINTID_1:         4
 GRID_COD_1: -2.05900000000e+003
   OBJECTID:         5
    POINTID:         5
  GRID_CODE:  9.97400000000e-001
 OBJECTID_1:         5
  POINTID_1:         5
 GRID_COD_1: -2.05700000000e+003
   OBJECTID:         6
    POINTID:         6
  GRID_CODE:  9.60800000000e-001
 OBJECTID_1:         6
  POINTID_1:         6
 GRID_COD_1: -2.05500000000e+003
   OBJECTID:         7
    POINTID:         7
  GRID_CODE:  2.02600000000e-001
 OBJECTID_1:         7
  POINTID_1:         7
 GRID_COD_1: -2.05300000000e+003
   OBJECTID:         8
    POINTID:         8
  GRID_CODE:  6.79400000000e-001
 OBJECTID_1:         8
  POINTID_1:         8
 GRID_COD_1: -2.05400000000e+003
   OBJECTID:         9
    POINTID:         9
  GRID_CODE:  1.18100000000e+000
 OBJECTID_1:         9
  POINTID_1:         9

-------

Following the CSV output:

# dbf --csv test.txt test.dbf
# more test.txt
"OBJECTID,N,9,0","POINTID,N,9,0","GRID_CODE,F","OBJECTID_1,N,9,0","POINTID_1,N,9,0","GRID_COD_1,F"
1,1,      0.00000000000,1,1,      0.00000000000
2,2,      0.00000000000,2,2,      0.00000000000
3,3,      0.00000000000,3,3,      0.00000000000
4,4,      0.00000000000,4,4,      0.00000000000
5,5,      0.00000000000,5,5,      0.00000000000
6,6,      0.00000000000,6,6,      0.00000000000
7,7,      0.00000000000,7,7,      0.00000000000
8,8,      0.00000000000,8,8,      0.00000000000
9,9,      0.00000000000,9,9,      0.00000000000
[...]

----

.. and the SQL output:

# dbf --sql test.sql test.dbf
# more test.sql

-- test.sql --
--
-- SQL code with the contents of dbf file test.dbf

drop table test;

CREATE TABLE test(
OBJECTID        int,
POINTID int,
GRID_CODE       numeric(19, 11),
OBJECTID_1      int,
POINTID_1       int,
GRID_COD_1      numeric(19, 11)
);
INSERT INTO test VALUES(
1,1,      0.00000000000,1,1,      0.00000000000);
INSERT INTO test VALUES(
2,2,      0.00000000000,2,2,      0.00000000000);
INSERT INTO test VALUES(
3,3,      0.00000000000,3,3,      0.00000000000);
INSERT INTO test VALUES(
4,4,      0.00000000000,4,4,      0.00000000000);
INSERT INTO test VALUES(
5,5,      0.00000000000,5,5,      0.00000000000);
INSERT INTO test VALUES(
6,6,      0.00000000000,6,6,      0.00000000000);
INSERT INTO test VALUES(
7,7,      0.00000000000,7,7,      0.00000000000);
INSERT INTO test VALUES(
8,8,      0.00000000000,8,8,      0.00000000000);
INSERT INTO test VALUES(
9,9,      0.00000000000,9,9,      0.00000000000);

[...]

>How-To-Repeat:
Take some extracts from a GIS, preferably ArcGIS (DBF) and try to convert them to CSV or a SQL-input.
>Fix:

>Release-Note:
>Audit-Trail:
>Unformatted: