Forum: Ruby on Rails Modifying generated SQL

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Pete (Guest)
on 2007-08-01 01:52
(Received via mailing list)
I am very new to Rails and Ruby.  Currently the SQL generated by ?? (I
don't know where the actual SQL IS created) has the wrong syntax that
I would like to "tweak" before it is passed to the DB.  Is there a way
to "intercept" the created SQL statement before is is passed to the
DB?

Or, for the forms that I am working with, can I directly create the
SQL rather than having it generated?

Examples? Tutorials?  Where do I start?

Thanks,

Pete
Faisal N Jawdat (Guest)
on 2007-08-01 08:06
(Received via mailing list)
On Jul 31, 2007, at 5:52 PM, Pete wrote:
> I am very new to Rails and Ruby.  Currently the SQL generated by ?? (I
> don't know where the actual SQL IS created) has the wrong syntax that
> I would like to "tweak" before it is passed to the DB.  Is there a way
> to "intercept" the created SQL statement before is is passed to the
> DB?

Rails should not be generating incorrect SQL.  Either this is a bug
or you have something misconfigured.

If by "wrong" you meant "not what I meant" then you should probably
examine what ActiveRecord messages you're sending and figure out if
you're asking for the right thing.  If it turns out what you want
isn't possibly using the default associations and find() messages
then it's possible to send raw sql (for example, find_by_sql).

-faisal
cammo (Guest)
on 2007-08-01 10:30
(Received via mailing list)
Or of course for the sake on simplicity in this case you could just
write find_by_sql instead of find, i.e.
If my model was User and therefore my table was users.

User.find(:all) # => SELECT * FROM users;
is the same as writing
User.find_by_sql("SELECT * FROM users");

Of course this is stupid and you wouldn't bother for such a simple
statement, but sometimes Active Record( that's the Rails gem that
writes all this sql etc for you ) does do stupid and very ineffecient
sql.
Here is something from my code base used to make a google sitemap

  # AR was doing a ton of SQL to get these records!
  # With AR =>  DB: 3.31273 (142%)
  # With find_by_sql =>  DB: 0.47021 (12%)
  def sitemap
    @products = Product.find_by_sql('SELECT p.permalink, p.updated_at,
c.permalink AS cat_link FROM products AS p INNER JOIN categories AS c
ON c.id = p.category_id')
  end

Note the massive difference in db times, from Active Record as opposed
to rolling your own. Of course these are generally corner cases, and
it's just worth watching your logs when you've got complex
relationships going on.
I'm not bagging AR, it rulZ mate, but sometimes it's not as clever as
we people are.... :D

Cam
Pete (Guest)
on 2007-08-01 23:12
(Received via mailing list)
Faisal,

>
> Rails should not be generating incorrect SQL.  Either this is a bug
> or you have something misconfigured.
>

The *wrong* part is that the DB driver produces *some* sql that the
database can't handle.  In this case the LIMIT and OFFSET clauses on a
standard select for a list is what causes the DB some grief.  Yes, I
could try to write my own adapter/driver but that is beyond my
capabilities.  However, if I can "control" the SQL that  gets passed
to the DB, then I can make sure that all is well.

Thanks,

Pete
Pete (Guest)
on 2007-08-01 23:25
(Received via mailing list)
Cam,

I gave this a try and I think this will work but my next problem was
that by adding a list method (in this case) Rails now wants me to
create a template to go with it.  I want to override the list method
but I'd like use the "default" template. This is what I did:

class CategoryController < ApplicationController
  active_scaffold :category
  layout "activescaffold"
   def list
     @categories = Category.find_by_sql("SELECT * FROM categories")
   end
end

 I posted to another thread for that one but if you have a quick fix,
I'll take it.

Thanks
Michael G. (Guest)
on 2007-08-01 23:43
(Received via mailing list)
On Aug 1, 2007, at 14:11 , Pete wrote:

>> Rails should not be generating incorrect SQL.  Either this is a bug
>> or you have something misconfigured.
>>
>
> The *wrong* part is that the DB driver produces *some* sql that the
> database can't handle.  In this case the LIMIT and OFFSET clauses on a
> standard select for a list is what causes the DB some grief.  Yes, I
> could try to write my own adapter/driver but that is beyond my
> capabilities.  However, if I can "control" the SQL that  gets passed
> to the DB, then I can make sure that all is well.

It sounds like either there's a bug in the connection adapter or
you're using an unsupported database server. What database server are
you using? Which connection adapter? Can you give a concrete example
of what you're seeing?

Michael G.
grzm seespotcode net
Pete (Guest)
on 2007-08-02 05:21
(Received via mailing list)
> It sounds like either there's a bug in the connection adapter or
> you're using an unsupported database server.

Yep, definitely unsupported.  That is why I am using this work-
around.  I am not sure "who" generates the SQL, it sounds like Active
Record does. And, if so, it assumes the database uses the LIMIT and
OFFSET.  In this case I am using a version of DB2 that has no adapter/
driver that is a perfect fit (DB2/400 is the DB I am connecting to)
and LIMIT and OFFSET have no analog in this DB.  Well, it does have an
analog, but since I have no control over the creation of the initial
SQL, I need to modify the SQL that is passed to the DB.  Or, if I can
originate the SQL, so much the better.  That way, no invalid syntax
gets passed to the DB.

I expect that in the future there will be a "supported" version of the
driver, but for right now I'd like to get some simple Rails apps
working with this DB.

Pete
Michael G. (Guest)
on 2007-08-02 06:56
(Received via mailing list)
On Aug 1, 2007, at 20:20 , Pete wrote:

>
>> It sounds like either there's a bug in the connection adapter or
>> you're using an unsupported database server.
>
> Yep, definitely unsupported.  That is why I am using this work-
> around.  I am not sure "who" generates the SQL, it sounds like Active
> Record does.

ActiveRecord and the appropriate adapter.

> I expect that in the future there will be a "supported" version of the
> driver, but for right now I'd like to get some simple Rails apps
> working with this DB.

I suggest you try to work on the adapter yourself rather than look
for some other type of workaround. I don't know how common DB2/400 is
with ActiveRecord users, so it may be quite a while before someone
else gets around to it.

Michael G.
grzm seespotcode net
cammo (Guest)
on 2007-08-02 08:56
(Received via mailing list)
In reply to your other question Pete.

If you hit the url categories/list then you should have a list.rhtml
in your views/categories
Otherwise what template is it that your wanting to render? You can
also use something like

render :template => .../templatename

Cheers,
Cam
Chris R. (Guest)
on 2007-08-02 13:44
searching through the ActiveRecord adapter and deleting all strings that
contain "LIMIT" or "OFFSET". Its simple but could work.
Pete (Guest)
on 2007-08-02 20:23
(Received via mailing list)
Michael,

> I suggest you try to work on the adapter yourself rather than look
> for some other type of workaround. I don't know how common DB2/400 is
> with ActiveRecord users, so it may be quite a while before someone
> else gets around to it.

I am working this angle.  I have found two folks who have attempted to
modify a driver to handle the peculiarities of DB2/400.  Making a bit
of progress there.

Thanks,

Pete
Pete (Guest)
on 2007-08-02 20:33
(Received via mailing list)
Cam,

> If you hit the url categories/list then you should have a list.rhtml
> in your views/categories

Nope.  No views anywhere in this app.  It is using the default views
provided by the scaffold (AFAIK).

I was hoping there was something like:

render :template => default

So I wouldn't have to think !   It is giving me a path where it is
looking for the list.rhtml.  I'll put something in there anyway since
I don't know how to tell it to use the default list.rhtml that it
would use out of the box.  The beauty of RoR is that I can just have
it build a scaffold around a database table and it "just works".  It
would be nice if when an override to a method is made it would first
look for a "local" copy of the template and then use the default one
for the scaffold.

Thanks for the tip. I'll figure his out.

Pete
This topic is locked and can not be replied to.