Prepared Statement on Postgresql in Rails

Question

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!

How can I find this out? I am asking because there is no output in the console regarding an exception. — Dominik Schreiber
– Dominik Schreiber, Commented Dec 10, 2012 at 17:42

mu is too short · Accepted Answer · 2012-12-10 18:01:23Z

29

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

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")
```

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.

answered Dec 10, 2012 at 18:01

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

1 Comment

Dominik Schreiber Over a year ago

You were right - giving the statement a name and using a dollar char instead of a question mark did the job. I am aware of the delete function - in this case I am deleting something from a joint table which does not have it's own model. Rather than creating one I went for a "quick" solution. Thanks!

Alex P · Accepted Answer · 2023-03-03 23:47:24Z

1

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

answered Mar 3, 2023 at 23:47

Alex P

1,7711 gold badge18 silver badges20 bronze badges

Collectives™ on Stack Overflow

Prepared Statement on Postgresql in Rails

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related