Brainstorming: providing SQLQuery for TDbf

[tatamata]

The obvious frustrating limitation of TDbf component is lack of SQL. It is not problem for simple data-aware applications but is a great problem if you need complicated datasets queried from several tables.
What are your ideas for starting point in implementing SQLQuery component for working with TDbf datasets?
Utilization of existing SQLQuery components?

Regards,

Zlatko

[tatamata]

In fact, what I am looking for, seems to be similar to TxQuery component that exists for Delphi. It is commercial though.
A component that would provide SQL querying capabilities for different datasets, such as Memds, Dbf, Sdf etc.
It seems to me that starting point could be JanSQL engine. But it deals with SQL strings, string lists, not TDataset descendants. Also, it is typeless, everything treating as Variant (performance issue?).
The good thing is that it has SQL Tokenizer and complete SQL engine.
So, either rewrite jansql completely, or extend it to transform resultset to TDataset? If the letter one, tranformation should be done twice- first for input datasets, then for resultset.
The component itself could be Memds descendant?

Your opinions?

[ozznixon]

Were you, or anyone else - able to get TxQuery to work in Lazarus???


I now own the IP rights to Halcyon 7 (full xBase engine) - and I would like to put TxQuery in front of Halcyon, using my DXSock Server suite. And make a simple xBase Server for Lazarus/FPC developers. [I am unable to get TxQuery to work in old Delphi7 - which is my starting port to port this to Lazarus].


Best Regards,
Ozz

[CharlyTango]

Hi,

TXQuery is undoubtedly a very fine piece of software which can help in different Situations.
As of  Nov 17, 2009 it is not commercial any more and can be found here.

In my optinion (as someone who grew up with dBaseII dBaseIII to different xBase dialects coded in Clipper) xBase is quite a bit outdated for database applications.

For single User DBs i mostly use SQLite which has some special features like every other SQL-DBs but provides a full set of SQL-features and is available in nearly all operating systems.
And in the need of more capabilities it can be migrated to other SQL-DBs easily.

[SymbolicFrank]

Making a selection of a DBF table is quite slow, if you use a JOIN you have to make sure everything referenced is indexed and the indexes are all in memory.

I made a program to calculate some totals, the equivalent of a select statement with three joins. It took more than a day to run over about one million records from the DBF database.  Inserting the whole database into SQLite took less than 10 minutes, after which the query finished in less than a second. So, more than 100 times as fast, including the time to copy the database, or 100,000 times if you only look at the time it took to execute.