SQL Server
SQL Server
Global Payments Card Authorization
See more Global Payments Examples
Demonstrates how to send a card payments authorization request.Chilkat SQL Server Downloads
-- 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 requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- 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
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
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
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha1'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hexlower'
DECLARE @sbA int
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
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
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 @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', @testUrl, @sTmp0, 'utf-8', 'application/xml', @resp
IF @success = 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
EXEC @hr = sp_OADestroy @resp
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
EXEC @hr = sp_OADestroy @resp
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 @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
EXEC @hr = sp_OADestroy @resp
END
GO