connecting to quickbooks database via odbc with php?

Question

[edit]
We're collecting credit application data from users on a web form.

I have not tied my web form directly into QB.

I have no idea what the QB table structure is for this collection of data - nor of how it displays it to the user because I've never actually worked directly with QB. Others in my office do however.

---

I would still appreciate any information about open source / free options.

---

I'll simplify the problem by removing the first desired option.

Let's just talk about importing a flat file into quickbooks.

I don't have a copy of quickbooks that I can play with, so I have no idea what options exist that are native to quickbooks but I see some chatter on the web about QB expecting an .ini file for imports. (don't know the format that it expects yet, but I'll get that worked out later)

Please share your story if you have managed to import a flat file from mySQL into quickbooks or know of links that offer insight (I'm not finding much right now).
[end edit]

I realize that this scenario is probably uncommon, but I need to connect to an existing quickbooks database in one of two ways.

1) either directly from a php script running on our web site -- insert user supplied data directly from web to quickbooks.

or

2) put user's data directly into mySQL database and later export data from mySQL to quickbooks.

If the first option is viable, I would appreciate your thoughts on establishing an odbc connection to the quickbooks database.

can I go about it with something like:

try {
  $conn = @odbc_connect("DSNName", "", "", "SQL_CUR_USE_ODBC");
  // un and pw parameters are passed as empty strings since the DSN 
  // has knowledge of the password already.
  // 4th parameter is optional

  $exec = @odbc_exec($conn, $insert) or die ("exec error");
  echo "success!";
}
catch (Exception $e) {
  echo $e->getMessage();
} // end try catch

Solution 1

The third option might work for you.

Option 1 - QODBC: If you're looking for simplicity, Qodbc as mentioned above is the main product that does it, and it does cost money.

Option 2 - Quickbooks SDK: If you're okay working through the SDK and using their XML structures, you can find some great resources on the developer network. Accounts are free for the SDK and you can work your way through it fairly easily.

This boils down to one thing, if you want it free, you might not be able to get it your way.. QODBC is reasonably priced the last time I checked.

The one thing that is important to be aware of with either approach -- ensure the tables you wish to write data into in Quickbooks are available by the SDK and QODBC.

As Quickbooks has gotten older, access to some tables have disappeared. For example, it's not possible to write directly to the payroll deductions table directly as it competes with Intuit's Payroll Service.

Option 3 - Direct SQL manipulation: Intuit encrypts their data in their SQL data making it unavailable for direct access.

Edit: Option 4 - Quickbooks Web Connector SOAP wrapper that sits on the computer with Quickbooks and interacts with it for you. Also free.

Good luck!

Solution 2

If you want ODBC access, QODBC is your only choice.

The newer versions of QuickBooks Enterprise Edition use an SQL database back-end, but it does not allow you to directly access the SQL tables. They lock the database down so you can't actually query any of the data.

AccessBooks is another option, which mirrors data from QuickBooks into MySQL, and then can push data back to QuickBooks as well. I have heard that it can be a bit flaky.

You can use the QuickBooks SDK and make it do pretty much anything you can do in the QuickBooks GUI, but it's a bit more work. If you're integrating a web application with QuickBooks, you'll want to use the Web Connector. I'm the developer of a QuickBooks/PHP framework which is quite stable and popular, and would probably be helpful to you:

https://idnforums.intuit.com/messageview.aspx?catid=56&threadid=9164

I'm in the middle of adding support for mirroring the entire QB schema to a database, keeping it in sync, and then allowing changes to be pushed back to QuickBooks. The work isn't near complete yet though. It currently pulls data from QuickBooks and keeps it in sync remarkably well however.

You can also import IIF files into QuickBooks, but it's now an unsupported and deprecated format by Intuit. They highly recommend you do not use it, here are some reasons why: http://wiki.consolibyte.com/wiki/doku.php/quickbooks_integration_methods

Comments

[OldBuildingAndLoan]

Thanks. I'll post back after checking QODBC out.

[OldBuildingAndLoan]

not really what I was hoping for, but I guess it's best to close this question for now.

[OldBuildingAndLoan]

Thank you. It sounds like Opt 3 may work - if this is available in the 207 version. I'm not sure what my boss wants yet, which will influence how I go about solving this problem. Thank you for the options.

[OldBuildingAndLoan]

I added note at top of my question about the type of transaction. Unfortunately, I don't know much more than that about it at this time. Once I get more info, I'll probably end up opening up more questions on this subject.

[Jas Panesar]

Glad it helped! If you're using the US version of quickbooks it's already in SQL, In Canada they switched in 2008. I hope your boss is willing to buy QODBC. It's stable, you'd get support, and he would pay you less time to work on the project. Free and open source doesn't always mean cheap..

[Keith Palmer Jr.]

Jas is incorrect about QuickBooks using/allowing direct SQL access. QuickBooks desktop products are not built on top of an SQL database. The newer versions of Enterprise edition are. Regardless, they do not allow you to directly query the SQL tables, they provide an XML interface instead. QODBC is an ODBC SQL layer built on top of the existing XML-based QuickBooks SDK, it does not provide direct access to the QuickBooks SQL tables. Read more here: qodbc.com/qodbc.htm

[Jas Panesar]

Keith, you are correct, it is encrypted, as I had mentioned as being a possibility. I forgot about this post as I had posted a similar question and forgot about this. Thanks for bringing this to my attention!

[Tim Child]

QuickBooks has used an embedded SQL database in its desktop product since QB2005. It's was not available for external use. It's good to see QB2011 starting to open this up.