Rails find_by_sql - how to run generic queries

Question

I need to execute this query to find out the next auto_increment value that will be used by MySQL for a particular table.

find_by_sql ["select auto_increment from information_schema.tables where   
table_schema = ? and table_name = ? and auto_increment is not null", db_name, tbl_name]

How to invoke this particular query? This works on any model that I invoke it with returning an array of size 1 containing the object of the model. Edit: The object contains a hash named attributes which contains the auto_increment value as desired.

Are there any other ways to run such generic queries ? Would like to know if a change in the whole approach of using find_by_sql is possible to solve the original problem as well.

Answer 1

So that array containing one object of the model is actually ActiveRecord trying to convert the results of your query into an object.

Take a look at the #columns [edit: err "attributes"] attribute of that object and see where ActiveRecord has put the result you're looking for (I suspect it will be under a column named auto_increment)

Answer 2

If you want to run raw SQL without involving a model, you can obtain the connection directly and call one of the select_* methods (select_value in this case). These methods just take a SQL statement as a string, so you can call sanitize_sql_array to transform your parameters array (note that sanitize_sql_array is protected, so send may be required).

ActiveRecord::Base.connection.select_value(
  ActiveRecord::Base.send(:sanitize_sql_array, 
   ["select auto_increment from information_schema.tables where
    table_schema = ? and table_name = ? and auto_increment is not null", 
    db_name, tbl_name]))

The result returned will be a string.