?
Solved

PHP and QuickBooks ODBC (QODBC) Problem

Posted on 2009-12-22
19
Medium Priority
?
1,307 Views
Last Modified: 2013-12-13
On server_1: I am running:-
Windows 2003 Web Edition SP2
IIS
PHP 5.2.5, QuickBooks Pro 2006
QODBC server edition 10.00.00.266
MySQL 4.0.26-nt

The QuickBooks data file is on server_2. Using QB Pro 2006, on server_1, I can connect to the data file on server_2 using QuickBooks on server_1.

I have set up the QODBC and have the System DSN as "QuickBooksData" (the default install was named "QuickBooks Data" but I removed the space to ensure it wasn't that causing my problems). Running a VB script supplied with the QODBC on server_1 I can connect to this DSN and submit queries.

Using the very simple PHP script attached:-

I get a time out with no error.

In order to discount the ODBC configuration of server_1, I then created a test DSN to MySQL called "TestMySQL". This works with  the VB script.

If I edit the PHP script to read:-

$strODBCName = 'TestMySQL';

I get a connection and can build queries and display the MySQL data. This would seem to indicate that there is something critically wrong between PHP and QODBC.

Can someone suggest a way of troubleshoot this please as there appear to be no errors I can capture in PHP and the server even log shows nothing.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
        <title>QODBC PHP Script To Display SQL Results</title>
    </head>
    <body>
        Show Results<br />
        <?php
            set_time_limit(120);
            $strODBCName = 'QuickBooksData';
            $oConnect = odbc_connect($strODBCName, '', '');
        ?>
    </body>
</html>

Open in new window

0
Comment
Question by:cescentman
  • 13
  • 3
17 Comments
 
LVL 29

Expert Comment

by:Bill Bach
ID: 26105016
OK, this might be an obvious suggestion, but what about just increasing the timeout value?  Or making it unlimited?

QODBC periodically has to reoptimize its internal cache data, and this is done, by default, before every query.  Running across the wire, this takes a LOT longer than running locally on a server.  In fact, for my own QODBC efforts, I recently moved an extract from a workstation to the QB server and saw a 20x increase in performance of queries.  Since you are running across the wire, you may have a similar issue.
0
 
LVL 1

Author Comment

by:cescentman
ID: 26108733
BillBach

Thanks for the speedy response and the suggestion. I have extended the time out and there is no difference. I'm not sure your caching suggestion is likely as there is no delay when running the "VB Demo" and if I run a browser on sever_1 and point it to the local PHP file it still fails to run. In this case there would be no difference in caching between the VB and PHP query in terms of caching behaviour would there?
0
 
LVL 29

Expert Comment

by:Bill Bach
ID: 26108904
Honestly, I've not tried to access QODBC directly from my web server.  In my initial tests (admittedly, back 4 years ago), the performance was abysmal.  In fact, I was doing QODBC queries in real-time while I had customers on the phone.  As the database continued to grow, "real-time" became 2-3 minutes.  Imagine how much fun it is to chat with a person on the phone for 2-3 minutes while waiting for their invoice details to show up!

Anyway, I opted long ago to perform a nightly extract of the data from QB/QODBC to CSV files, and I then use those data files to load my database into a Pervasive PSQL database. (You can use MySQL the same way.)  The advantage here is that I know PSQL/MySQL work fine from ASP and PHP, and this gives me FAST access to everything that I need.  The downside is that I only have data from yesterday.  (Luckily, my brain can still remember invoices I've written today, so this isn't a big deal.)


Anyway, back to the problem at hand for a few more ideas.  Have you tried changing the connection string to "DSN=QuickBooksData"?  Maybe.  Have you verified that the QuickBooksData DSN is a SYSTEM DSN, and *not* a User DSN?  This DOES make a big difference.   Also, have you verified that the QODBC driver is available from the login name being used for the PHP process?  Many web servers will create a different process, and this might have different access rights or system path that your "test" user.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:cescentman
ID: 26111164
I take your point about speed, I guess until I get it working I won't know if I need to adopt your strategy or not but I'll keep it in mind.

Yes I tried it our of the box where the DSN=QuickBooks Data and then changed the DSN to read QuickBooksData just in case the space was a problem. Neither made any difference. As you can see from the screen dump this is definitely a System DSN.

As to the user and permissions issue, I looked into this. The System DSN is normally available to all users on the system. Maybe I'm being dense but I can't see any obvious way of checking this. So I tried to approach it from a different angle; I copied the QB Company file locally and gave server_1\IWAM_SOUTHCOTWEB and server_1\IUSR_SOUTHCOTWEB full control of the file.

No difference. "VB Demo" runs fine but the PHP script doesn't. A good call but unfortunately not a successful one. :>(
DSN.PNG
0
 
LVL 1

Author Comment

by:cescentman
ID: 26111551
Just as an aside the version of the QODBC I'm running has a number of optmizer options:-

0
 
LVL 1

Author Comment

by:cescentman
ID: 26111561
seems that the image didn't upload. Try again:-
QODBC-Optmizer.PNG
0
 
LVL 29

Expert Comment

by:Bill Bach
ID: 26113013
I typically set the Optimizer to "the end of last month", but you could do it daily, too.  To make sure that the data is ready when I need it, I manually refresh the data that I am about to extract.  Since I typically only extract invoices and invoicelines, my batch file looks like the code below.  (SQLExec is just the command-line-based ODBC SQL query generator that I use.)  These limited refreshes go very fast, and it avoids waiting on the complete optimizer to run.
@echo off
cls
Echo Refreshing QODBC Data...
sqlexec "QuickBooks Data" "sp_optimizeupdatesync Invoice"
sqlexec "QuickBooks Data" "sp_optimizeupdatesync InvoiceLine"
Echo Extracting Invoices...
sqlexec "QuickBooks Data" /Mexportinvoices.sql /b /oC /H >invoices.sdf
Echo Extracting Invoice Line Item Details...
sqlexec "QuickBooks Data" /Mexportinvoicelines.sql /b /oC /H /F101 >invoicelines.sdf
Echo Done!

Open in new window

0
 
LVL 1

Author Comment

by:cescentman
ID: 26113200
It sounds as though It's worth a try but as I said until I get this thing working I won't be able to tell whether I need such a solution. Thanks for the information.
0
 
LVL 1

Author Comment

by:cescentman
ID: 26126576
I've been talking with the developer of the QODBC it may be that this is a DCOM issue doe to some MS updates. Not sure yet will post as soon as I have news.
0
 
LVL 1

Author Comment

by:cescentman
ID: 26346799
I'm still awaiting a solution from FLEXquarters.com Limited the developers of the QODBC. I have no reason to doubt that they will find a solution. Would suggest leaving it open until I can write it up.
0
 
LVL 1

Author Comment

by:cescentman
ID: 26346801
See my comment
0
 
LVL 1

Author Comment

by:cescentman
ID: 26378053
Still awaiting a solution from FLEXquarters.com Limited. The software wasn't cheap and I'm beginning to wish there was another solution available.
0
 
LVL 1

Author Comment

by:cescentman
ID: 26425281
Still awaiting a solution from FLEXquarters.com Limited.
0
 
LVL 1

Author Comment

by:cescentman
ID: 27657918
I am still awaiting an adequate response from FLEXquarters.com Limited as I would like to write up the result.
0
 
LVL 4

Accepted Solution

by:
ValleyENT earned 2000 total points
ID: 27657976
not sure if you have seen this but there is an automatic dcom configuration tool available.

http://www.qodbc.com/QODBCweb.htm 

the linked site also has methods of testing dcom.
0
 
LVL 1

Author Comment

by:cescentman
ID: 27663332
Thanks for that, I hadn't seen it and it has resolved the matter I can't believe that during the 3 months of trying to get help from FLEXquarters.com Limited no one suggested this. I am VERY unhappy about the level of support available they gave me. You can see from the ticket attached what happened, I am pretty certain that my subsequent problems were not resolved by the steps they outlined because the support technician made many changes on the system and they weren't reversed. When I escalated it I got the following response:-

"Sorry you feel that way but you were given a solution that was working on our system - now it is up to you to manage your system with a Microsoft Server expert, as it is too much to ask for us to setup your system for you. If the example you were given does not work, there is a difference between the two systems and i advise you find an expert in these matters to resolve the issue for you. John informs me the system works properly for him, and i feel the effort he has given this issue resolves the problem as far as QODBC is concerned. If you don't agree, you may request a refund from your distributor."

The resellers Architectronic did their best to help. At one point I even offered to pay if FLEXquarters.com  could identify an "expert" to resolve it. They refused saying it was down to me to find someone. So I was in the process of doing just that.

Then you resolve it at a stroke BRILLIANT.
QODBC-Ticket.pdf
0
 
LVL 1

Author Closing Comment

by:cescentman
ID: 31703075
many many thanks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month17 days, 5 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question