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.

11 Feb 2008

Link: smartbomb I love me some source

"As good as this is, I was really after something with less typing and smarter pants. Well tonight I cracked it and I thought I’d open the source"

22 Jan 2008

Bring Methods Back From The Dead

Tags:

Today was the first time I’d come across Ruby’s undef_method – it’s used in a few places in Rails, particularly with ActiveRecord’s associations. While I see the point of it, there were a few methods I wanted back – and I’ve figured out how to do it – you need to grab the method definition from the superclass. Here’s an example:

class AntiString
  undef_method to_s
end

AntiString.new.to_s
  #=> NameError: undefined method `to_s' for class `AntiString'

AntiString.send(:define_method, :to_s,
  AntiString.superclass.instance_method(:to_s))

AntiString.new.to_s #=> "#<AntiString:0x364ef8>"

Now, the obvious caveat – if the method was originally defined in that class, not the superclass, then I think you’re out of luck. Although I’m guessing you’ll rarely be in a position where you need to resurrect a method like this anyway.

21 Jan 2008

Mixing Merb and MYOB

For one of the contracts I’m working on at the moment, I’ve been using Merb to construct a web service that interacts with MYOB, and can be consumed with ActiveResource.

The connection to MYOB is ugly, using Christian Werner’s ODBC Bindings and the Rails/ActiveRecord ODBC Adapter, the latter of which had to be hacked slightly. However, the Merb side of things was quite clean. I’m really looking forward to seeing how Merb progresses, especially with their plans for merb_core and merb_more.

One of the rare snippets of code from the Merb app that I think is more verbose than the Rails equivalent is how to go about obtaining the query parameters (as opposed to routing parameters) of a request.

The Rails way:

request.query_parameters

The Merb way:

params.except *request.route_params.keys.collect { |key| key.to_s }

Also, in case you’re as stupid as I am and want to generate Merb controllers on the fly, you can’t use Class.new. The only way is by building the class in a string and eval’ing it:

Object.send(:eval, <<-CODE
class ::Object::#{controller_name} < Application
  # actions and such go here
end
CODE
)

It’s not particularly elegant, but at least it works.

17 Jan 2008

Sphinx 0.9.8r1065

Short post, as befitting the importance of the content: Riddle and Thinking Sphinx have both been updated to support the current version of Sphinx, 0.9.8r1065.

07 Jan 2008

Link: Le-Blog-à-Dam - Page Cache Test - Rails Cache Test Plugin

If I get some spare time, this is something that would be nice to adapt to rspec

01 Jan 2008

2007

Sinfest comic for New Year's Day

I don’t want to bore you all with an extensive recap of 2007, so I’ll keep this footnote of the last year’s highlights relatively brief.

Nullus Anxietas

After a few years planning, we produced the first Australian (and non-UK) Discworld Convention in February – and it was a smashing success. A few hundred attendees, dozens of sessions, a load of fun. We even made a small profit (which is rare for fan conventions) – and we proved the doubters wrong.

Rails and Freelancing

I began the year by switching jobs and finally getting paid to work with Ruby on Rails. Halfway through the year, I started freelancing. I’m really enjoying working from home, on my Mac, using tools and a programming language I enjoy. After far too long wrangling with ASP and ASP.NET, coding is fun again.

RailsCamps

As part of working with Rails, I’ve become involved in the local Ruby communities here in Australia. Through this, there’s been two awesome RailsCamps (and massive props to Ben Askins for leading the way with the first, and helping so generously with the second), and I’ve met a bunch of smart, friendly folk. Networking has become socialising.

Change

The last of my siblings has finished their secondary schooling. My sister’s moving to another state. I’m posting regularly to this blog. Howard’s out – and not much longer to put up with Bush. Climate change is being taken seriously by many governments. There will be a proper apology to the indigenous people of Australia.

What’s next?

For me, 2008 is looking to be a year of travel – to New Zealand for a holiday in a few weeks, and then to Portland for RailsConf, UK and Cambodia to visit friends (and in the case of the former, check out the Edinburgh Fringe Festival), and stops to New York and Istanbul will likely feature in there as well.

Freelancing will continue to be challenging as my first big contract ends and I look for new ones, and there’s already plans for more RailsCamps and a second Discworld Convention. I’m looking forward to all of it.

Endless thanks to my family, friends and peers for their support over the past twelve months – you’re all brilliant.

27 Dec 2007

Updates for Sphinx 0.9.8r985

