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

Absolute Database version 4.30 www.componentace.com
Advantage Database version 7.0 www.advantagedatabase.com
Apollo version 6.1 www.vistasoftware.com
DBISAM version 4.05 www.elevatesoft.com
KeyDB version 1.43.02 www.keydb.com
NexusDB version 1.0 www.nexusdb.com
TurboDB version 4.19 www.turbodb.com

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”:

ID

FIRST_NAME

LAST_NAME

EXPERIENCE

SALARY

JOINED

1

John

Connor

2.00

30000

06/05/2003

2

Dave

Rogerson

5.00

32000

09/15/2001

3

Mark

Barrel

4.50

34000

05/25/2002

4

Nick

Carlson

1.25

36000

11/30/2003

5

John

Smith

10.00

38000

02/15/1998

6

Luke

Skywalker

0.50

40000

02/01/2004

7

Bred

Canvus

3.30

42000

04/09/2003

8

Arthur

Clark

4.00

44000

05/25/2002

9

Jimmy

Toron

1.00

46000

04/06/2004

10

Ford

Smith

2.00

48000

07/18/2003

Test table “projects”:

ID

CAPTION

LEADER_ID

CODERS

COST

DEADLINE

1

Engine core

5

Dave Rogerson, Mark Barrel

200.00

10/15/2003

2

Core patch #1

5

Dave Rogerson

50.00

11/15/2003

3

Audio plugin

2

John Connor

100.00

12/10/2003

4

Core patch #2

5

Mark Barrel

25.00

12/05/2003

5

Video plugin

10

Nick Carlson

120.00

12/20/2003

6

Core patch #3

5

 

12.25

01/13/2004

7

Skins support

6

Luke Skywalker

20.00

02/10/2004

8

OS integration

8

Bred Canvus

50.50

02/10/2004

9

Core patch #4

2

Jimmy Toron, John Connor

10.00

02/12/2004

10

*nix implementation

3

Ford Smith

200.00

11/11/2004

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.

Queries

Absolute

Advantage

Apollo

DBISAM

KeyDB

NexusDB

TurboDB

 SELECT * FROM coders

OK

OK

OK

OK

OK

OK

OK

 SELECT * FROM coders ORDER BY FIRST_NAME

OK

OK

OK

OK

OK

OK

OK

 SELECT * FROM coders ORDER BY 2 ASC, 4 DESC

OK

OK

OK

OK

OK

OK

OK

 SELECT First_Name, Last_Name FROM coders ORDER BY Salary

OK

OK

Failed

Failed

Failed

Failed

Failed

 SELECT first_name AS Name FROM coders

OK

OK

OK

OK

OK

OK

OK

 SELECT DISTINCT first_name AS name FROM coders

OK

OK

OK

OK

OK

OK

Failed

 SELECT TOP 5 * FROM coders

OK

OK

Failed

OK

Failed

OK

Failed

 SELECT TOP 5,6 * FROM coders

OK

Failed

Failed

Failed

Failed

Failed

Failed

 SELECT * INTO newcoders FROM coders

OK

OK

Failed

OK

Failed

OK

Failed

 SELECT * FROM projects WHERE cost>50

OK

OK

OK

OK

OK

OK

OK

 SELECT Leader_ID, Sum(Cost) FROM projects GROUP BY Leader_ID

OK

OK

OK

OK

OK

OK

OK

 SELECT Leader_ID, Sum(Cost) FROM projects GROUP BY Leader_ID HAVING SUM(cost)>=100

OK

OK

OK

OK

OK

OK

Failed

 SELECT * FROM projects WHERE Cost>=100 UNION SELECT * FROM projects WHERE Leader_ID=2

OK

OK

OK

OK

OK

OK

Failed

 SELECT * FROM projects WHERE Cost>=100 UNION ALL SELECT * FROM projects WHERE Leader_ID=2

OK

OK

Failed

OK

OK

OK

Failed

 SELECT * FROM projects WHERE Cost>=100 UNION CORRESPONDING BY (Leader_ID, Coders, Caption, Cost) SELECT * FROM projects WHERE Leader_ID=2

OK

Failed

Failed

Failed

Failed

Failed

Failed

 SELECT * FROM projects WHERE Cost>=100 EXCEPT SELECT * FROM projects WHERE Leader_ID=2

OK

Failed

Failed

