Recent

Author Topic: Converting a Project from TDbf to SQLite [SOLVED]  (Read 608 times)

1HuntnMan

  • Sr. Member
  • ****
  • Posts: 276
  • From Delphi 7 to Lazarus
    • NewFound Photo Art
Converting a Project from TDbf to SQLite [SOLVED]
« on: November 06, 2024, 08:58:52 pm »
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.
« Last Edit: November 21, 2024, 05:55:22 pm by 1HuntnMan »

egsuh

  • Hero Member
  • *****
  • Posts: 1488
Re: Converting a Project from TDbf to SQLite
« Reply #1 on: November 07, 2024, 04:19:49 am »
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.
« Last Edit: November 07, 2024, 04:36:13 am by egsuh »

cdbc

  • Hero Member
  • *****
  • Posts: 1644
    • http://www.cdbc.dk
Re: Converting a Project from TDbf to SQLite
« Reply #2 on: November 07, 2024, 06:39:27 am »
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/  8)
Have fun
Benny
« Last Edit: November 07, 2024, 06:42:57 am by cdbc »
If it ain't broke, don't fix it ;)
PCLinuxOS(rolling release) 64bit -> KDE5 -> FPC 3.2.2 -> Lazarus 2.2.6 up until Jan 2024 from then on it's: KDE5/QT5 -> FPC 3.3.1 -> Lazarus 3.0

egsuh

  • Hero Member
  • *****
  • Posts: 1488
Re: Converting a Project from TDbf to SQLite
« Reply #3 on: November 07, 2024, 08:07:25 am »
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

  • Hero Member
  • *****
  • Posts: 2737
Re: Converting a Project from TDbf to SQLite
« Reply #4 on: November 07, 2024, 08:15:43 am »
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/  8)
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
« Last Edit: November 07, 2024, 09:01:37 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

jcmontherock

  • Sr. Member
  • ****
  • Posts: 270
Re: Converting a Project from TDbf to SQLite
« Reply #5 on: November 07, 2024, 10:54:26 am »
I found an export example. I do not remember where, but it seems to work.
Windows 11 UTF8-64 - Lazarus 4RC1-64 - FPC 3.2.2

1HuntnMan

  • Sr. Member
  • ****
  • Posts: 276
  • From Delphi 7 to Lazarus
    • NewFound Photo Art
Re: Converting a Project from TDbf to SQLite
« Reply #6 on: November 09, 2024, 06:06:59 pm »
Thanks all, on the way. But, will try the export example to see how that works from jcmontherock.

 

TinyPortal © 2005-2018