[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

Microsoft Access Automation

Leslie Viljoen

2/20/2006 10:25:00 AM

Hey everyone

I struggled a bit on trying to get data out of an Access database using
Ruby, so I thought I'd share the
results here in case someone else needs this. I found OLEDB automation
to be the easiest method.

Excel Automation is also extremely easy and you can use the macro writer
to create much of the
VBA code that you can then adapt directly to Ruby.

One more thing - getrows returns an array that's "rotated" - I added a
"rotate" routine to my Array
class to sort that out - see the end. If anyone can improve on the
routine, please post! It looks
very much like C right now...

Les

##################################################################
# Recordset with a connection, using JET
# This works, but for some reason the memory requirements are large.
# Using an ODBC source for the same data seems to use almost no memory!

require "win32ole"

conn = WIN32OLE.new("ADODB.Connection")
conn["Provider"] = "Microsoft.Jet.OLEDB.4.0"
conn.Open('c:\ruby\dev\calldata.mdb')

rs = conn.Execute("select date, dialednumber, extension, cost from
callrecords where call = 2 and date >=#01-jan-2005#")
rs.getrows.each do |row|
puts row
end

conn.Close

##################################################################
# Recordset without a connection, using JET
# This works, but for some reason the memory requirements are large.
# Using an ODBC source for the same data seems to use almost no memory!

require "win32ole"

rs = WIN32OLE.new("ADODB.recordset")

qry = "select date, dialednumber, extension, cost from callrecords where
call = 2 and date >=#01-jan-2005#"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\ruby\dev\calldata .mdb'

rs.open(qry,constr)

rs.getrows.each do |row|
puts row
end

##################################################################
# Recordset without connection using an ODBC source
# Program takes about 28MB mem vs. 39MB for the JET version
# The difference is massive when working with a lot of data
# Requires that you create the ODBC data source!

require "win32ole"

rs = WIN32OLE.new("ADODB.recordset")
qry = "select date, dialednumber, extension, cost from callrecords where
call = 2 and date >=#01-jan-2005#"
rs.open(qry,"DSN=calldata;")

rs.getrows.each do |row|
puts row
end


##################################################################
# Here's what I used in my application - an array that can get data from
the database.
# Can you improve on this code, or offer suggestions?
#
class Array
def readMDB(qry,dsn)
rs = WIN32OLE.new("ADODB.recordset")
rs.open(qry,"DSN=#{dsn};")
rs.getrows.each do |row|
self << row
end
end

def rotate
tmp = self.clone
self.clear

j = 0
tmp[0].length.times do
tmp.each_index do |i|
self[j] = [] if not self[j]
self[j][i] = tmp[i][j]
end
j += 1
end
end
end
3 Answers

William James

2/20/2006 11:51:00 AM

0


Leslie Viljoen wrote:
> Hey everyone
>
> I struggled a bit on trying to get data out of an Access database using
> Ruby, so I thought I'd share the
> results here in case someone else needs this. I found OLEDB automation
> to be the easiest method.
>
> Excel Automation is also extremely easy and you can use the macro writer
> to create much of the
> VBA code that you can then adapt directly to Ruby.
>
> One more thing - getrows returns an array that's "rotated" - I added a
> "rotate" routine to my Array
> class to sort that out - see the end. If anyone can improve on the
> routine, please post! It looks
> very much like C right now...

Use Array#transpose.

Leslie Viljoen

2/20/2006 12:27:00 PM

0

William James wrote:

>Leslie Viljoen wrote:
>
>
>>Hey everyone
>>
>>I struggled a bit on trying to get data out of an Access database using
>>Ruby, so I thought I'd share the
>>results here in case someone else needs this. I found OLEDB automation
>>to be the easiest method.
>>
>>Excel Automation is also extremely easy and you can use the macro writer
>>to create much of the
>>VBA code that you can then adapt directly to Ruby.
>>
>>One more thing - getrows returns an array that's "rotated" - I added a
>>"rotate" routine to my Array
>>class to sort that out - see the end. If anyone can improve on the
>>routine, please post! It looks
>>very much like C right now...
>>
>>
>
>Use Array#transpose.
>
>
Good. I really did search and search before writing my own - and there
you go, I just didn't look properly.
I'm so glad I always ask.

Carrie

9/26/2008 11:54:00 PM

0


<suebrd@aol.com> wrote in message
news:363c6c20-dac1-46f7-b695-36c3ce7a0f85@g17g2000prg.googlegroups.com...
On Sep 26, 2:04 pm, "Carrie" <starchild1...@yahoo.com> wrote:
> <sue...@aol.com> wrote in message
>
> news:4ccb7a7f-97a6-4aa9-a46d-201cb2a1080c@v13g2000pro.googlegroups.com...
> On Sep 26, 12:45 pm, Deborah <debo...@60milesanhour.com> wrote:
>
>
>
> > On Fri, 26 Sep 2008 12:21:04 -0700 (PDT), Jasmine <zehno...@gmail.com>
> > wrote:
>
> > >On Sep 26, 1:26 pm, "Carrie" <starchild1...@yahoo.com> wrote:
> > >> You also seem to know what you're talking about and that's
> > >> interesting
> > >> and
> > >> informative.
> > >> Of course for me (and maybe others) at some point I start thinking
> > >> "okay,
> > >> I believe all that, but what can I (or anyone) actually DO and do
> > >> now?"
> > >> That's why I like listening to Bill Clinton, I'm sure he knows what
> > >> is
> > >> going on and what might go on, but he is focusing on NOW and "what
> > >> can
> > >> I/we
> > >> do now". Go in the direction of solutions.- Hide quoted text -
>
> > >> - Show quoted text -
>
> > >Well, if I had a solution other than "honesty is the best policy,"
> > >then I would be running for a local state rep. position. For the now,
> > >i'm only in charge of me and find that it is best to stay honest with
> > >my self first, honest with the rest next. In terms of financial
> > >decisions, I like living within my means and saving up those pretty
> > >pennies for a rainy day. I can't go back to work right now, that
> > >bites, but I have to stay cool and be supportive of my hubby whom
> > >works very hard. it feels uneven, but love, trust and communication
> > >will see us through hard times. Plus, when I re-join the rat race,
> > >I'll be way more aggressive than any one could expect. Carrie, you
> > >know how tough children make a woman; mix in faith and determination
> > >then mothers are unstoppable. :)
>
> > >Voting with common sense seems good advice for the upcoming
> > >elections. Locally, i'm finding that very challenging the choice
> > >between crook number that barely speaks English or crook number two
> > >who speaks it well but clearly is lying (dude blinks way too
> > >much). :)
>
> > >Writing to your reps when you have a concern seems like a good idea,
> > >but how much does that work? I don't know.
>
> > >Clinton is cool, but it is easy to favor him without realizing that he
> > >played his part in all of what is occurring today. Too bad he can not
> > >get another 4 years, it would be worth amending the presidential term
> > >limitation just to have that bad boy back.
>
> > Obama's domestic policies are very similar to Bill Clinton's, so you
> > will be getting Clinton back.
>
> > McCain has totally blown it. Don't know if you stay up late enough to
> > watch Letterman, but McCain stood him up on Wednesday with one hour's
> > notice, making the bullshit excuse that he had to race back to DC to
> > deal with the financial crisis. Dave had to get a couple of emergency
> > guests and improvise on his show. In the middle of it he aired
> > "inside footage" from CBS showing McCain getting makeup applied for an
> > interview with Katie Couric at the same time as the Late Show was
> > taping. Exposed for a liar, big time. Now the McCain campaign is
> > saying that in light of the crisis they didn't think it was the right
> > time for comedy. Eh? Something to stop McCain from telling Dave
> > that? Instead of lying? He didn't return to DC until the following
> > morning.
>
> > Dave has absolutely nothing to lose. He could take it or leave it as
> > far as his job goes. He's going supernova on his show (though you
> > won't see it tonight because Friday's show is actually taped on
> > Monday, Dave only works four days a week.) But for two straight
> > nights now it's been a harange on McCain and will likely continue for
> > the rest of the campaign. Dave said of McCain "He doesn't get how it
> > works: if we don't see you on the Late Show we might not see you on
> > Inauguration Day".
>
> > If McCain knows what's good for him, he'll book an appearance on the
> > Late Show before his campaign is over.
>
> > God can Dave Letterman ever get pissed off. It's delightful to watch.
>
> > Deborah (BC)
>
> Cool! Yeah, nothin' like a pissed off Letterman. It is very
> interesting how with technology these guys/gals are beginning to not
> get away with their lies anymore. Very entertaining.
>
> It's hard to believe they think (thought) they could.
> And yet I still know people (online) who are FOR McCain and Palin and feel
> the democrats will raise taxes and not keep the country (and world) safe.
> They must feel those who show the lies and diversions are lying?
> Of course, in the long run who knows who they will vote for. It's hard for
> people who have been strongly behind and trusting of someone and then
> admit
> they have been wrong.

how they see it is how it is for them?

Yes, a good example of it.
Everyone seems to have their own version of reality and "truth".