Musical Nerdery - Why do all embedded databases suck???

Friday, 16 May 2008

Main Menu

Home

Web Links

Syndicate

Why do all embedded databases suck???

Written by WATYF on Wednesday, 20 July 2005 (8533 hits)
Category: .NET Programming

(IMPORTANT UPDATE: Since the writing of this article, I have finally found a .NET embedded database that works great for everything I need. I have left this article intact so you can see how things started out, but some of the opinions in it have been vastly revised. To find out which DB I went with, you can read all about it here.)

I've been hunting... and searching... and Googling for the last YEAR at least (well, maybe it's only been two months) trying to find an embedded database to use as the back end for TaskRunner.... but every single one I've tried ends up having some fatal flaw in it that makes it completely or nearly useless for TR.

What I need is an SQL capable, embedded database that is thread-safe, has a .NET provider and a small foot-print, and that is available in a .NET source code (although that last requirement, I can live without)...

Here's what I've tried so far, and why it was a miserable failure:

Firebird: This one almost worked... I actually used it as a test backend for a bit, but it requires two DLLs (the .NET provider and the engine dll) and it's larger than the other options (FB's 2MB vs others' ~250KB). Those aren’t show stoppers, though, and if none of my other options panned out, I was planning on going back to it as a "last resort". But then I found out that Firebird's embedded version can't be run on a network drive (which TR is sometimes run from)... so there goes my fall-back option.

SQLite: This one was out of the running pretty quick because it's not thread safe (despite what they claim about it

)... write operations lock the ENTIRE database *ack*... TR is heavily multi-threaded, so that one didn't last long. It also requires two dlls (the .NET provider and engine), but that wasn't what killed this option.

BerkeleyDB: This one doesn't have a .NET provider (nor is it SQL based). The Berkely XML DB has a .NET provider (available from a third party) but who the heck wants to use XML for data storage?

db4o: This one is an object mapping db (basically).... I needed one to handle SQL. It also has some weird quirks like storing BLOBs outside of the database in individual files (whuh???) and since most of my objects are stored using binary serialization, I'd have a heck of a lot of extra files. (UPDATE: I was told by someone at db4o that the external BLOB storage was optional and could be turned off, but since I'm looking for an SQL db, this still didn't suit me)

MSDE/JET/whatever: All the Microsoft options were pretty much worthless right out of the gate, considering they have a foot-print that's.... oh.... I dunno.... fifty THOUSAND times the size of TaskRunner itself.

OK... so maybe I'm exaggerating... but they might as well be that big... no one is gonna want to download 45 freaking MB of backend database just to use a ~500KB app. It's bad enough that they have to download the ~23MB of .NET Framework, if they don't already have that.

