We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

QuickBooks ODBC

Medium Priority
1,070 Views
Last Modified: 2012-05-11
QODBC by flexquarters Version 11.00.00.275
Quickbooks Premier 2010 version 026-513

Using ADO component in Borland Delphi 7,also fails in Embarcadero Delphi 2010
Tried Ado Table, Ado Command Set and ADO query
sql statement is: select * from "item"


This works in excel..does not when using ado command string in Borland Delphi 7. All other table queries appear to function correctly.
I've copied the exact connection string from Microsoft Query in excel to my ADO components.
Is this due to the probable conflict of table name "item" with a keyword or reserved word somewhere?
Comment
Watch Question

Commented:
I'm not familiar with Delphi, but I did find a similar issue regarding some of the other tables. I hope this helps, or at least makes sense. It looks like it has something to do with the boolean fields in the tables.

The issue is that the driver is having problems with the data type SQL_C_SHORT. I am not sure why Delphi is wanting Booleans returned as SQL_C_SHORT. Considering how many copies of QODBC are out there, this does not come up. Try the query below:

SELECT ListID, {fn CONVERT("IsActive", SQL_INTEGER)} AS "IsActive" FROM Customer
The select statement did work, and Delphi does want the Booleans as SQL_C_SHORT. The problem was with the BDE.

Note: If testing with a simple BDE TQuery with the database name set to 'QUICKBOOKS DATA'. Along with the error as above, you could always get a pop-up for the login info. So an option is to switch to using an ADOConnection and an ADOQuery with the connection string set to:

Provider=MSDASQL.1;Persist Security Info=False;Data Source=QuickBooks Data;

That could get rid of the pop up AND seems to handle the Boolean issue just fine.

Author

Commented:
I am using ADO, not the BDE. I can retrieve the [isactive] boolean field fine without any conversion.
I'm beginning to think it has something to do with the number of fields (55) in the record or it's total size..
Haven't had tome to try to isolate, but your angle is interesting

Commented:
Sorry, I couldn't help. I understand the qODBC great, Delphi, not so much.

Author

Commented:
The item table, i think, is a compliation of field data from several other tables..maybe it's a view or has custom fields in it..what do you think?

Commented:
While I wouldn't have thought of it as a compilation table, it just might be. There is a different table for each Item type plus the Item table. See if you can pull from the individual tables. ItemInventory, ItemService, ItemNonInventory and so forth.

Also, have you tried using the VBDemo32 tool? It can be helpful in seeing what fields are in what table. It should be in Program Files> QuickBooks QODBC Driver> VBDemo32.exe Not sure if it will be of any use or not, but it's fast and accurate. maybe it can shed some light.
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.