(SQL Server) Xero API through an HTTP Proxy

This example demonstrates connecting through an HTTP proxy w/ 2-legged OAuth for a Xero private application.

This example requires Chilkat v9.5.0.64 or later

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)
    -- This example requires Chilkat v9.5.0.64 or later

    -- This sample code would typically be placed in a subroutine or function
    -- where the rest object is passed by reference.
    -- It does the OAuth1 setup and makes the initial connection.
    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

    -- Connect to the Xero server through an HTTP proxy, and then tell the REST object
    -- to use the socket connection.
    DECLARE @socket int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Socket', @socket OUT

    -- Set the HTTP proxy domain or IP address, and port.
    EXEC sp_OASetProperty @socket, 'HttpProxyHostname', '192.168.1.100'
    EXEC sp_OASetProperty @socket, 'HttpProxyPort', 8088
    EXEC sp_OASetProperty @socket, 'HttpProxyForHttp', 1

    -- Connect through the HTTP proxy..
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 5000
    DECLARE @success int
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, 'api.xero.com', @port, @bTls, @maxWaitMs
    IF @success <> 1
      BEGIN

        EXEC sp_OAGetProperty @socket, 'ConnectFailReason', @iTmp0 OUT
        PRINT 'Connect Failure Error Code: ' + @iTmp0
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- Use the HTTP proxied TLS connection:
    EXEC sp_OAMethod @rest, 'UseConnection', @success OUT, @socket, 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @socket
        RETURN
      END

    -- OK, we're connected.  
    -- The UseConnection method has an internal reference to the underlying/internal socket.
    -- The application can does not need to keep its socket object in existence.
    -- -----------------------------------------------------------------
    -- Now setup the OAuth1 authenticator..

    DECLARE @consumerKey nvarchar(4000)
    SELECT @consumerKey = 'XERO_PRIVATE_APP_KEY'
    DECLARE @consumerSecret nvarchar(4000)
    SELECT @consumerSecret = 'XERO_PRIVATE_APP_SECRET'

    -- Let's get our private key from our PFX (password protected), or the PEM (unprotected).
    -- You can decide which to use.  Either is OK, although I would recommend keeping your
    -- private keys in a PFX and not in an unprotected PEM.
    DECLARE @pfx int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Pfx', @pfx OUT

    EXEC sp_OAMethod @pfx, 'LoadPfxFile', @success OUT, 'qa_data/certs/xero_private_app/public_privatekey.pfx', 'PFX_PASSWORD'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END
    DECLARE @privKeyFromPfx int
    EXEC sp_OAMethod @pfx, 'GetPrivateKey', @privKeyFromPfx OUT, 0
    EXEC sp_OAGetProperty @pfx, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    -- Or we can load from a PEM..
    DECLARE @privKeyFromPem int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.PrivateKey', @privKeyFromPem OUT

    EXEC sp_OAMethod @privKeyFromPem, 'LoadPemFile', @success OUT, 'qa_data/certs/xero_private_app/privatekey.pem'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @privKeyFromPem, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @privKeyFromPem
        RETURN
      END

    -- Note: There are many other means for loading a private key, including
    -- from other formats and directly from memory (i.e. not file-based).

    DECLARE @oauth1 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.OAuth1', @oauth1 OUT

    EXEC sp_OASetProperty @oauth1, 'ConsumerKey', @consumerKey
    EXEC sp_OASetProperty @oauth1, 'ConsumerSecret', @consumerSecret
    EXEC sp_OASetProperty @oauth1, 'Token', @consumerKey
    EXEC sp_OASetProperty @oauth1, 'TokenSecret', @consumerSecret
    EXEC sp_OASetProperty @oauth1, 'SignatureMethod', 'RSA-SHA1'
    EXEC sp_OAMethod @oauth1, 'SetRsaKey', @success OUT, @privKeyFromPfx
    EXEC @hr = sp_OADestroy @privKeyFromPfx

    -- Install the OAuth1 authenticator.
    EXEC sp_OAMethod @rest, 'SetAuthOAuth1', @success OUT, @oauth1, 0


    PRINT 'OK, the Xero OAuth1 is initialized and the REST object is ready to make REST API calls..'

    -- -----------------------------------------------------------------
    -- Make a call to verify that OAuth1 through an HTTP proxy works..

    -- Get the full list of contacts.
    DECLARE @sbXml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbXml OUT

    EXEC sp_OAMethod @rest, 'FullRequestNoBodySb', @success OUT, 'GET', '/api.xro/2.0/Contacts', @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @privKeyFromPem
        EXEC @hr = sp_OADestroy @oauth1
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- A 200 response is expected for actual success.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @socket
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @privKeyFromPem
        EXEC @hr = sp_OADestroy @oauth1
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- Iterate over the contacts..
    DECLARE @bAutoTrim int
    SELECT @bAutoTrim = 0
    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadSb', @success OUT, @sbXml, @bAutoTrim
    EXEC sp_OAMethod @xml, 'SaveXml', @success OUT, 'qa_cache/xero_contacts.xml'

    -- How many records exist?
    DECLARE @recordCount int
    EXEC sp_OAMethod @xml, 'NumChildrenAt', @recordCount OUT, 'Contacts'

    PRINT 'numRecords = ' + @recordCount

    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @recordCount
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i

        EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'Contacts|Contact[i]|ContactID'
        PRINT 'ContactID: ' + @sTmp0
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @socket
    EXEC @hr = sp_OADestroy @pfx
    EXEC @hr = sp_OADestroy @privKeyFromPem
    EXEC @hr = sp_OADestroy @oauth1
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @xml


END
GO