Hi all,

If I Googled correctly, the following field size limits are present for the various DBase formats:
3: DBase III: 10 characters
4: DBase IV: 10 characters
7: DBase VII: 32 characters
8: FoxPro: 10 characters

3,4: http://www.clicketyclick.dk/databases/xbase/format/dbf.html#DBF_STRUCT

7: IIRC, from http://www.dbase.com/KnowledgeBase/int/db7_file_fmt.htm (forwarded by Wikipedia), but that page is down now

8:
http://www.dynamic.com/v7/what-are-limitations-foxpro-full-isam-file http://www.foxite.com/archives/foxpro-field-names-0000066805.htm Apparently Visual FoxPro 8 has 27 characters in a DBC (Database container) file, but only 10 in a DBF file.

Is that correct?
The reason I ask:

In packages\fcl-db\src\export\fpdbfexport.pp
These tablelevels/formats are defined:

  TTableFormat = (tfDBaseIII,tfDBaseIV,tfDBaseVII,tfFoxPro);

In function
function TFPCustomDBFExport.BindFields: Boolean;
there is this part (line 136):
If FormatSettings.AutoRenameFields and
(FormatSettings.TableFormat=tfDbaseIII) then

    CheckExportFieldNames;

1. Shouldn't this be amended to (don't know if you can use in in this way): If FormatSettings.AutoRenameFields and (FormatSettings.TableFormat in [tfDbaseIII,tfDbaseIV,FoxPro]) then

    CheckExportFieldNames;

2.
http://devzone.advantagedatabase.com/dz/webhelp/Advantage8.1/server1/table_dbf_.htm Characters allowed seem to be for standard Xbase DBF tables that are compatible with CA-Clipper DBF tables, Microsoft FoxPro DBF tables, and dBASE III+ DBF tables:

may only contain the letters ‘a’-‘z’ and ‘A’-‘Z’, digits ‘0’-‘9’, and the underscore ‘_’ character

Presumably the CheckExportFieldNames could be amended for that, too, right?

Thanks

Presumably, yes.

Michael.

On Fri, 2 Sep 2011, Reinier Olislagers wrote: > Hi all,

<snip long-winded post>

Presumably, yes. Michael.

;)

Thanks, I'll give it a go and attach a patch to Mantis...

Reinier

On Fri, 2 Sep 2011, Reinier Olislagers wrote:

<snip>

<sip>

Presumably, yes. Michael.

Uploaded a patch, issue 20134
http://bugs.freepascal.org/view.php?id=20134

I think I've also found the solution to
http://bugs.freepascal.org/view.php?id=20069
(patch attached), so if somebody would be so kind to review & apply them, I'd be very happy.

Thanks,
Reinier

You can start being happy.

Thanks for the patches!

Michael.

:) :) :)

Thanks a lot, Michael, one step closer to a working data processing/export program the hard way (i.e. getting FPC patches in for everything that seems wrong, apart from my newbie mistakes)

Yes, I have a masochistic streak - but it's nice to be able contribute back for a change.

Thanks,
Reinier

Reinier Olislagers wrote

may only contain the letters ‘a’-‘z’ and ‘A’-‘Z’, digits ‘0’-‘9’, and the underscore ‘_’ character

well, that's interesting... i was writing back to say that the above all appeared as "garbage" characters while i was reading the message... evidently they are UTF-8 or such and my message reading settings are not UTF-8 compatible... what surprised me was that they appeared correctly when i started this reply with them quoted...

PS: sorry for offtopic if such...

Reinier Olislagers wrote

I think I've also found the solution to http://bugs.freepascal.org/view.php?id=20069 (patch attached), so if somebody would be so kind to review & apply them, I'd be very happy.

Turns out the dbf export code needs at least another patch. DBF export missed support for ftfloat and other field types.

Issue + patch at
http://bugs.freepascal.org/view.php?id=20160

Even with this patch, and the other 2 recently committed, I get some errors (cannot find field=> probably a field name/length issue).

When I know more, I'll post another issue & hopefully patch.

Thanks,
Reinier

I have applied the patch (partially). Thank you.

Just out of curiousity:
Are you planning extensive improvements in the Lazarus Data desktop ? :)

Michael.

Thank you, also for the explanation in the bug report. Wasn't aware of your/maintainers' preferences regarding naming, so I'll keep that in mind. To be honest, just thought you guys were lazy & old fashioned to use I & J as loop counter variables ;)

Just out of curiousity: Are you planning extensive improvements in the Lazarus Data desktop ? :)

