go ahead and create one 2010/4/13 Richard Brown (gmail) <[email protected]>
> On 30/03/2010 17:01, Fabio Maulo wrote: > > Thanks. > I will add a JIRA ticket with your patch before add the new dialect > > 2010/3/30 Glenn Paulley <[email protected]> > >> Fabio: >> >> You can download the SQLAnywhere11Dialect source code from >> >> >> http://iablog.sybase.com/paulley/wp-content/uploads/2009/09/sqlanywhere11dialect_nh210.zip >> >> Glenn >> >> On Mar 30, 7:56 am, Fabio Maulo <[email protected]> wrote: >> > Which are values of properties of >> > BindLimitParametersFirst >> > BindLimitParametersInReverseOrder >> > SupportsLimit >> > SupportsLimitOffset >> > SupportsVariableLimit >> > UseMaxForLimit >> > >> > and impls of >> > GetLimitString(SqlString querySqlString, int offset, int limit) >> > GetLimitString(SqlString querySqlString, bool hasOffset) >> > >> > of the dialect you are using ? >> > >> > 2010/3/30 Glenn Paulley <[email protected]> >> > >> > > At first glance, it would appear that Brian's diagnosis is correct - >> > > that this code in Loader.cs is incorrect: >> > >> > > 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); >> > > } >> > >> > > Changing the above call to queryParameters.BindParameters to >> > >> > > return >> > > queryParameters.BindParameters(statement, >> > > GetNamedParameterLocs, startIndex, session); >> > >> > > appears to fix the issue, and permits HQL and Criteria requests to >> > > work correctly with pagination performed by a dialect that supports >> > > hostvars for SetFirstResult() and SetMaxResults(). >> > >> > > On Mar 29, 10:22 pm, Fabio Maulo <[email protected]> wrote: >> > > > If it work with HQL and does not work with Criteria then there is a >> bug >> > > in >> > > > some place else than the dialect. >> > > > Thanks Glenn. >> > >> > > > 2010/3/29 Glenn Paulley <[email protected]> >> > >> > > > > 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, >> > >> > ... >> > >> > read more ยป >> >> 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 > > 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. > > > Was there ever a JIRA created, either for the bug? Or for the new Dialect? > > (Just wanted to check before I create one.) > > -- Fabio Maulo -- To unsubscribe, reply using "remove me" as the subject.
