Add Best BDE Replacement With SQL


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 v3.0 www.componentace.com
Advantage Database v7.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 subqueries.

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 stanadard 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/20
03
2 Dave Rogerson 5.00 32000 09/15/20
01
3 Mark Barrel 4.50 34000 05/25/20
02
4 Nick Carlson 1.25 36000 11/30/20
03
5 John Smith 10.00 38000 02/15/19
98
6 Luke Skywalker 0.50 40000 02/01/20
04
7 Bred Canvus 3.30 42000 04/09/20
03
8 Arthur Clark 4.00 44000 05/25/20
02
9 Jimmy Toron 1.00 46000 04/06/20
04
10 Ford Smith 2.00 48000 07/18/20
03
Test table “projects”:
ID CAPTION LEADER_ID CODERS COST DEADLINE
1 Engine core 5 Dave Rogerson, Mark 200.00 10/15/2003
Barrel
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 8 Bred Canvus 50.50 02/10/2004
integration
9 Core patch #4 2 Jimmy Toron, John 10.00 02/12/2004
Connor
10 *nix 3 Ford Smith 200.00 11/11/2004
implementatio
n

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 Advantag Apollo DBISAM KeyDB NexusDB TurboDB
e
SELECT * FROM coders OK OK OK OK OK OK OK
SELECT * FROM coders ORDER BY OK OK OK OK OK OK OK
FIRST_NAME
SELECT * FROM coders ORDER BY 2 OK OK OK OK OK OK OK
ASC, 4 DESC
SELECT First_Name, Last_Name FROM OK OK Failed Failed Failed Failed Failed
coders ORDER BY Salary
SELECT first_name AS Name FROM OK OK OK OK OK OK OK
coders
SELECT DISTINCT first_name AS name OK OK OK OK OK OK Failed
FROM coders
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 OK OK OK OK OK OK OK
cost>50
SELECT Leader_ID, Sum(Cost) FROM OK OK OK OK OK OK OK
projects GROUP BY Leader_ID
SELECT Leader_ID, Sum(Cost) FROM OK OK OK OK OK OK Failed
projects GROUP BY Leader_ID HAVING
SUM(cost)>=100
SELECT * FROM projects WHERE OK OK OK OK OK OK Failed
Cost>=100 UNION SELECT * FROM
projects WHERE Leader_ID=2
SELECT * FROM projects WHERE OK OK Failed OK OK OK Failed
Cost>=100 UNION ALL SELECT * FROM
projects WHERE Leader_ID=2
SELECT * FROM projects WHERE OK Failed Failed Failed Failed Failed Failed
Cost>=100 UNION CORRESPONDING BY
(Leader_ID, Coders, Caption, Cost)
SELECT * FROM projects WHERE
Leader_ID=2
SELECT * FROM projects WHERE OK Failed Failed Failed Failed Failed Failed
Cost>=100 EXCEPT SELECT * FROM
projects WHERE Leader_ID=2
SELECT * FROM projects WHERE OK Failed Failed Failed Failed Failed Failed
Cost>=100 EXCEPT CORRESPONDING BY
(Leader_ID, Coders, Caption, Cost)
SELECT * FROM projects WHERE
Leader_ID=2
SELECT * FROM projects WHERE OK Failed Failed Failed Failed Failed Failed
Cost>=100 INTERSECT SELECT * FROM
projects WHERE Leader_ID=2
SELECT * FROM projects WHERE OK Failed Failed Failed Failed Failed Failed
Cost>=100 INTERSECT ALL SELECT *
FROM projects WHERE Leader_ID=2
SELECT * FROM projects WHERE OK Failed Failed Failed Failed Failed Failed
Cost>=100 INTERSECT CORRESPONDING BY
(Caption, Cost) SELECT * FROM
projects WHERE Leader_ID=2
SELECT First_name+Last_Name FROM OK OK OK OK OK OK OK
coders WHERE
Experience*Salary>100000
SELECT c.First_name+c.Last_Name, OK OK OK OK OK OK OK
p.Caption FROM coders c, projects p
WHERE c.ID=p.Leader_ID
SELECT Caption, Cost FROM projects OK OK OK OK OK OK OK
WHERE Cost50
SELECT Caption, Cost FROM projects OK OK OK OK OK OK OK
WHERE Cost150
SELECT Caption, Cost FROM projects OK OK OK OK OK OK OK
WHERE NOT(Cost150)
SELECT Caption, Cost FROM projects OK OK OK OK OK OK OK
WHERE Caption LIKE '%plugin'
SELECT Caption, Cost FROM projects OK OK OK OK OK OK OK
WHERE Caption LIKE 'Core patch #_'
SELECT Caption, Cost FROM projects OK OK OK OK OK OK OK
WHERE Cost BETWEEN 50 AND 150
SELECT Caption, Cost FROM projects OK OK OK OK OK OK OK
WHERE Coders IS NULL
SELECT Leader_ID, Caption, Cost OK OK OK OK OK OK OK
FROM projects WHERE Leader_ID IN
(5,2)
SELECT * FROM coders, projects OK OK Failed OK OK OK OK
SELECT First_Name, Caption FROM OK OK OK OK OK OK OK
coders c, projects p WHERE
(c.ID=p.Leader_ID)
SELECT c.ID, c.First_Name, OK OK OK OK OK OK OK
c.Experience, p.Caption, p.Cost FROM
coders c INNER JOIN projects p ON
(c.ID=p.Leader_ID)
SELECT c.ID, c.First_Name, OK Failed Failed Failed OK OK Failed
c.Experience, p.Caption, p.Cost FROM
coders c INNER JOIN projects p USING
(ID)
SELECT * FROM coders c NATURAL OK Failed Failed Failed OK OK Failed
INNER JOIN projects
SELECT * FROM coders c LEFT JOIN OK OK Failed OK OK OK Failed
projects p ON (c.ID=p.Leader_ID)
SELECT * FROM coders c RIGHT JOIN OK Failed Failed OK OK OK Failed
projects p ON (c.ID=p.Leader_ID)
SELECT * FROM coders c FULL JOIN OK Failed Failed Failed OK OK Failed
projects p ON (c.ID=p.Leader_ID)
SELECT (First_Name || Last_Name) AS OK Failed OK OK OK OK OK
FullName FROM coders
SELECT UPPER(First_Name) FROM OK OK OK OK OK OK OK
coders
SELECT LOWER(First_Name) FROM OK OK OK OK OK OK OK
coders
SELECT LTRIM(First_Name) FROM OK OK OK OK Failed Failed OK
coders
SELECT RTRIM(First_Name) FROM OK OK OK OK Failed Failed OK
coders
SELECT TRIM(BOTH 'M' FROM OK Failed OK OK OK OK OK
First_Name) FROM coders
SELECT First_Name, OK OK OK OK Failed OK OK
LENGTH(First_Name) FROM coders
SELECT First_Name, OK OK OK OK OK OK OK
SUBSTRING(First_Name FROM 1 FOR 3)
FROM coders
SELECT First_Name, POSITION('a' IN OK OK OK OK Failed OK OK
First_Name) FROM coders
SELECT UPPER(SUBSTRING(First_Name OK OK OK OK OK OK OK
FROM 2 FOR 3)) FROM coders
SELECT * FROM projects WHERE OK Failed OK Failed Failed Failed OK
NOW50)
SELECT First_Name, Last_Name FROM OK OK Failed Failed OK OK Failed
coders c WHERE EXISTS (SELECT * FROM
projects p WHERE p.Leader_ID=c.ID)
SELECT * FROM coders WHERE Joined < OK OK OK Failed OK OK Failed
ANY (SELECT Deadline FROM projects
WHERE DeadLine
Related Discussions
Latest News
Submit News Form Past News
Latest Forum Entries