Which embedded database has maximum SQL compliance, and concurrency support?

Question

My application at present uses Microsoft Access, but now may be hosted on Linux boxes. Additionally while being accessed from multiple computers, one of these may update the records (when its being read by other users).

I also require that the embedded database should support complex SQL queries - like inner SQL, Joins, etc.

I tried SQLite, but many of the existing queries fail, or need to be fixed (like in a simple query using inner join the brackets after FROM was not acceptable to SQLite, and had to be removed). Right join too is not supported.

I came to know about Apache Derby and H2, but would first prefer your valuable opinion.

Edit:

I forgot to mention that my application is entirely written in Java.

Edit:

At preset I use Microsoft Access mdb, shared on network drive, making DSN-less connection from remote computers.

Update

I did trials with Firebird, and it really does seem very good, zero admin, and SQL compliant. It is fast, and I had no problems in any of the typical queries I could try on it. I am very satisfied with it, and hopefully will use it for the project for which I raised this question.

Hopefully Advantage Server too will be good, but could not get time to review it. After reviewing/using Firebird do not feel any need to try anything else.

Berns

According to HSqlDb, they support the widest range of SQL standard features among all open source RDBMS. – Mervin

Answer 1

I think it's Firebird.

Even Cte's are supported in Firebird 2.1 !

Firebird works on Windows, Linux, Mac OS...

Jaybird driver for Java.

There are some good migration tools from Access to Firebird:

• IBDatapump
• Microsoft Access to Firebird database converter (MDB2FDB)

Spoke

Thanks for reminding about Firebird. Looks good. However, many reviews/comparisons mention that inspite of being a mature database, most of the firebird developer tools are third-party commercial products. – Berns

- IBExpert (www.ibExpert.com) have personal edition - Database Workbench (www.upscene.com) have personal edition too You can also use FlameRobin (www.flamerobin.org) – Spoke

For the database to be accessed/connected from multiple computers, and to update from the system its hosted - it seems it will require to be setup up in non-embedded, that is server mode. Will this require quite some administration? Actually I was looking for embedded databases because of their zero administration simplicity. – Berns

@vikramsjn: even in server mode, Firebird is in most of case zero administration. – Spoke

Thanks Hugues, will try it ASAP. However I am surprised that there has been no responses from Derby or H2 factions – Berns

I'll second the on the zero admin. We use Firebird in non embeded mode as a background database. We have had it running for years without anyone touching it in hundreds of installations. – Magnetic

Answer 2

I would take a look at the Advantage Database Server (sybase.com). It runs on Windows, Linux, and NetWare. We have a commercial accounting / project management application for the construction industry and switched to Advantage 6 or 7 years ago. We have been extremly pleased with the performance and support. Customers are very happy too.

Decorative

I would add that Advantage has a Type 4 JDBC driver providing great Java support. Also Advantage has great query optimization tools and the GUI database utility has an SQL debugger! Other drivers include ODBC, PHP, .NET, Delphi, and OLE DB. – Munroe

Answer 3

You can find a fairly detailed description of Derby's SQL Standards compliance here: http://wiki.apache.org/db-derby/SQLvsDerbyFeatures

Clayborne

Answer 4

Our company is using Advantage as well. We came at it from an XBase background, but have since embraced its SQL nature. It runs on a lot of platforms (Windows, Linux, NetWare) and has a bunch of drivers (PHP, ADO, JDBC, ODBC, Crystal) so that it works in just about any environment in which you might program.

Chortle

Answer 5

Firebird is the most "comptible to SQL" embedded database. The engine of the embedded version is the same as the server edition.

Pay attention that with Firebird embedded only one client at a time can connect to the database, so you can span multiple connection from the same process but not connect from different processes. This issue is fixed in Firebird 2.5 and above.

Chortle

Answer 6

This might be helpful in your research: Comparison of relational database management systems.

I personally would suggest taking a serious look at Firebird. It is cross-platform, has a native .NET provider and supports the joins you mention.

Spaniel