Freelancing Gods 2014

God
23 Feb 2008

Talking to MYOB with Ruby

Okay, time for another code post. Prompted by a comment on an earlier post about Merb and MYOB, I thought I’d provide some more detail on how to talk to MYOB using Ruby, for any other people stuck in a similar position.

ODBC Bindings

If you like crafting your own SQL, this is the easiest approach. Firstly, you’ll need to download Christian Werner’s ODBC Bindings for Ruby, and install it using the following commands (or something along these lines) from within the source directory:

ruby extconf.rb
make
sudo make install

Documentation is a bit light on examples – and it’s really just a hook into the C libraries for ODBC, so good luck reading the source. Here’s some of the basic things you’ll need to do. (Don’t forget to require 'odbc' first, of course).

Setting up a Connection

You can either go through the DSN collection, or connect using the string name of the DSN (which in this case we’ll assume is ‘MYOB’):

dsn = ODBC.datasources.detect { |source| source.name == "MYOB" }
database = ODBC::Database.connect dsn

# or

database = ODBC::Database.connect "MYOB"

Note that the ODBC.datasources collection only contains system DSNs, not User-level ones. Not sure why, or how to access the latter.

SELECT Statements

statement = database.prepare "SELECT * FROM Customers"
statement.execute
statement.each_hash d |row|
  # results accessed as row["column_name"]
end
statement.drop

Make sure you drop the statement once you’re done with it, otherwise you’ll get complaints when you stop ruby that the statements weren’t all closed and dropped.

There’s other ways to access results – you can go through results as you see fit using fetch or fetch_hash, depending on whether you want an array or hash of the record.

INSERT Statements

With other SQL statements (although there’s no use of UPDATE or DEETE in MYOB connections), you don’t really care about the results, so that makes things a bit simpler, you can just call database.do("INSERT INTO Import_Customer_Cards (...) VALUES (...)"). The do method creates the statement, executes it and then drops it for you. MYOB makes this a little more complex though, as it all must be done in transactions – not that that’s a bad thing.

database.transaction do |env|
  env.do("INSERT INTO Import_Customer_Cards (...) VALUES (...)")
end

This won’t actually do anything though – and it won’t throw up an error or warning. The problem is you need to turn autocommit mode off, because the MYOB ODBC drivers don’t like it (it’s not a problem with Christian’s bindings). So, back when you create your connection:

database = ODBC::Database.connect "MYOB"
database.autocommit = false

ActiveRecord

If you prefer some level of abstraction above the messy SQL, then it might be worth looking at OpenLink’s ActiveRecord ODBCAdapter. I recommend the gem instead of the plugin, as the plugin takes it upon itself to modify your code, which I don’t like.

sudo gem install odbc-rails

Now, I’m not an ODBC expert, but there seems to be different flavours of ODBC connections possible – and MYOB’s is not one supported by this gem. So, if you’re using Rails, put the gem into vendor (and for Merb, into the local gems folder), then modify it with this patch. I make no promises for it being stable or reliable – but I’ve not had any problems yet.

Of course, using ActiveRecord is viable if you’re just reading data out – but if you want to write as well as read, then there’s issues. MYOB has separate write tables (prefixed with ‘Import_’), and they have denormalised schemas compared to the read-equivalents.

For database.yml, you’ll need something like the following:

development:
  adapter: odbc
  dsn: MYOB

Everything Else

In both the situations above, I’ve not put usernames or passwords into the connections – you can, but that is already handled by the ODBC DSN, so I keep my code that bit simpler.

For those who are new to coding for MYOB – it does cost money to get a developer account (several hundred dollars per year), which is the only way to get write access. I think read access is a once off fee of a couple of hundred dollars, but that’s specific to a MYOB file.

And if anyone is wondering – while I was initially using the odbc-rails library, I’ve now switched to constructing the SQL myself and just using the bindings, because of the table issues.

Comments

9 responses to this article

23 Feb 2008
Xavier Shay said:

Sweet, I can see this coming in handy

21 Apr 2008
Mike Aldred said:

I’m currently interested in getting a work Rails app integrated with MYOB for our accounts, and I’ve got my developer licence (just waiting on things to arrive).

I do have a question though, you using an ODBC-ODBC bridge or something? Doesn’t the MYOB ODBC driver only run under Windows?

Cheers

21 Apr 2008
pat said:

Hi Mike

Yes, MYOB’s driver does only work on Windows. There are bridges around – but none that were free (or even trial versions) that work on 10.5, my development platform of choice.

The project I did a lot of MYOB/Ruby work for involved a Windows server with the ODBC Driver and some generic ruby code that parsed XML provided from a Rails app on a *nix machine. Testing the code was not particularly fun, involving Parallels and Cygwin.

23 Apr 2008
Mike Aldred said:

Ahh, cool.

I was wondering about all the sudo commands.
I’ve got a Solaris server that’s going to host the database and web front end, and I’ll probably write up a little ruby app for the Windows end.

I’ll do a post of my efforts here sometime, just to help anyone else thinking of the same thing.

Cheers

23 Apr 2008
pat said:

Mike, would be great to see a write-up on how you go about it – always good to see some different approaches.

24 Apr 2008
Mike Aldred said:

Sorry to bother you again Pat.

The link to the diff seems to be broken. I’m assuming you’re just adding openrda to the dbmsNameToSym function in odbc_adapter.rb?

Cheers

24 Apr 2008
pat said:

Hi Mike – looks like I never tested that link. The file wasn’t named properly and I mispelt my own domain. Sorry about that.

And yeah, I wasn’t doing much more than adding openrda as a recognised dbms type.

13 Nov 2008
Faber said:

Sori for my newbie question :D

After buying MYOB’s license and installing it, can we customize it freely? Without having to buy another license or the like?

Thanks

13 Nov 2008
pat said:

Hi Faber

I’m afraid I’m not a MYOB expert, so I can’t really help you with that. Best to ask MYOB support (or on their forums).

Leave a Comment

Comments are formatted using Textile. Please be respectful of others when posting comments. Be nice.

RssSubscribe to the RSS feed

Related Links

Related Posts

About Freelancing Gods

Freelancing Gods is written by , who works on the web as a web developer in Melbourne, Australia, specialising in Ruby on Rails.

In case you're wondering what the likely content here will be about (besides code), keep in mind that Pat is passionate about the internet, music, politics, comedy, bringing people together, and making a difference. And pancakes.

His ego isn't as bad as you may think. Honest.

Here's more than you ever wanted to know.

Ruby on Rails Projects

Other Sites

Creative Commons Logo All original content on this site is available through a Creative Commons by-nc-sa licence.