(SQL Server) Xero Get Attachment (Download a Xero Attachment)

Demonstrates how to get the content of an attachment in Xero.

Note: This example requires Chilkat v9.5.0.64 or greater.

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
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    -- Note: Requires Chilkat v9.5.0.64 or greater.

    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @rest int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Before sending REST API calls, the REST object needs to be
    -- initialized for OAuth1.
    -- See Xero 2-Legged OAuth1 Setup for sample code.

    -- Assuming the REST object's OAuth1 authenticator is setup, and the initial
    -- connection was made, we may now send REST HTTP requests..

    -- --------------------------------------------------------------
    -- First get a list of attachments for a given document (in this case a Receipt).

    DECLARE @endPoint nvarchar(4000)
    SELECT @endPoint = 'Receipts'
    DECLARE @receiptID nvarchar(4000)
    SELECT @receiptID = 'c4f40e59-c390-0001-caff-ce731c707d00'

    DECLARE @sbPath int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbPath OUT

    EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '/api.xro/2.0/{Endpoint}/{Guid}/Attachments/'
    DECLARE @numReplaced int
    EXEC sp_OAMethod @sbPath, 'Replace', @numReplaced OUT, '{Endpoint}', @endPoint
    EXEC sp_OAMethod @sbPath, 'Replace', @numReplaced OUT, '{Guid}', @receiptID

    DECLARE @responseXml nvarchar(4000)
    EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseXml OUT, 'GET', @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 @rest
        EXEC @hr = sp_OADestroy @sbPath
        RETURN
      END

    -- A 200 response is expected for actual success.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        PRINT @responseXml
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @sbPath
        RETURN
      END

    -- Examine the response.

    PRINT @responseXml

    -- A sample response looks like this:

    -- 	<Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    -- 	  <Id>b235646f-34ac-4b15-90ce-d63267f7cd33</Id>
    -- 	  <Status>OK</Status>
    -- 	  <ProviderName>ChilkatPrivate</ProviderName>
    -- 	  <DateTimeUTC>2016-11-11T14:32:17.0908971Z</DateTimeUTC>
    -- 	  <Attachments>
    -- 	    <Attachment>
    -- 	      <AttachmentID>0edcddc8-325f-40c7-b950-8c71f14afc7c</AttachmentID>
    -- 	      <FileName>penguins.jpg</FileName>
    -- 	      <Url>http://api.xero.com/api.xro/2.0/Receipts/c4f40e59-c390-0001-caff-ce731c707d00/Attachments/penguins.jpg</Url>
    -- 	      <MimeType>image/jpg</MimeType>
    -- 	      <ContentLength>777835</ContentLength>
    -- 	    </Attachment>
    -- 	    <Attachment>
    -- 	      <AttachmentID>0adffdc8-325f-65c7-b950-4391f14af908</AttachmentID>
    -- 	      <FileName>starfish.jpg</FileName>
    -- 	      <Url>http://api.xero.com/api.xro/2.0/Receipts/c4f40e59-c390-0001-caff-ce731c707d00/Attachments/starfish.jpg</Url>
    -- 	      <MimeType>image/jpg</MimeType>
    -- 	      <ContentLength>24537</ContentLength>
    -- 	    </Attachment>
    -- 	  </Attachments>
    -- 	</Response>

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @responseXml

    -- Iterate over the attachments and download each.
    DECLARE @numRecords int
    EXEC sp_OAMethod @xml, 'NumChildrenAt', @numRecords OUT, 'Attachments'

    PRINT 'Number of Attachments = ' + @numRecords

    DECLARE @sbSaveFilePath int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbSaveFilePath OUT

    DECLARE @sbAttachmentPath int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbAttachmentPath OUT

    DECLARE @attachmentData int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @attachmentData OUT

    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numRecords
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i
        DECLARE @attachmentID nvarchar(4000)
        EXEC sp_OAMethod @xml, 'GetChildContent', @attachmentID OUT, 'Attachments|Attachment[i]|AttachmentID'
        DECLARE @filename nvarchar(4000)
        EXEC sp_OAMethod @xml, 'GetChildContent', @filename OUT, 'Attachments|Attachment[i]|FileName'


        PRINT 'AttachmentID: ' + @attachmentID

        PRINT 'Filename: ' + @filename

        PRINT '----'

        -- Download this attachment.
        -- First build the path for this particular attachment by appending the FileName to the path used to get the list of attachments.
        EXEC sp_OAMethod @sbAttachmentPath, 'Clear', NULL
        EXEC sp_OAMethod @sbAttachmentPath, 'AppendSb', @success OUT, @sbPath
        EXEC sp_OAMethod @sbAttachmentPath, 'Append', @success OUT, @filename

        -- Send the GET request in one call, and then get the response in the next two.
        EXEC sp_OAMethod @sbAttachmentPath, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @rest, 'SendReqNoBody', @success OUT, 'GET', @sTmp0
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @rest
            EXEC @hr = sp_OADestroy @sbPath
            EXEC @hr = sp_OADestroy @xml
            EXEC @hr = sp_OADestroy @sbSaveFilePath
            EXEC @hr = sp_OADestroy @sbAttachmentPath
            EXEC @hr = sp_OADestroy @attachmentData
            RETURN
          END

        -- Get the response header.  If it's not a 200 success status code, then the response body does NOT contain
        -- the attachment data.
        DECLARE @statusCode int
        EXEC sp_OAMethod @rest, 'ReadResponseHeader', @statusCode OUT
        IF @statusCode = -1
          BEGIN
            -- We didn't get any response..
            EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @rest
            EXEC @hr = sp_OADestroy @sbPath
            EXEC @hr = sp_OADestroy @xml
            EXEC @hr = sp_OADestroy @sbSaveFilePath
            EXEC @hr = sp_OADestroy @sbAttachmentPath
            EXEC @hr = sp_OADestroy @attachmentData
            RETURN
          END
        IF @statusCode <> 200
          BEGIN

            PRINT 'Response Status: ' + @statusCode
            DECLARE @responseBody nvarchar(4000)
            EXEC sp_OAMethod @rest, 'ReadRespBodyString', @responseBody OUT
            EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
            IF @iTmp0 <> 1
              BEGIN

                PRINT 'Failed to read the response body.'
              END
            ELSE
              BEGIN

                PRINT @responseBody
              END

            PRINT 'Failed.'
            EXEC @hr = sp_OADestroy @rest
            EXEC @hr = sp_OADestroy @sbPath
            EXEC @hr = sp_OADestroy @xml
            EXEC @hr = sp_OADestroy @sbSaveFilePath
            EXEC @hr = sp_OADestroy @sbAttachmentPath
            EXEC @hr = sp_OADestroy @attachmentData
            RETURN
          END

        -- OK, the response header indicates the attachment content is forthcoming...
        -- There are a few ways to get the response body. If it is a very large attachment, it can be streamed directly
        -- to a file.  We'll assume it's not, so we'll get the response into a BinData object, and then save it to a file.
        EXEC sp_OAMethod @attachmentData, 'Clear', @success OUT
        EXEC sp_OAMethod @rest, 'ReadRespBd', @success OUT, @attachmentData
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @rest
            EXEC @hr = sp_OADestroy @sbPath
            EXEC @hr = sp_OADestroy @xml
            EXEC @hr = sp_OADestroy @sbSaveFilePath
            EXEC @hr = sp_OADestroy @sbAttachmentPath
            EXEC @hr = sp_OADestroy @attachmentData
            RETURN
          END

        -- Save the data to the file.
        EXEC sp_OAMethod @sbSaveFilePath, 'SetString', @success OUT, 'qa_output/'
        EXEC sp_OAMethod @sbSaveFilePath, 'Append', @success OUT, @filename
        EXEC sp_OAMethod @sbSaveFilePath, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @attachmentData, 'WriteFile', @success OUT, @sTmp0
        IF @success <> 1
          BEGIN

            PRINT 'Failed to save to output file.'
          END
        ELSE
          BEGIN

            EXEC sp_OAMethod @sbSaveFilePath, 'GetAsString', @sTmp0 OUT
            PRINT 'Saved to ' + @sTmp0
          END

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @sbPath
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @sbSaveFilePath
    EXEC @hr = sp_OADestroy @sbAttachmentPath
    EXEC @hr = sp_OADestroy @attachmentData


END
GO