SQL Server
SQL Server
QuickBooks - Full Update an Account
See more QuickBooks Examples
Demonstrates how to update any of the writable fields of an existing account. The example first reads the JSON data for the account, makes some changes, and then POST's the updated JSON to update the account.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
-- 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
-- First get the full JSON for the account we'll be updating..
-- The company ID is the 123146096291789
-- The account ID is 29
DECLARE @responseBody nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseBody OUT, 'GET', '/v3/company/123146096291789/account/29?minorversion=45'
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
-- 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
-- Load the JSON response into a JSON object for parsing/updating
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @responseBody
-- Show the JSON..
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT '----'
-- The JSON passed in the HTTP POST for updating is the inner content of the Account
DECLARE @updateJson int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @updateJson OUT
EXEC sp_OAMethod @json, 'ObjectOf2', @success OUT, 'Account', @updateJson
-- Add or update the Description.
-- (If a Description already exists, it is replace, otherwise the Description is added.)
EXEC sp_OAMethod @updateJson, 'UpdateString', @success OUT, 'Description', 'Description added during update.'
-- Examine the JSON we'll be sending in the body of the POST.
EXEC sp_OASetProperty @updateJson, 'EmitCompact', 0
EXEC sp_OAMethod @updateJson, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT '----'
-- Now we're going to POST the changes to update the Quickbooks account.
EXEC sp_OAMethod @rest, 'ClearAllHeaders', @success OUT
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_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/json'
-- The updated JSON is passed as the HTTP request body.
EXEC sp_OAMethod @updateJson, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'FullRequestString', @responseBody OUT, 'POST', '/v3/company/123146096291789/account?minorversion=45', @sTmp0
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 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 @json
EXEC @hr = sp_OADestroy @updateJson
RETURN
END
-- 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
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @updateJson
RETURN
END
-- Load the JSON response into a JSON object to emit in indented form..
EXEC sp_OAMethod @json, 'Load', @success OUT, @responseBody
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
PRINT 'Success.'
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbAuth
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @updateJson
END
GO