No, still trying to get an application to work that sucks in a custom record type and spits out
- CSV (needs to be fixed, see below)
- DBF (as mentioned, needs more fixes, working on that next)
- Excel xls
- Access XML (Access .mdb or .accdb would have been nicer, but well...)
- possibly SQL insert statements when that is fixed (see below)

See outputparser directory in source:
https://bitbucket.org/reiniero/db2securityscript/src

I've noticed that SQL insert statement output (issue 19937) and CSV output (issue 19759) don't work well either (quoting issues for memo fields and/or blobs), so if anyone's feeling in a bug fixing mood, there's patches in there already ;)

I do intend also to upload the dbf export test I used in these bug reports for inclusion in the fcl db test directory. Also, creating a Lazarus XMLSDExport component, and possibly rewriting CSVexport to use sdfdata, so it needs less code (current code basically duplicates what sdfdata does...)

Why, are you looking for people to improve Datadesktop (any hints) or planning to improve it yoursel? ;)

Thanks,
Reinier

No, brevity is a virtue. Small is beautiful :-)

I'll definitely have a look at them later today.

I do intend also to upload the dbf export test I used in these bug reports for inclusion in the fcl db test directory.

Great !

Also, creating a Lazarus XMLSDExport component, and possibly rewriting CSVexport to use sdfdata, so it needs less code (current code basically duplicates what sdfdata does...)

Well, the reason I didn't do that was that I wanted to test the generated files with sdfdata. Kind of a cross-check.

You are aware that the Export components can be registered inside lazarus ?

Why, are you looking for people to improve Datadesktop (any hints) or planning to improve it yoursel? ;)

I continuously improve it (slowly), and now your patches do as well, all these components are used in the data desktop :-) But from your descriptions I gathered that you were working on something similar as the data desktop.

Michael.

So size does matter - as I've been told before ;)

Thanks.

> I do intend also to upload the dbf export test I used in these bug > reports for inclusion in the fcl db test directory. Great !

Done, issue 20163

< Feeling like I'm going to make obvious points, but well, I'm not afraid of appearing obvious ;) >

I understand.
1. However, the existing csvexport code could be modified & moved to the test suite to verify export. (Can't remember now if it already exists, but we can also create a csvimport module for sdfdata and test the export that way)
2. Alternatively, we could use a comprehensive but fairly static (i.e. test code/data doesn't change often) export test. That would include the generated CSV data as data. Then let the test compare the "sdfdataexport" generated file with the stored CSV data. The advantage would be that sdfdata gets more exposure, and, if needed, another tool (LibreOffice, Excel, Access, .Net, C++ application) could be used to read in & verify the "sdfdataexport" generated files. After all, that's the final test, lacking a csv import module.

You are aware that the Export components can be registered inside lazarus ?

Mmmm, yes, but I'm talking about having a nice shiny XMLXSDExport button (a la existing SimpleXMLExport button) for people to throw on a form. (Maybe that's what you mean, too...)
I had tried to create a button/component before but faild due to lack of knowledge.
I now have the Lazarus book & more knowledge, so I'll give it another go.

Ok.
No, it's something else ;)

Though my holy grail that is (can be) data desktop related is having a GUI/visual query builder such as those provided by Access, SQL Server management studio (and presumably Oracle tools, have only tried them a couple of times), and Business Intelligence tools. That would allow technically inclined end users (or end users of technical software ;) to construct their own queries, could be handy, also in my current project.

Coffee first now, though ;)

Reinier

Just thought of some more reasons - I'll be away for some days, so I thought I'd throw it out to the list to ponder.
(Reworded previous reasons for - hopefully - coherent argument):

<Feeling like I'm making obvious points, but well, I'm not afraid of appearing obvious ;)>

I understand, but:

1. An import test could be written using TStringList.LoadFromFile. This will probably work for simpler formats. Will probably need a fix in TStringList.DelimitedText (issue 19610)
Hint: Dataset.LoadFromFile data import functionality for FCL ;)

2. Alternatively, we could use a comprehensive but fairly static (i.e. test code/data doesn't change often) export test. That would include the generated CSV data as data. Then let the test compare the "sdfdataexport" generated file with the stored CSV data.

3. Additionally, if needed, another tool (LibreOffice, Excel, Access, .Net, C++ application) could be used to read in & verify the "sdfdataexport" generated files.

4. Using sdfdataset, you get multiline capability (embedding a line break in a field) for free (mantis 17285) ;)

The advantage of moving to sdfdataset would be code simplification/reuse Sdfdata also gets more exposure and therefore testing.

And, yes, I'm willing to write up a patch for review, but probably not soon...

Thanks,
Reinier