Using the QuickBooks SDK via JACOB Java-COM Bridge

So you want to be a hero?

You had better be prepared for a little heroics if you want to use the QuickBooks SDK with Java because it isn’t exactly straightforward. I saw a few mentions of people having done this, but certainly didn’t find any tutorials. Maybe I will be the first person on the planet to write this up. BOW TO YOUR NEW GOD!

Anyway, the problem is that the QBSDK is done with Microsoft’s COM and Java has no built-in COM support. I should mention that you can use Java with the Online Edition or apparently by setting up a Tomcat server and sending qbXML through a SOAP-to-COM pipe or something like that. In my case, I had a bunch of existing Java code that I wanted to connect as directly as possible to QuickBooks – preferably using QBFC.

If you don’t know what I’m talking about with QBSDK, qbXML, and QBFC then you should read this informative tutorial.

After hunting around the web, I found a free library called JACOB: Java-COM Bridge. The theory is that I could go from Java -> JACOB -> QBFC -> QuickBooks. I managed to create a proof of concept for this theory that I will begin sharing with you now.

SETUP

1. Obviously, install QuickBooks, run its updates, and create a test company file (I’m using QuickBooks Premier Nonprofit Edition 2007)
2. Sign up with the Intuit Developer Network and then download/install the SDK. Note that you must say “yes” to installing the .NET stuff or the install will fail. (I’m using QBSDK 7.0)
3. Download and extract JACOB (I’m using version 1.14)
4. Being on a 32-bit OS, I copied the x86 JACOB DLL (jacob-1.14-x86.dll) into C:\WINDOWS\system32
5. For Eclipse users, go to the Java Build Path property, click the Add External JARs button, and add jacob.jar to your project. For non-Eclipse users, make sure that the JAR is in your CLASSPATH.

NOTES ABOUT JACOB

Obviously everything you do with the QBSDK must be enveloped by JACOB API calls. The main beasts in JACOB seem to be Dispatch and Variant. Basically, you’ll use Dispatch to call functions on COM objects, and Variant to serve as parameters and outputs to those functions.

While JACOB seems to work well for me so far, the project lacks documentation of the HOWTO/tutorial variety. By cruising the web for examples and looking over the API, you can sort of figure out how things work. As I was wrestling with it, I stumbled across a QBFC wrapper for Ruby. Looking at the project’s source code helped me figure out a couple of things in the JACOB-QBFC connection.

One thing that JACOB lacks is the ability to lookup COM constants. Some of the QBFC functions require you to pass in one of their constants – hard to do when you can’t access them. The workaround for this is in the How can I get the value of constants? question in the JACOB FAQ. In short, you are supposed to use the Visual Basic editor in Excel to open the “object browser”, search for the constant, read its value, then code it into your program. Not exactly an elegant solution, but oh well. Note that when you’re in the object browser, you’ll need to go to Tools -> References and add the QBFC7Lib.

THE FIRST TIME HURTS

The first time you connect to QuickBooks through your application, you need to have QuickBooks already running. If you do not, then you’ll get an error message like:

This application is not allowed to log into this QuickBooks company data file automatically. The QuickBooks administrator can grant permission for an automatic login through the Integrated Application preferences.

The reason is that QuickBooks needs to pop open a dialog box and ask you if the application is really allowed to connect. After you’ve registered your application with QuickBooks, you’ll be able to access the data without the need to open it first.

SHOW ME THE CODE!

This code assumes that you have some vendors defined. If all goes according to plan, then it should print out all your defined vendors. Note that I am not doing all the various error, status, and type checking that the documentation mentions.

import com.jacob.com.*;

public class Foo {
  public static void main(String[] args) {
    System.out.println("Hello world!");
    // QBFC constants looked up using the object browser
    Variant ctLocalQDB = new Variant(1);
    Variant omDontCare = new Variant(2);
    // major and minor version of qbXML used to talk to QuickBooks
    Variant qbXMLMajor = new Variant(6);
    Variant qbXMLMinor = new Variant(0);
    // location of the company file to access
    String qbFile = "C:\\Documents and Settings\\rdavis\\Desktop\\FfAMETest.QBW";
    // connect to QuickBooks
    Dispatch qbsm = new Dispatch("QBFC7.QBSessionManager");
    Dispatch.call(qbsm, "OpenConnection2", "", "My Sample App", ctLocalQDB);
    Dispatch.call(qbsm, "BeginSession", qbFile, omDontCare);
    // build and execute a request to obtain all vendors
    Dispatch msgSet = Dispatch.call(qbsm, "CreateMsgSetRequest", "US", qbXMLMajor, qbXMLMinor).toDispatch();
    Dispatch.call(msgSet, "AppendVendorQueryRq");
    Dispatch resSet = Dispatch.call(qbsm, "DoRequests", msgSet).toDispatch();
    Dispatch resList = Dispatch.get(resSet, "ResponseList").toDispatch();
    int count = Dispatch.get(resList, "Count").getInt();
    for (int i = 0; i < count; i++) {
      Dispatch res = Dispatch.call(resList, "GetAt", new Variant(i)).toDispatch();
      // blindly assume that we have a vendor response
      Dispatch vendList = Dispatch.get(res, "Detail").toDispatch();
      int vcount = Dispatch.get(vendList, "Count").getInt();
      for (int j = 0; j < vcount; j++) {
        Dispatch vend = Dispatch.call(vendList, "GetAt", new Variant(j)).toDispatch();
        Dispatch dname = Dispatch.get(vend, "Name").toDispatch();
        String name = Dispatch.call(dname, "GetValue").getString();
        System.out.println("Vendor: " + name);
      }
    }
    // close the QuickBooks connection
    Dispatch.call(qbsm, "EndSession");
    Dispatch.call(qbsm, "CloseConnection");
    System.out.println("Goodbye world!");
  }
}

CONCLUSION

Using QBFC is already a somewhat verbose process and running it through JACOB certainly doesn't make the code more terse. Hopefully I'll be able to create my own little API to shield most of my code from needing to mess with all these Dispatches. My needs might be simple enough that I can wrap all the ugly stuff in a few function calls. After I get really down and dirty with QBFC I may write another post detailing my exploits.