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

try this, that is how AR defines id before insert in 2.3

id = DmozCategory.connection.next_sequence_value(DmozCategory.sequence_name)

Answer 2

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 3

If this is the mysql2 gem, and DmozCategory.connection is an instance of the client, DmozCategory.connection.last_id will work.

Answer 4

I think insert_sql method should help you

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

Answer 5

Since you know the id already, can't you just do this?

dmoz_category = DmozCategory.find(result['id'])
$stderr.puts dmoz_category.inspect

Answer 6

Assuming you are using MYSQL and the table auto increments, you could use:

SELECT LAST_INSERT_ID()

to do what it says, grab the last inserted ID into the table. This would add another query to your code, but it seems to stick with your using raw SQL.