Rails ActiveRecord: Getting the id of a raw insert

Question

sql = DmozCategory.send(:sanitize_sql_array, ["INSERT INTO dmoz_categories (id, dmoz_category_title, dmoz_category_name, dmoz_category_description, created_at, updated_at, dmoz_category_lastupdate) VALUES (?, ?, ?, ?, NOW(), NOW(), ?)", result['catid'], result['title'], result['name'], result['description'], result['lastupdate']])

res = DmozCategory.connection.execute(sql)
$stderr.puts res.inspect

res is always nil, even though I can see the DmozCategory inserts into the database. How to get the id following my insert?

I realize that I could use another SQL query SELECT LAST_INSERT_ID() to get the ID, but I was wondering if there was a way to get the id through Rails. M

Background: using Rails 2.3.14

UPDATE: Hmm, I think the problem lays with a plugin I'm using called Octopus. Sorry for discounting some of your answers.. It looks like I need to find how to get the last id of an insert with this plugin. My complete coe:

desc "load all categories from dmoz" # With this one we're loading all the 'structure' table in, not the parent-child relationships.
  task :load_categories_from_dmoz, [ :offset, :limit ] => :environment do |t, args|
    offset = !args[:offset].blank? ? args[:offset].to_i : 0 # Take offset from args.  Default of 0
    limit = !args[:limit].blank? ? args[:limit].to_i : 1 # Take limit from args.  Default of 1
    ActiveRecord::Base.octopus_establish_connection(:adapter=> "mysql", :host=> "localhost", :database => "dmoz", :username => "dmoz", :password => "dmoz")

    results = ActiveRecord::Base.connection.select_all("SELECT * FROM structure LIMIT #{ offset }, #{ limit }") # Fetches it directly from the dmoz database.
    count = offset
    conn = ActiveRecord::Base.octopus_establish_connection(:adapter=> "mysql", :host=> "localhost", :database => "talon_development", :username => "rails_shadow", :password => "husky")
    results.each do |result|
      if count % 1000 == 0
        puts count
      end
      count +=1

      begin
        sql = DmozCategory.send(:sanitize_sql_array, ["INSERT INTO dmoz_categories (id, dmoz_category_title, dmoz_category_name, dmoz_category_description, created_at, updated_at, dmoz_category_lastupdate) VALUES (?, ?, ?, ?, NOW(), NOW(), ?)", result['catid'], result['title'], result['name'], result['description'], result['lastupdate']]) #We leave parent_id NULL for the next task to handle relationships

        DmozCategory.connection.execute(sql) #doesn't get the ID..

      end
    end
  end

Answer 1

In general instead of using connection.execute, use connection.insert

This will return the generated id of the last inserted row. How it does this is database dependant. On MySQL, the last insert id is a property of the connection. On Postgres a 'returning' clause is appended to the query (with old versions of postgres a fallback asks the sequence for its last id).

Using insert rather than execute also clears the rails query cache.

On MySQL at least, this will work even if you set the id yourself.

Answer 2

I think insert_sql method should help you

DmozCategory.connection.insert_sql(sql) # => id