I''m working through the "4 Days on Rails" tutorial and
I''ve come up
against a problem. In my items_controller I have the following method
that''s erroring out:
def list_by_description
@item_pages, @items = paginate :item, :per_page => 10, :order_by
=> [description],due_date''
render_action ''list''
end
It gives me this result:
Execute
OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Invalid column name ''ASCription''.
HRESULT error code:0x80020009
Exception occurred.: SELECT * FROM ( SELECT TOP 2 * FROM (
SELECT TOP 2 * FROM items ORDER BY [description],due_date ) AS tmp1
ORDER BY [ASCription],due_date ) AS tmp2 ORDER BY
[description],due_date
If I change the :order_by to any other field in the table, it works
correctly. I''ve tried it with the square brackets and without. It
looks as if the sqlserver adapter is recognizing the DESC in
description and replacing it with ASC.
Thanks,
--
Marlon
"Now watch what you say or they''ll be calling you a radical,
liberal, fanatical, criminal. "
> If I change the :order_by to any other field in the table, it works > correctly. I''ve tried it with the square brackets and without. It > looks as if the sqlserver adapter is recognizing the DESC in > description and replacing it with ASC.Try explicitly stating the sorting order by using ASC or DESC before the column name _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Thanks for the suggestion, but it was a no go. Here''s an excerpt from the error now: AS tmp1 ORDER BY ASC [ASCription] On 8/23/05, Rafael Szuminski <raf1hh-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > > If I change the :order_by to any other field in the table, it works > > correctly. I''ve tried it with the square brackets and without. It > > looks as if the sqlserver adapter is recognizing the DESC in > > description and replacing it with ASC. > > Try explicitly stating the sorting order by using ASC or DESC before the > column name > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >-- Marlon "Now watch what you say or they''ll be calling you a radical, liberal, fanatical, criminal. "
On 24.8.2005, at 17.57, Marlon Moyer wrote:> Thanks for the suggestion, but it was a no go. Here''s an excerpt from > the error now: > > AS tmp1 ORDER BY ASC [ASCription]Err... at least in the sql standard the [desc|asc] keyword comes after the column name, not before it. Avoid this message if it''s the other way round in sqlserver. Try putting the table name in the clause: ...as tmp1 order by yourtablename.description //jarkko -- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Hey Jarkko,
I''ve tracked the problem down to the sqlserver adapter. Specifically
this code:
def change_order_direction(order)
case order
when /DESC/i then order.gsub(/DESC/i, "ASC")
when /ASC/i then order.gsub(/ASC/i, "DESC")
else String.new(order).insert(-1, "
DESC")
end
end
It looks like this code will replace any instance of ''DESC'' or
''ASC''
in the order by statement regardless of where (even if it''s in the
middle of a table name) it falls. I''m having a brain fart on the regex
to replace this with though.
On 8/24/05, Jarkko Laine <jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org>
wrote:>
> On 24.8.2005, at 17.57, Marlon Moyer wrote:
>
> Thanks for the suggestion, but it was a no go. Here''s an excerpt
from
> the error now:
>
> AS tmp1 ORDER BY ASC [ASCription]
>
> Err... at least in the sql standard the [desc|asc] keyword comes after the
> column name, not before it. Avoid this message if it''s the other
way round
> in sqlserver.
>
> Try putting the table name in the clause:
> ...as tmp1 order by yourtablename.description
>
> //jarkko
>
>
>
>
> --
>
> Jarkko Laine
>
> http://jlaine.net
>
> http://odesign.fi
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>
>
--
Marlon
"Now watch what you say or they''ll be calling you a radical,
liberal, fanatical, criminal. "
Marlon Moyer wrote:>Thanks for the suggestion, but it was a no go. Here''s an excerpt from >the error now: > >AS tmp1 ORDER BY ASC [ASCription] > > > >The problem is this code in the sqlserver_adapter.rb file: def change_order_direction(order) case order when /DESC/i then order.gsub(/DESC/i, "ASC") when /ASC/i then order.gsub(/ASC/i, "DESC") else String.new(order).insert(-1, " DESC") end end Not sure why/when this method is called, but you may try changing the gsubs()''s to look something like: gsub(/\bDESC\b/i, "ASC") which require the DESC to be on word boundaries, i.e. should not match "Description"
Thanks Steve, that did the trick. Should this be submitted as a bug, if so, how do you go about submitting it? Thanks Marlon On 8/24/05, Steve Downey <sldowney-TVLZxgkOlNX2fBVCVOL8/A@public.gmane.org> wrote:> Marlon Moyer wrote: > > >Thanks for the suggestion, but it was a no go. Here''s an excerpt from > >the error now: > > > >AS tmp1 ORDER BY ASC [ASCription] > > > > > > > > > > The problem is this code in the sqlserver_adapter.rb file: > > def change_order_direction(order) > case order > when /DESC/i then order.gsub(/DESC/i, "ASC") > when /ASC/i then order.gsub(/ASC/i, "DESC") > else String.new(order).insert(-1, " DESC") > end > end > > Not sure why/when this method is called, but you may try changing the > gsubs()''s to look something like: > > gsub(/\bDESC\b/i, "ASC") > > which require the DESC to be on word boundaries, i.e. should not match > "Description" > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Marlon "Now watch what you say or they''ll be calling you a radical, liberal, fanatical, criminal. "
>On 8/24/05, Marlon Moyer <marlon.moyer-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:Thanks Steve,that did the trick. Should this be submitted as a bug,>if so, how do you go about submitting it?Marlon, You can enter a new ticket on the Rails Trac site, http://dev.rubyonrails.com/newticket. If you want to submit a patch, then grab a copy of the source from the subversion repository. Make your change locally. Test. Create and attach a patch to the ticket. Ken