nhibernate / nhibernate.jetdriver Goto Github PK
View Code? Open in Web Editor NEWJet Driver for NHibernate
License: GNU Lesser General Public License v2.1
Jet Driver for NHibernate
License: GNU Lesser General Public License v2.1
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)
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.
Mark Junker added a comment - 25/Oct/11 10:31 PM
Fixes problems with the created Switch statements (multiple and duplicate WHEN parts)
Mark Junker added a comment - 25/Oct/11 10:34 PM
Use the original SQL string as cache key
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 convertsCAST(x AS INT)
toCInt(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
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 addRegisterFunction("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.
Mark Junker created issue - 21/Oct/11 12:23 AM
Access requires named parameters in SQL statements too.
Attachments:
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.