Another quick Sphinx post – Riddle is updated to support Sphinx’s latest release (0.9.8r985), and Thinking Sphinx now has that new version of Riddle as well.

I’ve not tested any of this with the recently released Ruby 1.9 yet, though (but it’s on my list of things to do).

Also, thank-you to Joost Hietbrink (again) and Jonathan Conway for their patches to Thinking Sphinx – very much appreciated.

13 Dec 2007

Rspec'ing controllers

I’m always trying to find a better way to write specs for my Rails apps with RSpec – it took a while for me to be comfortable with writing model specs, but just recently, I’ve developed a style of controller specs that I feel work well for me.

While it’s not too hard to write methods that automate some of the repetitive side of things, it can be hard to do so in a manner that fits RSpec’s DSL – but I’ve found the key to (my current style of) controller specs is shared behaviours. An example of a few actions from the news controller at ausdwcon.org:

describe NewsController, "index action" do
  before :each do
    @method = :get
    @action = :index
    @params = {}
  end
  
  it_should_behave_like "Public-Access Actions"
  
  it "should paginate the results" do
    @news = []
    News.stub_method(:paginate => @news)
    
    get :index
    
    News.should have_received(:paginate)
    assigns[:news].should == @news
  end
  
  it "should set the title to 'News'" do
    News.stub_method(:paginate => [])
    
    get :index
    
    assigns[:title].should == "News"
  end
end

describe NewsController, "new action" do
  before :each do
    @method = :get
    @action = :new
    @params = {}
  end
  
  it_should_behave_like "Admin-Only Actions"
  
  it "should set the title to 'News'" do
    controller.current_user = User.stub_instance(:admin? => true)
    
    get :new
    
    assigns[:title].should == "News"
  end
end

You can find the full spec for the controller on pastie.caboo.se. The shared behaviors – ‘Public-Access Actions’ and ‘Admin-Only Actions’ – are (at least for the moment) kept in my spec_helper.rb file – a sample of which is below:

describe "Admin-Only Actions", :shared => true do
  it "should not be accessible without authentication" do
    @controller.current_user = nil
    
    send @method, @action, @params
    
    response.should be_redirect
    response.should redirect_to(new_session_url)
  end
  
  it "should not be accessible by a normal user" do
    @controller.current_user = User.stub_instance(:admin? => false)
    
    send @method, @action, @params
    
    response.should be_redirect
    response.should redirect_to(new_session_url)
  end
  
  it "should be accessible by an admin user" do
    @controller.current_user = User.stub_instance(:admin? => true)
    
    send @method, @action, @params
    
    response.should_not redirect_to(new_session_url)
  end
end

Firstly – the not so nice stuff: the use of instance variables to communicate the method, action and parameters of requests to the shared behaviours. It’s not ideal, and apparently there’s plans to add arguments to the it_should_behave_like method, but for the moment it does the job.

I’m using Pete Yandell’s NotAMock for my stubbing – albeit with a few modifications of my own (which may make it back into the plugin itself at some point). I also use my own ActiveMatchers – but that’s more focused on models. It’s also not really feature-complete, but if you like what it offers, feel free to use it.

Oh, and the main caveat? This is my current way of spec’ing controllers – and it’s vastly better than the minimal specs I was writing before this – but it may/will change. I don’t even know if my style is ‘best practice’ – I’m putting them online to get feedback and provoke discussion. So please feel free to critique.

05 Dec 2007

Link: Bamboo Blog - Presenters & Conductors on Rails

"the presenter and conductor; the presenter sitting between the controller and view, and the conductor sitting between the model and controller."

05 Dec 2007

Link: Plain Text Stories: Part III

Examples of the new stories/integration testing in rspec

04 Dec 2007

validates_uniqueness_of_set

Rails code snippet for the day: validates_uniqueness_of_set. Useful for making sure each specific combination of the specified attributes is unique. Example from the pastie:

validates_uniqueness_of_set :first_name, :last_name

Just like the options it accepts, the code is very similar to validates_uniqueness_of – and the few tests I’ve thrown at it are handled without any problems.

03 Dec 2007

Link: :: GIANT ROBOTS SMASHING INTO OTHER GIANT ROBOTS :: save bang your head, active record will drive you mad

"exceptions should not be expected"

02 Dec 2007

Sphinx-related Updates

Two Sphinx-related tidbits:

Riddle

Riddle now has a tag in SVN for the 0.9.8-r909 release of Sphinx – not that there were really any functional changes compared to r871, besides the two extra match modes (Full Scan and Extended 2 – the latter isn’t going to hang around for long anyway).

