Example of a prepared INSERT statement using ruby pg gem

Question

Did some googling for about half a day and I can't find any sample of a prepared INSERT statement using the pg gem (postgresql ruby gem).

I tried this (after looking at the gem docs):

def test2
    conn = PG.connect( dbname: 'db1' )
    conn.prepare("statement1", 'INSERT INTO table1 (id, name, profile) VALUES (?, ?, ?)')
end

But I get the following error:

pgtest.rb:19:in `prepare': ERROR:  syntax error at or near "," (PG::Error)
LINE 1: INSERT INTO table1 (id, name, profile) VALUES (?, ?, ?)
                                                        ^
from pgtest.rb:19:in `test2'
from pgtest.rb:25:in `<main>'

Can you post the whole file please? That might help, since its a syntax error — it may have started farther back — Jwosty
– Jwosty, Commented May 31, 2012 at 21:20
@Jwosty: The ERROR: ... (PG::Error) indicates that error is coming from pg rather than Ruby. — mu is too short
– mu is too short, Commented May 31, 2012 at 21:30
Oh, that makes sense... It looked like it was a syntax mistake on the OP's part. I didn't realize that the gem was throwing the error (I've never used this one before); thanks! — Jwosty
– Jwosty, Commented Jun 1, 2012 at 1:05

mu is too short · Accepted Answer · 2012-05-31 21:24:26Z

35

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.

answered May 31, 2012 at 21:24

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

iphone007 Over a year ago

Thanks! That did the trick! Doh, I don't know how I missed that!

Martin Velez Over a year ago

When is it worth it to prepare the statement prior to executing it?

mu is too short Over a year ago

@Martin: If you want to execute the same statement several times with different values. Some DB interfaces require explicit prepared statement use to use placeholders at all but the pg gem lets you use placeholders with exec.

Martin Velez Over a year ago

@mu Thanks! I ran a small experiment: I ran a program 3 times with exec; about 1600 exec calls. I converted the exec calls to prepare, and exec_prepared, and reran program a 3 times. On average, the version with prepare calls was a bit slower, 1%. I remain stumped.

Martin Velez Over a year ago

@DavidAldridge Yes, I was using different values. However, I realize now that the latency was because they were writes. It masked the latency of preparing. When I do it with reads, I get a noticeable improvement.

|

Collectives™ on Stack Overflow

Example of a prepared INSERT statement using ruby pg gem

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related