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 stackoverflow.com/questions/13805627/… — fanta
– fanta, Commented Apr 15, 2016 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
– mu is too short, Commented Apr 15, 2016 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
– Pedro Vinícius, Commented Apr 15, 2016 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
– mu is too short, Commented Apr 15, 2016 at 23:11

Community · Accepted Answer · 2017-05-23 12:00:49Z

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

Collectives™ on Stack Overflow

Ruby array as parameter to a plain SQL Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related