关于Rails中Postgresql的准备好的声明 [英] Prepared Statement on Postgresql in Rails
问题描述
现在，我正处于从SQLite迁移到Postgresql的过程中，遇到了这个问题。以下准备好的语句适用于SQLite：
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
不幸的是，它不能与Postgresql一起使用-它在第2行抛出异常。
我在寻找解决方案并遇到了这个问题：
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 ])
此行在第3行失败。当我打印这样的异常
This one fails at line 3. When I print the exception like this
rescue => ex
ex包含此
{"connection":{}}
在命令中执行SQL线工程。知道我在做什么错吗？
Executing the SQL in a command line works. Any idea what I am doing wrong?
谢谢！
推荐答案
如果要使用准备，然后需要进行一些更改：
If you want to use prepare like that then you'll need to make a couple changes:
-
PostgreSQL驱动程序希望看到带编号的占位符（
$ 1，$ 2，...）不是问号，您需要为您准备好的语句起一个名字：
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")
调用顺序为 准备 ，后跟 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 ])
以上方法对ActiveRecord和PostgreSQL都适用，如果连接正确，您的 PG :: Connection.open 版本应该可以工作。
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)}
})
这是ActiveRecord通常在背后做的事情。
That's the sort of thing that ActiveRecord is usually doing behind your back.
直接交互数据库的使用与Rails有点混乱，因为Rails的人不认为您应该这样做。
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.
如果您确实是在尝试删除没有干扰的行，可以使用 delete ：
If you really are just trying to delete a row without interference, you could use delete:
delete（）
[...]
只需在记录的主键上使用SQL DELETE 语句删除该行，就不会执行回调。
The row is simply removed with an SQL DELETE statement on the record’s primary key, and no callbacks are executed.
所以你可以这样说：
MyTable.delete(id)
，您将从my_tables中发送一个简单的 delete，其中id = ... 进入数据库。
and you'll send a simple delete from my_tables where id = ... into the database.
这篇关于关于Rails中Postgresql的准备好的声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
