Hi again Brian.
Yes. In Loader.cs, I changed
protected virtual int BindParameterValues(IDbCommand statement,
QueryParameters queryParameters,
int
startIndex,
ISessionImplementor session)
{
// NH Different behavior:
// The responsibility of parameter binding was entirely
moved to
QueryParameters
// to deal with positionslParameter+NamedParameter
+ParameterOfFilters
// return queryParameters.BindParameters(statement,
GetNamedParameterLocs, 0, session);
return queryParameters.BindParameters(statement,
GetNamedParameterLocs, startIndex, session);
}
and now my test output is (correctly)
......
Updated Tobin and added Pierre Henri
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
%'
NHibernate: SELECT employee0_.id as id0_1_, employee0_.name as
name0_1_, employe
e0_.manager as manager0_1_, employee1_.id as id0_0_, employee1_.name
as name0_0_
, employee1_.manager as manager0_0_ FROM Employee employee0_ left
outer join Emp
loyee employee1_ on employee0_.manager=employee1_.id WHERE
employee0_.id=?;p0 =
10
NHibernate: SELECT employee0_.id as id0_1_, employee0_.name as
name0_1_, employe
e0_.manager as manager0_1_, employee1_.id as id0_0_, employee1_.name
as name0_0_
, employee1_.manager as manager0_0_ FROM Employee employee0_ left
outer join Emp
loyee employee1_ on employee0_.manager=employee1_.id WHERE
employee0_.id=?;p0 =
12
NHibernate: SELECT employee0_.id as id0_1_, employee0_.name as
name0_1_, employe
e0_.manager as manager0_1_, employee1_.id as id0_0_, employee1_.name
as name0_0_
, employee1_.manager as manager0_0_ FROM Employee employee0_ left
outer join Emp
loyee employee1_ on employee0_.manager=employee1_.id WHERE
employee0_.id=?;p0 =
14
NHibernate: SELECT employee0_.id as id0_1_, employee0_.name as
name0_1_, employe
e0_.manager as manager0_1_, employee1_.id as id0_0_, employee1_.name
as name0_0_
, employee1_.manager as manager0_0_ FROM Employee employee0_ left
outer join Emp
loyee employee1_ on employee0_.manager=employee1_.id WHERE
employee0_.id=?;p0 =
2
4 employees found:
'Hello World!', said Tobin David Harris.
'Hello World!', said Tobin David Harris.
'Hello World!', said Tobin David Harris.
'Hello World!', said Tobin David Harris.
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 = 10, p1 = 4, p2 = '%David%'
4 employees found:
'Hello World!', said Tobin David Harris.
'Hello World!', said Tobin David Harris.
'Hello World!', said Tobin David Harris.
'Hello World!', said Tobin David Harris.
Press any key to exit...
So it would appear that the code in Loader.cs is in error, as you had
surmised.
Glenn
On Mar 29, 8:28 pm, Brian Weeres <[email protected]> wrote:
> Just curious if they both work if you change line 1267 in Loader.cs to pass
> along the index value instead of 0? (Method BindParameterValues.)
>
> On Mon, Mar 29, 2010 at 4:13 PM, Glenn Paulley <[email protected]>wrote:
>
> > 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.
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.
