SQL Server
SQL Server
Quickbooks Query an Invoice
See more QuickBooks Examples
Demonstrates how to query for invoices matching a SELECT statement via the Quickbooks REST API.Chilkat SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- 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
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/qb-access-token.json'
DECLARE @rest int
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
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'sandbox-quickbooks.api.intuit.com', @port, @bTls, @bAutoReconnect
DECLARE @sbAuth int
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
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
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