insert array into postgresql map prepared statement

Question

I have this code :

require 'pg'
a = ["bla","foo","bar","test","wow"]

begin
con = PG.connect :dbname => 'base', :user => 'thatsme'

a.map do |e|
    con.prepare('statement1','INSERT INTO keywords (keyword, created_at, updated_at) VALUES ($1, $2, $3)')
    con.exec_params('statement1', ["#{e}", '2017-01-01 07:29:33.096192', '2017-01-01 07:29:33.096192' ])
end

This causes an error, which is

ERROR:  syntax error at or near "statement1"

I don't get it. I'm missing something...

it seems like you should use double quotes with string interpolation: con.exec_params('statement1', ["#{e}", ... — Ilya
– Ilya, Commented Jan 1, 2017 at 9:37
that's true too, thanks ! But that doesn't make the error go away. I still have ERROR: syntax error at or near "statement1" — thiebo
– thiebo, Commented Jan 1, 2017 at 9:49
Things like "#{e}" are concerning and look like Cargo Cult Programming. If you want to be sure e is a string, use `e.to_s, though the driver often does this conversion automatically. Additionally, instead of banging against the low-level driver, try and use something with a bit of abstraction, like Sequel. — tadman
– tadman, Commented Jan 1, 2017 at 10:19
it still causes the same error, whether I do ` con.exec_params('statement1', [e.to_s, ... ` or `"#{e.so_s}", ....`` — thiebo
– thiebo, Commented Jan 1, 2017 at 10:25
What I'm saying is drop the useless quotes. If e is a string then "#{e}" is just noise that distracts from what you're trying to do. — tadman
– tadman, Commented Jan 1, 2017 at 10:26

tadman · Accepted Answer · 2017-01-01 10:54:56Z

2

The exec_params method doesn't take a prepared statement name, that's not a supported argument.

You're probably intending to use the send_query_prepared method which does have that argument.

Here's a refactored version of your code:

a.each_with_index do |r, i|
  con.prepare("statement_#{i}","INSERT INTO keywords (keyword, created_at, updated_at) VALUES ($1, $2, $3)")
  con.exec_prepared("statement_#{i}", [r, '2017-01-01 07:29:33.096192', '2017-01-01 07:29:33.096192' ])
end

You should use each instead of map if you're not concerned with the results, as map creates a temporary array of the rewritten entries. Additionally each_with_index avoids manually manipulating i.

edited Jan 1, 2017 at 10:54

answered Jan 1, 2017 at 10:34

tadman

212k23 gold badges237 silver badges266 bronze badges

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

Comments

thiebo · Accepted Answer · 2017-01-01 10:38:55Z

0

Here's the solution (if this could help someone) :

con = PG.connect :dbname => 'base', :user => 'thatsme'
i = 1;

a.map do |r|
    con.prepare("statement_#{i}","INSERT INTO keywords (keyword, created_at, updated_at) VALUES ($1, $2, $3)")
    con.exec_prepared("statement_#{i}", [r.to_s, '2017-01-01 07:29:33.096192', '2017-01-01 07:29:33.096192' ])
    i += 1
end

answered Jan 1, 2017 at 10:38

thiebo

1,4672 gold badges24 silver badges46 bronze badges

2 Comments

tadman Over a year ago

I've updated my answer to address this and other issues.

julp Over a year ago

You should prepare your query only once, before the loop.

Collectives™ on Stack Overflow

insert array into postgresql map prepared statement

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related