Modifying generated SQL

Rails
1

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

2

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

3

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… :smiley:

Cam

4

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

5

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

6

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

7

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

8

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

9

searching through the ActiveRecord adapter and deleting all strings that
contain “LIMIT” or “OFFSET”. Its simple but could work.

10

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

11

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

12

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