luminousmonkey.org

MYOB ODBC with Ruby

Monday 10 August 2009, 16:54 WST —

I have previously written on how I was working on automating the invoice system between the sales database I wrote and MYOB.  I do have something written up, however it’s not code that I would want anyone to see, because I know it’s not high quality.

Anyway, the statements MYOB prints, are, in a word, crap. So I wanted to write a system that would take the information out of MYOB and generate my own format of statements for either mailing or e-mailing.

Now the problem with MYOB and Rails, or to be correct, the problem with MYOB and ActiveRecord is that MYOB is not a vaild SQL database.  This problem is compounded by the fact that ActiveRecord expects the database schema to be a specific way (this can be changed). ActiveRecord can be nice to work with, however there are cases where it can be a bit of a pain, and this is one of those.

I’ve had a little play with Sinatra and Sequel and this looked like a good chance to give Sequel a try. The statement generator I’m writing is just something to run, once or twice a month, so it doesn’t need a web interface.

JRuby is currently my Ruby VM of choice, because I’ve found the Java integration very useful for printing and faxing for our internal sales application, so I’ve stuck with that, which, when it comes to MYOB is a good thing.  Why?

As mentioned in my previous post, I had found information on getting MYOB and Rails/Activerecord talking to each other at Pat Allan’s site, Freelancing Gods.  The problem is the Rails ODBC driver doesn’t support MYOB (and I’m sure MYOB doesn’t have a 100% correct implementation), so you have to patch some files to get support.

However, JRuby allows you to use JDBC, so I figured that I could use the Java JDBC to connect to the MYOB ODBC driver.  It works, with no patching involved.

Ok, enough of the length explanation, how does it work?

First, get and install JRuby.

At the moment I’m using JRuby1.3.1, and I download the jruby-bin-1.3.1.zip file.  I’m going to assume you know how to get JRuby installed, and in your command path.

With that done, start up your command shell (I use Windows Powershell), and install the following gems:

• jruby-openssl
• sequel

That’s it! Next, you need to set up your MYOB ODBC datasource, again, I’m going to assume if you’re looking up this information then you know how.  There’s one thing however, if, like me, you’re using the 64bit version of Windows Vista, you need to run:

C:\Windows\SysWOW64\odbcad32.exe

Otherwise it will only list the MS SQL OSBC drivers.

With that done, we can then load up jirb and play with the database. Or you can write the following file and run it.

require 'rubygems'
require 'sequel'

#Setup the database connection
DB = Sequel.connect( 'jdbc:odbc:MYOBTest' )

customer = DB[:customers].all[0]

puts customer[:name]

DB.disconnect

Don’t forget the disconnect, otherwise JRuby will crash when you exit.

If you’re familar with Sequel, you might wonder why I do the bad thing, and go:

DB[:customers].all[0]

rather than:

DB[:customers].first

This problem is that the MYOB ODBC driver will complain about the SQL statement generated, best I can guess is that it doesn’t support the SQL option LIMIT.

Anyway, that’s it for now.