Thanks.
I will add a JIRA ticket with your patch before add the new dialect
2010/3/30 Glenn Paulley <[email protected]
<mailto:[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]
<mailto:[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]
<mailto:[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]
<mailto:[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]
<mailto:[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 <http://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]
<mailto:[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] <mailto:[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]
<mailto:[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] <mailto:[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]
<mailto:[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..
<http://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] <mailto:[email protected]>>
> > wrote:
>
> > > > > > > > > > The place where change is the dialect and
nothing more than
> > the
> > > > > > > > dialect.
>
> > > > > > > > > > 2010/3/29 brianw <[email protected]
<mailto:[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
<http://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.
