My application at the moment uses Microsoft Access, however might be located on Linux boxes. Furthermore while being utilized from multiple computer systems, one of these simple may update the records (when its being read by other customers).
I additionally require the embedded database should support complex SQL queries - like inner SQL, Joins, etc.
I attempted SQLite, quite a few the present queries fail, or have to be fixed (as with an easy query using inner join the brackets after FROM wasn't acceptable to SQLite, and needed to be removed). Right join too isn't supported.
I didn't remember to say that my application is entirely designed in Java.
At preset I personally use Microsoft Access mdb, shared on network drive, making DSN-less connection from remote computer systems.
Used to do tests with Firebird, also it does indeed appear excellent, zero admin, and SQL compliant. It's fast, and that i didn't have problems most of the typical queries I possibly could put on it. I'm very pleased with it, and hopefully will apply it the work for that we elevated this.
Hopefully Advantage Server too is going to be good, but tend to not get time for you to evaluate it. After looking at/using Firebird don't feel any have to try other things.
I'd have a look in the Advantage Database Server (sybase.com). It operates on Home windows, Linux, and NetWare. You will find there's commercial accounting / project management software application for that construction industry and switched to Advantage six or seven years back. We've been extremly happy with the performance and support. Clients are extremely happy too.
You'll find a reasonably detailed description of Derby's SQL Standards compliance here: http://wiki.apache.org/db-derby/SQLvsDerbyFeatures
This can be useful inside your research: Comparison of relational database management systems.
Personally, i indicate taking serious consideration at Firebird. It's mix-platform, includes a native .Internet provider and props up joins you mention.
Firebird is easily the most "comptible to SQL" embedded database. The engine from the embedded version is equivalent to the server edition.
Give consideration by using Firebird embedded just one client at any given time can connect with the database, to help you span multiple connection in the same process although not connect from different processes. This problem is bound in Firebird 2.5 and above.
We is applying Advantage too. We came in internet marketing from an XBase background, but have since accepted its SQL character. It operates on lots of platforms (Home windows, Linux, NetWare) and it has a lot of motorists (PHP, ADO, JDBC, ODBC, Very) to ensure that it really works in nearly any atmosphere that you might program.