If using raw ActiveRecord::Base.connection(), how obtain last inserted primary key value?

Question

This code runs on my local RoR/Windows 7 (64-bit):

sql = ActiveRecord::Base.connection()
last_pk = sql.insert("insert into manual (name) values ('hello new value')")
puts 'last_pk=', last_pk

but always displays "0."

For various reasons I can't use ActiveRecord in this situation.

(Note: The above code runs fine on my shared host. Also Note: I had to replace mysql5\bin\libmySQL.dll with a different DLL per another answer on StackOverflow.com in order to get ANY database connection to work.)

Answer 1

Change your code to use insert_sql instead of insert, i.e.

last_pk = sql.insert_sql("insert into manual (name) values ('hello new value')")
puts 'last_pk=', last_pk

The insert_sql call is supposed to return the primary key. This is the code for insert_sql in mysql_adapter.rb.

def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
  super sql, name
  id_value || @connection.insert_id
end

Answer 2

If in doubt get it from mysql:

SELECT LAST_INSERT_ID()

will return the last id used for insertion. Be sure to lock both statements in a synchronized block if you do multithreading.