1

I'm connecting to a .dbf using ODBC in Delphi using FireDAC. I've setup an ODBC connection, dBase 5.0, using the 32-bit Driver do Microsoft dBase (.dbf) driver.

In my IDE (Rad Studio 10.1 Berlin), I've setup the ODBC connection as a data source. The ODBCAdvanced connectiong string is DefaultDir=%s;DriverId=533;MaxBufferSize=2048;PageTimeout=5, where %s is the correct directory.

I managed to copy a table's structure to a SQLite db using TFields (code roughly as follows).

FieldNames := TStringList.Create;
PDOXTable.GetFieldNames(FieldNames);
FieldNames.Delimiter := ';';
FieldList := TList<TField>.Create;
PDOXTable.GetFieldList(FieldList, FieldNames.DelimitedText);

TempTable := TFDTable.Create(nil);
TempTable.Connection := TempConn;
TempTable.TableName := DataTable.TableName;
for I := 0 to FieldList.Count - 1 do TempTable.Fields.Add(FieldList.Items[I]);
TempTable.CreateTable(true, [tpTable, tpTriggers, tpIndexes]);

However, the data types are different and I don't have primary keys, notnull conditions, or 'dflt_value' which I got when I manually exported these same tables using an application called Exportizer (http://www.vlsoftware.net/exportizer/), which, though it has a command-line client, I'm not sure I'll be able to bundle with my application.

Comparing results of exporting to SQLite-from-Paradox-.dbf using Exportizer and code I wrote.

What is a reasonable way of copying a table from a paradox .dbf to a SQLite while saving as much of the datatypes and parameters as possible?

Nils Guillermin
  • 1,867
  • 3
  • 21
  • 51
  • Why are you talking about dBase *and* Paradox, they are not the same thing? Anyway, MS have drivers for both and FireDAC is fine for accessing Sqlite from Delphi, and includes batch-move components for moving data between formats. FireDAC seems to do a better job than some of the alternatives with extracting metadata, so give it a shot. – MartynA Feb 23 '17 at 22:40
  • Because I'm not sure what format it is. All I know is it a .dbf. I can open them in Paradox software, and it seems I can open them using an ODBC driver meant for dBase. I think I've found those batch-move components you mentioned, thanks. – Nils Guillermin Feb 24 '17 at 00:00
  • A .dbf can be either dBase III or early versions of FoxPro, and neither of those support default values, NULL, or any kind of constraints. Exportizer is apparently assigning their own idea of default values. dBase III had no concept of NULL; an unassigned numeric field has a value of zero, an unassigned date has the value `" / / ", and an unassigned Char(5) the default value of `" "`. – Ken White Feb 24 '17 at 00:46
  • @MartynA Don't those batch-move components require the tables to already be setup before the move? – Nils Guillermin Feb 25 '17 at 15:46
  • Yes, afaik they do. – MartynA Feb 25 '17 at 16:45
  • No, `TFDBatchMove` doesn't require tables pre-created. It's enough to keep the `poCreateDest` included in the `Options` property. – Victoria Jun 16 '17 at 07:17

1 Answers1

0

Use TFDBatchMove. SQLite is typeless, but FireDAC has its own pseudo data type mapping with which you might be able to preserve a lot from original data types. And if the data types won't be exactly by your will, you can define your custom Mappings.

Victoria
  • 7,822
  • 2
  • 21
  • 44