MS Access
Steve
wondering if it's possibly to write applications in Ruby that can use and
change the data in my MS Access files.
Thanks for your help.
Steve
Phlip
> I'm considering learning Ruby. I have no programming experience yet. I
> was wondering if it's possibly to write applications in Ruby that can use
> and change the data in my MS Access files.
Absolutely. Database are designed to access a wide warehouse of data through
a narrow interface. Narrow interfaces are easy to port to new languages.
Ruby will see tables of data, just like Visual Basic or C++ will.
Google Ruby DBI DAO or Ruby DBI ADO.
--
Phlip
http://www.greencheese.org/ZeekLand <-- NOT a blog!!!
Brock Weaver
Welcome, fellow rubyist!
It is great that you are choosing ruby as your language to learn
programming. It is a very powerful, robust language. But like the Uncle in
Spiderman says, "With great power comes great responsibility". This means
you may have to take a little longer to learn some things in ruby than you
would in other languages. But in the long run, it will be *well* worth it.
Just be patient, motivated, and stay optimistic. Post questions to this
list, no matter how "easy" you may think they are -- everybody here is very
supportive and prompt.
Good luck, and stick with it man!
--
Brock Weaver
brock...@gmail.com
/* you are not expected to understand this */
Sean Armstrong
>
> > I'm considering learning Ruby. I have no programming experience yet. I
> > was wondering if it's possibly to write applications in Ruby that can
> use
> > and change the data in my MS Access files.
>
> Absolutely. Database are designed to access a wide warehouse of data
> through
> a narrow interface. Narrow interfaces are easy to port to new languages.
> Ruby will see tables of data, just like Visual Basic or C++ will.
>
>
Steve, and myself, is that the documentation for these items are very
limited and not very revealing to newbies. For instance, I could not find a
single code example for connecting to a SQL database, let alone Access, in
the Pickaxe book. So if anyone has any good code examples for manipulating
data in Access or MySQL, I would like to see it here.
Thanks.
SA
--
"I can do everything on my Mac that I could do on a PC."
-- Me
Sascha Ebach
> Does anyone have any good code examples? The hardest thing for people like
> Steve, and myself, is that the documentation for these items are very
> limited and not very revealing to newbies. For instance, I could not find a
> single code example for connecting to a SQL database, let alone Access, in
> the Pickaxe book. So if anyone has any good code examples for manipulating
> data in Access or MySQL, I would like to see it here.
Usually you can ask the great oracle:
http://www.google.com/search?q=ruby+mysql
This revealed:
Using the Ruby MySQL Module
Article describes how to install the Tomita Masahiro's MySQL module and
use it to write MySQL-based Ruby scripts. By Paul DuBois.
www.kitebird.com/articles/ruby-mysql.html - 32k - Cached - Similar pages
I didn't try "ruby access". I will leave that to you. But you might want
to search for "ruby odbc" or "ruby ado" or the like. I am sure you are
going to find something.
Simon Kröger
Ok, this isn't 'good' but short and shows that it is realy easy to
connect to ms access.
-------------------------------------------------------------------
require 'dbi'
DBI.connect("DBI:ODBC:driver=Microsoft Access Driver (*.mdb);"+
"dbq=C:/path/to/my/db.mdb") do |dbh|
dbh.select_all('select * from myTable') {|row| p row}
end
-------------------------------------------------------------------
I guess it's so damm easy, nobody who figured it out writes HOWTO's.
cheers
Simon
Dave Burt
> I'm considering learning Ruby. I have no programming experience yet. I
> was wondering if it's possibly to write applications in Ruby that can
> use
> and change the data in my MS Access files.
Phlip wrote:
> Absolutely. Database are designed to access a wide warehouse of data
> through
> a narrow interface. Narrow interfaces are easy to port to new languages.
> Ruby will see tables of data, just like Visual Basic or C++ will.
Sean Armstrong wrote:
> Does anyone have any good code examples? The hardest thing for people like
> Steve, and myself, is that the documentation for these items are very
> limited and not very revealing to newbies. For instance, I could not find
> a
> single code example for connecting to a SQL database, let alone Access, in
> the Pickaxe book. So if anyone has any good code examples for manipulating
> data in Access or MySQL, I would like to see it here.
If you're not only concerned about the database, or even if you'd like to
use ADO (or DAO) through Access to manage it, I think a better option,
that's about as easy as writing VBA code (but with all the advantages of
Ruby) is using WIN32OLE and Access' COM automation. See
http://www.rubygarden.org/ruby?ScriptingExcel (I thought there was a
ScriptingAccess, but I can't see it...) It's mostly pretty obvious how to
write Ruby code from the VB docs included with Access.
Cheers,
Dave
Sean Armstrong
:)
SA
On 9/15/05, Sascha Ebach <s...@digitale-wertschoepfung.de> wrote:
>
> Sean Armstrong wrote:
> > Does anyone have any good code examples? The hardest thing for people
> like
> > Steve, and myself, is that the documentation for these items are very
> > limited and not very revealing to newbies. For instance, I could not
> find a
> > single code example for connecting to a SQL database, let alone Access,
> in
> > the Pickaxe book. So if anyone has any good code examples for
> manipulating
> > data in Access or MySQL, I would like to see it here.
>
> Usually you can ask the great oracle:
>
> http://www.google.com/search?q=ruby+mysql
>
> This revealed:
>
> Using the Ruby MySQL Module
> Article describes how to install the Tomita Masahiro's MySQL module and
> use it to write MySQL-based Ruby scripts. By Paul DuBois.
> www.kitebird.com/articles/ruby-mysql.html<http://www.kitebird.com/articles/ruby-mysql.html>- 32k - Cached - Similar pages
>
> I didn't try "ruby access". I will leave that to you. But you might want
> to search for "ruby odbc" or "ruby ado" or the like. I am sure you are
> going to find something.
>
>
Sean Armstrong
I installed MySQL for Windows and, although I have cygwin installed, the
library files required for the module are not present.
Thanks:)
SA
On 9/15/05, Sascha Ebach <s...@digitale-wertschoepfung.de> wrote:
>
> Sean Armstrong wrote:
> > Does anyone have any good code examples? The hardest thing for people
> like
> > Steve, and myself, is that the documentation for these items are very
> > limited and not very revealing to newbies. For instance, I could not
> find a
> > single code example for connecting to a SQL database, let alone Access,
> in
> > the Pickaxe book. So if anyone has any good code examples for
> manipulating
> > data in Access or MySQL, I would like to see it here.
>
> Usually you can ask the great oracle:
>
> http://www.google.com/search?q=ruby+mysql
>
> This revealed:
>
> Using the Ruby MySQL Module
> Article describes how to install the Tomita Masahiro's MySQL module and
> use it to write MySQL-based Ruby scripts. By Paul DuBois.
> www.kitebird.com/articles/ruby-mysql.html<http://www.kitebird.com/articles/ruby-mysql.html>- 32k - Cached - Similar pages
>
> I didn't try "ruby access". I will leave that to you. But you might want
> to search for "ruby odbc" or "ruby ado" or the like. I am sure you are
> going to find something.
>
>
Phlip
> If you're not only concerned about the database, or even if you'd like to
> use ADO (or DAO) through Access to manage it, I think a better option,
> that's about as easy as writing VBA code (but with all the advantages of
> Ruby) is using WIN32OLE and Access' COM automation.
Driving Access through COM, essentially operating its user interface to read
and write your data, is remarkably similar to laying your tongue on a
baseball bat, pounding a nail through it, and then hitting a home run with
it.
A good way to use COM the way it was intended is Ruby->WIN32OLE->ADO->any
data. That will come with Jet ("Access") drivers out-of-the-box.
Sean Armstrong
1. Download MySQL source
2. Compile it under Cygwin with the flags you mentioned
3. Remove the bin files?
4. Use the lib file generated from the compilation
5. This will work with the MySQL binarie install for Windows?
Thanks:)
Sean A.
On 9/15/05, Jacob Quinn Shenker <jqsh...@gmail.com> wrote:
>
> Sean,
> I needed to compile/install mysql (running ./configure
> --without-server) from source to get the required development
> libraries under Cygwin. (then I moved the newly-created client
> binaries out of the way so I could use the Win32-native mysql
> binaries.) After that, it worked like a charm. *Do not compile the
> Cygwin-ized mysql client with "--with-openssl"* I don't know why, but
> the gem refused to install if I did. Good luck, and let me know if you
> run into any issues. Overall, developing on Cygwin for Ruby/Rails is
> quite nice.
Jacob Quinn Shenker
After that, everything should be happy happy happy (you'll get my
exact setup, and it does work for me). If any step fails or is
confusing, I'll either try to help you or just send you my binaries.
Hope it works!
Jacob
Gavin Kistner
> Does anyone have any good code examples? The hardest thing for
> people like
> Steve, and myself, is that the documentation for these items are very
> limited and not very revealing to newbies. For instance, I could
> not find a
> single code example for connecting to a SQL database, let alone
> Access, in
> the Pickaxe book. So if anyone has any good code examples for
> manipulating
> data in Access or MySQL, I would like to see it here.
Following is a sanitized example of code I use at work to connect
both to an intranet-local SQL server, and also to a remote SQL server
on our web host over the 'net.
For both, I created a system ODBC DSN,which is what 'VerneCSR' and
'LiveServer' are.
DB_LOGIN = $test_db_flag ? [ 'dbi:ODBC:VerneCSR' ] :
[ 'dbi:ODBC:LiveServer', 'username_removed', 'password_removed' ]
puts "Working on the #{ $test_db_flag ? 'TEST' : 'LIVE' } database"
begin
require 'dbi'
dbh = DBI.connect( *DB_LOGIN )
# Ensure that the category exists, and get the ID
CATEGORY_NAME = 'Latest Behaviors'
row = dbh.select_one( 'SELECT acID FROM tblAssCategory WHERE
acName=?', CATEGORY_NAME )
if row
SCRIPT_REFERENCE_CATEGORY_ID = row[ 0 ]
else
dbh.do( 'INSERT INTO tblAssCategory (acName) VALUES (?)',
CATEGORY_NAME )
SCRIPT_REFERENCE_CATEGORY_ID = dbh.select_one( 'SELECT CAST
(@@IDENTITY AS int)' )[ 0 ]
end
#Prepare some SQL statements for speedier re-use
get_article = dbh.prepare( <<-ENDSQL
SELECT aID, aSummary, aBody, date_updated
FROM tblAssItems
WHERE acID=#{SCRIPT_REFERENCE_CATEGORY_ID} AND download_files=?
AND aTitle=?
ENDSQL
)
add_article = dbh.prepare( <<-ENDSQL
INSERT INTO tblAssItems
(author,aTitle,aSummary,aBody,download_files,acID,time_to_complete,diffi
culty,thumbnail,status)
VALUES (?,?,?,?,?,#{SCRIPT_REFERENCE_CATEGORY_ID},'5
Minutes','(Reference)','#{THUMBNAIL_URL}','public')
ENDSQL
)
update_article = dbh.prepare( <<-ENDSQL
UPDATE tblAssItems
SET
author=?,
aSummary=?,
aBody=?,
date_updated=#{Time.new.to_sql}
WHERE aID=?
ENDSQL
)
require 'erb'
Behavior.all.each_with_index{ |bvs,i|
#Check to see if the item should be added or updated in the DB
#(Don't just shove the current content, because that would
invalidate 'date_updated')
existing_article = get_article.execute( download_path, title )
rows = get_article.fetch_all
changed_db_flag = false
if rows.empty?
#Couldn't find an existing article for the behavior, time to
add a new one
puts "Adding article entry '#{title}'" if $verbose_flag
body.sub!( '%%CURRENT_TIME%%', Time.new.to_pretty )
add_article.execute( author, title, summary, body,
download_path )
changed_db_flag = true
$add_count += 1
else
existing_info = rows[ 0 ]
old_body = body.sub( '%%CURRENT_TIME%%', existing_info
[ 'date_updated' ].to_time.to_pretty )
if ( existing_info[ 'aSummary' ] != summary ) ||
( existing_info[ 'aBody' ] != old_body )
#Time to update the entry
body.sub!( '%%CURRENT_TIME%%', Time.new.to_pretty )
puts "Updating article entry '#{title}'" if $verbose_flag
update_article.execute( author, summary, body, existing_info
[ "aID" ] )
changed_db_flag = true
$update_count += 1
end
end
}
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
#close out statements
get_article.finish if get_article
add_article.finish if add_article
update_article.finish if update_article
#unplug from the database
dbh.disconnect if dbh
end #db safety
Derek O'Connell
once you find the right info (DBI). You may want to search for OG (Object
Graphing) which maps db's to objects and free's you from most/all SQL. So
far I'm impressed as hell with the code reduction by converting to Ruby.
Now if only we could get these CS geeks to inject some "Newby Tips" when
posting their hyper-meta-abstract creations ;-)
Dave Burt
> Driving Access through COM, essentially operating its user interface to
> read and write your data, is remarkably similar to laying your tongue on a
> baseball bat, pounding a nail through it, and then hitting a home run with
> it.
>
> A good way to use COM the way it was intended is Ruby->WIN32OLE->ADO->any
> data. That will come with Jet ("Access") drivers out-of-the-box.
You're right if all you care about is a nicely designed database. I haven't
seen too many of those; far more common in my experience is a mess of
junk-ridden tables with some forms that try to make them make some kind of
sense. It's like using Watir to drive IE because your web app is chock-full
of javascript and you're not just playing with a nice bit of XHTML.
Although I guess it's still not all that dissimilar to your baseball
analogy.
Cheers,
Dave
Sean Armstrong
--with flags. Any other ideas
Thanks:)
SA
Sean Armstrong
compile mysql-ruby with the --with-mysql-config flag because the gme did not
fully install. I can now use require 'mysql' and get a true result. My new
problem deals with the way MySQL is installed on my windows box. I did not
install it as a server. I just installed it locally. I use the -h . flag to
connect to my MySQL installation. It is not an option to install it as a
server. So I am now currently unable to connect to mysql with the mysql
module because it will not accept "localhost" as teh server string and it
will not accept "." as the server string like everything else does. Any new
ideas?
Thanks:)
SA
On 9/16/05, Jacob Quinn Shenker <jqsh...@gmail.com> wrote:
>
> Sean,
> I'm going to try to explain *exactly* what I did, and hopefully you'll
> see something you forgot to do.
> 1. Download mysql-essential-4.1.14-win32.msi from mysql.org<http://mysql.org>and install it.
> 2. Download mysql-4.1.13.tar.gz from mysql.org <http://mysql.org>
> 3. Extract the above, and run "./configure -C --without-server" (the
> -C enables config caching, I use it because the ./configure script
> runs very slowly under Cygwin. Optional, of course)
> 4. Run "make && make install"
> 5. Run "gem install mysql"
> 6. Go make cool rails apps!
>
> Jacob
Linus Sellberg
>
> server. So I am now currently unable to connect to mysql with the mysql
> module because it will not accept "localhost" as teh server string and it
> will not accept "." as the server string like everything else does. Any
> new
> ideas?
>
127.0.0.1 <http://127.0.0.1> ?
Sean Armstrong
Thanks:)
SA
> 127.0.0.1 <http://127.0.0.1> <http://127.0.0.1> ?
Jacob Quinn Shenker
Let me get this straight. The problem is when you installed MySQL for
Windows from the .msi installer, you didn't install the server. (So in
the wizard setp with all the white hard-drive icons, you selected th
install the client programs and possibly the development files. Not
the "MySQL Server") You needed to select the "Server" to get a running
MySQL installation. When you say "that is not an option", why? (Did
the installer not ask you or did you not want to because of
security/other reasons?) If you did indeed select to install the
server, a Database Cluster Initialization Wizard should have popped up
sometime during the install. Oh, and Linus is right in that "mysql -h"
doesn't accept localhost, use 127.0.0.1 instead. Once the server is
installed, "net start mysql" to make sure it is running. You're
getting really close, so hang on!
Jacob
Dave Burt
> Well you have got me the closest so far. I had to go back and manually
> compile mysql-ruby with the --with-mysql-config flag because the gme did
> not
Have you tried using MyODBC with Ruby-DBI via the ODBC DBD? It was mentioned
in another thread. I think you'd have to add an ODBC data source as an extra
step (rather than just specifying the host/user/database), but it should
work on Windows without having to compile anything against particular
versions, which seems to be where these things tend to fall over.
Cheers,
Dave