7
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
Ben G
  • 26,091
  • 34
  • 103
  • 170

6 Answers6

14

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.

Frederick Cheung
  • 83,189
  • 8
  • 152
  • 174
  • How can I get the id? `DmozCategory.connection.id` and `DmozCategory.connection.last_id` don't seem to have the id i'm inserting – Ben G Jun 20 '12 at 19:02
  • connection.insert "INSERT ..." should return the id (assuming that your id is a regular auto increment column) – Frederick Cheung Jun 20 '12 at 20:24
  • oh, so I don't need to use `.send` first? it does both send and execute in one? – Ben G Jun 20 '12 at 22:01
  • See the question for `.send`, it just gets the SQL. `DmozCategory.connection.insert(sql)` returns `0`, and `DmozCategory.connection.id` returns `2223123080` even though I can see that it's been inserted into the table with an id of `2`. – Ben G Jun 22 '12 at 00:52
  • If you happen to be using a Foreign Data Wrapper on PostgreSQL, then calling `#insert` will not automatically use `RETURNING`; however, you can add that the your `INSERT` command that is run on the remote server and it will return the remote id. See [the docs](http://www.postgresql.org/docs/9.3/static/sql-insert.html) for examples on `INSERT ... RETURNING` – jwadsack Apr 16 '14 at 00:09
1

I think insert_sql method should help you

DmozCategory.connection.insert_sql(sql) # => id
caulfield
  • 1,383
  • 1
  • 11
  • 21
0

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

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

Yuri Barbashov
  • 5,407
  • 1
  • 24
  • 20
0

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

Matthew Ratzloff
  • 4,518
  • 1
  • 31
  • 35
0

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

dmoz_category = DmozCategory.find(result['id'])
$stderr.puts dmoz_category.inspect
lee
  • 2,351
  • 1
  • 16
  • 18
  • it's starting to look like this is the only way. no clue why, but everything else returns either 0 or a huge/wrong number – Ben G Jun 26 '12 at 00:00
-1

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.

Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
BK22
  • 85
  • 3