Failed

Failed

Failed

Failed

 SELECT * FROM projects WHERE Cost>=100 EXCEPT CORRESPONDING BY (Leader_ID, Coders, Caption, Cost) SELECT * FROM projects WHERE Leader_ID=2

OK

Failed

Failed

Failed

Failed

Failed

Failed

 SELECT * FROM projects WHERE Cost>=100 INTERSECT SELECT * FROM projects WHERE Leader_ID=2

OK

Failed

Failed

Failed

Failed

Failed

Failed

 SELECT * FROM projects WHERE Cost>=100 INTERSECT ALL SELECT * FROM projects WHERE Leader_ID=2

OK

Failed

Failed

Failed

Failed

Failed

Failed

 SELECT * FROM projects WHERE Cost>=100 INTERSECT CORRESPONDING BY (Caption, Cost)  SELECT * FROM projects WHERE Leader_ID=2

OK

Failed

Failed

Failed

Failed

Failed

Failed

 SELECT First_name+Last_Name FROM coders WHERE Experience*Salary>100000

OK

OK

OK

OK

OK

OK

OK

 SELECT c.First_name+c.Last_Name, p.Caption FROM coders c, projects p WHERE c.ID=p.Leader_ID

OK

OK

OK

OK

OK

OK

OK

 SELECT Caption, Cost FROM projects WHERE Cost<150 AND Cost>50

OK

OK

OK

OK

OK

OK

OK

 SELECT Caption, Cost FROM projects WHERE Cost<50 OR Cost>150

OK

OK

OK

OK

OK

OK

OK

 SELECT Caption, Cost FROM projects WHERE NOT(Cost<50 OR Cost>150)

OK

OK

OK

OK

OK

OK

OK

 SELECT Caption, Cost FROM projects WHERE Caption LIKE '%plugin'

OK

OK

OK

OK

OK

OK

OK

 SELECT Caption, Cost FROM projects WHERE Caption LIKE 'Core patch #_'

OK

OK

OK

OK

OK

OK

OK

 SELECT Caption, Cost FROM projects WHERE Cost BETWEEN 50 AND 150

OK

OK

OK

OK

OK

OK

OK

 SELECT Caption, Cost FROM projects WHERE Coders IS NULL

OK

OK

OK

OK

OK

OK

OK

 SELECT Leader_ID, Caption, Cost FROM projects WHERE Leader_ID IN (5,2)

OK

OK

OK

OK

OK

OK

OK

 SELECT * FROM coders, projects

OK

OK

Failed

OK

OK

OK

OK

 SELECT First_Name, Caption FROM coders c, projects p WHERE (c.ID=p.Leader_ID)

OK

OK

OK

OK

OK

OK

OK

 SELECT c.ID, c.First_Name, c.Experience, p.Caption, p.Cost FROM coders c INNER JOIN projects p ON (c.ID=p.Leader_ID)

OK

OK

OK

OK

OK

OK

OK

 SELECT c.ID, c.First_Name, c.Experience, p.Caption, p.Cost FROM coders c INNER JOIN projects p USING (ID)

OK

Failed

Failed

Failed

OK

OK

Failed

 SELECT * FROM coders c NATURAL INNER JOIN projects

OK

Failed

Failed

Failed

OK

OK

Failed

 SELECT * FROM coders c LEFT JOIN projects p ON (c.ID=p.Leader_ID)

OK

OK

Failed

OK

OK

OK

Failed

 SELECT * FROM coders c RIGHT JOIN projects p ON (c.ID=p.Leader_ID)

OK

Failed

Failed

OK

OK

OK

Failed

 SELECT * FROM coders c FULL JOIN projects  p ON (c.ID=p.Leader_ID)

OK

Failed

Failed

Failed

OK

OK

Failed

 SELECT (First_Name || Last_Name) AS FullName FROM coders

OK

Failed

OK

OK

OK

OK

OK

 SELECT UPPER(First_Name) FROM coders

OK

OK

OK

OK

OK

OK

OK

 SELECT LOWER(First_Name) FROM coders

OK

OK

OK

OK

OK

OK

OK

 SELECT LTRIM(First_Name) FROM coders

OK

OK

OK

OK

Failed

Failed

OK

 SELECT RTRIM(First_Name) FROM coders

OK

OK

OK

OK

Failed

Failed

