How to use AR on access 2003?

[Carl Xu]

Castle AR did not suport access2003 directly,
How to change it?


best regards
carl xu

[Ayende Rahien]

You need to use the JetDriver from NHibernate

[Ken Egozi]

it's in a separate dll - NHibernate.JetDriver.dll

then just adjust the config and you're on.

expect some wackyness as Jet is not exactly ANSI-SQL compliant so sometime weird shit happens.
I won't expect schema creation to work.
complex joins might fail too as Jet syntax for joins is weird.


I'm using it for a client's website, running on a .mdb file (that was the client's request and I keep bugging him to move to SQL Express ...), for over a year without any major problems, but one that I fixed, patched, and it was applied on NH long ago.

--
Ken Egozi.
http://www.kenegozi.com/blog
http://www.musicglue.com
http://www.castleproject.org
http://www.mamaherb.com
http://www.gotfriends.co.il

[Jonathon Rossi]

If you are looking for an embedded database you should take a look at SQL Server Compact (http://www.microsoft.com/sql/editions/compact/default.mspx) of SQLite, as an alternative to MS Access.

The nice thing about SQL Server Compact is that you can edit it with SQL Server Management Studio Express like you do for SQL Server Express. It also allows you to move up from Compact to Express much easier, if you need to some day.

--
Jonathon Rossi

[Hamilton Verissimo]

Jonathon, long time no see!

--
Cheers,
hamilton verissimo
ham...@castlestronghold.com
http://www.castlestronghold.com/

[Ayende Rahien]

Does use SQL CE.

It breaks apart if you have multi threaded access to it.

[Jonathon Rossi]

Yer, I have been pretty silent lately because work is keeping me pretty busy. I haven't been doing any work with castle lately, however, I still read the devel and users lists to keep up with everything.

I am starting a project at work that is not part of our core product, that I plan to use Windsor and ActiveRecord so I'm sure you'll hear from me more the in near future.

--
Jonathon Rossi

[Jonathon Rossi]

From my experiences with Access many years ago it wasn't very good multi-threaded either. SQLite also has problems with being used from multiple threads. I can't imagine SQL Server CE being worse than Access for threading :)

--
Jonathon Rossi

[Ayende Rahien]

Access sucks for multi threading.
I tested SQLite for heavy multi threaded access, it showed the same behavior as SQL Server. That is, it worked correctly.

SQL CE caused errors and deadlocks in the application.

[Darius Damalakas]

For a full-feature embedded database, there's also FireBird, which can
run in embedded mode
http://www.codeproject.com/KB/database/EmbeddedFirebird.aspx

2008/5/31 Jonathon Rossi <jo...@jonorossi.com>:

--
Darius Damalakas

[Ken Egozi]

from my experience with Firebird - I didn't like it, at least - not the .NET components for accessing it, I guess that from Delphi and othe Borland stuff it'd be smoother.

It was missing all kinds of ANSI SQL stuff, and working with CLOBs was a nightmare.

Now - Access is (imo) better than SQL CE in terms of capabilities. I know more than a few web apps that manage their life with it.
never experienced deadlockes, however, there is alway the chance for corrupted mdb, sometimes under heavy loads it breaks.
so - it's very important to keep backups for the db file.

the pain is with the strange SQL (no CASE statements, JOINs behaviour is limited, etc).
and with the poor DDL which makes upgrading live DB a PITA

[Carl Xu]

thanks~I know little about SQLite,I'll read something about it

if i use sqlite on a virtual host,does it need extra support(for
example,we should setup the sqlite software on host)?like access?

if i import NHibernate.JetDriver.dll,how to deploy it in web.config?


thanks

[Ken Egozi]

for access, just replace the driver and dialect names
            <add key="connection.driver_class" value="NHibernate.JetDriver.JetDriver, NHibernate.JetDriver" />
            <add key="dialect"                 value="NHibernate.JetDriver.JetDialect, NHibernate.JetDriver" />

there's a page in the docs with <activerecord> snippets for many drivers