SQL CE Alternatives

[Guest]

We've had enough with SQL CE!
It's data corruption problem is just too much
for us and our customers to bear.

We are looking for mobile database alternatives.

At this moment we are evaluating SQL Anywhere UltraLite, Oracle 9i Lite and
DB2 Everyplace.

Has anyone used any of these products?

Thanks in advance

 

[Jamie Macleod]

I'm using SQLite with the .Net data provider

http://sourceforge.net/projects/adodotnetsqlite/

It is a very simple database engine but seems to work okay on the PocketPC

Jamie

 

[Beri Tamas]

Hello,

I've used Ultralite, a bit smaller and faster than SQLCE, more simply to
use - IMHO. But, it has several bugs of course, if you use a lot of tables,
indexes, complex database structure (as far as I have this problems, maybe
this bugs already fixed in version 9.0). Try to design your mobile database
as simply as possible.

You will face additional problems in the "replication", Ultralite using the
terminology "synchronization". It is _not_ similar ms merge replication at
all .

Let me know if you have any further questions on Ultralite.

Regards,
Tamas Beri

 

[chris-s]

I'm surprised to hear of you data corruption problems. We have been
using SQLCE heavily for the last 18 months or so on around 200 devices
and the only corruptions we got were when we used SD cards to store the
'working' databases on, we appeared to be exceeding the lifetime-writes
of the card in around 6 weeks. Since we moved the 'working' database to
the 'My Documents' folder no more problems.

Would you care to elaborate a bit on your corruptions experience?

Chris

 

[Jon Brunson]

I'm surprised to hear of you data corruption problems. We have been
using SQLCE heavily for the last 18 months or so on around 200 devices
and the only corruptions we got were when we used SD cards to store the
'working' databases on, we appeared to be exceeding the lifetime-writes
of the card in around 6 weeks. Since we moved the 'working' database to
the 'My Documents' folder no more problems.

Would you care to elaborate a bit on your corruptions experience?

Chris

SIX WEEKS! Whoa. That's worrying. We've got a project on the go atm and
we really have to use external (ie, none-volitile) memory to store our
database, but if they're going to "kill" the card after 6 weeks, or even
6 months, this is not acceptable. Nor is moving the data to My Docs, as
if the device is hard-reset (a likely situtaion) the data is gone.

Is this problem solvable?

 

[Andrea]

Consider using DB4O as an alternative gpl oo database (java, net and net
cf)...
http://www.db4o.com

i'm using it on a flash card... for the moment, simply don't know anythink
about corruptions ... use is far less intensive of your examples...
but the database is good.... and the approach beautiful ...

Best regards,
A.

 

[Guest]

Well, we actually do store the database on the storage card but I don't
believe that
the corruption has anything to do with the lifetime writes of the card.

It does not explain why the card is still usable when I delete and recreate
the database. It seems to happen randomly and it allways has the same form:
NULL values at non nullable columns - even at primary key fields!

I have seen corrupt databases on brand new machines (and storage cards)
after 3 day's work. I have also had users that have been using the
application with no problems for more than 6 months!

I have confirmed with customers that older applications they used that
stored SQL CE database files in ram have also had corruption problems
(although not as frequent).

I have been writing large scale database applications (SQL Server and
Oracle) for quite some time and I believe I am not misusing (or abusing) the
SQL CE engine. I am compacting and repairing the database (in ram) on a daily
basis, I am keeping my transactions as short as possible etc etc.

However, I will try the RAM installation just to make sure that I am not
doing this product wrong.

Thanks for answering!

 

[Guest]

Thanks Beri!

We are currently experimenting whith version 9.0.2
and it seems very promising.

DB2 Everyplace has also some very advanced features but
we haven't had the time to look into it yet.

We don't care about replication because we have developed our own
mechanism, which also happens to be much much faster than RDA
on narrowband connections (eg. GPRS).

Actually, there are quite a lot of questions that I would like to
ask you but I'll try digging into the documentation a little bit more first.

Here's a couple of them:

1) Does UltraLite support ACID Transactions?
2) Have you tested it on storage cards?
3) Does it release unused space automatically? Is it necessary
to so manually (see SQL CE compact and repair). ?

Thank you for your time.

 

[Syed N. Yousuf [MS]]

SQL CE 2.0 Books Online mentions that "If SQL Server CE is terminated when
data is being written (as a result of power failure or a full disk), the
data can become corrupted."

Are your users resetting the device(s) when the app is writing data ?
Have you tried using a transaction ?

You can access SQL Server CE Knowledge Base articles at:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;KBHOWTO
( select ' SQL Server CE ' as the product )

Thank you!
Syed N. Yousuf
Microsoft Developer Support Professional

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[j.edwards]

I'm using SQLite with the .Net data provider

I've just had a quick look at this and want to give it a go. However,
there only seems to be classes for getting data out of a db. E.g.
something that I can do ...