Thinking Sphinx

As well as supporting the above version of Sphinx, I’ve now added some brief documentation to Thinking Sphinx that discusses attributes, sorting and delta indexes. To summarise kinda-briefly:

Attributes

Attributes are defined in the define_index block as follows:

define_index do |index|
  index.has.created_at
  index.has.updated_at
  # Field definitions go here
end

They can only be from the indexed model (not associations), and in line with Sphinx’s limitations, must be either integers, floats or timestamps.

Sorting

Ties in closely with attributes – as that’s all Sphinx will let you order by. Use it in the same way as you would in a find call:

Invoice.search :conditions => "expensive", :order => "created_at DESC"

Same approach works for the :include parameter (although this has nothing to do with Sphinx itself).

Delta Indexes

Delta indexes track changes to model records between proper indexes (ie: from the rake task thinking_sphinx:index) – all they require is a boolean field in the model’s table called delta, and for delta indexing to be enabled as follows:

define_index do |index|
  index.delta = true
  # Fields and attributes go here
end

The one catch – at this point, delta indexes are one step off current, as they get indexed before the current transaction to the database is committed. This will get better soon, thanks to some help from Joost Hietbrink and his colleagues at YelloYello – once I find some free time, I’ll get that working much more neatly.

26 Nov 2007

Link: Eli Miller: Proper cache expiry with after_commit

Can use this for any actions after transactions are committed - ie: Thinking Sphinx's delta indexing

26 Nov 2007

RailsCamp Wrap-up

RailsCamp 2.0 finished earlier today – and I think it’s safe to declare it a fantastic success (even given my bias).

Massive thanks to Ben and Karen for their hard work getting everyone fed and co-ordinating people in the kitchen (and the RailsCamp bus from Melbourne to Sunnystones). Thanks too to everyone who helped at various points – both in the organising and over the weekend.

Finally, thank you to everyone who came along – these camps are so much fun because of the calibre of people who attend, and their willingness to share ideas, code and laughs.

Will be posting a version of my talk at some point soon… once I’ve recovered from the weekend.

14 Nov 2007

Link: David Heinemeier Hansson from 37 Signals (hatchthat.com)

Tags:

".. making a difference. Yeah, yeah, I too hear fuzzy teddy bears dancing to the tune of harps saying that, but none the less."

14 Nov 2007

Sphinx's Riddle

Edit: I’ve changed the Subversion reference to Github, and it’s worth noting that Riddle works with Sphinx 0.9.8, 0.9.9 and 1.10-beta at the time of writing (January 2011). Original post continues below:

Built out of the work I’ve done for Thinking Sphinx (which has just got basic support for delta indexes, attributes and sorting – although the documentation doesn’t reflect that), I’ve extracted a new Ruby client that communicates with Sphinx, which I’ve named Riddle.

I’m not going to delve into the code here – because I’m not expecting it to be that useful to many people (and I just wrote examples in the documentation – go read that instead!) – but I’m very happy with how it’s ended up, and it’s got some level of specs to give it a thorough test. It’s also compatible with the most recent release of Sphinx (0.9.8 r871). Should you wish to poke around with it, just clone it from Github:

git clone \
  git://github.com/freelancing-god/riddle.git

It’s also being used in Evan Weaver’s UltraSphinx plugin, which I’m pretty pleased about.

05 Nov 2007

Aliasing Actions in Rails

Tags:

Code snippet for application.rb that makes life just a little easier if multiple actions are doing exactly the same thing (both in the controller and the view):

def self.alias_action(existing, aliased)
  define_method(aliased.to_sym) do
    send(existing.to_sym)
    render :action => existing.to_s
  end
end

And then in the appropriate controller (where edit is an existing action, and show needs to be exactly the same):

alias_action :edit, :show

I know this is a specialised use – but perhaps someone else out there is doing something similar.

30 Oct 2007

RailsCamp Reminder

In case the few readers of this blog are not aware, there’s a RailsCamp happening just outside of Melbourne, from the 23rd-26th November (not far away at all). It will be a weekend of hacking, chatting, food & drink, and very likely some gaming (along the lines of GuitarHero and WiiSports), taking a similar approach to barcamps. The first one, back in June near Sydney, was a fantastic success, and judging by the current list of attendees, I’m expecting it to be much the same.

We’re edging closer and closer to being sold out, so if you’re considering coming along, I recommend signing up as soon as possible. Day passes for the Saturday and Sunday are also available.

RssSubscribe to the RSS feed

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.