Prepared Statement on Postgresql in Rails

Prepared Statement on Postgresql in Rails

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

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

2. 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.

Prepared statements in ruby/rails with postgres

See the discussion of PreparedStatements in Rails ('Using Prepared Statements') here - http://blog.daniel-azuma.com/archives/216 . Shows you which methods to call, and how to format your arguments.

UPDATE:

Paraphrased from the post:

For the delete method arguments use the template first, followed by a query name (which can be nil) and then an array of values to inject into the statement. So like this:

row_count = connection.delete("DELETE FROM foo WHERE foo.bar=$1", nil, [[nil, 'baz']])

Using prepared statement in Rails to insert multiple rows

try this

user_string = " ('code1','title1', 'aaa', ...), ('code2','title2'...)"

User.connection.insert("INSERT INTO films (code, title, did, date_prod, kind)VALUES"+user_string) 

Prepare and execute statements with ActiveRecord using PostgreSQL

Copying the answer from the edited question body, in order to remove this question from the "Unanswered" filter:

I solved the problem. Thanks for the response, but it didn't work for
PostgreSQL. The way to do it is:

stmt = "SELECT * from sampletable where id = $1 and name = $2"
values = [ { value: 1}, { value: "henry" } ]

where values is an array of hashes, each specifying a value, $1 is
bound to the 0th hash, $2 is bound to the 2nd hash in the array and so
on

con = PG::Connection.new(:dbname => "development_DB")
con.prepare("insert", stmt)
con.exec_prepared("insert", values)
con.close()

And this, ladies and gentlemen, works!

~ answer per alalani

undefined method 'exec_prepared' on Rails 4 postgresql query

The prepare method returns a result according to the docs:
http://deveiate.org/code/pg/PG/Connection.html#method-i-prepare

Maybe try call exec_prepared on the connection object

connection = ActiveRecord::Base.connection.raw_connection

def create_prizes

      begin 
        connection.describe_prepared('xixie')
      rescue PG::InvalidSqlStatementName
        connection.prepare('xixie', 'INSERT INTO prizes (deal_id) values ($1)')
      end

      Deal.transaction do  
        self.prize_number.times do |i| 
          connection.exec_prepared('xixie',  [ { value: self.id} ] )
        end
      end
end

UPDATE: I reworked the code above to first check if a prepared statement exists. If it doesn't exist it creates it. Sorry I haven't realized it in the first place but you don't need to prepare a statement more than once. This is the actual benefit of such a statement, since it has to be only parsed once and can than be executed with different values, which is then much faster than a regular query.
As prepared statements last for the duration of the AR connection you only need to prepare it once.

---

Related Topics