Re^5: Problem with DBI, DBD::XBase

[stefbv]

Hi Holger,

There is a utility included in DBD::XBase named dbfdump that might be used as validator. You can use it to post here the result from the following command:

dbfdump --info file.dbf

It can be used also to dump the contents of the database. Also Excel or Open/Libre Office can open DBFs (if you didn't know already).

Regards, Stefan

[hgtmp]

Hi Stefan.
Many thanks for your help.
Here is what I get with dbfdump:

dbfdump --info EEV_DET_BATCHRUN_1~MAIN.DBF
Filename: EEV_DET_BATCHRUN_1~MAIN.DBF
Version: 0x30 (ver. 5 (FoxPro))
Num of records: 67
Header length: 1000
Record length: 214
Last change: 2013/1/23
Num fields: 22
Field info:
Num Name Type Len Decima
1. PRODUCT C 8 0
2. PURPOSE C 8 0
3. GROUP C 50 0
4. DISC_RATE N 6 2
5. TIME C 10 0
6. PERIOD C 15 0
7. T_FROM N 5 0
8. T_TO N 5 0
9. CAL_MONTH B 8 6
10. CAL_YEAR B 8 6
11. CONTROL_CA B 8 6
12. FINALISE B 8 6
13. INITIALISE B 8 6
14. INIT_NB_MP B 8 6
15. PASS_DOWN_ B 8 6
16. PROJ_MONTH B 8 6
17. PROJ_YEAR B 8 6
18. STARTUP B 8 6
19. TCWP B 8 6
20. WRITE_CRED B 8 6
21. W_FORT_FLV B 8 6
22. _NULLFLAGS 0 2 0

Let me please recapitulate:
I have a problem with the field CAL_YEAR.
With Excel I get the years like 2012, 2013 etc.
but with DBD::XBase I get decimal numbers.

Regards, Holger

[stefbv]

Hi Holger,

I see you have 'B' (binary?) type for many fields, I'm not familiar with this type, but the year and month data type should be integer or numeric with 0 decimal places - N(4,0) for example.

Regards, Stefan

Update: Table with the official list of column types supported by the module:

use strict;
use warnings;
use DBI;

my $dbf_dir = '.';
my $table   = 'test_types';

my $dbh = DBI->connect("DBI:XBase:$dbf_dir") or die $DBI::errstr;

my $def =<<"STR";
CREATE TABLE $table (
    str_1 CHAR,
    num_1 NUMERIC(8,2),
    num_2 INTEGER,
    num_3 FLOAT,
    bol_1 BOOLEAN,
    blb_1 BLOB,
    blb_2 MEMO,
    dat_1 DATE,
    tim_1 TIME,
    dat_2 DATETIME
)
STR

unlink "test_types.dbf", "test_types.dbt";

$dbh->do($def);

Update2: Interesting, the other interface (non DBD) XBase can make B type columns, but the length is 10 and decimal places 0, ignoring the parameters:

use strict;
use warnings;

use XBase;

my $newtable = XBase->create(
    "name"           => "with_b.dbf",
    "field_names"    => [ "ID", "MSG", "BIN" ],
    "field_types"    => [ "N",  "C",   "B"   ],
    "field_lengths"  => [  6,    40,    18    ],
    "field_decimals" => [  0,   undef,  2    ]
) or die XBase->errstr;

So, my guess is that B(8,6) is a erroneous type.

Update3: 'B' = Type Double :)