From: Michael Litchard <michael@...>
Date: 2008-06-06T12:59:52+09:00
Subject: Re: Newbie question concerning ruby and mysql

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

Well, while waiting for a reply I decided to work out another problem. So,
in this example
imagine the top line has been manually removed.

Here's the code

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

   require "dbi"

   begin
     # connect to the MySQL server
     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) NOT NULL,
                latitude FLOAT,
                longitude FLOAT,
                city CHAR(20),
                state CHAR(20),
                PRIMARY KEY (zip))")
     sth = dbh.prepare("INSERT INTO map
(zip,st_abv,latitude,longitude,city,stat
e) VALUES(?, ?, ?, ?, ?, ?)")

   # read each line from file, split into values, and insert into database
   File.open("zips.csv", "r") do |f|
     f.each_line do |line|
zip,st_abv,latitude,longitude,city,state = line.chomp.split(",")
       sth.execute(zip, st_abv, latitude, longitude, city, state)
     end
  end
     rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code: #{e.err}"
     puts "Error message: #{e.errstr}"
   ensure
     # disconnect from server
     dbh.disconnect if dbh
   end

More important than understanding what is happening here, is understanding
how to find out.
Troubleshooting methodology is what I'm after most of all.


Here's the error

# ./create_db.rb
An error occurred
Error code: 1062
Error message: Duplicate entry '0' for key 1

Michael Litchard

On Thu, Jun 5, 2008 at 6:40 PM, Michael Litchard <michael@schmong.org>
wrote:

> Hi, I'm taking this flat file and putting it into a mysql table.
> The question is, given the code I will be working from, what's the Ruby way
> to skip the first line?
>
> Here's the table
> "zip code", "state abbreviation", "latitude", "longitude", "city", "state"
> "35004", "AL", " 33.606379", " -86.50249", "Moody", "Alabama"
> "35005", "AL", " 33.592585", " -86.95969", "Adamsville", "Alabama"
> "35006", "AL", " 33.451714", " -87.23957", "Adger", "Alabama"
> "35007", "AL", " 33.232422", " -86.80871", "Alabaster", "Alabama"
> "35010", "AL", " 32.903432", " -85.92669", "Alexander City", "Alabama"
> "35014", "AL", " 33.355960", " -86.27720", "Alpine", "Alabama"
> "35016", "AL", " 34.323715", " -86.49278", "Arab", "Alabama"
> "35019", "AL", " 34.292540", " -86.63505", "Baileyton", "Alabama"
> "35020", "AL", " 33.405559", " -86.95141", "Bessemer", "Alabama"
> "35022", "AL", " 33.346817", " -86.95252", "Bessemer", "Alabama"
> "35023", "AL", " 33.443039", " -87.01930", "Bessemer", "Alabama"
> "35031", "AL", " 34.111425", " -86.53380", "Blountsville", "Alabama"
> "35033", "AL", " 33.952939", " -87.02889", "Bremen", "Alabama"
> "35034", "AL", " 32.915182", " -87.21488", "Brent", "Alabama"
> "35035", "AL", " 33.041166", " -86.95117", "Brierfield", "Alabama"
> "35036", "AL", " 33.638150", " -86.91956", "Brookside", "Alabama"
> "35040", "AL", " 33.107572", " -86.74996", "Calera", "Alabama"
> "35042", "AL", " 32.963486", " -87.13867", "Centreville", "Alabama"
> "35043", "AL", " 33.317093", " -86.66295", "Chelsea", "Alabama"
> "35044", "AL", " 33.268471", " -86.35582", "Childersburg", "Alabama"
> "35045", "AL", " 32.834501", " -86.64355", "Clanton", "Alabama"
> "35046", "AL", " 32.894351", " -86.56504", "Clanton", "Alabama"
>
>
> Here's the code I'll be working from
>
> # prepare statement for use within insert loop
>   sth = dbh.prepare("INSERT INTO people (id, name, height) VALUES(?, ?,
> ?)")
>
>   # read each line from file, split into values, and insert into database
>   File.open("people.txt", "r") do |f|
>     f.each_line do |line|
>       name, height = line.chomp.split("\t")
>       sth.execute(nil, name, height)
>     end
>   end
>
>
>
> Thanks guys.
> Michael Litchard
>

------=_Part_6001_13927098.1212724857281--