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.
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>
ASKER
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?
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?
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.
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.
ASKER
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
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
ASKER
Just as an aside the version of the QODBC I'm running has a number of optmizer options:-
ASKER
seems that the image didn't upload. Try again:-
QODBC-Optmizer.PNG
QODBC-Optmizer.PNG
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!
ASKER
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.
ASKER
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.
ASKER
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.
ASKER
See my comment
ASKER
Still awaiting a solution from FLEXquarters.com Limited. The software wasn't cheap and I'm beginning to wish there was another solution available.
ASKER
Still awaiting a solution from FLEXquarters.com Limited.
ASKER
I am still awaiting an adequate response from FLEXquarters.com Limited as I would like to write up the result.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
"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
ASKER
many many thanks
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.