Converting a Project from TDbf to SQLite [SOLVED]

[1HuntnMan]

Has anyone accomplished this before and recommend the best way to approach this.

I'm thinking to just write the code to create the database.  Then get that accomplished. Then start educating myself on changing all the forms?????

I'm researching and finding:

Code: Pascal  [Select][+][-]

1. DestTable.FieldByName(s).Value := SourceTable.FieldByName(s).Value;
2.  

I'm thinking to just create the tables one by one. Trash the test data and modify the forms for SQLite3.

[egsuh]

I'd rather think of dbexport functions. The first that comes to mind is exporting existing file to csv file first, and then loading that csv file. You'd better check with TSQLiteDataSet of Lazarus.

--- I think I have been importing CSV files directly to other DB formats, but need to check.

[cdbc]

Hi
I'd write a little utility, that takes a '.dbf' file (a table) as input param and then

1) Connect to a new 'sqlite3' database, e.g.: named like the project,
   or connect to an existing database.
2) Reads the table layout of the 'dbf' and then forms a sql-statement, that will
   create a similar(copy of) table in the sqlite3 db and execute it.
3) Runs through the 'dbf' file, reads the data and insert it into the newly
   created sqlite3 table.
4) Closes the database and the 'dbf' table/file ...and we're done.

5) "Rinse and repeat" for every 'dbf' table you have.

This is called a /datapump/ utility.

When this is out of the way, you can start on your application and since both technologies (SqlQuery and Tdbf) descends from 'TDataset', this should be /doable/ 
Have fun
Benny

[egsuh]

I found the simplest method (conceptually, at least).

You can call TSQLQuery.CopyFromDataSet, and then its ApplyUpdates.
You would have to CreateDataSet of SQLite tables, but I think it would be also possible to copy the structure of DBF tables. In this way you don't have to write any codes that process each field and each record by yourself.

[Zvoni]

Hi
I'd write a little utility, that takes a '.dbf' file (a table) as input param and then

1) Connect to a new 'sqlite3' database, e.g.: named like the project,
   or connect to an existing database.
2) Reads the table layout of the 'dbf' and then forms a sql-statement, that will
   create a similar(copy of) table in the sqlite3 db and execute it.
3) Runs through the 'dbf' file, reads the data and insert it into the newly
   created sqlite3 table.
4) Closes the database and the 'dbf' table/file ...and we're done.

5) "Rinse and repeat" for every 'dbf' table you have.

This is called a /datapump/ utility.

When this is out of the way, you can start on your application and since both technologies (SqlQuery and Tdbf) descends from 'TDataset', this should be /doable/ 
Have fun
Benny

Be careful with this approach.
We don't know anything about Parent/Child-Tables/Relations (PrimaryKey/ForeignKey) of his design.

That said: Parent-Table before Child-Table
Meaning: Even the order in which you "export/import" any DBF-Tables to SQLite is important.
(Yes, i know there is a way in which you can ignore the order, but ... seriously???)

Might even be worth to check your design/layout of the Database-Structure and rework it if neccessary

EDIT: My Approach.
Create your SQLite-Database with an external tool (DBeaver, DB Browser for SQlite et al).
Create your Tables, Columns, Primary Keys, Foreign Keys, Constraints, Indexes whatever according to the Datatypes used for each Column.
Then use the CSV-Approach mentioned by egsuh exporting the Data in the DBF-Tables (IIRC DBeaver should be able to do that, if not event directly migrating it to SQLite)
and use the "Import CSV" which is available in DB Browser for SQlite.

That way you would be able to catch any "malformed" original Data.

Special care has to be provided for the "difficult" Datatypes, specifically DateTime

[jcmontherock]

I found an export example. I do not remember where, but it seems to work.

[1HuntnMan]

Thanks all, on the way. But, will try the export example to see how that works from jcmontherock.