How do I debug mysql syntax problems in ruby code?

[Michael Litchard]

[Note: parts of this message were removed to make it a legal post.]

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:atabaseError => 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.

 

[Michael Litchard]

[Note: parts of this message were removed to make it a legal post.]

I figured this out. I was using a reserved word.