Example of a Prepared Insert Statement Using Ruby Pg Gem

Example of a prepared INSERT statement using ruby pg gem

The pg gem wants you to use numbered placeholders ($1, $2, ...) rather than positional placeholders (?):

conn = PG.connect(:dbname => 'db1')
conn.prepare('statement1', 'insert into table1 (id, name, profile) values ($1, $2, $3)')
conn.exec_prepared('statement1', [ 11, 'J.R. "Bob" Dobbs', 'Too much is always better than not enough.' ])

The fine manual has this to say:

- (PGresult) prepare(stmt_name, sql[, param_types ])
[...]

PostgreSQL bind parameters are represented as $1, $1, $2, etc., inside the SQL query.

And again for exec_prepared:

PostgreSQL bind parameters are represented as $1, $1, $2, etc., inside the SQL query. The 0th element of the params array is bound to $1, the 1st element is bound to $2, etc.

select with multiple values in an insert statement using PG gem with Ruby

res = conn.exec("SELECT client_type, distance_in_ft, hostname, serial_number, -65, 224 FROM test_clients WHERE eth_ipv4 = 192.168.0.1").first

conn.prepare('statement2', 'INSERT INTO client_results (client_type, distance, hostname, serial_number, rssi, txrate) VALUES ($1, $2, $3, $4, $5, $6)')
conn.exec_prepared('statement2', res.values)

or simply run the query as is:

conn.exec("INSERT INTO client_results (client_type, distance, hostname, serial_number, rssi, txrate) SELECT client_type, distance_in_ft, hostname, serial_number, -65, 224 FROM test_clients WHERE eth_ipv4 = '192.168.0.1'")

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) 

Unable to insert data including a timestamp with the PG gem?

The problem is that you mixing Ruby and SQL strangely; for example, current_timestamp is SQL but conn.escape_string is Ruby. In this case I'd just inline the SQL in your prepared statement:

conn.prepare(
  'statement1',
  %q{
    insert into sites (url, note, type, last_viewed)
    values ($1, $2, $3, date_trunc('second', current_timestamp))
  }
)

That's perfectly safe to do as you're not interpolating anything into the SQL, you're just tossing bit more SQL into your INSERT.

Create PostgreSQL table comment using a prepared statement

It does appear that this is not possible.

So I went with the old "double all the single quotes" method.

safe_desc = description.gsub("'", "''")
con.exec "COMMENT ON TABLE #{table_name} IS '#{safe_desc}';"

This feels really hacky. But for now I'm marking it as the answer.

If there's a safer way, please let me know.

How to Conditionally Rollback a Transaction Using the PG Ruby Gem

Transactions rollback automatically if an exception is raised inside the block. So you just need to create a little Exception class for yourself, raise one inside the block, and then arrange to rescue it so your program doesn't error-exit due to the raised exception.

Try something like this:

class MyLittleIdError < StandardError
end

begin
  @conn.transaction do |conn|
    ins = conn.exec_prepared('insert', [i, "#{@words1[i % 100]} street"])
    raise MyLittleIdError if (ins[0]['id'].to_i % 2) == 0
  end
rescue MyLittleIdError
  puts "aha! we have rolled back."
end

---

Related Topics