Modifying generated SQL

rubyonrails-talk
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

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. What database server are you using? Which connection adapter? Can you give a concrete example of what you're seeing?

Michael Glaesemann grzm seespotcode net

7

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

8

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 Glaesemann grzm seespotcode net

9

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

10

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

11

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