Binding parameters in ruby/postres queries

Ruby
1

I’m using the postgres library and trying to improve the performance of
a database query that gets repeated many (~1 million) times.

The documentation suggests you can pass in bind parameters as so:

Documentation --------------------------------------------
conn.query(sql, *bind_values)

Sends SQL query request specified by sql to the PostgreSQL…
bind_values represents values for the PostgreSQL bind parameters found
in the sql. PostgreSQL bind parameters are presented as $1, $1, $2,
etc.

I’ve tried to do it (as I interpret the docs) like this

sql = “Select rating, date from rating where item_id = $1 and person_id
= $2 ;”
row = MyConnectionMgr.get_connection.query(sql, [movie, person])[0]

but the line starting with ‘row = ’ throws an exception that i’m
passing the wrong number of parameters: "in `query’: wrong number of
arguments (2 for 1)" I also tried using ? instead of $1, etc.

Can someone tell me what I’m doing wrong?
thanks.

2

larry wrote:

I’m using the postgres library and trying to improve the performance of
a database query that gets repeated many (~1 million) times.

The documentation suggests you can pass in bind parameters as so:

Documentation --------------------------------------------
conn.query(sql, *bind_values)

Sends SQL query request specified by sql to the PostgreSQL…
bind_values represents values for the PostgreSQL bind parameters found
in the sql. PostgreSQL bind parameters are presented as $1, $1, $2,
etc.

I’ve tried to do it (as I interpret the docs) like this

sql = “Select rating, date from rating where item_id = $1 and person_id
= $2 ;”
row = MyConnectionMgr.get_connection.query(sql, [movie, person])[0]

but the line starting with ‘row = ’ throws an exception that i’m
passing the wrong number of parameters: "in `query’: wrong number of
arguments (2 for 1)" I also tried using ? instead of $1, etc.

Can someone tell me what I’m doing wrong?
thanks.

Now, I’ve never used the postgres library, but if the method is
query(sql, *bind_values)
then you should call it like

row = MyConnectionMgr.get_connection.query(sql, movie, person)[0]

not

row = MyConnectionMgr.get_connection.query(sql, [movie, person])[0]

3

larry wrote:

I’m using the postgres library and trying to improve the performance of
a database query that gets repeated many (~1 million) times.

A prepared statement might be a better optimisation than using bind
params. Of course, prepared statements do require the use of argument
placeholders, so you still have to do that code conversion.

Also, you should use bind parameters irregardless of performance to
avoid SQL injection. The DB driver author probably took care of escaping
dangerous characters in his code, no point in doing it twice.

David V.