Ruby statement with embedded SQL INSERT - Syntax error

Question

I have the following Ruby script: It creates a database, reads a csv file and inserts each row into the database.

 require "sqlite3"
 require "csv"
 require "pp"

begin
 db = SQLite::Database.new("myDB.db")
 db.execute("CREATE TABLE IF NOT EXISTS MYTABLE(Id INTEGER PRIMARY KEY AUTOINCREMENT, 
  stations TEXT, dayparts TEXT, age TEXT, rtg DOUBLE, reach DOUBLE")

 myData  = {}
 CSV.foreach("test_file.csv", :headers=>true, :header_converters => :symbol, :converters => :all)              
 do |row|
  row.to_hash.each do |key, value|
   mydata[key.to_sym] = value
  end
 db.execute("INSERT INTO myDB(stations, dayparts, age, rtg, reach) VALUES(?,?,?,?,?)", 
 myDATA[:stations], myData[:dayparts], myData[:age], myData[:rtg], mydata[:dlyrch000])
 end

rescue SQLite3::Exception => e
  puts "Exception occured"
  puts e

ensure
  db.close if db
end

When I run this script with static data. that is this line:

 db.execute("INSERT INTO myDB(stations, dayparts, age, rtg, reach) VALUES(?,?,?,?,?)", 
   myDATA[:stations], myData[:dayparts], myData[:age], myData[:rtg], mydata[:dlyrch000])

is replaced by this:

 db.execute("INSERT INTO myDB(stations, dayparts, age, rtg, reach) VALUES(?,?,?,?,?)", 
 test, test, 33, .8989, .23434)

A database is created with this data.

But when I try this script as above it throws an exception:

 syntax error, unexpected ",", expecting ')' ... rtg, reach) VALUES (?,?,?,?,?)",  
 ---> myData [:stations], myData[....

etc.

I have tried different options but cannot seem to get around this. Can someone please help me with this

Answer 1

There are three syntax errors I can see.

• The CREATE TABLE SQL statement as a missing close parenthesis before the closing quote. It should look like

  db.execute("CREATE TABLE IF NOT EXISTS MYTABLE(Id INTEGER PRIMARY KEY AUTOINCREMENT,
      stations TEXT, dayparts TEXT, age TEXT, rtg DOUBLE, reach DOUBLE)")
  

• The block for CSV.foreach has to start on the same line as the closing parenthesis for the method call, like this

  CSV.foreach("test_file.csv", :headers => true, :header_converters => :symbol, :converters => :all) do |row|
    ...
  enc
  

• The database constructor uses SQLite when it should be SQLite3. Like this

  db = SQLite3::Database.new("myDB.db")
  

I can't see anything wrong with the part of your code that is raising an error, but I presume you aren't showing the current version of your program as it is a long way from getting as far as that.