PHP and QuickBooks ODBC (QODBC) Problem

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

LVL 1
cescentmanAsked:
Who is Participating?
 
ValleyENTConnect With a Mentor Commented:
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
 
Bill BachPresidentCommented:
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
 
cescentmanAuthor Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Bill BachPresidentCommented:
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
 
cescentmanAuthor Commented:
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
 
cescentmanAuthor Commented:
Just as an aside the version of the QODBC I'm running has a number of optmizer options:-

0
 
cescentmanAuthor Commented:
seems that the image didn't upload. Try again:-
QODBC-Optmizer.PNG
0
 
Bill BachPresidentCommented:
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
 
cescentmanAuthor Commented:
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
 
cescentmanAuthor Commented:
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
 
cescentmanAuthor Commented:
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
 
cescentmanAuthor Commented:
See my comment
0
 
cescentmanAuthor Commented:
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
 
cescentmanAuthor Commented:
Still awaiting a solution from FLEXquarters.com Limited.
0
 
cescentmanAuthor Commented:
I am still awaiting an adequate response from FLEXquarters.com Limited as I would like to write up the result.
0
 
cescentmanAuthor Commented:
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
 
cescentmanAuthor Commented:
many many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.