Delphi version 5.1.1.1 database connectivity

[mrcamo]

Hello, I have been asked to look into an issue of swapping a paradox database out of a delphi app and looking into which other databases could be used, its mainly due to an issue of concureny.

Currently the application is using the BDE which comes with delphi 5, 5.1.1.1.

The version of the paradox driver is V4.0.

I have been looking around to find alternative databases that could be used but I am really struggeling to find any information, and as a starting .Net developer this technology is before my time.

Does anyone have any suggestions where I should start looking?

What drivers/databases are compatible with delphi 5.1.1.1?

Any help would be greatly appriciated

Thanks

 

[smays]

Are you saying you want to:

1.) Keep using Delphi 5.
2.) Identify a RDBMS (with a server layer) to replace Paradox that will solve concurrency issues.
3.) Transfer your data from Paradox to the new database.
4.) Write/rewrite your code to use the new database to access your relocated data, i.e., you need new drivers to access the new database.

Do I have your situation at least sort of correct?

Steve.

 

[mrcamo]

Pretty much yes, the amount of code that has to be changed in the application has to be kept at a minimal level..

to expand on what I said earlier...

I have been given the task of swapping out a paradox database to a different dbms that is more scalable.

The application is used for electronic point of sales units (touch screen tills) currently the application is delphi 5.1.1.1 using BDE and paradox v.4.

paradox needs replacing for a different more scalable database.

To add further complications, each till has its own paradox database, and they are all tied into a back office data base (for the purpose of durability.. if the back office database goes down then all the tills still work)

Apparently the data from each till is batch processed to the back office database at the end of the day (im unsure how this happens.. any suggestions? )

From the research I have done, I have found that there are drivers for pretty much every database that are compatible with delphi 5?

The restriction would be down to data aware controlls? i.e am i right in thinking that the way delphi code is used to connect to and process SQL is a specific version of data controll such as TDataSet (D3+)?

Which means, that the new database would have to support drivers that worked with TDataSet (D3+) .. in order to limit the amount of code that needed changing if the paradox database were to be ported to something else?

Does this make sense to you? I may be totally wrong, I am used to relativley new technologies such as .Net, delphi and paradox are way before my time,

Your help is greatly appriciated

 

[smays]

There are various options available for a database. I am personally a big fan of MySQL. MySQL has a server layer that will allow multiple users to read/write data with little trouble.

To access the database for reading and writing (from and to MySQL), I use libraries (non-visual VCL) from

http://www.microolap.com.