OK

 SELECT TRIM(BOTH 'M' FROM First_Name) FROM coders

OK

Failed

OK

OK

OK

OK

OK

 SELECT First_Name, LENGTH(First_Name) FROM coders

OK

OK

OK

OK

Failed

OK

OK

 SELECT First_Name, SUBSTRING(First_Name FROM 1 FOR 3) FROM coders

OK

OK

OK

OK

OK

OK

OK

 SELECT First_Name, POSITION('a' IN First_Name) FROM coders

OK

OK

OK

OK

Failed

OK

OK

 SELECT UPPER(SUBSTRING(First_Name FROM 2 FOR 3)) FROM coders

OK

OK

OK

OK

OK

OK

OK

 SELECT * FROM projects WHERE NOW<Deadline

OK

Failed

OK

Failed

Failed

Failed

OK

 SELECT * FROM projects WHERE CURRENT_TIMESTAMP<Deadline

OK

Failed

Failed

OK

OK

OK

Failed

 SELECT * FROM projects WHERE CURRENT_DATE<Deadline

OK

OK

Failed

OK

OK

OK

Failed

 SELECT (First_Name || ' has ' || Experience || ' years of experience.') AS phrase FROM coders

OK

Failed

OK

Failed

Failed

Failed

FATAL

 SELECT First_Name+' joined at '+Joined FROM coders

OK

Failed

OK

Failed

Failed

Failed

FATAL

 SELECT COUNT(*) FROM projects

OK

OK

OK

OK

OK

OK

Failed

 SELECT COUNT(Coders) FROM projects

OK

OK

OK

OK

OK

OK

OK

 SELECT MIN(Cost) FROM projects

OK

OK

OK

OK

OK

OK

OK

 SELECT MAX(Cost) FROM projects

OK

OK

OK

OK

OK

OK

OK

 SELECT AVG(Cost) FROM projects

OK

OK

OK

OK

OK

OK

OK

 SELECT SUM(Experience*Salary) FROM coders WHERE ID<5

OK

OK

OK

OK

OK

OK

OK

 SELECT First_Name, CAST(Experience AS CHAR(10)) FROM coders

OK

OK

FATAL

OK

OK

OK

Failed

 SELECT First_Name, CAST(Joined AS CHAR(10)) FROM coders

OK

OK

FATAL

OK

OK

OK

Failed

 SELECT Caption, IFNULL(Coders, 'None') FROM projects

Failed

OK

Failed

OK

Failed

Failed

Failed

 SELECT First_Name,    CASE    WHEN Salary>40000 THEN 'Expensive'   WHEN Salary<=40000 THEN 'Not expensive'   END AS Category FROM coders

OK

OK

OK

Failed

Failed

OK

Failed

 SELECT Caption,    CASE Cost   WHEN 200 THEN 'Most expensive!'   WHEN 10 THEN 'Most cheap!'   ELSE 'Normal price'   END AS Category FROM projects

OK

OK

Failed

Failed

Failed

Failed

Failed

 SELECT COUNT(*) FROM  (SELECT DISTINCT First_Name FROM coders)

OK

Failed

OK

Failed

FATAL

OK

Failed

 SELECT First_Name FROM coders c WHERE c.ID IN (SELECT p.Leader_ID FROM projects p  WHERE p.Cost>50)

OK

OK

OK

OK

OK

OK

Failed

 SELECT First_Name, Last_Name FROM coders c WHERE EXISTS (SELECT * FROM projects p  WHERE p.Leader_ID=c.ID)

OK

OK

Failed

Failed

OK

OK

Failed

 SELECT * FROM coders WHERE Joined < ANY (SELECT Deadline FROM projects  WHERE DeadLine<'01.01.2004')

OK

OK

OK

Failed

OK

OK

Failed

 SELECT * FROM coders WHERE Joined < ALL (SELECT Deadline FROM projects  WHERE DeadLine<'01.01.2004')

OK

OK

OK

Failed

OK

OK

Failed

 SELECT * FROM coders WHERE Joined IN (SELECT Deadline FROM projects)

OK

OK

OK

OK

OK

OK

Failed

 SELECT * FROM coders WHERE Joined < ANY (SELECT Deadline FROM projects)

OK

OK

OK

Failed

OK

OK

Failed

 SELECT * FROM coders WHERE Joined < ALL (SELECT Deadline FROM projects)

