Rails 中关于 Postgresql 的准备语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13805627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Prepared Statement on Postgresql in Rails
提问by Dominik Schreiber
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:
现在我正在从 SQLite 迁移到 Postgresql，我遇到了这个问题。以下准备好的语句适用于 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:
不幸的是，它不适用于 Postgresql - 它在第 2 行抛出异常。我正在寻找解决方案并遇到了这个：
id = 5
require 'pg'
conn = PG::Connection.open(:dbname => 'my_db_development')
conn.prepare('statement1', 'DELETE FROM my_table WHERE id = ')
conn.exec_prepared('statement1', [ id ])
This one fails at line 3. When I print the exception like this
这个在第 3 行失败了。当我像这样打印异常时
rescue => ex
ex contains this
ex 包含这个
{"connection":{}}
Executing the SQL in a command line works. Any idea what I am doing wrong?
在命令行中执行 SQL 有效。知道我做错了什么吗？
Thanks in advance!
提前致谢！
回答by mu is too short
If you want to use preparelike that then you'll need to make a couple changes:
如果你想这样使用prepare，那么你需要做一些改变：
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 = ")The calling sequence is
preparefollowed byexec_prepared:connection = ActiveRecord::Base.connection.raw_connection connection.prepare('some_name', "DELETE FROM my_table WHERE id = ") st = connection.exec_prepared('some_name', [ id ])
PostgreSQL 驱动程序希望看到编号的占位符 (
$1,$2, ...) 而不是问号，您需要为准备好的语句命名：ActiveRecord::Base.connection.raw_connection.prepare('some_name', "DELETE FROM my_table WHERE id = ")调用顺序
prepare如下exec_prepared：connection = ActiveRecord::Base.connection.raw_connection connection.prepare('some_name', "DELETE FROM my_table WHERE id = ") st = connection.exec_prepared('some_name', [ id ])
The above approach works for me with ActiveRecord and PostgreSQL, your PG::Connection.openversion should work if you're connecting properly.
上述方法适用于 ActiveRecord 和 PostgreSQL，PG::Connection.open如果您连接正确，您的版本应该可以工作。
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.
这就是 ActiveRecord 通常在背后做的事情。
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.
直接与数据库交互对于 Rails 来说往往有点混乱，因为 Rails 的人认为你不应该这样做。
If you really are just trying to delete a row without interference, you could use delete:
如果你真的只是想在没有干扰的情况下删除一行，你可以使用delete：
delete()
[...]
The row is simply removed with an SQL
DELETEstatement on the record's primary key, and no callbacks are executed.
删除（）
[...]
使用
DELETE记录主键上的SQL语句简单地删除该行，并且不执行任何回调。
So you can just say this:
所以你可以这样说：
MyTable.delete(id)
and you'll send a simple delete from my_tables where id = ...into the database.
然后您将向delete from my_tables where id = ...数据库发送一个简单的信息。