VistaDB: (UPDATE: I found out why this option wasn't working when I tried it the first time. An updated analysis of VistaDB can be found here.) This was the most widely touted alternative to MS's options (seriously... the first ten pages of Google results for ".NET embedded database" is all them.

), and they seemed like they had their act together.... but their db costs money, so that's reason enough there not even to consider it.... but... at one point I was seriously considering paying for their db, just so I wouldn't have to deal with this stupid issue any more. (that's how desperate I was... and trust me... a nerd who is willing to actually pay cold, hard cash for software is one desperate nerd.

) So I tried their evaluation version, and it had more problems than most of the other options... lol. I kept getting errors when trying to open a connetion (both after creating the DB and after re-starting the app) and I couldn't create tables using DDL (for some reason). I can't say that their product is "bad", 'cause I didn't spend more than a couple of days (off and on) trying to get it to work... so there may be a reason for each issue I was encountering, but I can say this... every single other db that I tried (that had a .NET Provider) I was able to plug right in and go (by just changing the API code used to create the DB). Once the DB existed, all the .NET provider objects worked the same from one DB to another... except with VistaDB.

So... after all that, you're probably thinking that I gave up and went to cry in a corner... well... I can't gaurantee that there wasn't any crying involved, but I didn't give up. There was still one.... last... option...

SharpHSQL: I've been using #HSQL for the last month-ish while doing the TR upgrade, so thus far, it has been a functioning backend db. It is very thread safe (tested it every way from Sunday in that respect), it has a .NET provider, it's really small (~200KB or so compiled), and the best part is... it's written entirely in C#, so I can compile it right into my application. So here's where you'd be asking yourself, "So what's the problem??" Well... the problem is, it's still a little buggy... and there were a few flaws that made me ready to drop it altogether... but I stuck it out, and I think I have a working model now. The first issue involved committing transactions. If I executed a series of SQL commands and then killed the app, the last command (or two.. or whatever) that was run is not maintained... at least... that's what I thought. In reality... the transaction was being restored (on restart) but something about how I was coordinating the population of my listviews was making it look like that object hadn't been saved. So I did some tweaking (and designed the Listviews to function like they should have in the first place) and that problem went away. The other issue was/is corruption of the db that happens when killing the app. Sometimes, not always (very flaky), after killing the app, you can't open the db again... something about corruption of a cfg file. So that's a big problem... I've got an idea, though, on how I'm gonna remedy this, so at this point, I'm thinking that SharpHQSL is my db of choice. (Update: I have fixed the termination issue since first writting this entry. I talked it over with the #HSQL guy and came up with a way to mirror the cfg file, so it doesn't get corrupted.)

It does have a couple annoyances that I had to get around, though. It uses a number of db files (I prefer a single file), one of which is a transaction log file... which is stored in a plain text file!! So you can see every single transaction being done on the database, including login... lol. So don't bother using SharpHSQL if you need a secured backend... the user can just open the log file using Notepad and see what the passwords are. (doh!

) Anyway... those were minor things that didn't knock #HSQL out of the running. Plus, the guy keeping it up has been responsive so far in fixing every bug that I've found (which has been nice). He has kinda stalled on this app termination issue (which is why I'm trying to write a workaround myself), but it's good to know you can get quick turnaround from a developer when you find a bug.

At this point, I'm fairly settled on using SharpHSQL (assuming I can solve the app termination issue), but I'm still open to other options, so if anyone knows of a good, embedded db, just contact me or throw a post up on The WALL.

(UPDATE: The issues that SharpHSQL has with crashes and multithreading caused it to be too unreliable to use as a stable backend, so I gave up on it... I did find an alternative... see here for details.)

WATYF

P.S. I'm getting a number of requests for SharpHSQL samples, so here they are:

Create a database (using SharpHSQL API):

Dim db As SharpHsql.Database, ch As SharpHsql.Channel

                db = New SharpHsql.Database("C:\My Files\MyApp\MyDbName")
                ch = db.Connect("sa", "")
                'You're connected, so you can do other API stuff here if you want, then shutdown the db and disconnect
                db.Execute("shutdown", ch)
                ch.Disconnect()

Connect to the DB using .NET Provider:

Dim con As SharpHsqlConnection

            'Create a new connection to the db and open it
            con = New SharpHsqlConnection("Initial Catalog=C:\My Files\MyApp\MyDbName;User Id=sa;Pwd=;")
            con.Open()

Run a SELECT query using the .NET Provider (using the "con" you just opened):

Dim da As SharpHsqlDataAdapter, dt As DataTable

                    da = New SharpHsqlDataAdapter("SELECT * FROM MyTable", con)
                    dt = New DataTable
                    da.Fill(dt)

Run non-query (UPDATE, DELETE, etc) using the .NET Provider (using "con"):

Dim cmd As SharpHsqlCommand

                'Execute non-query
                cmd = con.CreateCommand
                cmd.CommandText = "DELETE * FROM MyTable"
                cmd.ExecuteNonQuery()

(you would also use this to create tables by making the CommantText = "CREATE TABLE MyTable (MyObjectName VARCHAR(250), MyObj OBJECT)"

Inserting a binary object into a table using the .NET Provider (using "con"):

Dim cmd As SharpHsqlCommand, par As SharpHsqlParameter

                cmd = con.CreateCommand
                cmd.CommandText = "INSERT INTO MyTable (MyObjectName, MyObject) VALUES('MyFirstObject',@ObjDat)"
                'Add the binary data to a cmd parameter
                par = New SharpHsqlParameter("@ObjDat", DbType.Object)
                par.Value = obj 'This var "obj" needs to be a serializable object
                cmd.Parameters.Add(par)
                'Write to the db
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()

I think that covers most of the scenarios that I'm using it for. Feel free to post any questions.

< Prev

[ Back ]

Show (22) - Add comments:

Comments

You must javascript enabled to use this form

I did look into TurboDB, and it does look like a very good option. I didn't bother trying it, though, since the price tag is almost twice as much as VistaDB's. At first glance, though, it does look like it meets most of my criteria.

WATYF

Posted by WATYF, on 09/10/2005 at 09:07

could you mail those samples to me again? my friend that runs the other account went nuts a little while back and applied one of the most restrictive filters on it that i've ever seen
-thanks again gabe

Posted by gabe, on 09/15/2005 at 03:47

Awesome article. I have been looking @ sharphsql but found the lack of samples also daunting. So if you could send me the samples as well I would greatly appreciate it.

Posted by Larry, on 09/18/2005 at 06:47

Great Stuff,been struggling with this issue myself for quite some time, seems like #HSQL is the way to go. Please email me the samples as well.

Posted by Andre, on 10/06/2005 at 08:48

Stellar roundup! I've been doing the same research for weeks, largely with the same results. The only difference is I'm not willing to forgive #HSQL's shortcomings yet.

Have you tried ANTs, TimesTen, or CloudScape/Derby? I can't find any good reviews on embedding them in a .NET app.

Also, what's the footprint on #HSQL?

Last thing: above in your header for #HSQL, there's a typo: SharpHQSL

Thanks again for the reviews. I've done a lot of research on this and your roundup is by far the most comprehensive. Here's the next best one (which I also recommend): http://cplan.cse.msu.edu/cs/blogs/harning/archive/2005/04/18/721.aspx

Posted by Dinah, on 10/27/2005 at 00:43

You're right to be reluctant about SharpHSQL. Since this article, I have spent a good deal of time writing code just to work around it's flakyness with app termination and multi-threading. From what I've gathered up to this point, it's "restore after crash" function doesn't work too thoroughly (mostly when handling multi-threading, I believe). It will sometimes end up with duplicate records that violate unique indexes and what not. Anyway... I would really like to find a rock-solid stable option out there... but there just doesn't seem to be one... so for now, #HSQL is going to have to do. I will check out the other db's you mentioned... who knows... maybe I'll finally find the solution I've been looking for. As for the footprint, I believe it's around 185KB (compiled). I'm not totally sure, though, since I bundle it with a few other components into a single dll.

WATYF

Posted by WATYF, on 10/27/2005 at 00:58

I downloaded SharpHSQL binaries: could you explain me the next steps? I mean: which files do I need to use and where? With Firebird I had to install a .NET provider, and copying a .dll file in my application directory, to be able to set an embedded DB; then, I had to add a reference (Firebirdsql provider), and I could begin to work on databases. How should I start with SharpHSQL? Which files to install?
Thanks Luca (Italy)

Posted by Luca, on 11/04/2005 at 08:10

If you have the binaries (it should be two DLLs... the SharpHSQL dll and the log4net dll), then all you have to do is add the SharpHSQL DLL as a reference to your project in Visual Studio. Then you'll need to add an import to your module/class for SharpHSQL and System.Data.Hsql. Then you can start making the DB and running queries against it using the samples above. WATYF

Posted by WATYF, on 11/04/2005 at 00:32

Do I miss anything, or it seems that SharpHSQL doesn't support derived tables (the reason why i left Firebird...)??
I have an exception after a ExecuteNonQuery command.. This is my SQL update statement:
cmd.CommandText = "UPDATE HISTORY SET END_MESSAGE= NOW() WHERE CODE = @CODE AND END_MESSAGE= NULL AND BEGIN_MESSAGE= (SELECT MAX(BEGIN_MESSAGE) FROM HISTORY WHERE CODE = @CODE AND END_MESSAGE= NULL )";

the exception says: e.Message "S0022 Column not found: CODE in statement [UPDATE HISTORY SET END_MESSAGE= NOW() WHERE CODE = '11' AND END_MESSAGE= NULL AND BEGIN_MESSAGE= (SELECT MAX(BEGIN_MESSAGE) FROM HISTORY WHERE CODE = '11' AND END_MESSAGE= NULL )]"

Posted by Luca, on 11/04/2005 at 02:37

I don't know if they support derived tables. If you have more specific questions about how to use SharpHSQL, you should check out their message board: http://www.gotdotnet.com/workspaces/messageboard/home.aspx?id=ea06f702-480a-4b60-8402-70b27c6472dd . WATYF

Posted by WATYF, on 11/04/2005 at 02:53

I tried to create a table with an auto-increment value like it is defined in hypersonicSQL (GENERATED BY DEFAULT AS IDENTITY (START WITH ), but it doesn't work. I can't use only IDENTITY because i have to define a startvalue for this column.
Is it possible to do that with sharphsql?

Posted by Florian, on 12/08/2005 at 08:10

Hi, I was looking for such article. Great One. I am looking for DB. Requrement is as follows.
1. Open source (Free)
2. Embeded db. Means easily i can use in my code as residential
3. Small and fast
4. Specially for Embeded system
5. If it is SQL based then plus point
6. C/C++ source.
7. Porting on other system must be easy.
8. Misc features are plus point. Ex thread safe

Could you please guide me which one is good. I found sqlite is pretty good. Please let me know you people's opinion.

Posted by vijaya kuamar, on 02/16/2006 at 09:08

Firebird will probably be your best bet. SQLite would work for all your points except for #8 (being thread safe). WATYF

Posted by WATYF, on 02/17/2006 at 07:25

I want to know about berkely db. Is it good solution for embeded db?

Posted by vijaya kumar, on 03/27/2006 at 03:19

SharpHSql is not working for group by clause. I want to know whether this was fixed by anyone

Posted by Ansari, on 05/02/2006 at 03:03

Probably a little late but you do know Win2K, XP and Vista support Jet out of the box?

http://www.codeguru.com/vb/gen/vb_database/microsoftaccess/article.php/c5149/

and

http://www.samspublishing.com/articles/article.asp?p=29463&seqNum=2&rl=1

Posted by Ifeanyi Echeruo, on 12/09/2006 at 04:04

Any update on this? I found this helpful then realized it was 2 years old... I expect some things have changed and (hopefully) some shortcomings fixed.

What are you using these days?

Posted by DaveW, on 03/22/2007 at 22:09

I haven't looked at any of these in over a year, so some of their issues may have been fixed (though I wouldn't really bet on it). Currently, I'm using VistaDB and working on upgrading to their new version (3.0). There's a link at the top of this article that leads to another article about how I found VistaDB and why I went with it. That was back on version 2.1. I'm having some issues implementing ver 3.0, though. Assuming those get fixed, it should still be a very good solution for my needs. WATYF

Posted by WATYF, on 03/23/2007 at 08:03

1
Page 2 of 1 ( 22 Comments )

Joomla! is Free Software released under the GNU/GPL License.