SQL Server
SQL Server
QuickBooks - Query an Account
See more QuickBooks Examples
Demonstrates how to query the QuickBooks chart of accounts.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
DECLARE @iTmp0 int
DECLARE @iTmp1 int
DECLARE @iTmp2 int
DECLARE @iTmp3 int
DECLARE @iTmp4 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 assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- Get our previously obtained OAuth2 access token, which should contain JSON like this:
-- {
-- "expires_in": 3600,
-- "x_refresh_token_expires_in": 8726400,
-- "refresh_token": "L011546037639r ... 3vR2DrbOmg0Sdagw",
-- "access_token": "eyJlbmMiOiJBMTI4Q0 ... oETJEMbeggg",
-- "token_type": "bearer"
-- }
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
-- Connect using TLS.
-- A single REST object, once connected, can be used for many Quickbooks REST API calls.
-- The auto-reconnect indicates that if the already-established HTTPS connection is closed,
-- then it will be automatically re-established as needed.
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'sandbox-quickbooks.api.intuit.com', 443, 1, @bAutoReconnect
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @rest
RETURN
END
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
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 Account where Metadata.CreateTime > ''2014-12-31'''
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'minorversion', '45'
-- The company ID is 123146096291789
DECLARE @responseBody nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseBody OUT, 'GET', '/v3/company/123146096291789/query'
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
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
RETURN
END
EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
-- We should expect a 200 response if successful.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
PRINT 'Request Header: '
EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
PRINT @sTmp0
PRINT '----'
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
PRINT 'Response StatusCode = ' + @iTmp0
EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
PRINT 'Response StatusLine: ' + @sTmp0
PRINT 'Response Header:'
EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
PRINT @sTmp0
PRINT @responseBody
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbAuth
RETURN
END
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- Load the JSON response into a JSON object for parsing.
-- A sample JSON response is shown below.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @responseBody
-- These will be used for parsing date/time strings..
DECLARE @dtime int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtime OUT
DECLARE @bLocalTime int
SELECT @bLocalTime = 1
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dt OUT
-- Iterate over each account returned.
DECLARE @numAccounts int
EXEC sp_OAMethod @json, 'SizeOfArray', @numAccounts OUT, 'QueryResponse.Account'
DECLARE @i int
SELECT @i = 0
WHILE @i < @numAccounts
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'QueryResponse.Account[i].Name'
PRINT 'Name: ' + @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'QueryResponse.Account[i].Id'
PRINT 'Id: ' + @sTmp0
-- Load the CreateTime into a CkDateTime...
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'QueryResponse.Account[i].MetaData.CreateTime'
EXEC sp_OAMethod @dtime, 'SetFromTimestamp', @success OUT, @sTmp0
EXEC sp_OAMethod @dtime, 'ToDtObj', NULL, @bLocalTime, @dt
EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT
EXEC sp_OAGetProperty @dt, 'Day', @iTmp1 OUT
EXEC sp_OAGetProperty @dt, 'Year', @iTmp2 OUT
EXEC sp_OAGetProperty @dt, 'Hour', @iTmp3 OUT
EXEC sp_OAGetProperty @dt, 'Minute', @iTmp4 OUT
PRINT @iTmp0 + '/' + @iTmp1 + '/' + @iTmp2 + ' ' + @iTmp3 + ':' + @iTmp4
PRINT '----'
SELECT @i = @i + 1
END
PRINT 'Success.'
-- ------------------------------------------------------
-- The JSON response looks like this:
-- {
-- "QueryResponse": {
-- "Account": [
-- {
-- "Name": "Accounts Payable (A/P)",
-- "SubAccount": false,
-- "FullyQualifiedName": "Accounts Payable (A/P)",
-- "Active": true,
-- "Classification": "Liability",
-- "AccountType": "Accounts Payable",
-- "AccountSubType": "AccountsPayable",
-- "CurrentBalance": -1602.67,
-- "CurrentBalanceWithSubAccounts": -1602.67,
-- "CurrencyRef": {
-- "value": "USD",
-- "name": "United States Dollar"
-- },
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "33",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2016-09-10T10:12:02-07:00",
-- "LastUpdatedTime": "2016-09-17T13:10:36-07:00"
-- }
-- },
-- {
-- "Name": "Accounts Receivable (A/R)",
-- "SubAccount": false,
-- "FullyQualifiedName": "Accounts Receivable (A/R)",
-- "Active": true,
-- "Classification": "Asset",
-- "AccountType": "Accounts Receivable",
-- "AccountSubType": "AccountsReceivable",
-- "CurrentBalance": 5281.52,
-- "CurrentBalanceWithSubAccounts": 5281.52,
-- "CurrencyRef": {
-- "value": "USD",
-- "name": "United States Dollar"
-- },
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "84",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2016-09-14T14:49:29-07:00",
-- "LastUpdatedTime": "2016-09-17T13:16:17-07:00"
-- }
-- },
-- {
-- "Name": "Advertising",
-- "SubAccount": false,
-- "FullyQualifiedName": "Advertising",
-- "Active": true,
-- "Classification": "Expense",
-- "AccountType": "Expense",
-- "AccountSubType": "AdvertisingPromotional",
-- "CurrentBalance": 0,
-- "CurrentBalanceWithSubAccounts": 0,
-- "CurrencyRef": {
-- "value": "USD",
-- "name": "United States Dollar"
-- },
-- "domain": "QBO",
-- "sparse": false,
-- "Id": "7",
-- "SyncToken": "0",
-- "MetaData": {
-- "CreateTime": "2016-09-09T14:42:07-07:00",
-- "LastUpdatedTime": "2016-09-09T14:42:07-07:00"
-- }
-- },
-- ...
-- "time": "2016-10-24T15:35:25.067-07:00"
--
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbAuth
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @dtime
EXEC @hr = sp_OADestroy @dt
END
GO