Get the connection
ActiveRecord::Base.connection is a singleton allowing you direct access to the database connection. It is an object of type ActiveRecord::ConnectionAdapters::AbstractAdapter
Useful Methods
The most useful methods are select_all which returns an array of hashes, corresponding to the columns, and select_one which selects a single row.
If you simply want to retrieve information from a single column (i.e. a group of id’s) you may use select_values. If you want to retrieve a single value from the database, use select_value.
It also includes methods for insertion and deletion.
Remember to use the quote method around user supplied strings to prevent SQL Injection attacks.
Example
ActiveRecord::Base.connection.select_one('select sum(quantity) from table')['sum(quantity)'].to_iCaveats
It’s important to note that select_one returns a hash with the column names as keys pointing to the record’s values, so just using
ActiveRecord::Base.connection.select_one('select sum(quantity) from table').to_idoesn’t work. If you simply want the value, use
select_value instead.
Execute()
You can use:
ActiveRecord::Base.connection.execute(sql)
to execute arbitrary insert statements. (the insert method tries to read the last inserted id, which will fail if you’re not inserting into an indexed table)
category: Howto
keywords: execute, SQL, raw
Get the connection
ActiveRecord::Base.connection is a singleton allowing you direct access to the database connection. It is an object of type ActiveRecord::ConnectionAdapters::AbstractAdapter
Useful Methods
The most useful methods are select_all which returns an array of hashes, corresponding to the columns, and select_one which selects a single row.
If you simply want to retrieve information from a single column (i.e. a group of id’s) you may use select_values. If you want to retrieve a single value from the database, use select_value.
It also includes methods for insertion and deletion.
Remember to use the quote method around user supplied strings to prevent SQL Injection attacks.
Example
ActiveRecord::Base.connection.select_one('select sum(quantity) from table')['sum(quantity)'].to_iCaveats
It’s important to note that select_one returns a hash with the column names as keys pointing to the record’s values, so just using
ActiveRecord::Base.connection.select_one('select sum(quantity) from table').to_idoesn’t work. If you simply want the value, use
select_value instead.
Execute()
You can use:
ActiveRecord::Base.connection.execute(sql)
to execute arbitrary insert statements. (the insert method tries to read the last inserted id, which will fail if you’re not inserting into an indexed table)
category: Howto
keywords: execute, SQL, raw