Thanks for looking into it Glenn. As per Richard's request I can certainly raise a JIRA once I figure out what that is. :-)
On Mon, Mar 29, 2010 at 8:31 PM, Glenn Paulley <[email protected]>wrote: > 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. > 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.
