CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3postgresqlprepared-statement
Dominik Schreiber
Dominik Schreiber

Reputation: 779

Prepared Statement on Postgresql in Rails

Right now I am in the middle of migrating from SQLite to Postgresql and I came across this problem. The following prepared statement works with SQLite:

id = 5
st = ActiveRecord::Base.connection.raw_connection.prepare("DELETE FROM my_table WHERE id = ?")
st.execute(id)
st.close

Unfortunately it is not working with Postgresql - it throws an exception at line 2. I was looking for solutions and came across this:

id = 5
require 'pg'
conn = PG::Connection.open(:dbname => 'my_db_development')
conn.prepare('statement1', 'DELETE FROM my_table WHERE id = $1')
conn.exec_prepared('statement1', [ id ])

This one fails at line 3. When I print the exception like this

rescue => ex

ex contains this

{"connection":{}}

Executing the SQL in a command line works. Any idea what I am doing wrong?

Thanks in advance!

Upvotes: 13

Views: 22091

Answers (2)

Alex P
Alex P

Reputation: 1751

To complement an already accepted answer for anyone Googling prepared statements in Rails for custom queries, here is a simpler way to do it via existing ActiveRecord interface (example is for Postgresql):

    ActiveRecord::Base.connection.exec_query("select * from table_name where id = $1", 
"example_query", [1], prepare: true)

And if you put binding.pry, you will see that prepared statement is indeed created:

    ActiveRecord::Base.connection.exec_query('select * from pg_prepared_statements')

Reference: https://api.rubyonrails.org/v7.0.4.2/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html

Upvotes: 1

mu is too short
mu is too short

Reputation: 434575

If you want to use prepare like that then you'll need to make a couple changes:

  1. The PostgreSQL driver wants to see numbered placeholders ($1, $2, ...) not question marks and you need to give your prepared statement a name:

     ActiveRecord::Base.connection.raw_connection.prepare('some_name', "DELETE FROM my_table WHERE id = $1")

  2. The calling sequence is prepare followed by exec_prepared:

    connection = ActiveRecord::Base.connection.raw_connection
connection.prepare('some_name', "DELETE FROM my_table WHERE id = $1")
st = connection.exec_prepared('some_name', [ id ])

The above approach works for me with ActiveRecord and PostgreSQL, your PG::Connection.open version should work if you're connecting properly.

Another way is to do the quoting yourself:

conn = ActiveRecord::Base.connection
conn.execute(%Q{
    delete from my_table
    where id = #{conn.quote(id)}
})

That's the sort of thing that ActiveRecord is usually doing behind your back.

Directly interacting with the database tends to be a bit of a mess with Rails since the Rails people don't think you should ever do it.

If you really are just trying to delete a row without interference, you could use delete:

delete()

[...]

The row is simply removed with an SQL DELETE statement on the record’s primary key, and no callbacks are executed.

So you can just say this:

MyTable.delete(id)

and you'll send a simple delete from my_tables where id = ... into the database.

Upvotes: 29

Related Questions