Date: Fri, 24 Sep 2010 08:49:40 GMT From: Hartmann@FreeBSD.org, "O." <ohartman@mail.zedat.fu-berlin.de> To: freebsd-gnats-submit@FreeBSD.org Subject: ports/150903: databases/dbf: options --sql / --csv does produce crap on floats/doubles Message-ID: <201009240849.o8O8ne2e029351@www.freebsd.org> Resent-Message-ID: <201009240850.o8O8o1mY005035@freefall.freebsd.org>
next in thread | raw e-mail | index | archive | help
>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:
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?201009240849.o8O8ne2e029351>