OK

OK

OK

Failed

OK

OK

Failed

 SELECT First_Name,  (SELECT COUNT(*) FROM projects p   WHERE c.ID=p.Leader_ID) AS Total FROM coders c

OK

Failed

Failed

Failed

Failed

OK

Failed

 INSERT INTO coders (ID, First_Name, Last_Name, Experience, Salary) VALUES (11, 'Mike', 'Row', 3, 250000)

OK

OK

OK

OK

OK

OK

OK

 INSERT INTO coders VALUES (21, 'Robert', 'Linster', 1, 30000, '10.03.2004')

OK

OK

OK

OK

OK

OK

OK

 INSERT INTO coders VALUES (16,'Roger', 'Pascal')

OK

Failed

OK

Failed

OK

OK

Failed

 INSERT INTO coders  SELECT * FROM coders

OK

OK

Failed

FATAL

OK

OK

OK

 UPDATE coders SET Salary=Salary+4000 WHERE Salary<40000

OK

OK

Failed

OK

Failed

OK

OK

 UPDATE projects SET Coders=(SELECT DISTINCT First_Name FROM coders c where c.ID=Leader_ID)

OK

OK

Failed

Failed

OK

Failed

OK

 DELETE FROM coders WHERE ID>5

OK

OK

OK

OK

OK

OK

OK

 CREATE INDEX MyIndex ON coders (ID, First_Name)

OK

OK

OK

OK

OK

OK

OK

 CREATE UNIQUE INDEX MyIndex2 ON projects (Caption)

OK

OK

OK

OK

OK

OK

OK

 CREATE INDEX MyIndex3 ON coders (Experience ASC, Last_Name DESC CASE)

OK

Failed

Failed

Failed

Failed

Failed

Failed

 DROP INDEX coders.MyIndex

OK

OK

Failed

OK

OK

OK

OK

 CREATE TABLE temp (ID INTEGER,  XText CHAR(40),  Created DATE)

OK

OK

OK

OK

OK

OK

OK

 ALTER TABLE temp ADD More_Text CHAR(20)

OK

OK

OK

OK

OK

OK

OK

 ALTER TABLE temp MODIFY More_Text CHAR(60);

OK

OK

Failed

OK

OK

Failed

OK

 ALTER TABLE temp RENAME More_Text TO NewText

OK

OK

Failed

OK

OK

Failed

OK

 ALTER TABLE temp DROP NewText

OK

OK

OK

OK

OK

Failed

OK

 DROP TABLE temp

OK

OK

OK

OK

OK

OK

OK

 DROP TABLE IF EXISTS projects

Failed

Failed

Failed

OK

Failed

Failed

Failed

 CREATE TABLE "temp tab" (ID INTEGER,  XText CHAR(40),  Created DATE)

OK

OK

OK

OK

OK

OK

Failed

 ALTER TABLE "temp tab" ADD "More Text" CHAR(20)

OK

OK

OK

OK

OK

OK

Failed

 INSERT INTO "temp tab" (ID, XText, "More Text") VALUES (33, 'One', 'Two')

OK

OK

Failed

OK

OK

OK

Failed

 SELECT * FROM "temp tab"

OK

OK

Failed

OK

OK

OK

Failed

 ALTER TABLE "temp tab" DROP "More text"

OK

OK

OK

OK

OK

OK

Failed

 DROP TABLE "temp tab"

OK

OK

OK

OK

OK

OK

Failed

 START TRANSACTION

OK

OK

Failed

OK

Failed

Failed

Failed

 INSERT INTO coders (ID, First_Name) VALUES (14,'Martin')

OK

OK

OK

OK

OK

OK

OK

 ROLLBACK WORK

OK

OK

Failed

OK

Failed

Failed

Failed

 START TRANSACTION

OK

OK

Failed

OK

Failed

Failed

Failed

 INSERT INTO coders (ID, First_Name) VALUES (14,'Marty')

OK

OK

OK

OK

OK

OK

OK

 COMMIT WORK

OK

OK

Failed

OK

Failed

Failed

Failed

 EMPTY TABLE coders

Failed

Failed

Failed

OK

Failed

Failed

Failed

Total 104 queries

101

81

54

72

69

78

41

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


Google
Web www.Delphi-Central.com
Delphi Central - Delphi Programming Tutorials, Hints and Tips