ruby on rails sql statement error

Question

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

Community · Accepted Answer · 2017-05-23 12:07:01Z

2

SQLite did not support multiple records insertion until version 3.7.11.

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Sep 11, 2010 at 10:14

Li0liQ

11.3k38 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stephan1990 Over a year ago

how can i accomplish something like this using SQLite3? Or would you recommend using mysql instead?

Li0liQ Over a year ago

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.

yoni Over a year ago

Version 3.7.11 of SQLite adds support for multi-row insert statements. sqlite.org/changes.html

Ransom Briggs · Accepted Answer · 2010-09-11 16:45:13Z

0

I would suggest redefining do_livestream_insert to instead to N inserts

answered Sep 11, 2010 at 16:45

Ransom Briggs

3,1053 gold badges36 silver badges46 bronze badges

1 Comment

Stephan1990 Over a year ago

for development ok, but for production? (I am asking for your opinion)

Collectives™ on Stack Overflow

ruby on rails sql statement error

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related