Ruby array as parameter to a plain SQL Query

Question

I'm building a command line application that needs to connect in various postgresql databases and execute different queries in the ones as Prepared Statements. In a specific query, I need to use the IN clause in conjunction with the ActiveRecord's connection_raw method. My code is so:

ActiveRecord::Base.connection_raw.prepare('read_publications', "UPDATE publications SET readed = TRUE WHERE id IN ($1);")

After, I try execute this query:

ActiveRecord::Base.connection_raw.exec_prepared('read_publications', [1,2,3,4])

The problem is this is not working. The following error is raised when the query runs:

no implicit conversion of Array into Integer

What I'm doing wrong? Exists a way in that I can convert this array to a value that the IN clause can understand?

you'll find your solution here http://stackoverflow.com/questions/13805627/prepared-statement-on-postgresql-in-rails — fanta, Apr 15 '16 at 22:46
Thanks @fanta but I only covered the basics of using prepared statements without ActiveRecord's interference. The issue here, I think, is specifically dealing with SQL's `IN` and an array of Ruby values. — mu is too short, Apr 15 '16 at 22:51
Yes @muistooshort! You're right! I did the correction. And yes! The problem is the one you described in your comment. — Pedro Vinícius, Apr 15 '16 at 22:53
I'm trying to figure out how to make the pg gem (which is the underlying gem for talking to PostgreSQL) play nice with arrays. There's always things like `a.length.times.map { |i| "$#{i + 1}" }.join(',')` to build a list of placeholders to match your array length and some string wrangling to get the placeholders into your SQL if I can't find something better. — mu is too short, Apr 15 '16 at 23:11

score 2 · Answer 1 · edited May 23 '17 at 12:00

If you are using a raw connection, you can't pass in arrays like you can with ActiveRecords. ActiveRecord does some preprocessing for you. If you need raw SQL, then you need a parameter for each array element.

arr = [1,2,3,4]
i = 1
param = []
arr.each { param.push(i); i+=1; }
sql = "UPDATE publications SET readed = TRUE WHERE id IN ($"+param.join(',$')+");"
ActiveRecord::Base.connection_raw.prepare('read_publications', sql)
ActiveRecord::Base.connection_raw.exec_prepared('read_publications', arr)

However, the documentation says the array parameters has to be in a certain format:

https://deveiate.org/code/pg/PG/Connection.html#method-i-exec_prepared

params is an array of the optional bind parameters for the SQL query. Each element of the params array may be either:

a hash of the form: {:value => String (value of bind parameter) :format => Fixnum (0 for text, 1 for binary) }

See similar question: Prepare and execute statements with ActiveRecord using PostgreSQL

Ruby array as parameter to a plain SQL Query

1 Answers1