The libraries from microolap for MySQL include, amongst other things, a "TBDE2MySQLDAC" component ("class is intended for the easy conversion of BDE Database Objects into DAC for MySQL Database Objects") and a "TMySQLDump" component ("allows to get SQL script with a dump of a DataBase.
This script can be executed on another MySQL server by TMySQLBatchExecute component."). These components are indeed 'TDataSet compliant', and, in fact, claim to be made for many Delphi/C++ Builder versions starting with Delphi 5-7. I would think one big characteristic of the VCL that *could* be helpful is the fact the code-wise interface to components is very similar (if not identical) to the BDE components.

I hope this helps a little, at least. I am curious to see what the other guys have to offer.

"Apparently the data from each till is batch processed to the back office database at the end of the day (im unsure how this happens.. any suggestions? )"

Gee, for the 'new' system, perhaps you could install a ??? (MySQL, MsSQL, PostGRE, etc.) server on each system and perform two quick database writes, one to the local server and one to the main server. Perhaps each night a 'dump' (SQL script) of critical tables (I suppose the menu listings and associated prices) is made available to all the POS systems, and first thing in the morning each individual POS system possibly truncates a matching local table and replaces the truncated local data with the 'dump'ed data.

Anyway, I hope this helps a little.

Steve.

 

[mrcamo]

So Am i correct in thinking when writing a query string in delphi they could be constructed using delphi specific syntac

eg. LOOKUP , FIRST, NEXT , LOCATE .....

is this an example of data aware controlls?

As for example if i swithched the app to a paroadox database, there driver would have to be able to interpret the dephi sql and translate into 'SQL' to the database and visa versa?

Thanks

 

[smays]

I suppose one could literally consider Delphi code written using Lookup, Locate, etc... as a query. In general, when one refers to a database query, one is referring to a Structured Query Language (SQL) statement constructed to either read or write data from or to a table or tables. SQL is literally different and separate from Delphi. Delphi is essentially object oriented PASCAL for a Win32 environment. SQL statements are typically found in one of three locations: 'embedded' in a program such as one written in Delphi, 'embedded' in a Stored Procedure or the like in a database, or stored in a field of a table in a database.

Do you have access to the Delphi code that uses the BDE? If so, are there any TQuery components on the TForm or in a Data Module? If so, take a look at the "SQL" in the "Object Inspector", you will likely find an SQL statement there. If not there search the Delphi code for ".SQL".

The Lookup, Locate, First, and Next statements are member functions of TDataSet. The Direct Access Components I mentioned earlier are descendants of TDataSet.

A Data Aware Control is anything that does what it is intended to do when data is available. For example, a TDBGrid will display data as soon as data is made available to it by a TDataSource. A TDataSource will make available the data to a TDBGrid as soon as data is made available to it from a TDataSet. TTable and TQuery are both descendants of a TBDEDataSet.


Imagine you placed on a TForm the following: a TButton, a TDBGrid, a TDatabase, a TQuery, and a TDataSource. Now imagine a table:

family_names
------------
FamilyMemberID LastName FirstName
1 Doe Jane
2 Doe John
3 Smith Mary
4 Smith Mike

Let's construct an SQL statement:

SELECT FamilyMemberID, CONCATENATE(FirstName, ' ', LastName)
FROM family_names
WHERE LastName = 'Smith';

In the "Object Inspector" of the TQuery, you assigned the SQL statement (query). For the event covered by the TButton, you Open()'ed the TQuery.

procedure TForm1.Button1Click(Sender: TObject);
begin
  Query1.Open;
end;

Data is now in memory and is made available from the TQuery to the TDBGrid via the TDataSource. Because TDataSource and TDBGrid are both data aware, I do not have to explicitly handle that in my code. Suddenly the TDBGrid shows the data. Now then, for whatever reason, if I decided to look up (yes, Lookup) the FamilyMemberID based on a specified concatenated first and last name, I can use the Lookup method made available by the TQuery.


I think, after the data from your back office source is moved from Paradox tables to ??? (MySQL, MsSQL, Oracle, FirebirdSQL, whatever), you will find most Direct Access Components will make it so you only have to update the text in your Delphi program(s) to replace all the references to the current BDE related queries and tables. I would imagine even the embedded SQL statements would be 'transportable' and not require significant changes. It is possible MySQL would not be appropriate for this project, but I would imagine it would work nicely. That said, if you adopt MySQL and the DAC from microolap, I am confident the project would go smoothly.

By the way, I swear I do not work for microolap, I have just had good success with them...

Steve.

 

[mrcamo]

Thanks,

Just to clarify...

The following link takes you to information on a replacement DBE driver for microsoft access... if I were to port the paradox database to an access database (for the sake of example)

http://kylecordes.com/bag/sol/titanaccess.html

Using this driver, could I then replace the BDE for it and it would simply work in my application (maybe changing a few connection strings etc?)

If all you knew was that the app was delphi 5, using a paradox v4 database, and i wanted to move to access using that driver... then what questions would you be asking your self or what changes in the code would you be anticipating?

Thanks, you have been really helpful so far

 

[mrcamo]

Sorry I meant this link

http://kylecordes.com/bag/sol/opusda.html

also say i wanted to change to MySQL via the following driver, what would the implications be here..

http://kylecordes.com/bag/sol/odbcexpress.html

cheers

 

[smays]

If all you knew was that the app was delphi 5, using a paradox v4 database, and i wanted to move to access using that driver... then what questions would you be asking your self or what changes in the code would you be anticipating?

When I consider this question and when I consider the drivers you mentioned, the following come to mind:

1.) At least some of these drivers require ODBC (primarily for MySQL) to access the database. Personally I avoid dependencies like that. I do realize a lot of the work can be done in code to write to the Registry so as to avoid having to manually set up the ODBC drivers for every PC using my Apps. Microsoft Access can be accessed directly through Jet OleDb, which conveniently ships with Windows, via a connection string using dbGo for ADO, which probably shipped with Delphi 5.

2.) The three drivers you mentioned are all probably descendants of TDataSet, which will help simplify your change-over work. That said, little is known about the actual Database, Table, and Query components themselves (notice I did not precede database, table, and query with a capital "T", I do not want to suggest a component name, since little is known about the components), therefore the similarity between how items like parameterization is handled is also unknown. If the syntax is the same, change-over should be straight forward, if it is different, you will have to figure out how, for example, parameterization is handled with the new libraries and change everything over accordingly.

The syntax of the dbGo for ADO components differ occasionally from the syntax of the BDE components. You would have to experiment with the dbGo for ADO components to see the differences.

A spreadsheet is literally a BDE alternative, but it doesn't mean it is similar enough to reduce your work. I am confident there will be syntax updates and re-compilation of the programs no matter what you choose. You goal, as I understand it, is to minimize your work.

3.) Something to keep in mind when porting over to a new database is data types. Compatibility of data types is, of course, one issue, but Microsoft Access, for example, loves to default anything that looks like a number as a NUMBER and anything else (except possibly BLOBs) as a CHAR(256). You will have to watch Access carefully. Access can be quite handy; Access can actually be used to transfer data ("File"|"Export...")from (in this case) Paradox tables to any database for which you have an ODBC client set up.


Ultimately, if you are interested in any of those libraries, you should download them (is there a trial version available?) and check them out.

I have set up a MySQL database, set up all the tables, and written a number of Apps to read/write from and to the database. The beginnings of my learning came from a book called "SQL for Dummies" and a PDF I downloaded from

http://www.drbob42.com

From the PDF, I learned about the BDE, which (good, bad, or indifferent) set the tone for what I expected code-wise for data access components. That said, I tend to base part of my decision to cough up cash for a component based on whether it is similar to the BDE components or not. The idea is, I would have less to learn (pretty pathetic, huh?).


I hope this helps at least a little.

Steve.