(SQL Server) Quickbooks Query an Invoice

Demonstrates how to query for invoices matching a SELECT statement via the Quickbooks REST API.

For more information, see https://www.developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/invoice#query-an-invoice

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- First get our previously obtained OAuth2 access token.
    DECLARE @jsonToken int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/qb-access-token.json'

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT

    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'sandbox-quickbooks.api.intuit.com', @port, @bTls, @bAutoReconnect

    DECLARE @sbAuth int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbAuth OUT

    EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, 'Bearer '
    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbAuth, 'GetAsString', @sTmp0 OUT
    EXEC sp_OASetProperty @rest, 'Authorization', @sTmp0

    -- --------------------------------------------------------------------------
    -- Note: The above REST connection and setup of the AWS credentials
    -- can be done once.  After connecting, any number of REST calls can be made.
    -- The "auto reconnect" property passed to rest.Connect indicates that if
    -- the connection is lost, a REST method call will automatically reconnect
    -- if needed.
    -- --------------------------------------------------------------------------

    -- This is a GET request, so there should be no Content-Type
    -- This line of code is just to make sure..
    EXEC sp_OAMethod @rest, 'RemoveHeader', @success OUT, 'Content-Type'

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Accept', 'application/json'
    EXEC sp_OASetProperty @rest, 'AllowHeaderFolding', 0

    -- Add a SELECT statement 
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'query', 'select * from Invoice where id = ''239'''

    DECLARE @sbResponseBody int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @rest, 'FullRequestNoBodySb', @success OUT, 'GET', '/v3/company/<realmID>/invoice', @sbResponseBody
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @sbAuth
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @respStatusCode OUT
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Status Code = ' + @respStatusCode

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Response Body:'
        EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @sbAuth
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    -- Success is indicated by a 200 response status.

    PRINT 'response status code = ' + @respStatusCode

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample output:
    -- Use the this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    -- {
    --   "QueryResponse": {
    --     "startPosition": 1, 
    --     "totalCount": 1, 
    --     "maxResults": 1, 
    --     "Invoice": [
    --       {
    --         "DocNumber": "1070", 
    --         "SyncToken": "0", 
    --         "domain": "QBO", 
    --         "Balance": 150.0, 
    --         "BillAddr": {
    --           "City": "Bayshore", 
    --           "Line1": "4581 Finch St.", 
    --           "PostalCode": "94326", 
    --           "Lat": "INVALID", 
    --           "Long": "INVALID", 
    --           "CountrySubDivisionCode": "CA", 
    --           "Id": "2"
    --         }, 
    --         "TxnDate": "2015-07-24", 
    --         "TotalAmt": 150.0, 
    --         "CustomerRef": {
    --           "name": "Amy's Bird Sanctuary", 
    --           "value": "1"
    --         }, 
    --         "ShipAddr": {
    --           "City": "Bayshore", 
    --           "Line1": "4581 Finch St.", 
    --           "PostalCode": "94326", 
    --           "Lat": "INVALID", 
    --           "Long": "INVALID", 
    --           "CountrySubDivisionCode": "CA", 
    --           "Id": "109"
    --         }, 
    --         "LinkedTxn": [], 
    --         "DueDate": "2015-08-23", 
    --         "PrintStatus": "NeedToPrint", 
    --         "Deposit": 0, 
    --         "sparse": false, 
    --         "EmailStatus": "NotSet", 
    --         "Line": [
    --           {
    --             "LineNum": 1, 
    --             "Amount": 150.0, 
    --             "SalesItemLineDetail": {
    --               "TaxCodeRef": {
    --                 "value": "NON"
    --               }, 
    --               "ItemRef": {
    --                 "name": "Services", 
    --                 "value": "1"
    --               }
    --             }, 
    --             "Id": "1", 
    --             "DetailType": "SalesItemLineDetail"
    --           }, 
    --           {
    --             "DetailType": "SubTotalLineDetail", 
    --             "Amount": 150.0, 
    --             "SubTotalLineDetail": {}
    --           }
    --         ], 
    --         "ApplyTaxAfterDiscount": false, 
    --         "CustomField": [
    --           {
    --             "DefinitionId": "1", 
    --             "Type": "StringType", 
    --             "Name": "Crew #"
    --           }
    --         ], 
    --         "Id": "239", 
    --         "TxnTaxDetail": {
    --           "TotalTax": 0
    --         }, 
    --         "MetaData": {
    --           "CreateTime": "2015-07-24T10:35:08-07:00", 
    --           "LastUpdatedTime": "2015-07-24T10:35:08-07:00"
    --         }
    --       }
    --     ]
    --   }, 
    --   "time": "2015-07-24T10:38:50.01-07:00"
    -- }

    DECLARE @DocNumber nvarchar(4000)

    DECLARE @SyncToken nvarchar(4000)

    DECLARE @domain nvarchar(4000)

    DECLARE @Balance nvarchar(4000)

    DECLARE @BillAddrCity nvarchar(4000)

    DECLARE @BillAddrLine1 nvarchar(4000)

    DECLARE @BillAddrPostalCode nvarchar(4000)

    DECLARE @BillAddrLat nvarchar(4000)

    DECLARE @BillAddrLong nvarchar(4000)

    DECLARE @BillAddrCountrySubDivisionCode nvarchar(4000)

    DECLARE @BillAddrId nvarchar(4000)

    DECLARE @TxnDate nvarchar(4000)

    DECLARE @TotalAmt nvarchar(4000)

    DECLARE @CustomerRefName nvarchar(4000)

    DECLARE @CustomerRefValue nvarchar(4000)

    DECLARE @ShipAddrCity nvarchar(4000)

    DECLARE @ShipAddrLine1 nvarchar(4000)

    DECLARE @ShipAddrPostalCode nvarchar(4000)

    DECLARE @ShipAddrLat nvarchar(4000)

    DECLARE @ShipAddrLong nvarchar(4000)

    DECLARE @ShipAddrCountrySubDivisionCode nvarchar(4000)

    DECLARE @ShipAddrId nvarchar(4000)

    DECLARE @DueDate nvarchar(4000)

    DECLARE @PrintStatus nvarchar(4000)

    DECLARE @Deposit int

    DECLARE @sparse int

    DECLARE @EmailStatus nvarchar(4000)

    DECLARE @ApplyTaxAfterDiscount int

    DECLARE @Id nvarchar(4000)

    DECLARE @TxnTaxDetailTotalTax int

    DECLARE @MetaDataCreateTime nvarchar(4000)

    DECLARE @MetaDataLastUpdatedTime nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @LineNum int

    DECLARE @Amount nvarchar(4000)

    DECLARE @SalesItemLineDetailTaxCodeRefValue nvarchar(4000)

    DECLARE @SalesItemLineDetailItemRefName nvarchar(4000)

    DECLARE @SalesItemLineDetailItemRefValue nvarchar(4000)

    DECLARE @DetailType nvarchar(4000)

    DECLARE @DefinitionId nvarchar(4000)

    DECLARE @invType nvarchar(4000)

    DECLARE @Name nvarchar(4000)

    DECLARE @QueryResponseStartPosition int
    EXEC sp_OAMethod @json, 'IntOf', @QueryResponseStartPosition OUT, 'QueryResponse.startPosition'
    DECLARE @QueryResponseTotalCount int
    EXEC sp_OAMethod @json, 'IntOf', @QueryResponseTotalCount OUT, 'QueryResponse.totalCount'
    DECLARE @QueryResponseMaxResults int
    EXEC sp_OAMethod @json, 'IntOf', @QueryResponseMaxResults OUT, 'QueryResponse.maxResults'
    DECLARE @time nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @time OUT, 'time'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'QueryResponse.Invoice'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @DocNumber OUT, 'QueryResponse.Invoice[i].DocNumber'
        EXEC sp_OAMethod @json, 'StringOf', @SyncToken OUT, 'QueryResponse.Invoice[i].SyncToken'
        EXEC sp_OAMethod @json, 'StringOf', @domain OUT, 'QueryResponse.Invoice[i].domain'
        EXEC sp_OAMethod @json, 'StringOf', @Balance OUT, 'QueryResponse.Invoice[i].Balance'
        EXEC sp_OAMethod @json, 'StringOf', @BillAddrCity OUT, 'QueryResponse.Invoice[i].BillAddr.City'
        EXEC sp_OAMethod @json, 'StringOf', @BillAddrLine1 OUT, 'QueryResponse.Invoice[i].BillAddr.Line1'
        EXEC sp_OAMethod @json, 'StringOf', @BillAddrPostalCode OUT, 'QueryResponse.Invoice[i].BillAddr.PostalCode'
        EXEC sp_OAMethod @json, 'StringOf', @BillAddrLat OUT, 'QueryResponse.Invoice[i].BillAddr.Lat'
        EXEC sp_OAMethod @json, 'StringOf', @BillAddrLong OUT, 'QueryResponse.Invoice[i].BillAddr.Long'
        EXEC sp_OAMethod @json, 'StringOf', @BillAddrCountrySubDivisionCode OUT, 'QueryResponse.Invoice[i].BillAddr.CountrySubDivisionCode'
        EXEC sp_OAMethod @json, 'StringOf', @BillAddrId OUT, 'QueryResponse.Invoice[i].BillAddr.Id'
        EXEC sp_OAMethod @json, 'StringOf', @TxnDate OUT, 'QueryResponse.Invoice[i].TxnDate'
        EXEC sp_OAMethod @json, 'StringOf', @TotalAmt OUT, 'QueryResponse.Invoice[i].TotalAmt'
        EXEC sp_OAMethod @json, 'StringOf', @CustomerRefName OUT, 'QueryResponse.Invoice[i].CustomerRef.name'
        EXEC sp_OAMethod @json, 'StringOf', @CustomerRefValue OUT, 'QueryResponse.Invoice[i].CustomerRef.value'
        EXEC sp_OAMethod @json, 'StringOf', @ShipAddrCity OUT, 'QueryResponse.Invoice[i].ShipAddr.City'
        EXEC sp_OAMethod @json, 'StringOf', @ShipAddrLine1 OUT, 'QueryResponse.Invoice[i].ShipAddr.Line1'
        EXEC sp_OAMethod @json, 'StringOf', @ShipAddrPostalCode OUT, 'QueryResponse.Invoice[i].ShipAddr.PostalCode'
        EXEC sp_OAMethod @json, 'StringOf', @ShipAddrLat OUT, 'QueryResponse.Invoice[i].ShipAddr.Lat'
        EXEC sp_OAMethod @json, 'StringOf', @ShipAddrLong OUT, 'QueryResponse.Invoice[i].ShipAddr.Long'
        EXEC sp_OAMethod @json, 'StringOf', @ShipAddrCountrySubDivisionCode OUT, 'QueryResponse.Invoice[i].ShipAddr.CountrySubDivisionCode'
        EXEC sp_OAMethod @json, 'StringOf', @ShipAddrId OUT, 'QueryResponse.Invoice[i].ShipAddr.Id'
        EXEC sp_OAMethod @json, 'StringOf', @DueDate OUT, 'QueryResponse.Invoice[i].DueDate'
        EXEC sp_OAMethod @json, 'StringOf', @PrintStatus OUT, 'QueryResponse.Invoice[i].PrintStatus'
        EXEC sp_OAMethod @json, 'IntOf', @Deposit OUT, 'QueryResponse.Invoice[i].Deposit'
        EXEC sp_OAMethod @json, 'BoolOf', @sparse OUT, 'QueryResponse.Invoice[i].sparse'
        EXEC sp_OAMethod @json, 'StringOf', @EmailStatus OUT, 'QueryResponse.Invoice[i].EmailStatus'
        EXEC sp_OAMethod @json, 'BoolOf', @ApplyTaxAfterDiscount OUT, 'QueryResponse.Invoice[i].ApplyTaxAfterDiscount'
        EXEC sp_OAMethod @json, 'StringOf', @Id OUT, 'QueryResponse.Invoice[i].Id'
        EXEC sp_OAMethod @json, 'IntOf', @TxnTaxDetailTotalTax OUT, 'QueryResponse.Invoice[i].TxnTaxDetail.TotalTax'
        EXEC sp_OAMethod @json, 'StringOf', @MetaDataCreateTime OUT, 'QueryResponse.Invoice[i].MetaData.CreateTime'
        EXEC sp_OAMethod @json, 'StringOf', @MetaDataLastUpdatedTime OUT, 'QueryResponse.Invoice[i].MetaData.LastUpdatedTime'
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'QueryResponse.Invoice[i].LinkedTxn'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'QueryResponse.Invoice[i].Line'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'IntOf', @LineNum OUT, 'QueryResponse.Invoice[i].Line[j].LineNum'
            EXEC sp_OAMethod @json, 'StringOf', @Amount OUT, 'QueryResponse.Invoice[i].Line[j].Amount'
            EXEC sp_OAMethod @json, 'StringOf', @SalesItemLineDetailTaxCodeRefValue OUT, 'QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.TaxCodeRef.value'
            EXEC sp_OAMethod @json, 'StringOf', @SalesItemLineDetailItemRefName OUT, 'QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.ItemRef.name'
            EXEC sp_OAMethod @json, 'StringOf', @SalesItemLineDetailItemRefValue OUT, 'QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.ItemRef.value'
            EXEC sp_OAMethod @json, 'StringOf', @Id OUT, 'QueryResponse.Invoice[i].Line[j].Id'
            EXEC sp_OAMethod @json, 'StringOf', @DetailType OUT, 'QueryResponse.Invoice[i].Line[j].DetailType'
            SELECT @j = @j + 1
          END
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'QueryResponse.Invoice[i].CustomField'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @DefinitionId OUT, 'QueryResponse.Invoice[i].CustomField[j].DefinitionId'
            EXEC sp_OAMethod @json, 'StringOf', @invType OUT, 'QueryResponse.Invoice[i].CustomField[j].Type'
            EXEC sp_OAMethod @json, 'StringOf', @Name OUT, 'QueryResponse.Invoice[i].CustomField[j].Name'
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @sbAuth
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @json


END
GO