[GLLUG] Moving from a Paradox database on Microsoft to Linux

[James Courtier-Dutton]

On Tue, 28 Jul 2020 at 17:02, Chris Bell via GLLUG
<gllug at mailman.lug.org.uk> wrote:
>
> Hello,
> I have been asked how best to transfer the data from a Paradox database to
> something suitable on Linux. Any information welcome. Thanks.
> --

While I have not used Paradox. I do have experience of transferring
data from proprietary databases to more open ones.
Steps I take:
1) Backup the existing database and create a VM or similar that can
read it. This gives you a safe copy of the database and more
importantly, the application that is used to access the database, in
case months down the line you realise something is missing.
2) Note down as much about the database as possible. DB version.
Driver versions etc. Depending on how old the database is, you might
have a standard SQL interface to it.
3) Count the amount of rows in each table.
4) Do some test queries, and capture the output. Used later where you
then do the same queries on the exported data to test compare that the
data is identical.
5) Repeat (3) so that it covers all the tables.
6) Based on what you gather in (2) you have the following options:
a) Use the original database's export features. (Quite often the best option)
b) Use standard connectors, e.g. ODBC or JDBC to extract the data.
c) Use a tool already written by someone to migrate the data. E.g.
Paradox to Postgres or Paradox to mysql.
Once you have the data in open source DBs such as postgres or mysql,
it is then easily accessible and can be converted to other formats.
7) Points to note:
DBs store fields in a large variety of character encodings.
Rerun the queries you found in step 3 and 4 to ensure that you get all
the same results on the exported data.
DBs have field types, foreign keys, schemas. This metadata also needs
exporting, and not only the records themselves.
DBs also have "stored procedures" and various defined "views",
different types of indexed fields.
DBs behave differently. For example MS ACCESS definition of TRUE/FALSE
in SQL query expressions is different from MYSQL's definition of
TRUE/FALSE
Exporting the DATA is one thing. Ensuring that the new application
that you are using to access the DATA works the same as the old
application is quite another matter and can be difficult to get right
due to SQL differences between databases.

Kind Regards

James