nhibernate / nhibernate.jetdriver Goto Github PK

When connected to access, doing linq queries over boolean properties translates into 0 and 1, while access uses 0 and -1 (or false and true)

Mark Junker created issue - 21/Oct/11 12:25 AM

The first patch simplifies the SQL transformation function.

The second patch has the following effect:
Change all (case when ... then ... else ... end) expressions into something like:
IIf(IsNull(Switch(when_1, then_1, ... when_n, then_n)), else, Switch(when_1, then_1, ... when_n, then_n))
or (when no "else" part exists):
Switch(when_1, then_1, ... when_n, then_n)

• nhcontrib-hg_1657.patch.zip
• nhcontrib-hg_1658.patch.zip

Mark Junker added a comment - 24/Oct/11 9:07 PM

This patch moves the CASE conversion to the top of the function. It also renames the function to something more suitable.

• nhcontrib-hg_1659.patch.zip

Mark Junker added a comment - 25/Oct/11 10:31 PM

Fixes problems with the created Switch statements (multiple and duplicate WHEN parts)

• NHibernate.JetDriver_51.patch.zip
• NHibernate.JetDriver_52.patch.zip

Mark Junker added a comment - 25/Oct/11 10:34 PM

Use the original SQL string as cache key

• NHibernate.JetDriver_46.patch.zip

erik konijnenburg created issue - 25/Jan/12 9:19 AM

When a HQL query contain a condition that is mapped to a boolean value the generate SQL query is incorrect. For example
"from Account where locked = true" is translated into "select ....... from Acount where locked=1". When using access it should be "..... where locked=-1". It only fails with with the ASTQueryTranslatorFactory. The ClassicQueryTranslatorFactory works as expected.

Fix is trivial. Add override for ToBooleanValueString to JetDialect that creates the correct values for the SQL statement.

/// <summary> The SQL literal value to which this database maps boolean values. </summary>
/// <param name="value">The boolean value </param>
/// <returns> The appropriate SQL literal. </returns>
public override string ToBooleanValueString(bool value)
{
	return value ? "-1" : "0";
}

Mark Junker created issue - 21/Oct/11 12:20 AM

Access doesn't know the CAST() expression. We have to use the VBA convert functions instead.
This patch converts CAST(x AS INT) to CInt(x). Other types are supported as well.

Sergio Moreno added a comment - 08/Nov/11 12:13 AM

The path 1653 doesn't generates the JetCastFunction.cs file
It's said rejected patch when using tortoise. I have created it manually and copied the code inside the patch.
Thanks for your work

Attachments

• nhcontrib-hg_1653.patch.zip
• nhcontrib-hg_1654.patch.zip

Thomas Kalmar created issue - 22/Jul/09 12:00 PM

the JetDialect has some strange ways to try to extract the year(month, day) part of an datetime:

crit.Add(Restrictions.Eq(Projections.SqlFunction("Year", NHibernateUtil.DateTime, Projections.Property("lastChanged")), filter.LastChanged.Year));

adds some "and (extract year from ?)" to the sql. This does not work in M$ Access.
To fix this add

RegisterFunction("year", new SQLFunctionTemplate(NHibernateUtil.DateTime, "year(?1)"));
RegisterFunction("month", new SQLFunctionTemplate(NHibernateUtil.DateTime, "month(?1)"));
RegisterFunction("day", new SQLFunctionTemplate(NHibernateUtil.DateTime, "day(?1)"));

to the JetDialect
After that all worked like a charm

Thomas Kalmar added a comment - 17/Sep/09 10:13 PM

Silly me. Should be:

RegisterFunction("year", new SQLFunctionTemplate(NHibernateUtil.Int32, "year(?1)"));
RegisterFunction("month", new SQLFunctionTemplate(NHibernateUtil.Int32, "month(?1)"));
RegisterFunction("day", new SQLFunctionTemplate(NHibernateUtil.Int32, "day(?1)"));

Martin Gämperle created issue - 20/Sep/10 1:28 PM

Class hierarchy: 'B1' and 'B2' inherit from 'BBase'. 'A' has a list of 'B1's and a list of 'B2's as properties.

Mapping-file of A:

<class name="A" table="A" dynamic-update="true" lazy="false">
  <id name="SID" type="Int32" column="SID" unsaved-value="0">
    <generator class="hilo">
      <param name="max_lo">100</param>
    </generator>
  </id>
  <bag name="B1s" inverse="true" lazy="true" cascade="all-delete-orphan" batch-size="50">
    <key column="FK_A_SID"/>
    <one-to-many class="B1"/>
  </bag>
  <bag name="B2s" inverse="true" lazy="true" ascade="all-delete-orphan" batch-size="50">
    <key column="FK_A_SID"/>
    <one-to-many class="B2"/>
  </bag>
</class>

Mapping-file of BBase:

<class name="BBase" table="BBASE" discriminator-value="-1" dynamic-update="true" lazy="false">
  <id name="SID" type="Int32" column="SID" unsaved-value="0">
    <generator class="hilo">
    <param name="max_lo">100</param>
    </generator>
  </id>
  <discriminator column="TYPE" type="Int32" force="true"/>
  <many-to-one name="A" column="FK_A_SID" class="A" not-null="true"/>

  <!-- B1 -->
  <subclass name="B1" discriminator-value="0" lazy="false">
    ...
  </subclass>

  <!-- B2 -->
  <subclass name="B2" discriminator-value="1" lazy="false">
    ...
  </subclass>
