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

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 rray.new
>  dbh  BI.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  bh.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  ine.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.
>

------art_13972_9782661.1212964180772--