Title: The SQL functionality test of embedded Delphi databases
Question: This article presents test results of all existing embedded databases available for Delphi (8 products). The goal is to find the best embedded database in SQL support.
Answer:
This research was investigated by me and my students as a practical task to learn the existing embedded database systems available for Delphi programmers. The results seemed interesting to me and I would like to share them with all the Delphi community.
The full version of this article is published on http://sql-db.cz.cc. This is the permanent URL where you can find the latest version of the database investigation we made.
This article presents test results of all existing embedded databases available for Delphi (8 products). Also, MS SQL Server 2008 is included to refer to it as etalon engine for standard SQL statements / functions. My goal was to find the best embedded database in SQL support for my students.
In this research, results of execution of 299 SQL scripts were tested for each product to emulate a real work on a simple model of the relational database for automobile sales centre.
Also, we compare the main SQL functionality of all the tested products.
The results of these investigations are the following:
1. MS SQL Server (out of competition): 245 points for 299 SQL statements, 58 points for functionality. Overall: 303 points.
Very solid SQL engine (19 bugs standard statements that are not supported or returns wrong results, 35 unsupported features), leader in 4 categories: Math Operators, Date and Time Functions, Subqueries, Supported Data Types. Runner-up (2-4 place, if it got more then 75% of leaders score) in 5 categories: Basic Queries (2nd place), Math Functions (2nd place), SELECT Statements With Joins (2nd place), SELECT Statements With UNION, EXCEPT, INTERSECT (2nd place), DDL Statements (2nd place). However, there is nothing perfect in the world. The main imperfection is lack of internal SQL functions (aggregate functions 6/16 failed, string functions 19/39 failed). It has even no TRIM function, so we must write LTRIM(RTRIM(x)) to remove all leading and trailing spaces. DDL statements support also could be much better (10/43 failed). However, in most important areas it is on top: Joins 35/40, Unions 12/13, Subqueries - 22/22, Date and time functions 36/37. It has all possible SQL features (58 points of 58).
2. Accuracer: 262 points for 299 SQL statements (1st place), 54 points for functionality (1st place). Overall: 316 points (1st place).
The best SQL engine among all tested (18 bugs, 18 unsupported features), leader in 7 categories: Basic Queries, Aggregate Functions, Math Operators, String Functions, Logical Functions, Supported Data Types, DDL Statements. Runner-up in 4 categories: Date and Time Functions (2nd place), SELECT Statements With UNION, EXCEPT, INTERSECT (2nd place), SELECT Statements With Joins (4th place), Subqueries (4th place). It has very good set of useful internal functions, can work with date and time values in any format and supports expressions aliases not only in ORDER BY, but also in WHERE, HAVING and even in SELECT columns (it allows to make SQL scripts with lots of expressions much shorter and easier to understand). However it has very limited mathematical functions support (no trigonometry functions), some minor bugs and 1 access violation (I hope, it will be solved in the following version). In any case it is undisputable leader in SQL statements test and the best of embedded databases in SQL functionality comparison, though does not have full text indexing support. Very easy to deploy compiles into executable, stores database in single file, and even can merge database and executable in single read-only database file.
3. Advantage: 228 points for 299 SQL statements (2nd place), 46 points for functionality (2nd place). Overall: 274 points (2nd place).
Good SQL engine (28 bugs, 43 unsupported features), leader in 2 categories: Math Operators , Logical Functions. Runner-up in 5 categories: Basic Queries (3rd place), String Functions (4th place), Date and Time Functions (3rd place), SELECT Statements With Joins (3rd place), Subqueries (3rd place). It has a good set of SQL functions and supported statements. However it does not support operators INTERSECT, EXCEPT and CORRESPONDING BY (6/13 failed in UNION, EXCEPT, INTERSECT) and has very limited data types support (10/27 failed).
It does not support || concatenation operator (described in all existing SQL standards, including SQL 2008: 5.2). The engine is rather solid it got no penalties. However it has some minor bugs, like ' Trim Test' instead of ' Trim Test ' and in DATE_DIFF evaluation with QUARTER and WEEK parameters. As for functionality, it has almost everything, except foreign keys rather serious miss. The transactions are supported only in client-server mode. As a result 3rd place in functionality, 2nd place in absolute and in SQL statements. Supports well known DBF format, requires to deploy library and configuration files.
4. NexusDB: 215 points for 299 SQL statements (4th place), 54 points for functionality (1st place). Overall: 269 points (3rd place).
Good SQL engine (29 bugs, 55 unsupported features), leader in 3 categories: Basic Queries, Math Operators, Supported Data Types. Runner-up in 4 categories: Aggregate Functions (3rd place), Logical Functions (3rd place), SELECT Statements With Joins (2nd place), Subqueries (2nd place). It has an adequate set of SQL functions and supported statements. However it has poor results in String Functions (16/39 failed), Date and Time Functions (27/37 failed), Logical Functions (7/22 failed), DDL Statements (11/43 failed) and has problems with ORDER BY in UNION statements (6/13 failed). NexusDB as well as Firebird has major problems with floating point values, it displays and calculates them incorrectly (1000.20001220703 instead of 1000.2). It cannot find record by condition Total = 1400.35. Nevertheless it has very good feature set that lacks only reverse engineering (export data to SQL) 1st place in features comparison and 3rd place in absolute. Easy to deploy can be compiled into executable, creates one file for each table.
5. Firebird Embedded: 217 points for 299 SQL statements (3rd place), 44 points for functionality (3rd place). Overall: 261 points (4th place).
Average SQL engine (31 bugs, 51 unsupported features), leader in 2 categories: SELECT Statements With Joins, Subqueries. Runner-up in 4 categories: Basic Queries (3rd place), Math Operators (2nd place), Math Functions (3rd place), Supported Data Types (2nd place). It has an adequate set of SQL functions and supported statements. But it has poor results in SELECT Statements With UNION, EXCEPT, INTERSECT (8/13 failed), Logical Functions (10/22 failed), DDL Statements (22/43 failed). It is the only engine that hangs in endless loop on INSERT INTO t_insert SELECT * FROM t_insert. It is very serious problem if operator will not kill the application, it will use all available disk space. The main disadvantages are poor operation with floating point and date values: lots of bugs in simple expressions like 1.0/d, CAST(Birthday AS CHAR(10)), EXTRACT(WEEK FROM Birthday). The minor problems are unsupported auto-increment fields (it requires to create generator and trigger for each auto-inc field), unsupported || concatenation operator, converting all field names to upper case, global namespace for database objects (so it is impossible to have two indexes with same names even in different tables). The list of supported internal functions can be much longer for such well known database. The functionality is very good it lacks full text indexing and normal support of SQL scripts (CREATE TABLE ; INSERT INTO..;) and auto-increment fields, 2nd place in functionality comparison. It requires library files to deploy and stores all data in single file.
6. ElevateDB: 211 points for 299 SQL statements (5th place), 46 points for functionality (2nd place). Overall: 281 points (5th place).
Average SQL engine (36 bugs, 52 unsupported features). Runner-up in 7 categories: Basic Queries (3rd place), Aggregate Functions (2nd place), String Functions (2nd place), Logical Functions (3rd place), Math Functions (3rd place), SELECT Statements With UNION, EXCEPT, INTERSECT (3rd place), Supported Data Types (3rd place). Its SQL engine is not the best it was unable to win even single category of 12. It has problems in Math Operators (3/9 failed cannot divide integer values correctly), Logical Functions (7/22 failed), Date and Time Functions (25/37 failed), SELECT Statements With Joins (10/40 failed, does not support FULL JOIN), DDL Statements (12/43 failed). The troubles started since installation it failed to install in both RAD Studio XE and Delphi 7 most of units missed, only .bpl files were extracted. So all tests has been done from ElevateDB Manager. It is the only database from our test that does not support standard SQL scripts (DDL statements separated by semi-colon) and numbers in ORDER BY clause (ORDER BY 1 DESC). As for minor bugs, there are a lot of them in different tests EXTRACT(WEEK FROM Birthday), CAST(Birthday as CHAR), CASE Gender WHEN 'F' THEN 'Female' ELSE 'Male' END, CASE WHEN Gender = 'F' THEN 'Female' ELSE 'Male' END, bugs in correlated subqueries, incorrect evaluation of COT function, and so on. The feature set is rather good, it lacks only support of normal SQL scripts and full support of auto-increment fields (with possibility to get last inserted auto-increment value). As for deployment, ElevateDB can be compiled into executable and stores data in own proprietary format (2 files per table plus configuration and lock files).
7. DBISAM: 181 points for 299 SQL statements (6th place), 40 points for functionality (4th place). Overall: 221 points (6th place).
Mediocre SQL engine (62 bugs, 56 unsupported features), leader in category Math Functions. Runner-up in 3 categories: Basic Queries (4th place), Logical Functions (2nd place), SELECT Statements With UNION, EXCEPT, INTERSECT (3rd place). The SQL engine could be much better it practically does not support subqueries (only 2 uncorrelated subqueries executed correctly, all correlated and most of uncorrelated subqueries failed). It has problems in Math Operators (4/9 failed), String Functions (15/39 failed), Date And Time Functions (26/37 failed), SELECT Statements With Joins (13/40 failed, does not support FULL JOIN), Subqueries (20/22 failed), Supported Data Types (8/27 failed), DDL Statements (15/43 failed). It does not execute SELECT with ORDER BY on the field that was not selected for output, has serious bugs in operations with date values, like d1-d2, EXTRACT(WEEK FROM Birthday) and almost does not support subqueries. DBISAM got 30 point lesser then ElevateDB (181 vs 211), rather poor system. The feature set has serious misses - referential integrity (foreign keys) and stored functions are not supported. RowsAffected property of TDBISAMQuery does not work on DDL statements. As for deployment, DBISAM can be compiled into executable and stores data in own proprietary format (2-3 files per table plus lock file).
8. TurboDB: 159 points for 299 SQL statements (7th place), 35 points for functionality (5th place). Overall: 192 points (7th place).
Poor SQL engine (57 bugs, 75 unsupported features), leader in category SELECT Statements With UNION, EXCEPT, INTERSECT. It will take too long time to describe all numerous bugs and unsupported features of this engine, just mention that it has good result only in 1 category of 12 (1st place, 13/13 queries). The weakest categories are: Basic Queries (7/14 failed), Aggregate Functions (8/16 failed), String Functions (17/39 failed), Logical Functions (13/22 failed), Math Functions (10/15 failed), Date And Time Functions (25/37 failed), SELECT Statements With Joins (16/40 failed), Subqueries (13/22), Supported Data Types (9/27 failed), DDL Statements (19/43 failed). The engine is not stable simple SQL statement (SELECT ID,Car FROM orders ORDER BY Sale_price) crashes the application, the engine cannot execute any other statements, on exit Windows shows the dialog with message that application needs to be closed. TurboDB like Firebird cannot execute CREATE TABLE and INSERT in same script, transactions cannot be handled by SQL statements, cannot export database to SQL script (reverse engineering), like DBISAM does not support RowsAffected, has no triggers and views. However it has full text indexing and stored functions support. TurboDB compiles into executable and can store all tables in single database file. Unfortunately it cannot be recommended due to instability and poor SQL functionality.
9. Apollo: 102 points for 299 SQL statements (8th place), 9 points for functionality (6th place). Overall: 111 points (8th place).
The worst SQL engine among all tested databases (102 bugs, 95 unsupported features). Runner-up in String Functions category (3rd place). Appollo has serious problems in all categories, the best result is in String Functions (12/39 failed), the worst result in Subqueries (22/22 failed). The product causes an overwhelming desire to uninstsall it and forget about it just after few minutes of use. The DDL statements does not work at all, even simple CREATE TABLE type1 (f1 INTEGER). The worst thing, that it is caused by the bugs, not data types support. The CREATE TABLE example from help file does not work too. The application built by the evaluation version requires to start it as Administrator under Vista/Windows 7. The library files are not installed into IDE automatically. The CAST operator seems having same bugs as in CREATE TABLE totally unusable. The subqueries are not supported in any form (22/22 failed, even uncorrelated). The list of bugs is so long, that I will not spend my and your time on it. Everybody can try to run SELECT Now() FROM emp WHERE id = 1 to get fun you will see some buggy characters instead of the current date and time. The feature set is so poor, that only forces to uninstall it as soon as possible. Apollo requires library files to deploy, stores data in DBF format. There are no any significant advantages, but lots of bugs and unsupported features. Shortly, I have only one conclusion many years lagging from all competitors. The worst of all SQL database systems that I ever tried for last 25 years.
Daniel K. Johnson
http://sql-db.cz.cc
sql.db.team@gmail.com