The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Compact Edition or Firebird Embedded ?

I need to choose an embeded database for my Windows desktop application programmed in C# .NET.

I have tried both SQL Server Compact Edition 3.5 and FireBird Embedded 2.0.1 and both seem to work. I have no experience with either of them, so I ask here. Which one to choose?

The app will be run only on a local Windows machine all over the World. Typical database size can be about 250 000 new rows per year with data storage for about 5 years. The rows in the tables will be pretty simple, I have no big SQL experience anyway.

Thank you for suggestions,
Petr
Petr Veit
Wednesday, March 19, 2008
 
 
If you aren't familiar with it, consider SQLite. It's a remarkable product and a quick google search shows lots of advice for using it with .net. Don't let the "lite" part of the name fool you -- it is an extremely robust, full featured, mature, zero-config, serverless transactional database.

And it's in the public domain. That's right, no license at all. Remarkable.
Just another voice in the crowd
Wednesday, March 19, 2008
 
 
Firebird and SSCE differ significantly in capabilities. Do you need views? Only Firebird supports them. Stored procs? Again, only Firebird.

This is not a situation where the two products have feature parity. Choose the one with the features you need.
Matt.
Wednesday, March 19, 2008
 
 
I use derby (formally JAVA DB) myself, it scales up really well.
Richard Corsale Send private email
Wednesday, March 19, 2008
 
 
Matt: I've never used stored procedures or views, so I think I don't need them.

It seems that SQL CE is better supported directly in Visual Studio. I am just fighting with Firebird to be able to see my database in Server Explorer in VS.
Petr Veit
Wednesday, March 19, 2008
 
 
The biggest reason I went with Firebird over SSCE is the option to switch from embedded to a multi-user setup with Firebird. SSCE is single-user (embedded) only.
Mark Pearce Send private email
Wednesday, March 19, 2008
 
 
MYSQL also has an available embedded db.  Just to confuse the decision even further.

http://www.mysql.com/oem/

Wednesday, March 19, 2008
 
 
++1 for SQLite which I've recently started using. Lots of support and very easy to use. I was going to use Firebird but limits like max 64K column size and storing blob's outside the DB or somesuch put me off. Also the documentation seems dated.

It is nice to use something small and lightweight and non-proprietary.
Neville Franks Send private email
Wednesday, March 19, 2008
 
 
We use Firebird Server edition for a product and it isn't bad. But as Neville alluded to there doesn't seem to be as much movement on the product recently. We've been using it for probably five years and its velocity seems to be slowing considerably. But maybe it's just me. Also, we've run into some bugs that I considered to be unacceptable at the time. Stuff that never should have gotten through QA.

But overall it isn't bad. I'd personally opt for something else next time though. There are a lot of other options that are probably better.
dood mcdoogle
Wednesday, March 19, 2008
 
 
Features of SQL92 that SQLite doesn't support:
http://www.sqlite.org/omitted.html
Mark Pearce Send private email
Wednesday, March 19, 2008
 
 
"Firebird and SSCE differ significantly in capabilities. Do you need views? Only Firebird supports them. Stored procs? Again, only Firebird."

True, but SQL Server Express Edition, which is also free, has both of those things.
Kyralessa Send private email
Wednesday, March 19, 2008
 
 
Petr,

In addition to the other comments, an advantage of Firebird is that is can run on various operating systems. For you embeded application that may not matter, but should it ever grow and you want to develop something that is not embedded this gives you extra flexibility.

The suggestion that you investigate the feature set of these products is an important one. Stored Procedures are so useful I can't imagine making a project without them. Firebird also adheres well to SQL standards which means you do not risk getting tied to proprietary features or behaviours.

Don't be surprised that non-Microsoft products are not quite as integrated in VS as the Microsoft products. That is another advantage of Firebird. It works with Microsoft languages/products but also with others like Java.

--
Geoff Wass
Geoff Wass Send private email
Thursday, March 20, 2008
 
 
Geoff: thanks for your comment!

Another advantage of Firebird is that it runs on Windows 98, while SQLce only on Windows 2000 and up. My app should be in .NET 2.0, which AFAIK can run on Windows 98.