Dim Engine As New System.Data.SqlServerCe.SqlCeEngine("Data
Source=" + Filename)
Engine.CreateDatabase()
Engine.Dispose()

I need to be able to dynamically create dbs on the pocket pc end, but
other than that the switch from SQLServerCe looks fairly painless. I
haven't had any corruption issues, but the speed has been a bit
unbearable, so I'm hoping SQLite is faster.

 

[Adam Tatusko, MCSD, MCAD, MCDBA, MCSE, MCSA]

Many devices, such as the Dell Axim X30, have a "Built-in Storage" area
of memory that is not cleared after a hard reset. It's 32 MB in size on
the Dell Axim X30. That may solve the data corruption after excessive
rewrite problem with an SD Card database implementation.

 

[Jamie Macleod]

Hi j.edwards,

You create db's by setting New=True in the connection string. You create
your tables using SQL.
Something like this
SQLiteConnection conn = new SQLiteConnection();

SQLiteCommand cmd = new SQLiteCommand();

// create new database

conn.ConnectionString = "Data
Source="+App.appDir+"\\"+newName+".db;New=True;Compress=True;Version=3;Synchronous=Off";

conn.Open();

// create card table

cmd = conn.CreateCommand();

cmd.CommandText = "CREATE TABLE Test "+

"(testId INTEGER PRIMARY KEY, " +

" testCol1 VARCHAR(300) NOT NULL, "+

" testCol2l INTEGER NOT NULL, "+

" testCol3 DATETIME NOT NULL)";

cmd.ExecuteNonQuery();

cmd = conn.CreateCommand();


Jamie

 

[Beri Tamas]

Hello,

I'll try to answer your questions:
1.) Actually, I do not know, what "ACID Transaction" means, sorry. I've
used Ultralite 8.0, the transaction handling was automatic on the client,
I've just called Committrans() throught the API (after a inserts, updates,
deletes).
2.) No. We are using the storage cards for backup purposes only.
3.) Ultralite has a built-in defragmenter, you can call it through the API,
which is a quite similar to SQLCE's Compact() method (but you do not need
temporaly file, it's automatic).

Hope it helps, regards:
Tams Beri

 

[Boris Nienke]

At this moment we are evaluating SQL Anywhere UltraLite, Oracle 9i Lite and
DB2 Everyplace.

BTW: you don't need UltraLite - on the PocketPC you can use the
SQL-Anywhere database-files 1:1 as on the desktop. It works. Of course you
should have a simple database design to keep the size small.
I havn't tried it by myself but in our company we have a solution that
works like that.

If you like to try something other than SQL you could take a look at the
object-database "db4o" (http://www.db4o.com)
They have a newsgroup too for support

Boris

 

[Donny Ray via DotNetMonster.com]

I have had the same problem with corrupted DBs using SQLCE. It not only happens if you get a PDA reset. If anything causes the program to hang in the middle of a write, or sometimes even a read, the DB is subject to getting corrupted. And there have been a high number of incidences when this occured as the result of system errors that were no fault of my own code. Since then I have managed to figure out how to setup and use data adapters to handle all of my DB transactions. It adds a lot more code that I have to write since there is no wizard for the adapters as in the full framework. However, since making that change I haven't had a SQLCE database get corrupt once.

 

[j.edwards]

Jamie, thanks for that - works great. Had some trouble with testing in
the emulator - maybe it just doesn't work - but is fine on actual
device. For the system I'm working on the comparison is:

db size: 260kb (SQLServer) 40kb (SQLite)

The footprint is only around 300kb for version 3 (240kb + 60kb .net
wrapper) compared to a couple of MB for SQL Server. Plus you don't have
the deployment issues that come with SQL Server.

Speed is at worst equivilent to SQL Server. I was expecting it to be a
fair bit faster but it seems fairly comparable - haven't done any
specific tests, just judging the by the perceived speed while using the
app.

Overall, I'm impressed.

 

[Jamie Macleod]

j.edwards,

You need the sqlite that has been compiled for the emulator (X86).

Peter Bromberg has already compiled it, so just download it at the
following location. You can also use eMbedded C++ to compile the source
yourself.
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=720

Jamie

 

[j.edwards]

Hey Jamie, do you know if SQLite is supposed to run on ppc2002? I've
done some searching but everything seems to indicate it only works on
ppc2003. I tried to compile it using eVC++ 3 for ppc2002 but it still
doesn't work. I haven't tried to look through the source to find out
what could be causing it yet, but it must be using something that isn't
backwards compatible.

 

[Jamie Macleod]

Sorry, you got me there. I only have it on the PPC2003

 

[Jamie Macleod]

Hi J.Edwards,

Just noticed that SharpHSQL has been ported to the CF. I ran some simple
tests and it seems to work. If this works it is ideal since it is written
in .Net and thus will run on any PocketPC that supports the CF. It comes in
around 190kb. Not sure about the size of the database is though.

Jamie