From: Michael Litchard <michael@...>
Date: 2008-06-09T06:21:13+09:00
Subject: How do I debug mysql syntax problems in ruby code?

------=_Part_13861_15561526.1212960156999
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

So, I have a mysql syntax problem in my ruby code. Being new to both, I'm
not sure how to proceed.
Here's the code.

!/usr/pkg/bin/ruby18 -w
# simple.rb - simple MySQL script using Ruby DBI module

require "dbi"
require "generator"

begin
  lines= Array.new
  dbh = DBI.connect("DBI:mysql:test:localhost","root","")
  dbh.do("DROP TABLE IF EXISTS map")
  dbh.do("CREATE TABLE map (
            zip INT UNSIGNED,
            st_abv CHAR(2),
            latitude FLOAT,
            longitude FLOAT, #experiment
            city CHAR(20),
            state CHAR(20),
            PRIMARY KEY (zip))")
  sth = dbh.prepare("INSERT INTO map (zip,st_abv,lat,long,city,state)
                     VALUES(?, ?, ?, ?, ?, ?)")
  File.open("new.csv", "r") do |f|
    f.each_line do |line|
      lines.push(line.gsub(/\"/, '\''))
    end
  #lines.each {|line| print line }
  end
  lines.each do |line|
    zip,st_abv,lat,long,city,state = line.chomp.split(",")
    print zip,st_abv,lat,long,city,state
    print "\n"
    sth.execute(zip, st_abv, lat, long, city, state)
  end
  sth.finish
    rescue DBI::DatabaseError => e
puts "An error occurred"
    puts "Error code: #{e.err}"
    puts "Error message: #{e.errstr}"
  ensure
  dbh.disconnect if dbh
end


Here's the text file I'm using

"00968", "PR", " 18.408479", " -66.10250", "Guaynabo", "Puerto Rico"
"00969", "PR", " 18.366981", " -66.10889", "Guaynabo", "Puerto Rico"
"00971", "PR", " 18.329688", " -66.11876", "Guaynabo", "Puerto Rico"
"00976", "PR", " 18.346767", " -66.00561", "Trujillo Alto", "Puerto Rico"
"00979", "PR", " 18.431885", " -66.01270", "Carolina", "Puerto Rico"
"00982", "PR", " 18.409345", " -65.99313", "Carolina", "Puerto Rico"
"00983", "PR", " 18.414408", " -65.97582", "Carolina", "Puerto Rico"
"00985", "PR", " 18.374896", " -65.94691", "Carolina", "Puerto Rico"
"00987", "PR", " 18.372228", " -65.96275", "Carolina", "Puerto Rico"
"009HH", "PR", " 18.435287", " -66.06653", "", "Puerto Rico"

and here's the error message I got

$ ./test_slurp.rb
'00968' 'PR' ' 18.408479' ' -66.10250' 'Guaynabo' 'Puerto Rico'
An error occurred
Error code: 1064
Error message: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'long,city,state)
                     VALUES('\'00968\'', ' \'PR\'', ' \' 18.408' at line 1


thanks for your help gang.

------=_Part_13861_15561526.1212960156999--