Prepared Statements Already Exists

Question

I am trying to use the prepared statements in ruby with pg gem. This is how my statement looks like

conn.prepare("insert_values", "insert into " + objectName + "(" + headerStr + ") values (" + prep_values + ")")  
conn.exec_prepared("insert_values", arr)

I keep getting the error

Prepared Statement insert_values already exists.

How Do i Fix this?? Thanks

Uncommonly

Yeah good question, how do you clear or reuse a name for a prepared statement? – Ize

@Ize clear with the "deallocate" statement. manage your own cache of which names have already been used. if you just want to prepare/execute to get safe parameters, use an anonymous prepared statement – Isaacson

Manual deallocation is a better solution than trapping an error and doing nothing to it. – Leuco

Answer 1

Try to run:

conn.exec("DEALLOCATE name_of_prepared_statement")

In your example:

conn.exec("DEALLOCATE insert_values")

Simple test and it is working in my irb:

1.8.7 :001 > require 'rubygems'
  => true
1.8.7 :002 > require 'pg'
  => true
1.8.7 :003 > conn = PGconn.connect(:host => 'localhost', :port => 5912, :user => 'test', :dbname => 'test' )
  => #<PGconn:0x7fe6ac703970> 
1.8.7 :005 > conn.prepare("insert_values", "select * from data where id < $1")
  => #<PGresult:0x7fe6ac6b2e58> 
1.8.7 :006 > conn.prepare("insert_values", "select * from data where id < $1 and id >   $2")
  PGError: ERROR:  prepared statement "insert_values" already exists

  from (irb):6:in 'prepare'
  from (irb):6
1.8.7 :007 > conn.prepare("insert_values", "select * from data where id < $1")
  PGError: ERROR:  prepared statement "insert_values" already exists

  from (irb):7:in 'prepare'
  from (irb):7
1.8.7 :008 > conn.exec("DEALLOCATE insert_values")
  => #<PGresult:0x7fe6ac6738c0> 
1.8.7 :009 > conn.prepare("insert_values", "select * from data where id < $1")
  => #<PGresult:0x7fe6ac665fe0> 
1.8.7 :010 > conn.exec_prepared("insert_values",[200])
  => #<PGresult:0x7fe6ac65d188> 
1.8.7 :011 > conn.exec("DEALLOCATE insert_values")
  => #<PGresult:0x7fe6ac654df8> 
1.8.7 :012 > conn.exec_prepared("insert_values",[200])
  PGError: ERROR:  prepared statement "insert_values" does not exist

  from (irb):12:in 'exec_prepared'
  from (irb):12

Pythia

I am getting the same error message after running this statement – Uncommonly

@Gaurav look at my simple test in irb. – Pythia

Answer 2

This is a better way reusing prepare(), avoid prepared statement "my_statement" already exists:

sql = "select * from my_table where id = $1"
begin
  ActiveRecord::Base.connection.raw_connection.prepare('my_statement', sql)
rescue PG::DuplicatePstatement => e
end

pg_result = ActiveRecord::Base.connection.raw_connection.exec_prepared('my_statement', [my_table_id])

Athletics