How to translate the following sqlite3 insert statement into equivalent mysql insert statement

Ruby
1

sqlite3 insert statement:
db.execute “INSERT INTO events(#{keys.join(’,’)}) VALUES (#{([’?’] *
keys.size).join(’,’)})”, *values

I’m very confused about this part:
(#{([’?’] * keys.size).join(’,’)})", *values

how can I write a mysql insert statement that functions the same?

Thanks a lot!

2

On Thu, Feb 28, 2013 at 6:08 PM, Yu W. [email protected] wrote:

sqlite3 insert statement:
db.execute “INSERT INTO events(#{keys.join(‘,’)}) VALUES (#{([‘?’] *
keys.size).join(‘,’)})”, *values

I’m very confused about this part:
(#{([‘?’] * keys.size).join(‘,’)})", *values

It’s basically creating a string with a certain number of question
marks, and then supplying values at the end to be substituted for
those question marks. E.g. if keys is [‘foo’ ‘bar’ ‘baz’] and values
is [123, 456, 789], it will generate the command (with placeholders)
‘INSERT INTO events(foo, bar, baz) VALUES (?, ?, ?)’; db.execute then
plugs in 123, 456, and 789 into the question mark slots.

how can I write a mysql insert statement that functions the same?

Have you tried it in MySQL? It looks like it should work just the same
in pretty much any SQL engine.

3

Thanks a lot for reply! I’ve tried mysql gem. But it doesn’t allow
expression like this
“(#{([’?’] * keys.size).join(’,’)})”, *values"

it says mysql query method only allows one parameter.

4

On Thu, Feb 28, 2013 at 7:18 PM, Yu W. [email protected] wrote:

Thanks a lot for reply! I’ve tried mysql gem. But it doesn’t allow
expression like this
“(#{([‘?’] * keys.size).join(‘,’)})”, *values"

it says mysql query method only allows one parameter.

Oh, sorry. It’s been a while since I did this, but I believe you have
to call Mysql#prepare on the string containing the ‘?’ placeholders,
which returns a Mysql::Stmt object. Call #execute on that object with
the *values. That’s how it works with the Mysql2 gem, anyway; see
http://zetcode.com/db/mysqlrubytutorial/ .

5

Thanks a lot! This is exactly what I need. Really appreciate it.