CODEWITHSUNDEEP
quickbooksintuit-partner-platformquickbooks-online
NotSimon
NotSimon

Reputation: 1815

Quickbooks v3 query url returning Error

First - my question:

When accessing the Quickbooks API, v3 (as has been forced on me as of this weekend by Intuit) I am trying to access Journal Entries (but the following problem persists across any other query) and I'm trying to use the prescribed query?query=SELECT * FROM JournalEntry (what?). 

https://qb.sbfinance.intuit.com/v3/company/<id>/query?query=SELECT * FROM JournalEntry

I get as result:

{"Fault":{"Error":[{"Message":"message=Exception authenticating OAuth; errorCode=003200; statusCode=401","code":"3200"}],"type":"AUTHENTICATION"},"requestId":"6f5e5f14af7d4867ad0d8f639ade7d04","time":"2013-11-12T16:10:44.724Z"}

Which, yes, tells me that there was an error with authentication. However, when I access a URL that doesn't include this ridiculous query syntax, everything works fine:

https://qb.sbfinance.intuit.com/v3/company/<id>/journalentry/<id>

I had a similar error when accessing the v2 API, and that was bad formatting on my part, but I don't see what's wrong with my query.

And because my code for generating the authentication tokens etc is identical for both types of request, I doubt that the problem is with how I'm authenticating. Similarly "exception" tells me that there's something going wrong that the API isn't identifying. Probably a formatting of the URL that is going wrong.

I've tried replacing the query URL spaces with both a '+' and a '%20', which returns the same error.

I'm using python and rauth. The code works fine for v2 (but that was deprecated over the weekend without warning, and now is no longer documented).

As a bonus, and because apparently this is Intuit's primary mode of communication with their clients: I'm shocked that Intuit no longer has private support tickets available on their website, and that they rely on a community environment like SO to provide support. The least they could do is provide their own support. Especially if we're paying for use of the API. This is absolutely shocking.

On top of that, the API returns inconsistent responses (the same request will return an error or a valid result, depending on... no change at all). An error I have reported through their support tickets, and they have duly ignored.

Oh, and the documentation says to use 

https://quickbooks.api.intuit.com/v3/v3/company/companyID/query?query=selectStmt

while the API Explorer uses:

https://qb.sbfinance.intuit.com/v3/company/<id>/query?query=SELECT * FROM JournalEntry

Anyone know which one I should actually use?

Edit

For the response that is failing, my request headers are:

{
    'Content-Length': u'62',
    'Accept-Encoding': 'gzip,
    deflate,
    compress',
    'accept': 'application/json',
    'User-Agent': 'python-requests/1.2.3CPython/2.7.5Darwin/13.0.0',
    'Content-Type': 'application/x-www-form-urlencoded',
    'authorization': 'OAuthrealm="<companyId>",
    oauth_nonce="3ad98c5f71bc9f102cc31ac9815cb6d08994454e",
    oauth_timestamp="1384280420",
    oauth_consumer_key="<consumerKey>",
    oauth_signature_method="HMAC-SHA1",
    oauth_version="1.0",
    oauth_token="<oauthToken>",
    oauth_signature="<oauthSignature"'
}

My url is:

https://quickbooks.api.intuit.com/v3/company/<id>/query?query=SELECT+*+FROM+JournalEntry&

And my response headers are: 

{'content-length': '227', 'server': 'Apache/2.2.22 (Unix)', 'connection': 'close', 'date': 'Tue, 12 Nov 2013 18:20:20 GMT', 'content-type': 'application/json;charset=ISO-8859-1', 'www-authenticate': 'OAuth oauth_problem="signature_invalid"'}

My signature hashing function is correct. It's the standard function used by Rauth, and works fine for more standard API calls (that don't have spaces or SQL select queries in them).

Upvotes: 2

Views: 3064

Answers (4)

Basile
Basile

Reputation: 36

  1. Pass the URL to your HTTP call without encoding:

    URL = https://quickbooks.api.intuit.com/v3/company/123456789/query?query="Select * from Customer"

  2. But to build the signature, separate the parameters from the URL, then encode separately, you should get:

    "GET" + "&" + URLEncode(https://quickbooks.api.intuit.com/v3/company/123456789/query) + "&" + URLEncode(query=Select%20%2A%20from%20Customer), where Select%20%2A%20from%20Customer is the encoding of Select * from Customer

Note the SQL gets encoded a second time, when generating the signature.

Et voila ! I spent a week on this, I know what I'm talking about.

(notations are from VBA language, so replace as appropriate)

Upvotes: 1

Basile
Basile

Reputation: 36

you need to encode the query, but not the whole url

https://quickbooks.api.intuit.com/v3/company/123456789/query?query=" & URLEncode("Select * from Customer")

see sample explained here :

https://developer.intuit.com/docs/0100_quickbooks_online/0300_references/0000_programming_guide/0050_data_queries

Upvotes: -1

NotSimon
NotSimon

Reputation: 1815

It turns out that the actual problem is that the Quickbooks documentation is wrong as of this writing (2013/11/14).

The documentation says that the query URL expects a GET request, which is not the case. This works when submitting SELECT statement as part of the body of a POST request.

See here for more details: https://intuitpartnerplatform.lc.intuit.com/questions/786661-python-script-to-integrate-with-quickbook

Upvotes: 1

Manas Mukherjee
Manas Mukherjee

Reputation: 5340

I had tried this API call using Java devkit.

JournalEntry je = GenerateQuery.createQueryEntity(JournalEntry.class);
String jeQuery = select($(je)).generate();
System.out.println("Query - " + jeQuery);
QueryResult JournalEntryRes = service.executeQuery(jeQuery);

Request URI : https://quickbooks.api.intuit.com/v3/company/688779980/query?query=SELECT+*+FROM+JournalEntry&

Response XML

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<IntuitResponse xmlns="http://schema.intuit.com/finance/v3" time="2013-11-12T09:50:39.836-08:00">
    <QueryResponse startPosition="1" maxResults="1" totalCount="1">
        <JournalEntry domain="QBO" sparse="false">
            <Id>22734</Id>
            <SyncToken>0</SyncToken>
            <MetaData>
                <CreateTime>2013-10-15T08:42:12-07:00</CreateTime>
                <LastUpdatedTime>2013-10-15T08:42:12-07:00</LastUpdatedTime>
            </MetaData>
            <TxnDate>2013-10-15</TxnDate>
            <Line>
                <Id>0</Id>
                <Amount>100.00</Amount>
                <DetailType>JournalEntryLineDetail</DetailType>
                <JournalEntryLineDetail>
                    <PostingType>Debit</PostingType>
                    <AccountRef name="Advertising">9</AccountRef>
                </JournalEntryLineDetail>
            </Line>
            <Line>
                <Id>1</Id>
                <Amount>100.00</Amount>
                <DetailType>JournalEntryLineDetail</DetailType>
                <JournalEntryLineDetail>
                    <PostingType>Credit</PostingType>
                    <AccountRef name="Advertising">9</AccountRef>
                </JournalEntryLineDetail>
            </Line>
            <Adjustment>false</Adjustment>
        </JournalEntry>
    </QueryResponse>
</IntuitResponse>

You can try this call from V3 QBO ApiExplorer as well.

Query - SELECT * FROM JournalEntry

Thanks

Upvotes: 0

Related Questions