SQL Server

Xero Create New Accounts

Demonstrates how to create a new account in Xero.

Note: Requires Chilkat v9.5.0.64 or greater.

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

    -- 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.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    -- --------------------------------------------------------------
    -- To add certain accounts, we need a unique Code that hasn't yet been used.
    -- Chilkat provided an example to download and save the Accounts data.
    -- We can load this data into a hashtable to help us find an unused Code.
    -- See Hash Xero Account Codes to see how this file was created.
    DECLARE @sbAccounts int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbAccounts OUT

    EXEC sp_OAMethod @sbAccounts, 'LoadFile', @success OUT, 'qa_cache/xero_accounts_by_code.xml', 'utf-8'
    IF Not @success
      BEGIN

        PRINT 'failed to load xero_accounts_by_code.xml'
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @sbAccounts
        RETURN
      END
    DECLARE @htAccounts int
    EXEC @hr = sp_OACreate 'Chilkat.Hashtable', @htAccounts OUT

    EXEC sp_OAMethod @htAccounts, 'AddFromXmlSb', @success OUT, @sbAccounts

    -- --------------------------------------------------------------
    -- Build the request XML to create a Xero sales account.
    -- Find an unused Code...
    DECLARE @code int
    SELECT @code = 600
    EXEC sp_OAMethod @htAccounts, 'ContainsIntKey', @iTmp0 OUT, @code
    WHILE @iTmp0 = 1
      BEGIN
        SELECT @code = @code + 1
      END

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

    EXEC sp_OASetProperty @xml, 'Tag', 'Account'
    EXEC sp_OAMethod @xml, 'NewChildInt2', NULL, 'Code', @code
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'Name', 'Sales - clearance lines'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'Type', 'SALES'
    EXEC sp_OASetProperty @xml, 'EmitCompact', 1

    -- Do not emit the XML declarator. Xero does not accept the XML if it
    -- has the initial line: <?xml version="1.0" encoding="utf-8"?>
    EXEC sp_OASetProperty @xml, 'EmitXmlDecl', 0

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'xml', @sTmp0

    EXEC sp_OASetProperty @rest, 'VerboseLogging', 1
    DECLARE @responseXml nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestFormUrlEncoded', @responseXml OUT, 'PUT', '/api.xro/2.0/Accounts'
    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 @sbAccounts
        EXEC @hr = sp_OADestroy @htAccounts
        EXEC @hr = sp_OADestroy @xml
        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 @sbAccounts
        EXEC @hr = sp_OADestroy @htAccounts
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- Examine the XML response

    PRINT @responseXml

    -- A successful XML response is as follows:

    -- <Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    --   <Id>dac71f1b-7afb-49a7-8a57-719b91f2088e</Id>
    --   <Status>OK</Status>
    --   <ProviderName>ChilkatPrivate</ProviderName>
    --   <DateTimeUTC>2016-11-10T23:53:43.487791Z</DateTimeUTC>
    --   <Accounts>
    --     <Account>
    --       <AccountID>cb8c94cf-57d4-41ee-b866-4c27632fe838</AccountID>
    --       <Code>601</Code>
    --       <Name>Sales - clearance lines</Name>
    --       <Status>ACTIVE</Status>
    --       <Type>SALES</Type>
    --       <TaxType>OUTPUT</TaxType>
    --       <Class>REVENUE</Class>
    --       <EnablePaymentsToAccount>false</EnablePaymentsToAccount>
    --       <ShowInExpenseClaims>false</ShowInExpenseClaims>
    --       <ReportingCode>REV</ReportingCode>
    --       <ReportingCodeName>Revenue</ReportingCodeName>
    --       <UpdatedDateUTC>2016-11-10T23:53:43.94</UpdatedDateUTC>
    --     </Account>
    --   </Accounts>
    -- </Response>

    -- To access the information:
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @responseXml


    EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'Accounts|Account|AccountID'
    PRINT 'AccountID: ' + @sTmp0

    EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'Accounts|Account|TaxType'
    PRINT 'TaxType: ' + @sTmp0
    -- ..

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @sbAccounts
    EXEC @hr = sp_OADestroy @htAccounts
    EXEC @hr = sp_OADestroy @xml


END
GO