Prepare and execute statements with ActiveRecord using PostgreSQL

Question

I am trying to insert values via a prepared statement using ActiveRecord. However, everytime I try:

conn = ActiveRecord::Base.connection
conn.prepare "SELECT * from sampletable where id = $1"
conn.execute 3

After the second statement, I get:

NoMethodError: undefined method `prepare' for
#<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter:0x000001027442c8>

What should I do? I'm running Rails 3.2.1 and Ruby 1.9.2

UPDATE:

I solved the problem. Thanks for the response, but it didn't work for PostgreSQL. The way to do it is:

stmt = "SELECT * from sampletable where id = $1 and name = $2"
values = [ { value: 1}, { value: "henry" } ]

where values is an array of hashes, each specifying a value, $1 is bound to the 0th hash, $2 is bound to the 2nd hash in the array and so on

con = PG::Connection.new(:dbname => "development_DB")
con.prepare("insert", stmt)
con.exec_prepared("insert", values)
con.close()

And this, ladies and gentlemen, works!

If you're game to recap the solution as your own answer, I'll delete my answer. (See http://meta.stackexchange.com/questions/90263/unanswered-question-answered-in-comments for elaboration of why this is helpful.) Thanks! — DreadPirateShawn, Oct 09 '13 at 16:30

score 4 · Accepted Answer · edited May 23 '17 at 12:17

4

Copying the answer from the edited question body, in order to remove this question from the "Unanswered" filter:

I solved the problem. Thanks for the response, but it didn't work for PostgreSQL. The way to do it is:
stmt = "SELECT * from sampletable where id = $1 and name = $2"
values = [ { value: 1}, { value: "henry" } ]
where values is an array of hashes, each specifying a value, $1 is bound to the 0th hash, $2 is bound to the 2nd hash in the array and so on
con = PG::Connection.new(:dbname => "development_DB")
con.prepare("insert", stmt)
con.exec_prepared("insert", values)
con.close()
And this, ladies and gentlemen, works!

~ answer per alalani

edited May 23 '17 at 12:17

Community

1
1

answered Oct 09 '13 at 04:47

DreadPirateShawn

8,164
4
49
71

1

Rather than recreating a new db connection (`con = PG::Connection.new(:dbname => "development_DB")`), I think it would be better to get any existing connection from your adapter, like so: `con = ActiveRecord::Base.connection.raw_connection` But otherwise, it works great! – qix Mar 18 '16 at 23:02

Prepare and execute statements with ActiveRecord using PostgreSQL

1 Answers1

Linked