(SQL Server) Global Payments Card Authorization

Demonstrates how to send a card payments authorization request.

For more information, see https://developer.globalpay.com/#!/api/card-payments#api-authorisation

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- if you don't have a Client ID yet you can still quickly test some basic request types using the following URL and credentials:

    -- Test URL - https://test.realexpayments.com/epage-remote.cgi
    -- Client ID: realexsandbox
    -- Shared Secret: Po8lRRT67a
    DECLARE @testUrl nvarchar(4000)
    SELECT @testUrl = 'https://test.realexpayments.com/epage-remote.cgi'
    DECLARE @clientID nvarchar(4000)
    SELECT @clientID = 'realexsandbox'
    DECLARE @sharedSecret nvarchar(4000)
    SELECT @sharedSecret = 'Po8lRRT67a'

    DECLARE @amount nvarchar(4000)
    SELECT @amount = '1001'
    DECLARE @currency nvarchar(4000)
    SELECT @currency = 'EUR'
    DECLARE @cardNumber nvarchar(4000)
    SELECT @cardNumber = '4263970000005262'

    -- We'll be sending the following XML in the body of the request:

    -- <?xml version="1.0" encoding="UTF-8"?>
    -- <request type="auth" timestamp="20180613141207">
    --   <merchantid>MerchantId</merchantid>
    --   <account>internet</account>
    --   <channel>ECOM</channel>
    --   <orderid>N6qsk4kYRZihmPrTXWYS6g</orderid>
    --   <amount currency="EUR">1001</amount>
    --   <card>
    --     <number>4263970000005262</number>
    --     <expdate>0425</expdate>
    --     <chname>James Mason</chname>
    --     <type>VISA</type>
    --     <cvn>
    --       <number>123</number>
    --       <presind>1</presind>
    --     </cvn>
    --   </card>
    --   <autosettle flag="1"/>
    --   <sha1hash>87707637a34ba651b6185718c863abc64b673f20</sha1hash>
    -- </request>

    -- Use this online tool to generate code from sample XML: 
    -- Generate Code to Create XML

    -- Get the current date/time in this format:  20180613141207
    DECLARE @dt int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT

    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
    DECLARE @dtStr nvarchar(4000)
    EXEC sp_OAMethod @dt, 'GetAsIso8601', @dtStr OUT, 'YYYYMMDDhhmmss', 1

    -- Generate a unique order ID
    DECLARE @prng int
    -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT

    DECLARE @orderId nvarchar(4000)
    EXEC sp_OAMethod @prng, 'GenRandom', @orderId OUT, 32, 'base64url'

    -- Compute the sha1hash
    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha1'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hexlower'

    DECLARE @sbA int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbA OUT

    EXEC sp_OAMethod @sbA, 'Append', @success OUT, 'timestamp.merchantid.orderid.amount.currency.cardnumber'
    DECLARE @numReplaced int
    EXEC sp_OAMethod @sbA, 'Replace', @numReplaced OUT, 'timestamp', @dtStr
    EXEC sp_OAMethod @sbA, 'Replace', @numReplaced OUT, 'merchantid', @clientID
    EXEC sp_OAMethod @sbA, 'Replace', @numReplaced OUT, 'orderid', @orderId
    EXEC sp_OAMethod @sbA, 'Replace', @numReplaced OUT, 'amount', @amount
    EXEC sp_OAMethod @sbA, 'Replace', @numReplaced OUT, 'currency', @currency
    EXEC sp_OAMethod @sbA, 'Replace', @numReplaced OUT, 'cardnumber', @cardNumber

    DECLARE @hashA nvarchar(4000)
    EXEC sp_OAMethod @sbA, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'HashStringENC', @hashA OUT, @sTmp0

    DECLARE @sbB int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbB OUT

    EXEC sp_OAMethod @sbB, 'Append', @success OUT, @hashA
    EXEC sp_OAMethod @sbB, 'Append', @success OUT, '.'
    EXEC sp_OAMethod @sbB, 'Append', @success OUT, @sharedSecret

    DECLARE @hashB nvarchar(4000)
    EXEC sp_OAMethod @sbB, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'HashStringENC', @hashB OUT, @sTmp0

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'request'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'type', 'auth'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'timestamp', @dtStr
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'merchantid', @clientID
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'account', 'internet'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'channel', 'ECOM'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'orderid', @orderId
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'amount', 1, 'currency', @currency
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'amount', @amount
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'card|number', @cardNumber
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'card|expdate', '0425'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'card|chname', 'James Mason'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'card|type', 'VISA'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'card|cvn|number', '123'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'card|cvn|presind', '1'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'autosettle', 1, 'flag', '1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sha1hash', @hashB

    DECLARE @resp int
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostXml', @resp OUT, @testUrl, @sTmp0, 'utf-8'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @sbA
        EXEC @hr = sp_OADestroy @sbB
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response Status Code: ' + @iTmp0


    PRINT 'Response Body:'
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @sbA
        EXEC @hr = sp_OADestroy @sbB
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- A status code of 200 indicates we received an XML response, but it could be an error message.
    -- Here's an example of an error response:

    -- <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    -- <response timestamp="20200418142356">
    --     <orderid>N6qsk4kYRZihmPrTXWYS6g</orderid>
    --     <result>508</result>
    --     <message>Invalid timestamp: '{' Value exceeds allowable limit: '}'</message>
    -- </response>

    -- We need to check the "result" within the XML.
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0

    DECLARE @result int
    EXEC sp_OAMethod @xml, 'GetChildIntValue', @result OUT, 'result'

    PRINT 'result = ' + @result

    -- A successful result looks like this:

    -- <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    -- <response timestamp="20200418145519">
    --     <merchantid>realexsandbox</merchantid>
    --     <account>internet</account>
    --     <orderid>Cw93I1nFWVZuaATh46qMUCBlCcfrOvLo65C2cq5X-AY</orderid>
    --     <result>00</result>
    --     <authcode>L3pHww</authcode>
    --     <message>AUTH CODE: L3pHww</message>
    --     <pasref>96838535689610806</pasref>
    --     <cvnresult>M</cvnresult>
    --     <timetaken>87</timetaken>
    --     <authtimetaken>67</authtimetaken>
    --     <batchid>6322506</batchid>
    --     <sha1hash>2fd2f15f97f1775779fe9bda536dc8317a4b39f6</sha1hash>
    -- </response>

    IF @result = 0
      BEGIN

        EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'authcode'
        PRINT 'authcode = ' + @sTmp0

        PRINT 'Success.'

      END
    ELSE
      BEGIN

        PRINT 'Failed.'
      END

    EXEC @hr = sp_OADestroy @resp


    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbA
    EXEC @hr = sp_OADestroy @sbB
    EXEC @hr = sp_OADestroy @xml


END
GO