Inserting data from an array into an ms access 2000 table

Discussion in 'Ruby' started by Cooper Deford, Jul 20, 2008.

  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.
     
    Cooper Deford, Jul 20, 2008
    #1
    1. Advertisements

  2. Cooper Deford

    David Mullet Guest

    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
     
    David Mullet, Jul 22, 2008
    #2
    1. Advertisements

  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
     
    Cooper Deford, Jul 22, 2008
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.