CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresql
Pedro Vinícius
Pedro Vinícius

Reputation: 496

Ruby array as parameter to a plain SQL Query

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?

Upvotes: 4

Views: 3669

Answers (1)

Chloe
Chloe

Reputation: 26264

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

Upvotes: 2

Related Questions