QuickBooks ODBC

Posted on 2011-04-28
Last Modified: 2012-05-11
QODBC by flexquarters Version
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?
Question by:meyerworks
    LVL 11

    Expert Comment

    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 Comment

    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
    LVL 11

    Expert Comment

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

    Author Comment

    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?
    LVL 11

    Expert Comment

    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.
    LVL 24

    Accepted Solution

    >   "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:

    I never used QODBC but you may try creating a thread in the support page about your issue:

    The problem might not be coding or Delphi, could be configuration, for instance.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video discusses moving either the default database or any database to a new volume.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now