SQL Server
SQL Server
CardConnect Capture Level 3 Data
See more CardConnect Examples
Demonstrates how to send a CardConnect Capture request with Lavel 3 line item data.If available, Level 3 line item data can be sent with the capture request, particularly for any commercial or corporate payment cards. To qualify for Level 3 Interchange rates, Level 2 data must also be provided. ...
See https://developer.cardconnect.com/cardconnect-api#capture-level-3-data
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 assumes 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
EXEC sp_OASetProperty @http, 'BasicAuth', 1
EXEC sp_OASetProperty @http, 'Login', 'API_USERNAME'
EXEC sp_OASetProperty @http, 'Password', 'API_PASSWORD'
-- Build and send the following JSON:
-- The "retref" is the value returned in the JSON response for the Authorization request.
-- {
-- "retref": "2880000333",
-- "shiptozip": "11111-1111",
-- "shipfromzip": "99999-9999",
-- "amount": "596.00",
-- "items": [
-- {
-- "discamnt": "0",
-- "unitcost": "900",
-- "uom": "CS",
-- "lineno": "1",
-- "description": "DESCRIPTION-1",
-- "taxamnt": "117",
-- "quantity": "1000",
-- "upc": "UPC-1",
-- "netamnt": "150",
-- "material": "MATERIAL-1"
-- },
-- {
-- "discamnt": "0",
-- "unitcost": "450",
-- "uom": "CS",
-- "lineno": "2",
-- "description": "DESCRIPTION-2",
-- "taxamnt": "117",
-- "quantity": "2000",
-- "upc": "UPC-1",
-- "netamnt": "300",
-- "material": "MATERIAL-2"
-- }
-- ],
-- "taxamount": "40.00",
-- "merchid": "123456789012",
-- "account": "4111111111111111",
-- "ponumber": "PO-0736332"
-- }
--
-- Use this online tool to generate the code from sample JSON:
-- Generate Code to Create JSON
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'retref', '112989260941'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shiptozip', '11111-1111'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipfromzip', '99999-9999'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'amount', '596.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].discamnt', '0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].unitcost', '900'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].uom', 'CS'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].lineno', '1'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].description', 'DESCRIPTION-1'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].taxamnt', '117'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].quantity', '1000'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].upc', 'UPC-1'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].netamnt', '150'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[0].material', 'MATERIAL-1'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[1].discamnt', '0'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[1].unitcost', '450'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[1].uom', 'CS'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[1].lineno', '2'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[1].description', 'DESCRIPTION-2'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[1].taxamnt', '117'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[1].quantity', '2000'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[1].upc', 'UPC-1'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[1].netamnt', '300'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'items[1].material', 'MATERIAL-2'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'taxamount', '40.00'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'merchid', 'MERCHANT_ID'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'account', '4111111111111111'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'ponumber', 'PO-0736332'
DECLARE @url nvarchar(4000)
SELECT @url = 'https://<site>.cardconnect.com:<port>/cardconnect/rest/capture'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'PUT', @url, @sTmp0, 'utf-8', 'application/json', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- A response status of 200 indicates potential success. The JSON response body
-- must be examined to determine if it was truly successful or an error.
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
DECLARE @jsonResp int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResp OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @jsonResp, 'Load', @success OUT, @sTmp0
EXEC sp_OASetProperty @jsonResp, 'EmitCompact', 0
PRINT 'response JSON:'
EXEC sp_OAMethod @jsonResp, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- A successful response looks like this:
-- {
-- "amount": "596.00",
-- "resptext": "Approval",
-- "setlstat": "Queued for Capture",
-- "commcard": " C ",
-- "respcode": "00",
-- "batchid": "1900942291",
-- "merchid": "MERCHANT_ID",
-- "token": "9418594164541111",
-- "authcode": "PPS158",
-- "respproc": "FNOR",
-- "retref": "112989260941",
-- "respstat": "A",
-- "account": "9418594164541111"
-- }
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
DECLARE @amount nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @amount OUT, 'amount'
DECLARE @resptext nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @resptext OUT, 'resptext'
DECLARE @setlstat nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @setlstat OUT, 'setlstat'
DECLARE @commcard nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @commcard OUT, 'commcard'
DECLARE @respcode nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @respcode OUT, 'respcode'
DECLARE @batchid nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @batchid OUT, 'batchid'
DECLARE @merchid nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @merchid OUT, 'merchid'
DECLARE @token nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @token OUT, 'token'
DECLARE @authcode nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @authcode OUT, 'authcode'
DECLARE @respproc nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @respproc OUT, 'respproc'
DECLARE @retref nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @retref OUT, 'retref'
DECLARE @respstat nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @respstat OUT, 'respstat'
DECLARE @account nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @account OUT, 'account'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonResp
END
GO