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 meta.stackexchange.com/questions/90263/… for elaboration of why this is helpful.) Thanks! — DreadPirateShawn
– DreadPirateShawn, Commented Oct 9, 2013 at 16:30

Community · Accepted Answer · 2017-05-23 12:17:45Z

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, 2017 at 12:17

CommunityBot

11 silver badge

answered Oct 9, 2013 at 4:47

DreadPirateShawn

8,4304 gold badges51 silver badges71 bronze badges

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

1 Comment

qix Over a year ago

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!

Collectives™ on Stack Overflow

Prepare and execute statements with ActiveRecord using PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related