Rails ActiveRecord: Get Source Insert ID

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 always nil , although I can see that DmozCategory inserts into the database. How to get id after my insert?

I understand that to get the identifier I could use a different SQL query SELECT LAST_INSERT_ID() , but I was wondering if there is a way to get the identifier via Rails. M

Reference: Using Rails 2.3.14

UPDATE : Hmm, I think the problem is with the plugin that I use called Octopus . Sorry for discounting some of your answers. It looks like I need to find how to get the last insert id using this plugin. My total ratio:

 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, use connection.insert instead of connection.insert

This will return the generated identifier of the last inserted row. How it depends on the database. In MySQL, the last insert identifier is a connection property. In Postgres, the return clause is added to the request (with older versions of postgres, the reserve requests a sequence for its last id).

Using insert rather than execute also clears the relay request cache.

In MySQL, at least this will work even if you set the identifier yourself .

Answer 2

I think the insert_sql method should help you

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

Answer 3

try this since AR defines id before pasting in 2.3

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

Answer 4

If it is a mysql2 stone and DmozCategory.connection is an instance of the client, DmozCategory.connection.last_id will work.

Answer 5

Since you already know the id , can you do this?

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

Answer 6

Assuming you are using MYSQL and automatically increasing the number of tables, you can use:

 SELECT LAST_INSERT_ID() 

to do what it says, take the last inserted ID into the table. This will add another query to your code, but it seems to be using your raw SQL.