Prepared statements in ruby/rails with postgres

Question

I want to execute a rather nasty recursive update query in rails. This means I want to write some raw postgres sql, with parameters, and execute it inside a rails controller.

How do I do that? I can't find a PreparedStatement class in activerecord, there don't seem to be any methods named 'native', I have tried ActiveRecord::Base.connection.exec_delete, I have looked through the source - just cannot, cannot work it out.

I've looked everywhere - the documentation goes in circles.

How would I tell postgres to execute

delete from foo where foo.bar=?

bind 'baz' to the q-mark, and do it without using the active record objects, finders, you-beaut subset thingies and all the rest.

I just want to execute a prepared statement with some bindings. How hard can it be?

(PS, and no: I don't want to jam the parameters into the string myself and execute it as unparameterised sql. It's wrong and it means I have to worry about sanitising the data.)

Are you trying to create some sort of ETL system?

Chandranshu
– Chandranshu

2013-11-21 07:27:59 +00:00
Commented Nov 21, 2013 at 7:27 — Chandranshu
– Chandranshu, Commented Nov 21, 2013 at 7:27

Peter Goldstein · Accepted Answer · 2013-11-21 07:46:34Z

2

See the discussion of PreparedStatements in Rails ('Using Prepared Statements') here - http://blog.daniel-azuma.com/archives/216 . Shows you which methods to call, and how to format your arguments.

UPDATE:

Paraphrased from the post:

For the delete method arguments use the template first, followed by a query name (which can be nil) and then an array of values to inject into the statement. So like this:

row_count = connection.delete("DELETE FROM foo WHERE foo.bar=$1", nil, [[nil, 'baz']])

edited Nov 21, 2013 at 7:46

answered Nov 21, 2013 at 7:28

Peter Goldstein

4,5552 gold badges21 silver badges17 bronze badges

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

1 Comment

PaulMurrayCbr Over a year ago

Thank you, this is what I needed to know. Only issue now is - where in the documentation is this? How was I supposed to find out for myself that this is how to do it? … ok, I found the doco in connection aciverecord/connectionadapters/databasestatements, but it's not terribly helpful. I have to say - I'm finding the "code by cookbook example" aspect of Ruby development not to my liking. But thanks. I can proceed with this.

Collectives™ on Stack Overflow

Prepared statements in ruby/rails with postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related