From: Todd Benson <caduceass@...>
Date: 2007-07-22T21:49:10+09:00
Subject: Re: first ruby script mysql data input

On 7/21/07, PB0711 <hpbenton@gmail.com> wrote:
> Hello all,
>
> I'm trying to use ruby for the first time. I want to make a script
> that is going to parse through my file and pick stuff up and place it
> into a database. When I put it into the database I would like to first
> ask the database if there is something already like it and if there is
> an "empty" primary key to use.
> The key ID's in the database go something like:

I think you meant the key ID's in a table.

> 1 2 3 5 6 7 8 10 11 12 18 22 23 24 and so on. I want to put stuff into
> ID 4, 9, 13 and so on. I was wondering what modules I should use and
> how to go about this.
> I come from Perl and I'm pretty use to doing this type of thing in
> Perl, expect for asking what ID is "free",
>
> So just ideas of how to do it and what tools you all think I will
> need

I've never used the mysql gem, but it might look something like this
after you successfully connect to the database:

ids = []
res = db.query( "select id from mytable")
while row = res.fetch_row do
  ids << row[0]
end
res.free
ids.map! { |i| i.to_i } # do this just in case mysql doesn't return integers
# at this point you have an array of integers, how you get here is up to you
# now, for the ruby chocolate ...
free_ids = (1..ids.max).to_a - ids

There are other gems for mysql access (activerecord, sequel, and I
think there's an odbc one).

hth,
Todd