How to connect to an MS Access database using Ruby and ODBC

If you search for an answer to this question in Google, you will get mostly sarcastic answers and if you're lucky maybe some help.

You must first download Ruby ODBC from Christian Werner's site and copy odbc.so and odbc_utf8.so into the ...\ruby\1.8\i386-msvcrt directory where you installed Ruby. Uf you have installed InstantRails in default location, it should be under C:\InstantRails\ruby\lib\ruby\1.8\i386-mswin32

In the example below I connect to an Access file (c:\ruby.mdb) and print the contents of a table (Table1)

require 'DBI'

access_dsn_base = 'DBI:ODBC:Driver=Microsoft Access Driver (*.mdb);DBQ='
access_file = 'C:\ruby.mdb'

DBI.connect(access_dsn_base+access_file) do |dbh|
  dbh.select_all('Select * From Table1') {|row| p row}
end

Similarly, you can use DBI:ODBC to read MS Excel files.
In the example below I connect to an Excel file (c:\my_file.xls) and print the contents of a spreadsheet (Sheet1)

require 'DBI'

excel_dsn_base = 'DBI:ODBC:Driver={Microsoft Excel Driver (*.xls)};DBQ='
excel_file = 'C:\my_file.xls'

DBI.connect(excel_dsn_base+excel_file) do |dbh|
  dbh.select_all('Select * From [Sheet1$]') {|row| p row}
end

My configuration is Windows XP + MS Access 2000 + MS Excel 2002 + Ruby 1.8.6 + Ruby ODBC 0.9995

WebLogic 9.2 is not Maven 2 friendly

Using Maven 2 is more difficult when you use a platform like BEA WebLogic 9.2 because of their "split directory" convention which creates a gap with Maven's own conventions.

A first approach could be to avoid using the split directory structure, however it's not that easy because it comes with a set of specific ant tasks to pre-compile your JSPs, generate a web service from an EJB, package an ear file, ...
These tasks are not open sourced and are too high level to be easily integrated into Maven: for instance, if you want to pre-compile your JSPs, you cannot use anymore jspc because it has been deprecated in favor of appc which depends on the split directory structure.
This makes almost impossible to write an equivalent Maven plugin for a 3rd party, only BEA could do it.

Another issue with these tasks is that they are defined in weblogic.jar: a 50 MB jar which contains a lot of stuff that may collides with dependencies in your classpath, especially because BEA chose to specify a relative classpath in its jar manifest.

An alternative is to use the WebLogic ant tasks from Maven using the antrun plugin, unfortunately this plugin is poorly documented and does not offer easy options to configure a classpath to integrate weblogic.jar. Also, antrun retrieves its own version of ant from Maven's repository that may collide with the one required by WebLogic's tasks; you can find workarounds for this but then you will have to trick Maven to make it think that it has generated the artifact generated by appc.

So, unless BEA endorses Maven and provides its own plugins, it will be difficult to use Maven for a non trivial WebLogic application. My next step will be to try to use ant and Maven's antlib in order to be able to use Maven's dependency management.

P.S.: I just found Hussein Badakhchani's excellent post about maven and appc, I may try his approach as well.