QuickBooks ODBC

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?
meyerworksAsked:
Who is Participating?
 
jimyXConnect With a Mentor Commented:
>   "Is this due to the probable conflict of table name "item" with a keyword or reserved word somewhere?"

I don't think that's the reason. Below is an example of using "Item". It might be different scenario than yours but the point is it worked when 'Select * from Item' was used:
http://www.qdeveloper.com.au/forum.php?homeinclude=topicdetails&forum_id=1&category_id=19&post_id=1737
http://www.qdeveloper.com.au/forum.php?homeinclude=topicdetails&forum_id=1&category_id=40&post_id=1303

I never used QODBC but you may try creating a thread in the support page about your issue:
http://www.qodbc.com/QODBCAutoFAQ.htm?faq_id=67

The problem might not be coding or Delphi, could be configuration, for instance.
0
 
stlinaeCommented:
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.
0
 
meyerworksAuthor 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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
stlinaeCommented:
Sorry, I couldn't help. I understand the qODBC great, Delphi, not so much.
0
 
meyerworksAuthor 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?
0
 
stlinaeCommented:
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.
0
All Courses

From novice to tech pro — start learning today.