Remove square brackets from array?

Question

I have an array that I'm trying to load into a SQL statement. When I compile the SQL statement and include the array, it also puts the [] brackets into the string, and I need to remove them in order to insert it into my DB.

Here's my code:

i = 0

while i < rows.length
     sql = "INSERT INTO my_table VALUES(#{rows[i]})"
     puts sql
end

Current output:

INSERT INTO my_table VALUES(["value 1", "value 2", "value 3"])

Desired output:

INSERT INTO my_table VALUES("value 1", "value 2", "value 3")

Answer 1

Use inspect and join:

sql = "INSERT INTO my_table VALUES(#{rows[i].map{|x| x.inspect}.join(', ')})"

• map calls a block on each element of an array, then sets that element to the return value of the block
• inspect turns a string into its representation (like "the string" (with quotation marks around it))
• join puts them all together with a delimiter in between

Answer 2

Here's what you're seeing:

ary = ['value 1', 'value 2', 'value 3']
"#{ ary }" # => "[\"value 1\", \"value 2\", \"value 3\"]"

which is the "inspected" output, caused by using Array.to_s AKA Array.inspect. Looking at it like we'd normally see it:

puts "#{ ary }"
# >> ["value 1", "value 2", "value 3"]

And here's how to fix it:

sql = "INSERT INTO my_table VALUES(#{ ary.map{ |i| '"%s"' % i }.join(', ') })" 
# => "INSERT INTO my_table VALUES(\"value 1\", \"value 2\", \"value 3\")"

puts sql
# >> INSERT INTO my_table VALUES("value 1", "value 2", "value 3")

'"%s"' % i is a String format that wraps a string in double-quotes.

That all said, the proper way to communicate with a database these days is to use a decent ORM. Ruby has several of them, of which I favor Sequel. The major win for an ORM is it removes the close dependency of your code to the language used by the database. Read through the list of databases it supports, without needing to change anything in your code beyond the DSN needed to connect.

Sequel currently has adapters for ADO, Amalgalite, CUBRID, DataObjects, DB2, DBI, Firebird, IBM_DB, Informix, JDBC, MySQL, Mysql2, ODBC, OpenBase, Oracle, PostgreSQL, SQLAnywhere, SQLite3, Swift, and TinyTDS.

Notice, TinyTDS is listed.

Migrations work, queries work, inserts, deletes, updates, everything is abstracted, which is a huge win as you migrate to bigger/other systems. You can start with SQLite locally, use MySQL or PostgreSQL, then switch to Oracle without code changes, just tweak the DSN.

Read through the first page of the site, along with the README, and you'll get an idea of how easy it is to use, and wonder why you've done it any other way.