Prepared Statement on Postgresql in Rails
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 byexec_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.
Dominik Schreiber
Updated on June 05, 2022Comments
-
Dominik Schreiber almost 2 years
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!
-
Dominik Schreiber over 11 yearsYou 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!