[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 gang.

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

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

On Sun, Jun 8, 2008 at 2:21 PM, Michael Litchard <michael@schmong.org>
wrote:

> 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 gang.
>