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

Question

Can you guys help me out with this. If I use this db query directly in the postgresql database it works fine, however when using the PG gem in Ruby it expects me to have 6 values in the array, however I want to get those values from a SELECT statement.

The error message that I'm getting is:

ERROR:  bind message supplies 1 parameters, but prepared statement "statement2" requires 6 (PG::ProtocolViolation)

Here's the code in Ruby using PG gem.

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', ["SELECT client_type, distance_in_ft, hostname, serial_number, -65, 224 FROM test_clients WHERE eth_ipv4 = 192.168.0.1"])

The actual SQL that works looks like this:

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';

Patrick Oscity · Accepted Answer · 2014-06-03 00:02:47Z

1

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

answered Jun 3, 2014 at 0:02

Patrick Oscity

54.9k19 gold badges149 silver badges173 bronze badges

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

1 Comment

sylvian Over a year ago

Thanks, running the simple query with conn.exec worked fine, however when trying the separate conn_prepare options, the error message that I would get was that it would supply 5 parameters instead of 6 even though I checked several times that the parameters were 6. Manually checking the "res" array would show that the '-65' value was missing and I don't understand why.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related