[QODBC-Desktop] How to use QODBC with ColdFusion
Posted by brad waddell on 12 March 2009 05:21 PM

Note:  DCOM component is deprecated. As an alternative to DCOM, you can use QRemote.

Please refer to the articles for more information:

How to configure QRemote

How to use QRemote with ColdFusion

How to use QODBC with ColdFusion

Instructions about using QODBC with ColdFusion

   Using ColdFusion on an internal Intranet or the World Wide Web requires the Server Edition of QODBC. The QODBC Server Edition includes DCOM servers required to allow QODBC to run as a system service to find the instance of QuickBooks running on the server console. QuickBooks must be running with the company file opened on the system console.

     You have two choices in setting up ColdFusion. The first is using the QODBC DCOM Servers. The second is using the Remote Connector Option.

     The DCOM Servers require QuickBooks to run on the Server Console, not a Terminal Services session. They are more secure and require no extra overhead but are more rigid in how it is set up, and it cannot be easy to configure the security of the DCOM servers.

     The Remote Connector option allows the window that is running QuickBooks to be anywhere that is accessible via TCP/IP. That means QuickBooks can be run in a Terminal Sessions window, on another computer on your LAN, or anywhere on the WEB. If your data are transmitted over the internet, we recommend upgrading the Remote Connector to the SSL secure version. The instructions on setting up the Remote Connector version are found in Using QODBC Remote.

QODBC Basic Setup for Using the DCOM Servers Method

     You must turn on the use of the DCOM servers. You do this by running the QODBC Setup Screen and selecting the Server Edition tab page. Check the checkbox for Use DCOM Servers.

     If you're working with the QuickBooks sample company file, this option is available to check even if you do not have a Server Edition license. It will be ignored if you open a standard company file and do not have a QODBC Server Edition or evaluation. [Note: You may need to check the Run 2.1 ODBC Compatibility checkbox - Certain applications require ODBC 2.1 Compatibility to use QODBC.] For example, anything that uses the Java ODBC library, like ColdFusion, requires this option turned on, or recordsets will be returned with no rowsets or only one rowset. 


Internet Information Server (IIS) Basic Setup

     Ensure the following are installed and registered on your web server or even your notebook running Windows XP Pro :

bullet Microsoft Internet Information Server (IIS5 or later)

bullet Uncheck the Allow IIS to control password setting for Anonymous access in IIS as shown here, and set your password.

bullet A supported version of QuickBooks 2006/07, 2005/06, 2004, and 2003 and above installed with your company file open (set the company file to Multiuser Mode)

bullet The QODBC Driver for QuickBooks is installed and running in Server Edition mode (either as a 30 Day Server Evaluation or a fully registered copy).

bullet It would be best if you created a system DSN. By default, the DSN QuickBooks Data is created for you when QODBC is installed, but you can use any system DSN designed for QODBC.

bullet If running ASP.NET, ensure the.NET framework is loaded and patched to the current release level. Also, verify that MDAC 2.6 or greater is installed.

bullet To get access to the security tab (assuming you have an NTFS partition), do the following: give the read and write rights to the QuickBooks application folder (normally C:\Program Files\QODBC Driver for QuickBooks) to the Anonymous web user IUSR_, ASPNET user, and INTERACTIVE. This allows for the creation and update of QODBC trace and message logs and provides access to the DCOM server executables.,

        bullet In Windows Explorer, go to Tools -> Folder Options...

        bullet Select the View tab

        bullet In the list of Advanced Settings, de-select Use simple file sharing (Recommended)

        bullet Select Ok

        bullet Now you can give IUSR_all rights to the C:\Program Files\QODBC Driver for QuickBooks using the Security tab.


Automatic DCOM Configuration


bullet Dcpinst.exe is an installation setup for the automated DCOM configuration object. This needs to be run first and only once. Click here: to download the following automated tools:

bullet QODBCSetDCOM.VBS is a VB script that uses the installed configuration object to set up the DCOM Servers. It will reset and automatically add all necessary security each time it is run. The script can be edited if you need to change the Accounts it is configuring.

bullet The new settings will not take effect until the DCOM servers (qbXMLRPeInteractive.exe and qbXMLRP2eInteractive.exe) are removed from memory. Rebooting is the simplest way to accomplish this, but running the Windows Task Manager and Ending Process on any running copies will also work.

bullet Test your DCOM setup using one of the following:

       bullet IIS users download TestDCOM.ASP

       bullet PHP users download: TestDCOM.PHP

       bullet Cold Fusion users download TestDCOM.CFM


ColdFusion Data Source Setup Instructions

      Run the ColdFusion Administrator. Under DATA & SERVICES, choose Data Sources. Use "QBs" as the Data Source name to match the DisplaySQL.cfm sample script below. The Driver is ODBC Socket. Press the Add button.

      Under ODBC DSN, choose a QuickBooks DSN. QuickBooks Data is a DSN that is created by the QODBC installation. The username should be set to Admin. You can put any notes you want in the description field.

      Next, Press the Show Advanced Settings Button.

      Make sure the Limit Connections and Maintain Connections are unchecked. You can remove the Create, Drop, Alter, Grant and Revoke checkboxes. Add checkboxes for Select, Insert, Update, Delete, and Stored Procedures.

      Press Submit and your QODBC Data Source should appear on the Data Sources list.

      You can test your QODBC DCOM Setup by downloading: TestDCOM.CFM and test QODBC with your new Macromedia ColdFusion ODBC Socket connection using the following sample script: DisplaySQL.CFM.

      DisplaySQL.cfm uses dataSource="QBs," which is the QuickBooks Data DSN. For this to work, you must also configure the "QuickBooks Data" data source to auto-login your company file, as shown in How to set up QODBC for Auto-login into QuickBooks.


Keywords: PHP, IIS, ColdFusion, can it work with php, service-based application

(108 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please complete the captcha below (we use this to prevent automated submissions).