From: David Mullet <david.mullet@...>
Date: 2008-07-22T11:19:10+09:00
Subject: Re: Inserting data from an array into an ms access 2000 table

Cooper Deford wrote:
> Good Afternoon,
> 
> I am trying to figure out how to get data from an array into an access
> table.
> 
> On the rubyonwindows site I found the following code:
> 
> db = AccessDb.new('c:\Baseball\lahman54.mdb')
> db.opendb.query("SELECT * FROM AllStar WHERE playerID =
> 'conceda01';")field_names = db.fields
> rows = db.data
> db.execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")
> db.close
> 
> This code works fine if I break the array up into seperate elements but
> I think there must be a simpler way.
> 
> this is how the array is built:
> 
>     class <<Array
>           def multi(n, *args, &block)
>                   if args.empty?
>                     Array.new(n, &block)
>                           else
>                             Array.new(n) do
>                             Array.multi(*args, &block)
>                           end
>                   end
>           end
>     end
> card=Array.multi(24,24)
> 
> 
> so the question is; How do i get the data loaded into this array into my
> access table called wps in a database wps.mdb?
> 
> I have been trying to do something like this:
> 
> db.execute("INSERT INTO wps_test VALUES('card [$h]');"
> 
> Any help would be greatly appreciated.

Assuming you are working with something like the AccessDb class defined 
here...

http://rubyonwindows.blogspot.com/2007/06/using-ruby-ado-to-work-with-ms-access.html

...you could iterate over your array and generate/execute an SQL insert 
statement, like this:

my_array.each do |a|
    b = a.collect{|x| x = "'" + x + "'"}
    c = b.join(",")
    sql = "INSERT INTO MyTable VALUES (#{c});"
    db.execute(sql)
end

The above code can certainly be improved upon, but hopefully gives you 
an idea.

David

http://rubyonwindows.blogspot.com
-- 
Posted via http://www.ruby-forum.com/.