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

    On the rubyonwindows site I found the following code:

    db ='c:\Baseball\lahman54.mdb')
    db.opendb.query("SELECT * FROM AllStar WHERE playerID =
    'conceda01';")field_names = db.fields
    rows =
    db.execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")

    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?, &block)
    else do
    Array.multi(*args, &block)

    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. Advertisements

  2. Cooper Deford

    David Mullet Guest

    Assuming you are working with something like the AccessDb class defined
    here... 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});"

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

    David Mullet, Jul 22, 2008
    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 Deford, Jul 22, 2008
    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.