(SQL Server) Payeezy Place Temp Authorization Hold on Buyer’s Credit Card

Demonstrates how to place a temporary authorization hold for the desired amount on the buyer’s credit card. You can Capture the authorized amount on completion of service or Void/Refund the transaction as required.

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 @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    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 @success int

    -- An API key such as y6pWAJNyJyjGv66IsVuWnklkKUPFbb0a
    DECLARE @apiKey nvarchar(4000)
    SELECT @apiKey = 'my_api_key'
    -- An API secret such as 86fbae7030253af3cd15faef2a1f4b67353e41fb6799f576b5093ae52901e6f7
    DECLARE @apiSecret nvarchar(4000)
    SELECT @apiSecret = 'my_api_secret'
    -- A token such as fdoa-a480ce8951daa73262734cf102641994c1e55e7cdf4c02b6
    DECLARE @token nvarchar(4000)
    SELECT @token = 'my_merchant_token'

    -- The nonce is a random number (bytes), something like "6057786719490086000"
    DECLARE @nonce nvarchar(4000)
    EXEC sp_OAMethod @prng, 'GenRandom', @nonce OUT, 8, 'decimal'

    PRINT 'nonce = ' + @nonce

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

    EXEC sp_OAMethod @dtNow, 'SetFromCurrentSystemTime', @success OUT
    DECLARE @sbTimestamp int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbTimestamp OUT

    -- Get the epoch timestamp in seconds
    EXEC sp_OAMethod @dtNow, 'GetAsUnixTimeStr', @sTmp0 OUT, 0
    EXEC sp_OAMethod @sbTimestamp, 'Append', @success OUT, @sTmp0
    -- Change it to milliseconds
    EXEC sp_OAMethod @sbTimestamp, 'Append', @success OUT, '000'
    -- The timestamp is a number similar to this: 1546011905000 (which is a timestamp taken on 28-Dec-2018).
    DECLARE @timestamp nvarchar(4000)
    EXEC sp_OAMethod @sbTimestamp, 'GetAsString', @timestamp OUT

    PRINT 'timestamp = ' + @timestamp

    -- Generate the following JSON request body:
    -- {
    --   "merchant_ref": "Astonishing-Sale",
    --   "transaction_type": "authorize",
    --   "method": "credit_card",
    --   "amount": "1299",
    --   "currency_code": "USD",
    --   "credit_card": {
    --     "type": "visa",
    --     "cardholder_name": "John Smith",
    --     "card_number": "4788250000028291",
    --     "exp_date": "1020",
    --     "cvv": "123"
    --   }
    -- }

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

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'merchant_ref', 'Astonishing-Sale'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'transaction_type', 'authorize'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'method', 'credit_card'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'amount', '1299'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'currency_code', 'USD'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'credit_card.type', 'visa'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'credit_card.cardholder_name', 'John Smith'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'credit_card.card_number', '4788250000028291'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'credit_card.exp_date', '1020'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'credit_card.cvv', '123'
    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- string hashData = apiKey + nonce + timestamp + token + jsonString;
    DECLARE @sbHmacData int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHmacData OUT

    EXEC sp_OAMethod @sbHmacData, 'Append', @success OUT, @apiKey
    EXEC sp_OAMethod @sbHmacData, 'Append', @success OUT, @nonce
    EXEC sp_OAMethod @sbHmacData, 'Append', @success OUT, @timestamp
    EXEC sp_OAMethod @sbHmacData, 'Append', @success OUT, @token
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OAMethod @sbHmacData, 'Append', @success OUT, @sTmp0

    -- HMAC the data to produce a hex string.
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hexlower'
    EXEC sp_OASetProperty @crypt, 'MacAlgorithm', 'hmac'
    EXEC sp_OAMethod @crypt, 'SetMacKeyString', @success OUT, @apiSecret
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
    EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'
    DECLARE @hexHash nvarchar(4000)
    EXEC sp_OAMethod @sbHmacData, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'MacStringENC', @hexHash OUT, @sTmp0

    PRINT 'hexHash = ' + @hexHash

    -- Now base64 encode the hex string:
    DECLARE @sbBase64Hash int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbBase64Hash OUT

    EXEC sp_OAMethod @sbBase64Hash, 'Append', @success OUT, @hexHash
    EXEC sp_OAMethod @sbBase64Hash, 'Encode', @success OUT, 'base64', 'utf-8'


    PRINT 'This is the Authorization header to be sent with the payeezy request:'

    EXEC sp_OAMethod @sbBase64Hash, 'GetAsString', @sTmp0 OUT
    PRINT 'Authorization: ' + @sTmp0

    -- -----------------------------------------------------------
    -- Now that we have the value for the Authorization header, send the POST containing the JSON.

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

    EXEC sp_OASetProperty @http, 'Accept', '*/*'
    EXEC sp_OASetProperty @http, 'UserAgent', ''
    EXEC sp_OAMethod @sbBase64Hash, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', @sTmp0
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'apikey', @apiKey
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'nonce', @nonce
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'timestamp', @timestamp
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'token', @token

    EXEC sp_OASetProperty @http, 'SessionLogFilename', 'qa_output/payeezy.txt'

    DECLARE @url nvarchar(4000)
    SELECT @url = 'https://api-cert.payeezy.com/v1/transactions'
    DECLARE @resp int
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostJson2', @resp OUT, @url, 'application/json', @sTmp0
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @sbTimestamp
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbHmacData
        EXEC @hr = sp_OADestroy @sbBase64Hash
        EXEC @hr = sp_OADestroy @http
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'response status code = ' + @iTmp0
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0
    EXEC @hr = sp_OADestroy @resp

    -- Sample JSON response:

    -- {
    --   "correlation_id": "228.4604632998994",
    --   "transaction_status": "approved",
    --   "validation_status": "success",
    --   "transaction_type": "authorize",
    --   "transaction_id": "ET175628",
    --   "transaction_tag": "2313721985",
    --   "method": "credit_card",
    --   "amount": "1299",
    --   "currency": "USD",
    --   "cvv2": "M",
    --   "token": {
    --     "token_type": "FDToken",
    --     "token_data": {
    --       "value": "9732261336638291"
    --     }
    --   },
    --   "card": {
    --     "type": "visa",
    --     "cardholder_name": "John Smith",
    --     "card_number": "8291",
    --     "exp_date": "1020"
    --   },
    --   "bank_resp_code": "100",
    --   "bank_message": "Approved",
    --   "gateway_resp_code": "00",
    --   "gateway_message": "Transaction Normal"
    -- }
    -- 

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'correlation_id'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'transaction_status'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'validation_status'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'transaction_type'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'transaction_id'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'transaction_tag'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'method'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'amount'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'currency'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'cvv2'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'token.token_type'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'token.token_data.value'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'card.type'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'card.cardholder_name'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'card.card_number'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'card.exp_date'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'bank_resp_code'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'bank_message'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'gateway_resp_code'
    PRINT @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'gateway_message'
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @dtNow
    EXEC @hr = sp_OADestroy @sbTimestamp
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sbHmacData
    EXEC @hr = sp_OADestroy @sbBase64Hash
    EXEC @hr = sp_OADestroy @http


END
GO