Query substitution shouldn't change ANYTHING on the criteria query.
The idea behind query substitution is being able to write DB-agnostic HQL.
That way, you can write something like "... where MyBoolProp = true" and
have that true LITERAL (not string) translated to 1, 'T', or whatever the DB
uses to represent a true value.
Test:
Declare a Foo class with a string property named Data.
Execute this criteria:
session.CreateCriteria<Foo>().Add(Restrictions.Eq("Data", "true") ||
Restrictions.Eq("Data", "12.3")).List();
This is the resulting SQL using MsSql 2008:
SELECT
this_.Id as Id0_0_,
this_.[Data] as Data2_0_0_
FROM
Foo this_
WHERE
(
this_.[Data] = @p0
or this_.[Data] = @p1
);
@p0 = 'true' [Type: String (4000)], @p1 = '12.3' [Type: String (4000)]
Diego
On Tue, Aug 31, 2010 at 13:20, jcomet <[email protected]> wrote:
> I thought the query substitution would substitute 1 for the boolean
> true. It should not change the string "true" to anything - right?
>
> On Aug 31, 12:17 pm, cliff vaughn <[email protected]> wrote:
> > I think there are 2 separate issues. The query substitution should
> > explain the first where he was using the value "true" and it was being
> > turned into 1. The second issue is that NH seems to be looking at the
> > value and deciding to quote it or not based on its type instead of the
> > type of the column.
> >
> > On Tue, Aug 31, 2010 at 12:07 PM, Diego Mijelshon
> >
> >
> >
> >
> >
> > <[email protected]> wrote:
> > > That does 4 specific conversions in HQL queries.
> > > It doesn't convert arbitrary strings into numbers in any query method.
> >
> > > Diego
> >
> > > On Tue, Aug 31, 2010 at 13:04, cliff vaughn <[email protected]>
> > > wrote:
> >
> > >> sure it will if you have the default:
> >
> > >> <property name="query.substitutions">
> > >> true 1, false 0, yes 'Y', no 'N'
> > >> </property>
> >
> > >> On Tue, Aug 31, 2010 at 12:00 PM, Diego Mijelshon
> > >> <[email protected]> wrote:
> > >> > Query substitutions won't turn strings into numers.
> > >> > There's something else going on.
> >
> > >> > Diego
> >
> > >> > On Tue, Aug 31, 2010 at 12:58, cliff vaughn <
> [email protected]>
> > >> > wrote:
> >
> > >> >> have you seen my last message. Look at hibernate.cfg.xml and the
> > >> >> query.substitutions element.
> >
> > >> >> On Tue, Aug 31, 2010 at 11:49 AM, jcomet <[email protected]>
> > >> >> wrote:
> > >> >> > It isn't just booleans, either:
> >
> > >> >> > IList<Employee> emps = _EmployeeDAO.GetSession()
> > >> >> > .CreateCriteria<Employee>()
> > >> >> > .Add(Restrictions.Eq("FirstName","23.56"))
> > >> >> > .List<Employee>();
> >
> > >> >> > yields
> >
> > >> >> > SELECT this_.ID as ID10_0_,
> > >> >> > this_.LastName as LastName10_0_,
> > >> >> > this_.FirstName as FirstName10_0_,
> > >> >> > FROM Employee this_
> > >> >> > WHERE this_.FirstName = 23.56 /* @p0 */
> >
> > >> >> > yikes!
> >
> > >> >> > On Aug 31, 11:41 am, jcomet <[email protected]> wrote:
> > >> >> >> I have created some new tests using NH 3, and I consistently get
> the
> > >> >> >> same behavior. When adding a restriction to a string property,
> NH
> > >> >> >> converts the string to a bit/int if it can. For example, this
> > >> >> >> query:
> >
> > >> >> >> IList<Employee> emps = _EmployeeDAO.GetSession()
> > >> >> >> .CreateCriteria<Employee>()
> > >> >> >> .Add(Restrictions.Eq("FirstName","true"))
> > >> >> >> .List<Employee>();
> >
> > >> >> >> Results in this:
> >
> > >> >> >> SELECT this_.ID as ID10_0_,
> > >> >> >> this_.LastName as LastName10_0_,
> > >> >> >> this_.FirstName as FirstName10_0_,
> > >> >> >> FROM Employee this_
> > >> >> >> WHERE this_.FirstName = 1 /* @p0 */
> >
> > >> >> >> On Aug 31, 11:30 am, jcomet <[email protected]> wrote:
> >
> > >> >> >> > I get the same behavior in NH 3.
> >
> > >> >> >> > On Aug 31, 11:06 am, Diego Mijelshon <[email protected]>
> > >> >> >> > wrote:
> >
> > >> >> >> > > I couldn't reproduce that in NH 3. Can you check with it?
> >
> > >> >> >> > > Diego
> >
> > >> >> >> > > On Tue, Aug 31, 2010 at 11:45, jcomet <
> [email protected]>
> > >> >> >> > > wrote:
> > >> >> >> > > > When add a restriction on a particular string property
> with a
> > >> >> >> > > > value
> > >> >> >> > > > of "true" or "false", NH (2.1.2.4000) changes it to a bit
> > >> >> >> > > > value.
> > >> >> >> > > > See
> > >> >> >> > > > example class, hbm, query and resulting SQL. Note in the
> last
> > >> >> >> > > > snip,
> > >> >> >> > > > the line "WHERE this_.SettingValue = 1" should read "WHERE
> > >> >> >> > > > this_.SettingValue = 'true'. If I change the restriction
> > >> >> >> > > > from
> > >> >> >> > > > "true"
> > >> >> >> > > > to "poop", the NH correctly surrounds poop with quotes.
> My
> > >> >> >> > > > conclusion
> > >> >> >> > > > is that NH tries to convert the string value to boolean,
> and
> > >> >> >> > > > if
> > >> >> >> > > > it
> > >> >> >> > > > can, it does so using the value substitution from the
> session
> > >> >> >> > > > factory
> > >> >> >> > > > configuration. This is curious, since NH knows that
> column is
> > >> >> >> > > > a
> > >> >> >> > > > string from the mapping, so why would it need to guess?
> Is
> > >> >> >> > > > there a
> > >> >> >> > > > way to inform NH not to do this?
> >
> > >> >> >> > > > namespace MYAPP.Core.Domain
> > >> >> >> > > > {
> > >> >> >> > > > public class EmployeeSetting
> > >> >> >> > > > {
> > >> >> >> > > > public virtual int ID { get; set; }
> > >> >> >> > > > public virtual string SettingValue { get; set; }
> > >> >> >> > > > public virtual Employee Employee { get; set; }
> > >> >> >> > > > public virtual SettingType SettingType { get; set;
> }
> > >> >> >> > > > }
> > >> >> >> > > > }
> >
> > >> >> >> > > > <?xml version="1.0" encoding="utf-8" ?>
> > >> >> >> > > > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
> > >> >> >> > > > assembly="MYAPP.Core" namespace="MYAPP.Core.Domain">
> > >> >> >> > > > <class
> name="MYAPP.Core.Domain.EmployeeSetting,MYAPP.Core"
> > >> >> >> > > > table="EmployeeSetting">
> > >> >> >> > > > <id name="ID" column="ID" type="Int32"
> unsaved-value="0">
> > >> >> >> > > > <generator class="native" />
> > >> >> >> > > > </id>
> > >> >> >> > > > <property name="SettingType" column="SettingType"
> > >> >> >> > > > type="MYAPP.Core.Domain.SettingType, MYAPP.Core"
> > >> >> >> > > > not-null="false"
> > >> >> >> > > > />
> > >> >> >> > > > <property name="SettingValue" column="SettingValue"
> > >> >> >> > > > type="string"
> > >> >> >> > > > not-null="false" />
> > >> >> >> > > > <many-to-one name="Employee" column="EmployeeID"
> > >> >> >> > > > class="MYAPP.Core.Domain.Employee,MYAPP.Core.Domain"/>
> > >> >> >> > > > </class>
> > >> >> >> > > > </hibernate-mapping>
> >
> > >> >> >> > > > IList<EmployeeSetting> es =
> _EmployeeSettingDAO.GetSession()
> > >> >> >> > > > .CreateCriteria<EmployeeSetting>()
> > >> >> >> > > > .Add(Restrictions.Eq("SettingValue",
> "true"))
> > >> >> >> > > > .Add(Restrictions.Eq("SettingType",
> > >> >> >> > > > SettingType.ReceiveAlerts))
> > >> >> >> > > > .List<EmployeeSetting>();
> >
> > >> >> >> > > > SELECT this_.ID as ID8_0_,
> > >> >> >> > > > this_.SettingType as SettingID8_0_,
> > >> >> >> > > > this_.SettingValue as SettingV3_8_0_,
> > >> >> >> > > > this_.EmployeeID as EmployeeID8_0_
> > >> >> >> > > > FROM EmployeeSetting this_
> > >> >> >> > > > WHERE this_.SettingValue = 1 /* @p0 */
> > >> >> >> > > > and this_.SettingType = 1 /* @p1 */
> >
> > >> >> >> > > > --
> > >> >> >> > > > You received this message because you are subscribed to
> the
> > >> >> >> > > > Google Groups
> > >> >> >> > > > "nhusers" group.
> > >> >> >> > > > To post to this group, send email to
> [email protected].
> > >> >> >> > > > To unsubscribe from this group, send email to
> >
> > >> >> >> > > > [email protected]<nhusers%[email protected]>
> <nhusers%[email protected]<nhusers%[email protected]>
> >
> > >> >> >> > > > .
> > >> >> >> > > > For more options, visit this group at
> > >> >> >> > > >
> http://groups.google.com/group/nhusers?hl=en.-Hidequotedtext -
> >
> > >> >> >> > > - Show quoted text -- Hide quoted text -
> >
> > >> >> >> > - Show quoted text -- Hide quoted text -
> >
> > >> >> >> - Show quoted text -
> >
> > >> >> > --
> > >> >> > You received this message because you are subscribed to the
> Google
> > >> >> > Groups "nhusers" group.
> > >> >> > To post to this group, send email to [email protected].
> > >> >> > To unsubscribe from this group, send email to
> > >> >> > [email protected]<nhusers%[email protected]>
> .
> > >> >> > For more options, visit this group at
> > >> >> >http://groups.google.com/group/nhusers?hl=en.
> >
> > >> >> --
> > >> >> thanks
> >
> > >> >> cliff
> >
> > >> >> --
> > >> >> You received this message because you are subscribed to the Google
> > >> >> Groups
> > >> >> "nhusers" group.
> > >> >> To post to this group, send email to [email protected].
> > >> >> To unsubscribe from this group, send email to
> > >> >> [email protected]<nhusers%[email protected]>
> .
> > >> >> For more options, visit this group at
> > >> >>http://groups.google.com/group/nhusers?hl=en.
> >
> > >> > --
> > >> > You received this message because you are subscribed to the Google
> > >> > Groups
> > >> > "nhusers" group.
> > >> > To post to this group, send email to [email protected].
> > >> > To unsubscribe from this group, send email to
> > >> > [email protected]<nhusers%[email protected]>
> .
> > >> > For more options, visit this group at
> > >> >http://groups.google.com/group/nhusers?hl=en.
> >
> > >> --
> > >> thanks
> >
> > >> cliff
> >
> > >> --
> > >> You received this message because you are subscribed to the Google
> Groups
> > >> "nhusers" group.
> > >> To post to this group, send email to [email protected].
> > >> To unsubscribe from this group, send email to
> > >> [email protected]<nhusers%[email protected]>
> .
> > >> For more options, visit this group at
> > >>http://groups.google.com/group/nhusers?hl=en.
> >
> > > --
> > > You received this message because you are subscribed to the Google
> Groups
> > > "nhusers" group.
> > > To post to this group, send email to [email protected].
> > > To unsubscribe from this group, send email to
> > > [email protected]<nhusers%[email protected]>
> .
> > > For more options, visit this group at
> > >http://groups.google.com/group/nhusers?hl=en.
> >
> > --
> > thanks
> >
> > cliff- Hide quoted text -
> >
> > - Show quoted text -
>
> --
> You received this message because you are subscribed to the Google Groups
> "nhusers" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected]<nhusers%[email protected]>
> .
> For more options, visit this group at
> http://groups.google.com/group/nhusers?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
