Best BDE Replacement With SQL

Introduction

At one time, BDE was the database management system of choice, but since Borland stopped supporting it, many companies need to find a good replacement. As Chief Developer of DistcomSoft, the task of sorting out which DBMS we’d go with fell to me. This article is a summary of the results of my testing the leading systems.

DistcomSoft’s databases contain more than 5 gigabytes of data and because we mostly use SQL queries in our projects, SQL support is crucial in any DBMS upgrade. Rather than rely on a system’s documentation, I decided to test each product with actual data and queries to make certain it was compatible with our existing software and to determine which supported the richest implementation of SQL for future development. Hopefully, this data will be of some help to others who need to replace their BDE software.

The Database Management Systems Tested

How the tests were performed

We tested the following groups of queries:

· Simple SELECT that included WHERE, GROUP BY, ORDER BY, DISTINCT, TOP, INTO, various kinds of JOINs, UNION, EXCEPT, and INTERSECT.
· Expressions in SELECT including comparison, logical, arithmetical, string operations, date/time, aggregate functions, data types for auto-conversion, CAST, CASE and IFNULL functions, and expressions in GROUP BY.
· Nested correlated and uncorrelated queries, such as IN, EXIST, ANY, ALL, as well as scalar sub queries.
· Data manipulation using INSERT, UPDATE, DELETE, CREATE TABLE, ALTER, and DROP.
· Index support with CREATE INDEX and DROP, involving case sensitivity and direction.
· Transaction support, specifically START TRANSACTION, COMMIT, and ROLLBACK.
· Other capabilities such as EMPTY TABLE, spaces in table/field names, etc.

Only the standard syntax of queries was checked with each engine. If a query with standard syntax was not processed successfully by a database engine, I tried to adapt the query to make it work with this DBMS. I tested only statement support, not the speed of execution. The entire list of test queries was saved in a file, which was read by the test program that checked each query on a sample database loaded into each DBMS.

The sample database was in dBase IV format and included the two tables you see below. The tables were filled with plausible data of type integer, string, float and date. The queries are similar to those typically executed on these types of datasets. The tables were either imported from their dBase IV format using utilities shipped with each DBMS or were created in the specific format used by the engine.

Test table “coders”:

Test table “projects”:

The evaluation scheme is simple: if a DBMS can perform a query it gains a point; otherwise it gains nothing. If a query is fatal to the application (access violation, runtime error, infinite loop, etc), the DBMS loses 5 points. In some cases the success of a query depended on the success of a previous query, such as dropping a field after renaming it, so this must be kept in mind when analyzing the results table.

The table itself contains a column for each DBMS tested and a column showing the query used in that test. “OK” means no exception was raised by the query. “Failed” means the statement/capability is not supported. “FATAL” signifies the dismal fact that the query killed program execution.

The tests were run on a common desktop: AMD Duron 1400MHz, 256MB RAM, 20GB HDD running Windows 2000 Professional SP3, Delphi 7.

Summary of Results for Each DBMS

Absolute Database

This DBMS proved the most powerful and stable of all the systems tested. It was missing a few capabilities such as EMPTY TABLE, DROP TABLE IF EXISTS and IFNULL, but it far surpassed its closest competitor. I was especially pleased with its transactions support, auto-conversion of data fields and how it manipulated datasets with the EXCEPT and INTERSECT statements. All subqueries were accomplished correctly.

Surprisingly, for many tasks, Absolute Database was the only engine that was capable. I also found it could insert a BLOB field within the SQL query MimeToBin() function, as well as work with tables entirely within RAM, which is very handy (and fast).

AbsoluteDB comes with two useful utilities—DBManager and DBImportExport—that greatly simplify work and migration from old database formats. Absolute has a single file database format and can access tables from different databases using the same query.

Advantage Database

This DBMS displayed good stability and SQL support though it has its own command eccentricities. For example, double and single quotes (“ and ‘) sometime mean different things, which may cause trouble until you’re fully acquainted with this software.

It cannot access different databases with the same query. Advantage DB does not understand EXCEPT, INTERSECT, CORRESPONDING BY and has problems with JOINs and data type auto-conversions. It has problems with nested SELECTs (examples: “SELECT First_Name, (SELECT COUNT(*) FROM projects p WHERE c.ID=p.Leader_ID) AS Total FROM coders c”; “SELECT COUNT(*) FROM (SELECT DISTINCT First_Name FROM coders)”). When performing an INSERT omitting column names you can’t insert only some of the first fields; it must be the entire row of data.

No queries appeared to be fatal for Advantage Database and it stands in Second Place on the pedestal to the right of AbsoluteDB.

Apollo

This is the strangest engine I tested. Apollo uses the DBF format to store tables so I thought it would be easy to import the original databases. However, opening the tables in both utilities shipped with the installation resulted in empty columns, all of type DATE. Moreover, when I switched to table structure I saw that the fields simply did not exist! At the same time when I set it all up and performed a simple “SELECT *” I mysteriously got the correct dataset with all dates visible. Needless to say, I was not impressed.

