1

I have an array

ziparray = ["95626", "95645", "95837"]

I want to pass this to my sql query ,

sql = "SELECT * from table_name WHERE code in ($1);"
res1 = conn.exec(sql, [ziparray])

It does work for single values.

I am using pg gem and connecting to database using 

conn = PG.connect()

I am using postgres and it doesn't take double quotes . I am assuming that to be the problem. How to achieve this.

Update

I could convert to desired string using 

str = "'"
str << ziparray.join("','")
str << "'"
#print str

But I guess the problem is passing of multiple parameters.

this works -

res1 = conn.exec(fipscodesql, ['95626'])

But not this 

res1 = conn.exec(fipscodesql, ['95626', '95625'])

and this is exactly what I did when I converted the array to string. I guess this is not the right way to use parameters. is there any other way.

Improve this question
3
  • res1 = conn.exec(sql, [ziparray].join(',')) should work. Commented Sep 27, 2018 at 10:05
  • I am trying to convert it to string using str = ziparray.join("','") .output I am getting is 95626','95645','95837. The start and end quotes are missing . How to use join here? Commented Sep 27, 2018 at 10:28
  • [ziparray].map { |zip| "'#{zip}'" }.join(',') Commented Sep 27, 2018 at 10:37

4 Answers 4

Reset to default
4

As others said, you can't parametrise a whole array. Use this instead:

ziparray = ["95626", "95645", "95837"]
zip_placeholders = ziparray.map.with_index(1) { |_, i| "$#{i}" }.join(', ')
sql = "SELECT * from table_name WHERE code in (#{zip_placeholders});"
# => "SELECT * from table_name WHERE code in ($1, $2, $3)"

Then you can use the normal parameter binding.

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

5 Comments

Your update is not relevant to my answer. I am creating multiple placeholders, each for one value in your array.
This is the way to go.
@themaster you invoke the query via conn.exec(sql, ziparray) – no need to convert ziparray yourself.
@Stefan yes .. I was kind of confused. This works. Thanks a lot
There is another question for it stackoverflow.com/q/54496161/3483708.
1

[ziparray].map { |zip| "'#{zip}'" }.join(',')

Improve this answer

Comments

0

SQL method "IN" doesn't use array.
So:

IN(234) - correct 
IN(234,543) - correct
IN([234,543]) - wrong

You can try to convert array to string and pass variable $1 like '95626, 95645, 95837'

Improve this answer

4 Comments

I am trying to convert it to string using str = ziparray.join("','") .output I am getting is 95626','95645','95837. The start and end quotes are missing . How to use join here?
["2", "3", "4"].join(',') it is return "2,3,4"
In my IN its string parameters not int .I need with single quotes each value.
["2", "3", "4"].to_s[1..-2] this returned as you want, but I don`t like this way)
-1

If x is an array and x = ['a','b','c']

quotes = (select * from quote in (?), x)
  • We cannot use this in a SQL parameter
  • x.join(',') returns "a,b,c". If it is int this is not a problem.
  • Instead, save this array in a variable and use it in a SQL parameter.

a = x and then use quotes = (select * from quote in (?),a)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.