Inserting data from an array into an ms access 2000 table

Ruby
1

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.

2

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…

…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

3

David,

Thanks very much for your response. This works just fine as is. I fooled
around quite a bit with join as part of the sql= statement but never
thought to iterate through the array. This is great.

Thanks again,

Cooper