To avoid possible format conflicts I decided to create tables and fill them using the engine itself. Here lay another ambush. If you create the table “coders” and fill it with data, then create and fill “projects”, Apollo gives you an “sql_execSQL” error. On the other hand, if you create and fill “projects” first, then “coders”, everything goes fine. Obviously, Apollo has a very unstable engine. Being fed with SELECT First_Name, CAST(Experience AS CHAR(20)) FROM coders, SELECT First_Name, CAST(Joined AS CHAR(20)) FROM coders it hangs up with an access violation. Some queries fail from time to time with “NIL stream”, “sql_closeSQL”, “sql_getRecord”. Sometimes the same query describes its failure with two different explanations. DROP TABLE and DROP INDEX are not supported. Simple math operations like UPDATE … SET do not work. It doesn’t support special directions for fields of an index. For a complete list of what Apollo cannot do refer to the table above.

It appears to be that the Apollo developers are taking their first steps in including SQL support into their product. Apollo supports some extended features, yet cannot complete the more basic and typical requests. Although TurboDB scored less, I definitely placed Apollo last on my list of DBMS’s to select from.

DBISAM

This engine comes with transaction support and a flexible command syntax; for instance it understands function calls with different parameter notations: SUBSTRING(astring FROM 3 TO 5) and SUBSTRING(astring, 3, 5) for example. Surprisingly however, it does not support non-alias table prefixes, such as “coders.First_Name”. It also has problems with nested queries and neither supports the CASE statement nor field type auto-conversions. What really drew blood in its score was the simple query “INSERT INTO coders SELECT * FROM coders” resulted in an infinite loop with the database growing to eventually fill the entire disk drive and bring down the server. But, even this liability was somewhat compensated by a few DBISAM-specific SQL extensions (like IMPORT, EXPORT, REPAIR TABLE) that may occasionally come in handy.

KeyDB

KeyDB’s syntax remained mostly at the ANSI ‘92 standard (not bad in my opinion), but its developers included inadequate help on any extensions they did provide. Specifically, there’s no information available on which functions can be used, their syntax and their limitations.

Further, for some reason, the developers decided to add their own, personal feature. When assigning UdbQuery.SQL property (UdbQuery.Active is False!), some processing occurs and the expression “SELECT COUNT(*) FROM (SELECT DISTINCT First_Name FROM coders)” results in the message “SexprNoRParen, [TokenName]”. Such behavior is suspicious. Also, the not unusual command SET Salary=Salary+4000 did not work. The functions NULLIF and CAST are not supported. It cannot access tables from different databases in one request. However, KeyDB executed all JOINs correctly and successfully performed many of the SELECT FROM SELECT queries, which gives me a bit of hope that with hard work the developers could produce a decent engine some day.

NexusDB

Working with NexusDB I got the distinct feeling that its developers just love complexities. To perform a single query on the local table it took five (!) components on the form. This DBMS is recommended for those people who love to customize and tweak. Transactions cannot be accomplished through SQL, it does not support EXCEPT/INTERSECT, field auto conversion, IFNULL nor searched CASE and chokes on “UPDATE projects SET Coders=(SELECT DISTINCT First_Name FROM coders c where c.ID=Leader_ID)”. It cannot access tables from different databases and does not support non-alias table prefixes (coders.First_Name). But, on other groups of queries NexusDB deservedly received a high score and gets Bronze.

TurboDB

It is hard to say anything good about TurboDB. My first disappointment was its buggy export/import utility. Second was in discovering it does not understand a capitalization change: field First_Name is not First_name, but is recognized as FIRST_NAME. For example, the functions Ltrim, Rtrim must be written exactly that way, not ltrim or LTRIM. Queries such as “SELECT (First_Name || ' has ' || Experience || ' years of experience.') AS phrase FROM coders” and “SELECT First_Name+' joined at '+Joined FROM coders” result in a 216 error with its inevitable consequences. The unsupported SQL statements list is much too long. Refer to the table above for details.

Summary

Nothing’s perfect in this world, I’m told, and so it is with these top DBMS’s. I guess I was naively hoping to find an ultimate database system, but every one I tested was missing something. Even so, the DBMS I chose to migrate to was clear: Absolute Database. I give it top awards as: most reliable, easiest to use and widest range of SQL commands available. Besides having a first-rate SQL engine, it provides high-quality utilities and useful documentation. If you need to build a client-server complex, try Advantage Database or NexusDB to setup connection options to possibly achieve a better speed. But if a file-server is what you’re looking for, I haven’t found any better than Absolute DB. In multi-user mode it preserves all its advantages.

Use the link below to get the sample test tables, SQL queries list used on each DBMS and the source code of the test program.
sqltest.zip

Send any questions or remarks to roman.korzh@hotpop.com
I hope this overview helps you save some time and money in your work.

Roman Korzh,
Chief Developer of DistcomSoft

Web www.Delphi-Central.com