2

Hi guys i have a model livestream which has two columns in the database, 'user_id' and 'activity_id'. Very straight forward.

Now when I try to insert multiple records into the database I get an invalid SQL statement:

SQLite3::SQLException: near ",": syntax error: INSERT INTO livestreams (user_id, activity_id) 
          VALUES (1, 2), (101, 2)

The code used to do this comes from insoshi, and is as followes:

def do_livestream_insert(users_ids, activity_id)
      sql = %(INSERT INTO livestreams (user_id, activity_id) 
              VALUES #{values(users_ids, activity_id)})
      ActiveRecord::Base.connection.execute(sql)
end

def values(ids, common_value)
      common_values = [common_value] * ids.length
      convert_to_sql(ids.zip(common_values))
end

def convert_to_sql(array_of_values)
      array_of_values.inspect[1...-1].gsub('[', '(').gsub(']', ')')
end

Why does rails do that? Does SQLlite not support the insertion of multiple records at one time?

I have reseted the database, and repopulated it.

Thanks for your help, much appreciated.

Have a nice day, Stefano

Stephan1990
  • 445
  • 4
  • 19

2 Answers2

2

SQLite did not support multiple records insertion until version 3.7.11.

Community
  • 1
  • 1
Li0liQ
  • 11,158
  • 35
  • 52
  • how can i accomplish something like this using SQLite3? Or would you recommend using mysql instead? – Stephan1990 Sep 11 '10 at 13:20
  • The best alternative will be inserting multiple records within a single transaction (change `do_livestream_insert` appropriately). But you can surely switch to MySQL if you don't need SQLite's portability. – Li0liQ Sep 11 '10 at 19:10
  • Version 3.7.11 of SQLite adds support for multi-row insert statements. http://www.sqlite.org/changes.html – yoni Mar 27 '13 at 18:35
0

I would suggest redefining do_livestream_insert to instead to N inserts

Ransom Briggs
  • 3,025
  • 3
  • 32
  • 46