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?

提前致谢！

推荐答案

如果你想像那样使用 prepare 那么你需要做一些改变:

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

1. PostgreSQL 驱动程序希望看到编号的占位符($1、$2、...)而不是问号，您需要为准备好的语句命名:

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

调用顺序是prepare 后跟 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.

如果你真的只是想无干扰地删除一行，你可以使用 删除:

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)

然后您将发送一个简单的 delete from my_tables where id = ... 到数据库中.

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

这篇关于Rails 中关于 Postgresql 的准备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！