Hey,

I am starting this thread to discuss all the issues and their solutions regarding using NHibernate with Jet 4.0 databases.

As probably most of you know who ever tried to use NHibernate with Jet that this is doable through the OleDbDriver and setting a bunch of configuration options. Also, there are a few differences between the MsSql7 dialect and Jet's SQL dialect. Stone Well has already contributed an msaccess2000dialect.cs file and also an msaccess2000joinfragment.cs file here. The dialect is rather outdated and is missing a bunch of Register* calls that the other dialects in 0.8.4 have. It is perfect as a starting point though.

The huge problem with Jet is its fucked up JOIN syntax. The contributed Join Fragment implementation can only produce inner joins as it implements Jet's alternative SQL syntax (theta-style inner joins). This works for multiple inner joins too. The problem is that left joins and right joins are also treated as inner joins. 'inner join' and 'left join' ends up with the same SQL which is not very smart.

The problem is that there is no alternative syntax for (left and right) outer joins in Jet and all we could do is implement its nested syntax. The implementation is not trivial because NHibernate treats each join fragment as totally separate part of the full SQL query. In Jet's case, we would want to know all the joins before we start figuring out the right SQL statement so that we can nest them and open the right amount of parenthesis.

A slightly modified ANSIJoinFragment will work with both inner and outer joins as long as there is only one join in the HQL query (as there is no need for nesting / parenthesis in that case). As soon as there are two or more joins, it won't work, Jet will come back with an error although NHibernate will treat the HQL as correct.

My problem in my project is that I would need two left joins in one query to do something like:



Because SQL for both join statements are generated totally separate and there's no state information, there is no way to figure out how to end up with the correct SQL query which would be:

I hope I got that right :) Also, there is an alternative way, nesting the JOINs the other way around:

Both syntaxes are just fine but need some kind of a state-aware SQL generation.

I will post anything I find. Please contribute your ideas or maybe your existing solutions to this problem. It would be nice to bring NHibernate / Jet 4.0 to a point where most of HQL is covered and this feature is a must. A lot of people use Jet databases in production, it's not that bad as people treat it, except the stupid JOIN syntax. I would break the guy's hand who came up with that. :twisted: 8)

I am posting the so-called Theta-inner join syntax that Jet also supports. Oracle uses this (or at least used to use) this syntax but they added the (+) modifier that could be added to an operand in the WHERE clause. This way outer and full joins can be expressed with this syntax in Oracle. This doesn't work in Jet.

If my last example used only INNER JOINs, a correct syntax would be:

Of course, as I previously said the problem with this is that for instance, in the case of a 1..* relationship there is a difference between saying


and


as in the first case, I would not get back those parent objects that have no children, although I want all my parents and their children (if any) fetched in one query.

Another issue that needs to be adressed is the fact that Access wants any outer joins before the inner joins. I don't think NHibernate does add the join fragments in any particular order, so this might be hard to solve.

I didn't even know that. Wouldn't it follow the order of the HQL we pass in? Of course, if that is generated based on mappings (outer join attributes in the XML mapping maybe) then it might be a problem. Is this not the case with other databases? It sort of makes sense to have the outer joins first, no?

Other databases don't seem to have the limitation of needing outer joins before inner joins.
I've experienced the issue with Access when I was also trying to create an Access dialect and some tests failed because of this.

Did you get anywhere with the outer join syntax? I am in need of an implementation of JetJoinFragment that can do multiple outer joins in Jet's syntax. I am afraid it is fairly hard to fit this into NHibernate's architecture. At least, it's not doable just by only implementing a JetJoinFragment class, because multiple fragments are created for each join. I wish AddJoin would be called multiple times on the same JoinFragment, then I could do it.

No, unfortunately I didn't get anywhere with it. If you want to add a join fragment, it must be nested in the last join fragment that is in the query. I managed to do this with some extreme dirty string manipulation but then there was still the issue with the opening parentheses that have to be in front of the join fragments. I couldn't find any way to accomplish that, so I gave up, also because there where lots of other issues with running all unit tests.

Well, I've got the latest source tree from CVS now and running all the tests on a local MSDE2000 with nant. All succeed of course right now. I will put my changes and also see how Access performs with all the tests. All I want for now is of course the right JOIN syntax. I will post my results as soon as I get something useful.

Anybody else? :)

MsSql7 dialect and Jet's SQL dialect. Stone Well has already contributed an msaccess2000dialect.cs file and also an msaccess2000joinfragment.cs file here. The dialect is rather outdated and is missing a bunch of Register* calls that the other dialects in 0.8.4 have. It is perfect as a starting point though.
---------------------------------------------------------------------

I've took this files and made them 0.8.4 compatible.
Shall I place them somewhere?
If anybody is interested let me know...

Well, I fed up with the Jet/Nhibernate combination a long time ago and started using SQLite as a lightweight, file based portable database. It's a bummer i don't have MsAccess to open it up or anything that compares to it but shit just works and that's what matters.

I would still love to use Jet but it seems it really does have a lot of limitations. Now I wish there was a GUI for SQLite. If not, I might as well just write one ;)

(Although there's an ADO.NET implementation for SQLite)

RGabo

*joins thread a little late*

Actually the JOINS in access aren't (quite) as bad as people seem to think. For example, this is perfectly legit:



This will return parents with or without children. Further, to do multiple joins in a statement, you just nest with brackets:



To contradict martijnb - as far as I can tell, it doesn't matter what order the joins go in so long as they're nested in brackets.

So, if my knowledge of NHibernate and in particular JoinFragment was better (sorry, I only started it last week), I'd be thinking that this wouldn't be too tricky to whip up, would it?

I'd love to see a working solution, if anyone can do it.

In Access help, there's a note that INNER JOINS cannot be nested in LEFT or RIGHT joins. Ie. this should not work:

On the other hand, one should not need this kind of joins when translating from HQL.
IMO this

has the same meaning and can be safely rewritten as:

where join means (left | right | inner) join..


Actually I ended up having to slightly modify the NHibernate itself to accomplish this kind of transformation.

Can you contribute the changes you have done? It could be included in NHibernate :wink:

It took me a while to get back to the issue.. :) I have uploaded the Jet Dialect to JIRA. In the end it was not neccessary to modify any existing code in NHibernate...

Thanks :)

Many people will be happy to see that MS Access is now well supported.