The only feature that I like on SQLce is the VS integration. I have installed the Firebird DDEX for Visual Studio, but the integration is just not as good as the SQLce (I can't see data in tables in Server Explorer etc.).

On the other hand, the only thing I don't like on Firebird is that I can't find any free tool where I could see data in tables in Unicode. I've tried IBExpert and IBEasy+, but both have no Unicode support. IBExpert has some Unicode Edit box for strings, but this is not usable very well. With SQLce, I can display the table directly in VS. Other than that, Unicode (UTF8) support looks to be working well in Firebird.

Also, it is interesting that an empty DB in SQLce has about 20kB, while the same DB in Firefox has 600kB. This is not important anyway.

Petr
Petr Veit
Thursday, March 20, 2008
 
 
"Features of SQL92 that SQLite doesn't support:
http://www.sqlite.org/omitted.html "

Yikes! Some of those (like not being able to drop columns or alter contraints) are real deal breakers in my opinion.

Thanks for the link Mark.
dood mcdoogle
Thursday, March 20, 2008
 
 
you can try flamerobin it works very well with unicode
in fact it is designed with unicode type of users from the beginnings

http://flamerobin.org

you can compare sql express with firebird but there are new
features addded in firebird 2.1 and the next release 2.5

http://www.firebirdsql.org/dotnetfirebird/blog/2005/02/embedded-firebird-and-microsoft-sql.html

firebird project is very active , you can follow the mailing list and you can see from the download stats on sourceforge

http://sourceforge.net/project/stats/detail.php?group_id=9028&ugn=firebird&mode=alltime&&type=prdownload

yes documentation is our main priority and is work in progress so it will be at the same level compared with postgresql or mysql (you can donate time or money to this subproject)

http://www.firebirdsql.org/?op=doc

firebird is the best in the embedded arena and more

http://www.firebirdnews.org/?p=1445
Popa Marius Adrian Send private email
Thursday, March 20, 2008
 
 
"firebird project is very active , you can follow the mailing list and you can see from the download stats on sourceforge "

Sorry Popa. You can see from looking at the Flamerobin project page that your signature links to that Firebird's "activeness" is in serious question:

27.03.2006 - FlameRobin whitepaper from the Firebird Conference 2005

18.03.2008 - A new release is out. (version 0.8.6)

That's over two years to create a simple Firebird admin application. And the current version is still considered "Beta" as far as I can tell based on the version number. I've tried it a couple of times over the past two years and have always had to dump it because it lacked even basic functionality and had way too many bugs.

As for the Firebird server itself, the story seems to be much the same. A long time between minor releases with not a lot of new functionality being added.

But maybe that's just the way I interpret it. Don't get me wrong. We've been using Firebird internally for around five years now and it has been fine. But in that time development has seemed to be slowing down considerably. I only hope that I'm wrong.
dood mcdoogle
Thursday, March 20, 2008
 
 
FlameRobin is not the integral part of Firebird project. The reason for it's slow development are that there are so many good free (and commercial) administration tools out there. At least on Windows plaform. FlameRobin was created to fill in the Linux and Mac OS side of the story. Contrary to what you might say, it is a full featured tool - but its user interface is not what you might expect coming from a Windows world. Compare FlameRobin concept with some other mainstream Linux program like Gimp: your comments about FlameRobin look very similar to what Photoshop users have to say about Gimp. Still, most Linux users agree that Gimp is a high quality application.

So, if you're really a Windows power user, consider Firebird with IBExpert Personal Edition - this might change your mind completely, as IBE is on par with MS Enterprise Studio, if not better in some areas.

Development of Firebird has the same pace from the start of project in 2000, so I wouldn't say it is slowing down. It could be faster, but instead of "doesn't work", maybe you would care to tell us what doesn't work for you. I've been using Firebird for few years, and didn't find it lacks any features.
Firebird is growing, not slowing down Send private email
Thursday, March 20, 2008
 
 
"FlameRobin is not the integral part of Firebird project."

That's not an excuse. The problem is that it SHOULD be an integral part of the project. A product that has active development AND active marketing would have identified that deficiency and worked to fix the problem much sooner. That is part of the problem. You can't have developers running a product or else things that are important to real users never get done (like tools and documentation for example).

And as for the look of Flamerobin that doesn't bother me. When I tried Flamerobin a year ago it would hardly even run let alone do anything valuable. And now it is still in some beta state as far as I can tell. By the way, we do use IBExpert. But you can't use the personal edition for business use so we are having to license tools that are typically free with most databases. Again, it's not that big of a deal but I do think that it at least speaks to the mindset of the Firebird project.

"It could be faster, but instead of "doesn't work", maybe you would care to tell us what doesn't work for you."

I never said that anything "didn't work" for me. Quite the contrary.

Don't get your panties in a bunch just because someone gives you a little criticism. Overall I like the product but it appears to me that there are many other alternatives that are moving forward at greater speed. When we chose Firebird originally it appeared to have good momentum. My PERSONAL OPINION is that that isn't the case anymore. You are free to your own personal opinions.
dood mcdoogle
Thursday, March 20, 2008
 
 
usually is better to not feed the trolls

i guess if you really want an full firebird package you can do it

put firebird + flamerobin in 7-zip installer and announce it

yes flamerobin is an basic tool no an swiss army knife and is good because is not bloated (just KISS) it may be included in the installer but this is an foundation decision
http://www.firebirdsql.org/index.php?op=ffoundation
by the way you can vote for this if you want (an request must be made)

another thing : if you look at mysql you will see that there is only mysql server download and then the separate tools
in oss world people want simple tools that do one thing and do it good , they don't like monolithic monsters (sendmail/xfree)

gmail is in beta for many years and nobody complains about it,is simpler than outlook , now tell me what is better and how many users use both
Popa Marius Adrian Send private email
Friday, March 21, 2008
 
 
"usually is better to not feed the trolls "

You're right. I should stop feeding you now. ;)

I don't know why you can't take a little constructive criticism. Clearly you are too close to the situation. I have only stated my opinion.

For what it's worth, I downloaded Flamerobin yesterday and gave it a quick spin. I quickly found that it was going to be unusable for me. When adding a new row to a table you get a big dialog popup. The dialog does not have a scroll bar so if you have more than about 10 columns in your table you can't edit them all and the bottom ones obscure the "insert/cancel" buttons. It's ugly and unusable to say the least. But the rest of the product seemed fine. But again, I have to use some third party tool that I pay for in order to do simple database administration. Why is that?

As for packaging that is just semantics. I don't care if it is a separate download. What I care about is whether or not it is endorsed and developed by the Firebird team and whether or not it will ever get done to the point of being usuable in my situation. I have the very common need of being able to edit data in tables. The tech support staff in another state needs to be able to log in to a production database oat a customer location ccasionally and edit table data as well. Right now we have to purchase two site licenses of IBExpert in order to do something that should be a standard offering. If it is a separate download then so be it. I don't care.

My point wasn't to rant about the lack of basic admin tools for Firebird. I was originally just stating that in my opinion there isn't much velocity there. Flamerobin was just an example of that belief.
dood mcdoogle
Friday, March 21, 2008
 
 
I'd go with Firebird, it's a full featured database, it can grow from embedded to full server without much ado.
Flamerobin is a basic admin tool which does well and it's cross platform.
Firebird is also evolving with medium pace, i'd not say it's the fastest but definitely not abandonware!!!
You can get good support from mailing lists and forums, certainly the docs suck if you compare to MySQL or PostgreSQL.
I've put up a few blog posts about it's new features and using Flamerobin as a gui for embedded firebird which might be useful to you.

Friday, March 21, 2008
 
 
dood mcdoogle,
you should open a bug report about your problems with Flamerobin, I've found the development team quite responsive to my past requests (or consider funding your feature requests with an amount equivalent to frontend licenses paid).

Friday, March 21, 2008
 
 
Well, Firebird is going forward at higher velocity than ever before. The 2.1 release is around the corner (RC2 now), which would translate to 4 major version releases in 8 years since beginning of the project (IMHO very good pace for product like enterprise-grade database server), with 2.5 scheduled for end of this year. And don't get fooled by conservative stance of FB developers to version numbering, 2.1 is loaded with significant new stuff, just read the Release Notes for 2.1.
Pavel Cisar
Friday, March 21, 2008
 
 
I've just tried FlameRobin and it looks very good to me. Simple to use and its best feature is Unicode support. I use only the basics with databases, so it fits to my need very well.

Is there any other tool with Unicode support?

Petr
Petr Veit
Saturday, March 22, 2008
 
 
Petr, did you try adding a new row to a table? That's the biggest problem that I saw. Everything else is usable. It has definitely come a long way. I tried it when it first came out and then again about a year ago. My biggest issue though is that it has taken this long to get to a point where I still can't really use it (but it's almost there).
dood mcdoogle
Saturday, March 22, 2008
 
 
dood: Yes, I have just tried it and adding a new row works for me. What do you think doesn't work? I use FB 2.0.1 (embedded for my app, but normal server for FlameRobin).
Petr Veit
Saturday, March 22, 2008
 
 
What happens when I try to add a new row to a table is a dialog box pops up with all of the columns in the table listed vertically. If you have more columns in the table than can fit on the dialog there is no scroll bar and the column input fields obscure the insert/cancel buttons. Are you seeing that as well?

After a very cursory look at the product that is the only thing that would keep us from being able to at least use it for the technical support team. The development group might would want to stick with IBExpert as it appears to be more feature rich. But the tech support team has a need to be able to log in to production databases at customer sites and having a small free tool to use for simple stuff like that would be very handy. Right now we license IBExpert with site licenses but I have a feeling the tech support group is installing copies on customer machines to do their work. And I'm not sure that they are uninstalling it when they are done.
dood mcdoogle
Saturday, March 22, 2008
 
 
dood: you are right, now I tried it with 30 columns and there is really no access to the fields on the bottom and there is no scroll bar.

This could be maybe easy to fix? Other than that it seems to be very simple tool.
Petr Veit
Sunday, March 23, 2008
 
 
I found it listed as an open bug here: http://sourceforge.net/tracker/index.php?func=detail&aid=1863261&group_id=124340&atid=699234

Let's hope somebody will fix it soon. There are several bugs repaired in last months, so it looks that the development is still running.
Petr Veit
Sunday, March 23, 2008
 
 
Yes, I'm sure that it is a fairly easy fix. I'm just surprised that it got out into the wild that way. I'm also not sure that popping up a dialog is the best user interface approach to this problem. Most products let you edit new rows inline in the same grid that you use to edit existing rows. Maybe this dialog interface is a short term decision given that the product still appears to be in beta. Regardless, this is a deal breaker for me right now. But I'll come back to it in another six months and re-evaluate it.

Good luck.
dood mcdoogle
Monday, March 24, 2008
 
 
Documentation is the biggest issue I see with Firebird.

What "Enterprise-Level" RDBMS comes with Documentation from the year 2000 (in 2008) and a Release Note text file.

The is "The Firebird Book" for version 1.5 (mostly applicable to 2.0) and I've actually red it in B&N and it isn't that good compared to the books that I read about PostgreSQL and MySQL.  It's not even as thorough as those products' online documentations or help files.

I would have thought after 6+ years they would have switched from the "download the Interbase 6.0 PDFs, they are still mostly applicable" stance to "we have Firebird 2.xxx Documentation at *random URL*"...

3rd party tools and documentation is always welcome.  People use them even for products that come with excellent tools and documentation (like MSSQL Server, for example).  But in the year 2008 people expect a product to be fairly self-sufficient and come with at least the basics.  Every other Open Source Database comes with bare minimums (Product, Interface, Documentation) except Firebird.

And some of the tools mentioned don't even work on Linux, and the RPMs from Firebird's website failed to install correctly on Red Hat Enterprise Linux for me anyways, so I just opted to play with the PostgreSQL that it came with...
Nathaniel Walker Send private email
Tuesday, March 25, 2008
 
 
"And some of the tools mentioned don't even work on Linux, and the RPMs from Firebird's website failed to install correctly on Red Hat Enterprise Linux for me anyways, so I just opted to play with the PostgreSQL that it came with... "

Thanks for that info. We just signed our first Linux customer and we were just wondering how well Firebird would run on Linux. They have opted to use MySQL instead anyway but given your information I don't think that we will even bother trying to install Firebird on Linux at this time.
dood mcdoogle
Tuesday, March 25, 2008
 
 
dood,

Try a Google search on IBConsole. It's written by one of the Borland (now CodeGear) TeamB guys. It's actually designed for Interbase, but the latest version (designed for Interbase 2007) works fine for Firebird. It's free, too.

(I'd post the URL, but I forgot where I got it - if someone has to search Google, it might as well be you. <g>)
Ken White Send private email
Thursday, March 27, 2008
 
 
There are one more options, which I suggest you to consider.

http://www.scimore.com

* free & fast & unrestricted
* .net data provider
* t-sql support
* free text search
* mature
* possiblity to switch to standalone server and distributed.

A quick get started sample here:
http://www.scimore.com/products/tutorial/Embedded-Get-Started-in-3-minutes/

When you download and install, a couple of samples follow. making it easy to getting started.

Regards,
Scimore Team
Scimore Send private email
Tuesday, April 08, 2008
 
 
dood mcdoogle > [SQLite] Yikes! Some of those (like not being able to drop columns or alter contraints) are real deal breakers in my opinion.

For the odd case when you need to change a table schema afterwards, just create an in-memory, temporary table in which to dump data, delete the original table, re-create it with the right columns, and insert data from the temporary table:

http://sqlite.org/faq.html#q11
ZeFred
Wednesday, April 09, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz