Hi Brian,
I've reproduced your problem with NH 2.1.0GA - the issue is that the
SQLAnywhere11Dialect code works properly with the CreateQuery API, but
it does not with the Criteria API. If I modify the HelloNHibernate
code to add a Criteria list of Employees, as so:
static void LoadEmployeesFromDatabase()
{
using (ISession session = OpenSession())
{
IQuery query = session.CreateQuery(
"from Employee as emp where name like ? order by
emp.name asc");
query.SetString(0, "%David%");
query.SetMaxResults(10);
query.SetFirstResult(4);
IList<Employee> foundEmployees;
foundEmployees = query.List<Employee>();
Console.WriteLine("\n{0} employees found:",
foundEmployees.Count);
foreach (Employee employee in foundEmployees)
Console.WriteLine(employee.SayHello());
Console.WriteLine("\n Using criteria query");
ICriteria crit =
session.CreateCriteria(typeof(Employee));
crit.AddOrder(Order.Asc("name"));
crit.SetMaxResults(10);
crit.SetFirstResult(4);
crit.Add(Expression.Like("name", "David",
MatchMode.Anywhere));
foundEmployees = crit.List<Employee>();
Console.WriteLine("\n{0} employees found:",
foundEmployees.Count);
foreach (Employee employee in foundEmployees)
Console.WriteLine(employee.SayHello());
}
}
The first query is generated correctly, reordering the parameters as
required:
NHibernate: SELECT TOP ? START AT ? employee0_.id as id0_,
employee0_.name as na
me0_, employee0_.manager as manager0_ from Employee employee0_ where
employee0_.
name like ? order by employee0_.name asc;p0 = 10, p1 = 4, p2 = '%David
%'
but the second Criteria query causes
Using criteria query
NHibernate: SELECT TOP ? START AT ? this_.id as id0_1_, this_.name as
name0_1_,
this_.manager as manager0_1_, employee2_.id as id0_0_, employee2_.name
as name0_
0_, employee2_.manager as manager0_0_ FROM Employee this_ left outer
join Employ
ee employee2_ on this_.manager=employee2_.id WHERE this_.name like ?
ORDER BY th
is_.name asc;p0 = %David%, p1 = 4, p2 = NULL
which, as you have seen, is completely erroneous. I wonder if Ayende
had thought of Criteria queries when he made his original fix
(NH-1528).
I will try to see what can be done; as far as I can tell the dialect
is establishing the parameters (and their order) correctly.
Glenn
On Mar 29, 3:17 pm, Brian Weeres <[email protected]> wrote:
> Pretty much anything that causes a parameter list to be generated.
>
> using (ISession session =
> NHibernateHelper.OpenSessionWithAutoFlush())
> {
> ICriteria crit =
> session.CreateCriteria(typeof(Organization));
> crit.SetMaxResults(500);
> crit.Add(Expression.Like("LegalName",
> searchCriteria.LegalName, MatchMode.Anywhere));
> List<Organization> orgs = new
> List<Organization>(crit.List<Organization>());
> }
>
> On Mon, Mar 29, 2010 at 1:26 PM, Glenn Paulley <[email protected]>wrote:
>
> > A code snippet from your application that illustrates your usage of
> > SetMaxResults() would be helpful, so that I can try to mimic that
> > usage in my testing.
>
> > Glenn
>
> > On Mar 29, 2:25 pm, Brian Weeres <[email protected]> wrote:
> > > Thanks Glenn. I will wait for your results. If you need me to test
> > anything
> > > let me know.
>
> > > On Mon, Mar 29, 2010 at 1:19 PM, Glenn Paulley <[email protected]
> > >wrote:
>
> > > > The most recently-posted SQLAnywhere11Dialect does support
> > > > SetMaxResults() correctly, at least in my testing with the NH 2.1.0 GA
> > > > release. I do not (yet) have NH 2.1.2 installed, but I am happy to do
> > > > so and try to reproduce the problem you're seeing.
>
> > > > Glenn
>
> > > > On Mar 29, 12:55 pm, brianw <[email protected]> wrote:
> > > > > The Sybase11Dialect error actually turned out to be that particular
> > > > > dialect creates outer joins that are no longer supported in SQL
> > > > > Anywhere 11.
>
> > > > > The other Sybase 11 dialect I tried is from herehttp://
> > > > iablog.sybase.com/paulley/2009/09/revised-sql-anywhere-nhibern....
> > > > > (SQLAnywhere11Dialect). This is the one that has the error with the
> > > > > SetMaxRowCounts.
>
> > > > > This may be the place I need to make changes but to me the changes
> > are
> > > > > not obvious without getting a whole lot deeper than I have the
> > > > > inclination to do. I am just trying to use this stuff not write
> > > > > nhibernate and dialects. As long as my dialect returns true for
> > > > > BindLimitParametersFirst, SupportsLimitOffset and
> > > > > SupportsVariableLimit which it does then this code in loader.cs will
> > > > > wipe out my limit parameter since the BindParameterValues call does
> > > > > not use the colIndex that is passed in.
>
> > > > > if (useLimit && dialect.BindLimitParametersFirst)
> > > > > {
> > > > > colIndex += BindLimitParameters(command, colIndex, selection,
> > > > > session);
>
> > > > > }
>
> > > > > colIndex += BindParameterValues(command, queryParameters, colIndex,
> > > > > session);
>
> > > > > I suppose in the SQLAnywhere11Dialect I have to either change
> > > > > GetLimitString or the other settings to get the correct combination.
> > I
> > > > > think at this point I will just leave my change in loader.cs in and
> > > > > worry about changing the dialect later.
>
> > > > > On Mar 29, 10:22 am, Fabio Maulo <[email protected]> wrote:
>
> > > > > > The place where change is the dialect and nothing more than the
> > > > dialect.
>
> > > > > > 2010/3/29 brianw <[email protected]>
>
> > > > > > > I am using Nhibernate 2.1.2 with Sybase 11. Using Sybase11Dialect
> > I
> > > > > > > get sql errors using SetMaxResults. The same code works fine with
> > > > > > > SybaseASA10Dialect. The Sybase11 dialect adds a parameter for the
> > TOP
> > > > > > > value whereas the SybaseASA10Dialect updates the sql directly
> > with
> > > > > > > Select TOP 500 starting at 1. The issues appears to be in
> > > > > > > PrepareQueryCommand in loader.cs. The count parameter is set
> > > > correctly
> > > > > > > in BindLimitParameters but then it gets wiped out in the next
> > call
> > > > > > > which is to BindParameterValues. BindParameterValues is getting
> > the
> > > > > > > value of 1 for the next parameter to bind which is correct but
> > the
> > > > > > > method itself calls queryParameters.BindParameters with a
> > hardcoded
> > > > > > > zero instead of the passed in starting parameter. This causes the
> > > > > > > first parameter which is the count to get set to whatever the
> > next
> > > > > > > parameter is supposed to be.
>
> > > > > > > queryParameters.BindParameters(statement, GetNamedParameterLocs,
> > 0,
> > > > > > > session);
> > > > > > > perhaps should be
> > > > > > > queryParameters.BindParameters(statement, GetNamedParameterLocs,
> > > > > > > startIndex, session);
>
> > > > > > > I am reluctant to make this change in my nhibernate code as I
> > really
> > > > > > > don't know the full impact of the change.
>
> > > > > > > To unsubscribe from this group, send email to
> > nhibernate-development+
> > > > > > > unsubscribegooglegroups.com or reply to this email with the
> > words
> > > > "REMOVE
> > > > > > > ME" as the subject.
>
> > > > > > --
> > > > > > Fabio Maulo- Hide quoted text -
>
> > > > > > - Show quoted text -
>
> > > > To unsubscribe from this group, send email to nhibernate-development+
> > > > unsubscribegooglegroups.com or reply to this email with the words
> > "REMOVE
> > > > ME" as the subject.
>
> > To unsubscribe from this group, send email to nhibernate-development+
> > unsubscribegooglegroups.com or reply to this email with the words "REMOVE
> > ME" as the subject.
To unsubscribe from this group, send email to
nhibernate-development+unsubscribegooglegroups.com or reply to this email with
the words "REMOVE ME" as the subject.