</class>

When 'session.Refresh(A)' is called it produces an SQL-string that looks like this:
SELECT a.SID as ..., ..., b1s.FK_A_SID as ..., b1s.SID as ..., ... FROM A a LEFT OUTER JOIN BBASE b1s ON a.SID=b1s.FK_A_SID AND b1s.TYPE=0 WHERE a.SID=?

This SQL-string leads to an error. When I manually switch the 'AND' and 'WHERE' statements then it works:
SELECT a.SID as ..., ..., b1s.FK_A_SID as ..., b1s.SID as ..., ... FROM A a LEFT OUTER JOIN BBASE b1s ON a.SID=b1s.FK_A_SID WHERE a.SID=? AND b1s.TYPE=0

Remark: This happens only for the first bag in 'A's mapping-file.

Mark Junker added a comment - 21/Oct/11 8:17 PM

Using additional conditions in the JOIN .. ON .. clause is common practice in modern RDBMS and the SQL would execute just fine in those environments. I will take a look at this problem when I get to the point where I need sublcasses too (which may take some months).

Hello all,

I am using JetDriver 2.0.0.1001 for MsAccess on Net 4.5 and with NHibernate 3.3.3.4001
I need some help to clarify why the following SQL text is post-processed incorrectly by JetDriver's ConnectionProvider.Driver.GenerateCommand(). I think it is related to the formula property I have, without it all is ok.

<property name="CutCount" type="System.Int32" formula="(SELECT COUNT(CuttingPart.ID) FROM CuttingPart WHERE CuttingPart.PartID = ID)"/>

NHibernate generated SQL passed to AbstractBatcher.Generate method (correct)

SELECT 
	this_.ID as ID42_4_, 
	this_.Title as Title42_4_, 
	this_.Length as Length42_4_, 
        (SELECT COUNT(CuttingPart.ID) FROM CuttingPart WHERE CuttingPart.PartID = this_.ID) as formula1_4_, 
        o2_.ID as ID40_0_, 
        o2_.Title as Title40_0_, 
        m1_.ID as ID16_1_, 
        m1_.Title as Title16_1_, 
        pc3_.PartID as PartID6_, 
        pc3_.ID as ID6_, 
        pcv4_.ContourID as ContourID7_, 
        pcv4_.ID as ID7_, 
        pcv4_.ID as ID50_3_, 
        pcv4_.[Type] as column2_50_3_, 
        pcv4_.XM as XM50_3_, 
        pcv4_.YM as YM50_3_, 
        pcv4_.Radius as Radius50_3_, 
FROM Part this_ 
        left outer join AppOrder o2_ on this_.AppOrderID=o2_.ID 
        left outer join Material m1_ on this_.MaterialID=m1_.ID 
        left outer join Contour pc3_ on this_.ID=pc3_.PartID 
        left outer join ContourValue pcv4_ on pc3_.ID=pcv4_.ContourID 
WHERE 
        this_.IsArchived = 0 
ORDER BY pcv4_.PointIndex asc

Jet Driver result after its _factory.ConnectionProvider.Driver.GenerateCommand

SELECT 
	this_.ID as ID42_4_, 
	this_.Title as Title42_4_, 
	this_.Length as Length42_4_, 
	(SELECT COUNT(CuttingPart.ID) from CuttingPart WHERE CuttingPart.PartID = this_.ID) as formula1_4_,
	o2_.ID as ID40_0_, 
	o2_.Title as Title40_0_, 
	m1_.ID as ID16_1_, 
	m1_.Title as Title16_1_, 
	pc3_.PartID as PartID6_, 
	pc3_.ID as ID6_, 
	pcv4_.ContourID as ContourID7_, 
	pcv4_.ID as ID7_, 
	pcv4_.ID as ID50_3_, 
	pcv4_.[Type] as column2_50_3_, 
	pcv4_.XM as XM50_3_, 
	pcv4_.YM as YM50_3_, 
	pcv4_.Radius as Radius50_3_,
        -- from here I have incorrect SQL text
        (select * from  
	((( pcv4_.ContourID as ContourID50_3_ FROM Part this_
	left outer join AppOrder o2_ on this_.AppOrderID=o2_.ID) 
	left outer join Material m1_ on this_.MaterialID=m1_.ID) 
	left outer join Contour pc3_ on this_.ID=pc3_.PartID) 
	left outer join ContourValue pcv4_ on pc3_.ID=pcv4_.ContourID 

WHERE 
	this_.IsArchived = 0) as jetJoinAlias29 

ORDER BY pcv4_.PointIndex asc

Is it a known problem ? Could you please give me a hint about a possible workaround if any? Thanks a lot.

Mark Junker created issue - 24/Oct/11 9:09 PM

This patch adds support for a variable limit by manually replacing the parameter value in ExpandQueryParameters. This change is required for Linq support because Take() requires variable limits.

• nhcontrib-hg_1660.patch.zip

Mark Junker created issue - 21/Oct/11 12:23 AM

Access requires named parameters in SQL statements too.

Attachments:

• nhcontrib-hg_1656.patch.zip