Haidi,
I made some changes to Jetdriver to run Nhibernate 3 LINQ trunk
tests. I changed a few things in Jetdriver to pass the tests and
create test database without errors with minimum change in LINQ Trunk
Tests. I just changed the column name of "Output" to "Out_Put" because
I think "Output" is an Access reserved word. I will try to describe
the problems that I found.
Jetdriver:
I registered the a few SQL functions translating to JET Syntax:
concat, length, substring, cast, iif , date.
Datatypes:
I changed
DbType.Int64 to INT and not real. I made this to avoid
changes in LINQ hbm files that create database column with this type
and make relations, current implementation of Jetdrivre use REAL and
the database is not created.
DbType.Decimal to FLOAT. I think jet has a bug with Decimal
type when calling SQL functions like AVG (average). Throws an ADO.NET
exception with Decimal and works fine with double.
DbType.Boolean to BYTE and use 0=false , 1=true. Some queries
in LINQ hardcode 1 to represent "true" and Access needs -1. I prefer
to use SQL Server convention. Maybe LINQ bug.
DbType.DateTime: I do not use Jetdriver option to change Dates
to Strings,I force datatype to OleDbType.Date
( ((OleDbParameter)p).OleDbType = OleDbType.Date;) and date queries
work fine, at least for my usage.
I use UseNamedPrefixInSql to true to use named parameters
(p0,p1) , but this change affect the function FinalizeJoins that does
not works well with parameters of Nhibernate SqlString. I changed
this function to restore the parameters parts of SqlString. Maybe
this can be a temporary fix if Nhibernate change LINQ to accept "?" as
parameters again.
I think there are some issue that are Nhibernate LINQ bugs:
I think Nhibernate is hardcoding some things in the SQL generated and
not using database driver.
- Using 1 as boolean true.
- SQL expression "Case When" and not calling JetCaseFragment.
- SQL function "extract from"
There is also a problem with the SQL function locate, I do not know
how to implement.
I tried to register the function as the example below, but does not
work if ?1,?2 and ?3 are all database query parameters
(OleDbParameters).
RegisterFunction("locate",new
SQLFunctionTemplate(NHibernateUtil.Int32, "(instr(?3+1,?2,?1)-1)"));
I solved these issues making some little dirty hacks changing SQL
string directly and works for basic cases.
After making some changes only two minor tests are not working with
Access. The function IndexOf , String.IndexOf("A",3), with two
parameters and String.Replace function. I think Access does not
support this function using ADO.NET
If someone is interested I can publish the Jet driver that I made,
but I tried to describe the difficulties that I have found.
Ricardo
On May 30, 2:40 am, Hadi Eskandari <h.eskand...@gmail.com> wrote:
> Hello Ricardo,
>
> Thanks for the patch. I'll take a deeper into it and see what I can do. Is
> there any other scenario where LINQ query is not working using Jet driver?
>
> Hadi
>
> On Sat, May 29, 2010 at 7:30 AM, Ricardo <rs...@usa.com> wrote:
> > I am trying to use Jetdriver and new version of Linq, but it is not
> > working. I made a test below using the Foo class of Jetdriver unit
> > tests to make some queries with strings. I think LINQ driver of has
> > some problems and is not finished, but I think these queries work with
> > SQL Server driver.
>
> > The new LINQ create some SQL queries like the examples below that do
> > not work with Access.
>
> > LINQ
> > s.Query<Foo>().Where(f => f.Module == "bar");
>
> > SQL
> > NHibernate: select foo0_.ModuleId as ModuleId4_, foo0_.`Module` as
> > Module2_4_, foo0_.ModuleValue as ModuleVa3_4_, foo0_.ShortName as
> > ShortName4_, foo0_.LongName as LongName4_, foo0_.Description as
> > Descript6_4_ from Foo foo0_ where (foo0_.`Module` is null) and (? is
> > null) or foo0_.`Module`=?;@p0 = 'bar' [Type: String (3)]
>
> > LINQ
> > var q = s.Query<Foo>().Where(f => f.Module.Contains("a"));
>
> > SQL
> > NHibernate: select foo0_.ModuleId as ModuleId4_, foo0_.`Module` as
> > Module2_4_, foo0_.ModuleValue as ModuleVa3_4_, foo0_.ShortName as
> > ShortName4_, foo0_.LongName as LongName4_, foo0_.Description as
> > Descript6_4_ from Foo foo0_ where foo0_.`Module` like
> > ('%'||?||'%');@p0 = 'a' [Type: String (1)]
>
> > I made some workarounds , force true to UseNamedPrefixInSql to use
> > named parameters instead of "?" in the JetDriver class and register
> > some functions in JetDialect class
>
> > RegisterFunction("concat", new
> > VarArgsSQLFunction(NHibernateUtil.String, "(", "+", ")"));
> > RegisterFunction("length", new StandardSQLFunction("len",
> > NHibernateUtil.Int32));
> > RegisterFunction("substring", new
> > SQLFunctionTemplate(NHibernateUtil.String, "mid(?1, ?2+1, ?3)"));
> > RegisterFunction("locate", new
> > SQLFunctionTemplate(NHibernateUtil.Int32, "(instr(?3+1,?2,?1)-1)"));
>
> > [Test]
> > public void can_execute_string_functions()
> > {
> > object savedId;
> > using (var s = SessionFactory.OpenSession())
> > {
> > using (var t = s.BeginTransaction())
> > {
> > savedId = s.Save(new Foo
> > {
> > ModuleId = -1,
> > Module = "bar"
> > });
>
> > t.Commit();
> > }
>
> > using (var t = s.BeginTransaction())
> > {
>
> > var q = s.Query<Foo>().Where(f => f.Module ==
> > "bar");
> > var list = q.ToList();
>
> > q = s.Query<Foo>().Where(f => f.Module.Length>1);
> > list = q.ToList();
>
> > q = s.Query<Foo>().Where(f =>
> > f.Module.StartsWith("b"));
> > list = q.ToList();
>
> > q = s.Query<Foo>().Where(f =>
> > f.Module.EndsWith("r"));
> > list = q.ToList();
>
> > q = s.Query<Foo>().Where(f =>
> > f.Module.Contains("a"));
> > list = q.ToList();
>
> > q = s.Query<Foo>().Where(f =>
> > f.Module.ToUpper()=="BAR");
> > list = q.ToList();
>
> > q = s.Query<Foo>().Where(f => f.Module.ToLower()
> > == "bar");
> > list = q.ToList();
>
> > q = s.Query<Foo>().Where(f =>
> > f.Module.Substring(1, 1) == "bar".Substring(1, 1));
> > list = q.ToList();
>
> > q = s.Query<Foo>().Where(f =>
> > f.Module.IndexOf("r") == "bar".IndexOf("r"));
> > list = q.ToList();
>
> > q = s.Query<Foo>().Where(f => f.Module==null);
> > list = q.ToList();
>
> > q = s.Query<Foo>().Where(f => null==f.Module);
> > list = q.ToList();
>
> > t.Commit();
> > }
> > }
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "NHibernate Contrib - Development Group" group.
> > To post to this group, send email to nhcd...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > nhcdevs+unsubscr...@googlegroups.com<nhcdevs%2bunsubscr...@googlegroups.com>
> > .
> > For more options, visit this group at
> >http://groups.google.com/group/nhcdevs?hl=en.
--
You received this message because you are subscribed to the Google Groups
"NHibernate Contrib - Development Group" group.
To post to this group, send email to nhcd...@googlegroups.com.
To unsubscribe from this group, send email to
nhcdevs+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/nhcdevs?hl=en.
