Fog Creek Software
Discussion Board




Embedded DB Solutions

Just wondering if anyone around here has any experience with picking an embedded SQL RDBMS product.  Looking for something to use in conjunction with the .NET platform to enable offline mode for a smart-client app that deals with lots of data.

I've googled my brains out on this one and am still coming up mostly empty.  So far I've looked at TurboDB.Net and  VistaDB.Net.  They both seem like they could be promising in another couple version releases, but for now seem a little incomplete or beta-ish.

I just downloaded the Sybase SQL Anywhere eval, which looks pretty good from the website description, but I still have to play with it.

Anybody have any experience with these or other solutions?

Joe
Wednesday, July 28, 2004

Have you seen this?
http://www.microsoft.com/sql/msde/default.asp

Yoey
Wednesday, July 28, 2004

Firebird?

It's free to use and has an embedded mode where all you have to distribite is a dll.

http://www.firebirdsql.org

Chris Donges
Wednesday, July 28, 2004

Yoey -- Yes, I know what MSDE is.  However, MSDE is *NOT* an embedded database.  It's a running service which processes requests in exactly the same way that the full SQL Server does.  That means it's a royal PITA to manage across the installed client base, and it's vulnerable to pretty much all the same attacks as MS SQL.  I need something more transparent, and more secure.

Chris --  I did download Firebird, but I have to say I'm having a bit of a difficult time figuring it out (although admittedly I haven't spent very much time trying yet).  I perused the Contributed Downloads section, but had trouble finding an admin tool that I could actually use to create a DB & schema...unfortunately my knowledge of SQL isn't quite good enough to create a whole DB using DDL statements :)

Joe
Wednesday, July 28, 2004

Why not use Jet/Access? Mostlikely it would be easiest to code against if you're already writing against SQL Server, and you just need to include a blank db file in your install. I assume you know this, so is there a reason you don't want to go that route?

JWA
Wednesday, July 28, 2004

You might also want to look at sqlite: http://www.sqlite.org

This is written in unmanaged code, but there are several managed wrappers around it, and a couple of ADO.NET providers in varying levels of completion. It's been around for quite a while, is well respected, and is public domain.

Chris Tavares
Wednesday, July 28, 2004

If you want more info on getting started with firebird send me an email.

Chris Donges
Wednesday, July 28, 2004

I'll second the recommendation of Sqlite.

lumberjack
Thursday, July 29, 2004

Third vote for SQLite.

Ori Berger
Thursday, July 29, 2004

I started using IBOConsole for Firebird admin - but eventually wrote the DDL statements myself. The advantage of this route is: You end up with a batch script that can recreate the database and all your test data (--> automated testing etc.) and you can change the model as often as you like whilst developing (add fields only when feature is actually being implemented) etc.

It took a while to learn the syntax and stuff (Firebird has a bunch of PDF documents describing everything you need to know about it. Building the batch file (cmd.exe - ugh! next time I use Python!) was a pain, but taught me lots: Did you know that cmd.exe supports for loops and if branches?

I didn't use the embedded version of Firebird - but I did use the FirebirdNETProvider 1.6 (final) and it just works :)

Did all the fancy stored procedure stuff (are they evil or not?) and wow! it was fun!

Daren Thomas
Thursday, July 29, 2004

Sounds like a job for Visual FoxPro.

I wonder how well the current version plays with .NET? Probably badly. Sigh.

Les C
Thursday, July 29, 2004

Sqlite has a lot of limitations as a DBMS (eg you have one lock for the whole thing) but it can certainly work well in some cases.

If you need a professionally supported product, you could try:
  http://www.solidtech.com/products/data_mgmt.html
  http://www.simple-sw.com/index2.htm

disclaimer: I haven't looked at either in detail. I'm not affiliated with either company.

Matt Freestone
Thursday, July 29, 2004

MySQL can run in embedded mode... so can BerkeleyDB

PopCulture
Thursday, July 29, 2004

I'll second SQL Anywhere. It's a very powerful DBMS that supports plenty of features that you're used to:
user defined functions
triggers
stored procedures
views
java in DB
user-level (grant/revoke) security
etc.

The best part, though, is that it's fairly easy to replicate changes from the mobile device up to your main DBMS -- e.g. the pocket PC creates an order and automagically synchs it up to your main DB.

Honestly, if you can afford a solution like that I fail to see why many people choose products that *don't* have those -- because you end up having to reinvent the wheel in your application which is much more difficult/time consuming/error prone.

Captain McFly
Thursday, July 29, 2004

My vote for SQLite as well ... works phenomenally well for us under Windows and MacOS.

jedidjab79
Thursday, July 29, 2004

Thanks for the tip on SQLite!  I don't think it's quite what I'm looking for for this particular project, but I'll definately keep it in mind for the future.  What's up with the lack of strong typing though (or "manifest typing" in V3)?  I also wonder when they'll get support for enforcing foreign key constraints...

As for using Jet/Access...another good option, if the application didn't need to manage so much data.  But the 65,536 row limit problem would certainly rear its ugly head.

MySQL is a decent enough platform, but as far as I can tell the licensing mode includes per-seat royalties, which would be restrictive.  Not sure yet if SQL Anywhere does that too, need to call them and find out I guess.

So far I'm leaning towards SQL Anywhere UltraLite.  One of the req's for this app is that the DB file(s) be stored in an encrypted format -- SQL Anywhere supports AES, but I don't see much (if any) support for encryption in the other products that have been mentioned.  Does anyone know if Firebird/MySQL can do anything here?

The mobile sync stuff in Anywhere looks pretty cool too at a first glance.  Of course I still need to dig deeper - find out if it requires SQL Anywhere on the back-end (or can I use MS SQL?), what wire transports are available, etc.

Joe
Thursday, July 29, 2004

< But the 65,536 row limit problem would certainly rear its ugly head.>

You might be mixing jet up with an Excel spread sheet. I believe there is a 2 gb size limit on a jet db.

wannabe
Thursday, July 29, 2004

Not if Access == Jet...  Access is limited to the 65,536 row max per table.

Joe
Thursday, July 29, 2004

Then why does this code work fine with access 2000?

Public Sub test()
  Dim rs As DAO.Recordset
  Dim i As Long

  Set rs = CurrentDb.OpenRecordset("select * from a")
 
  For i = 0 To 70000
    rs.AddNew
    rs("a") = i
    rs.Update
  Next i

End Sub

Look at that...70,000 rows in a table!

Chris Donges
Friday, July 30, 2004

Huh...you're completely right...I wonder why I had that silly thought stuck in my head...*scratch scratch*.

OK...now just give me a way to encrypt the access database, and I'll be good to go :)

Joe
Friday, July 30, 2004

*  Recent Topics

*  Fog Creek Home