SQL Server

QuickBooks - Read an Employee (with Error Response)

See more QuickBooks Examples

Demonstrates how to fetch the information for a specific Quickbooks employee, but tries to fetch using an invalid employee id. Shows the error response and how to parse it. SQL Server

-- 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 <> 1
      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

    -- The company ID is 123146096291789
    -- The employee ID is 999  (There is no employee with this ID and we should get a 400 response code w/ a response body indicating the error.)
    DECLARE @responseBody nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseBody OUT, 'GET', '/v3/company/123146096291789/employee/999?minorversion=45'
    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
        RETURN
      END

    -- 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
    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- A 400 response is what we'd expect if trying to query for a non-existent employee ID
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 400
      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
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @sbAuth
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- If we got here, the response status code was 400..
    -- Iterate over the errors..
    DECLARE @numErrors int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numErrors OUT, 'Fault.Error'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numErrors
      BEGIN

        EXEC sp_OASetProperty @json, 'I', @i

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'Fault.Error[i].Message'
        PRINT 'Message: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'Fault.Error[i].Detail'
        PRINT 'Detail: ' + @sTmp0

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'Fault.Error[i].code'
        PRINT 'code: ' + @sTmp0

        PRINT '----'
        SELECT @i = @i + 1
      END

    -- ------------------------------------------------------
    -- The JSON error response looks like this:

    -- {
    --   "Fault": {
    --     "Error": [
    --       {
    --         "Message": "Object Not Found",
    --         "Detail": "Object Not Found : Something you're trying to use has been made inactive. Check the fields with accounts, customers, items, vendors or employees.",
    --         "code": "610",
    --         "element": ""
    --       }
    --     ],
    --     "type": "ValidationFault"
    --   },
    --   "time": "2020-03-07T10:21:19.089-08:00"
    -- }

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


END
GO