I am trying to pass a quoted list to a placeholder in an IN
clause with Ruby Sequel.
Specifically:
query = "
SELECT *
FROM PRICE
WHERE REGION IN (?)"
Then I use Ruby Sequel: dataset = dbr[query, param_values]; dataset.sql
.
If I pass param_values
as a string like this: param_values = "'NSW1', 'VIC1'"
, the where clause becomes:
WHERE REGION IN ((N'''NSW1'', ''VIC1'''))
which doesn't work - presumably too many quotes. If I try passing the parameter as an array of quoted strings like this: param_values = %w[NSW1 VIC1]
the where clause becomes: WHERE REGION IN ((N'NSW1' = N'VIC1'))
which won't work.
Is there something that will work?
CodePudding user response:
I would use Sequel's query builder language instead of writing SQL manually which would look like this if you used default naming conventions:
DB[:prices]
.select(:date, :price, :region_id)
.where(region_id: %w[NSW1 VIC1])
CodePudding user response:
query = "
SELECT *
FROM PRICE
WHERE REGION IN ?"
regions = Sequel.lit("'NSW1', 'VIC1'")
sql = db.fetch(query, regions).sql
puts "sql: #{sql}"
Gives:
sql: SELECT *
FROM PRICE
WHERE REGION IN ('NSW1', 'VIC1')
which is what we want.