SQL Server
SQL Server
ClickBank Parse Instant Notification JSON
See more ClickBank Examples
Demonstrates how to parse the JSON of a ClickBank instant notification.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 @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @success int
EXEC sp_OAMethod @json, 'Load', @success OUT, '{ ... }'
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- See the parsing code below..
-- Sample notification JSON:
-- {
-- "transactionTime": "2016-06-05T13:47:51-06:00",
-- "receipt": "CWOGBZLN",
-- "transactionType": "SALE",
-- "vendor": "testacct",
-- "affiliate": "affiliate1",
-- "role": "VENDOR",
-- "totalAccountAmount": 0.00,
-- "paymentMethod": "VISA",
-- "totalOrderAmount": 0.00,
-- "totalTaxAmount": 0.00,
-- "totalShippingAmount": 0.00,
-- "currency": "USD",
-- "orderLanguage": "EN",
-- "trackingCodes": [
-- "tracking_code"
-- ],
-- "lineItems": [
-- {
-- "itemNo": "1",
-- "productTitle": "Product Title",
-- "shippable": false,
-- "recurring": false,
-- "accountAmount": 5.00,
-- "quantity": 1,
-- "downloadUrl": "<download_url>"
-- "lineItemType": "CART"
-- }
-- {
-- "itemNo": "2",
-- "productTitle": "Second Product",
-- "shippable": false,
-- "recurring": true,
-- "accountAmount": 2.99,
-- "quantity": 1,
-- "downloadUrl": "<download_url>"
-- "lineItemType": "CART"
-- }
--
-- ],
-- "customer": {
-- "shipping": {
-- "firstName": "TEST",
-- "lastName": "GUY",
-- "fullName": "Test Guy",
-- "phoneNumber": "",
-- "email": "test@example.net",
-- "address": {
-- "address1": "12 Test Lane",
-- "address2": "Suite 100",
-- "city": "LAS VEGAS",
-- "county": "LAS VEGAS",
-- "state": "NV",
-- "postalCode": "89101",
-- "country": "US"
-- }
-- },
-- "billing": {
-- "firstName": "TEST",
-- "lastName": "GUY",
-- "fullName": "Test Guy",
-- "phoneNumber": "",
-- "email": "test@example.net",
-- "address": {
-- "state": "NV",
-- "postalCode": "89101",
-- "country": "US"
-- }
-- }
-- },
-- "upsell": {
-- "upsellOriginalReceipt": "XXXXXXXX",
-- "upsellFlowId": 55,
-- "upsellSession": "VVVVVVVVVV",
-- "upsellPath": "upsell_path"
-- },
-- "hopfeed": {
-- "hopfeedClickId": "hopfeed_click",
-- "hopfeedApplicationId": 0000,
-- "hopfeedCreativeId": 0000,
-- "hopfeedApplicationPayout": 0.00,
-- "hopfeedVendorPayout": 0.00
-- },
-- "version": 6.0,
-- "attemptCount": 1,
-- "vendorVariables": {
-- "v1": "variable1",
-- "v2": "variable2"
-- }
-- }
--
--
DECLARE @transactionTime nvarchar(4000)
DECLARE @receipt nvarchar(4000)
DECLARE @transactionType nvarchar(4000)
DECLARE @vendor nvarchar(4000)
DECLARE @affiliate nvarchar(4000)
DECLARE @role nvarchar(4000)
DECLARE @totalAccountAmount nvarchar(4000)
DECLARE @paymentMethod nvarchar(4000)
DECLARE @totalOrderAmount nvarchar(4000)
DECLARE @totalTaxAmount nvarchar(4000)
DECLARE @totalShippingAmount nvarchar(4000)
DECLARE @currency nvarchar(4000)
DECLARE @orderLanguage nvarchar(4000)
DECLARE @customerShippingFirstName nvarchar(4000)
DECLARE @customerShippingLastName nvarchar(4000)
DECLARE @customerShippingFullName nvarchar(4000)
DECLARE @customerShippingPhoneNumber nvarchar(4000)
DECLARE @customerShippingEmail nvarchar(4000)
DECLARE @customerShippingAddressAddress1 nvarchar(4000)
DECLARE @customerShippingAddressAddress2 nvarchar(4000)
DECLARE @customerShippingAddressCity nvarchar(4000)
DECLARE @customerShippingAddressCounty nvarchar(4000)
DECLARE @customerShippingAddressState nvarchar(4000)
DECLARE @customerShippingAddressPostalCode nvarchar(4000)
DECLARE @customerShippingAddressCountry nvarchar(4000)
DECLARE @customerBillingFirstName nvarchar(4000)
DECLARE @customerBillingLastName nvarchar(4000)
DECLARE @customerBillingFullName nvarchar(4000)
DECLARE @customerBillingPhoneNumber nvarchar(4000)
DECLARE @customerBillingEmail nvarchar(4000)
DECLARE @customerBillingAddressState nvarchar(4000)
DECLARE @customerBillingAddressPostalCode nvarchar(4000)
DECLARE @customerBillingAddressCountry nvarchar(4000)
DECLARE @upsellUpsellOriginalReceipt nvarchar(4000)
DECLARE @upsellUpsellFlowId int
DECLARE @upsellUpsellSession nvarchar(4000)
DECLARE @upsellUpsellPath nvarchar(4000)
DECLARE @hopfeedHopfeedClickId nvarchar(4000)
DECLARE @hopfeedHopfeedApplicationId int
DECLARE @hopfeedHopfeedCreativeId int
DECLARE @hopfeedHopfeedApplicationPayout nvarchar(4000)
DECLARE @hopfeedHopfeedVendorPayout nvarchar(4000)
DECLARE @version nvarchar(4000)
DECLARE @attemptCount int
DECLARE @vendorVariablesV1 nvarchar(4000)
DECLARE @vendorVariablesV2 nvarchar(4000)
DECLARE @i int
DECLARE @count_i int
DECLARE @strVal nvarchar(4000)
DECLARE @itemNo nvarchar(4000)
DECLARE @productTitle nvarchar(4000)
DECLARE @shippable int
DECLARE @recurring int
DECLARE @accountAmount nvarchar(4000)
DECLARE @quantity int
DECLARE @downloadUrl nvarchar(4000)
DECLARE @lineItemType nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @transactionTime OUT, 'transactionTime'
EXEC sp_OAMethod @json, 'StringOf', @receipt OUT, 'receipt'
EXEC sp_OAMethod @json, 'StringOf', @transactionType OUT, 'transactionType'
EXEC sp_OAMethod @json, 'StringOf', @vendor OUT, 'vendor'
EXEC sp_OAMethod @json, 'StringOf', @affiliate OUT, 'affiliate'
EXEC sp_OAMethod @json, 'StringOf', @role OUT, 'role'
EXEC sp_OAMethod @json, 'StringOf', @totalAccountAmount OUT, 'totalAccountAmount'
EXEC sp_OAMethod @json, 'StringOf', @paymentMethod OUT, 'paymentMethod'
EXEC sp_OAMethod @json, 'StringOf', @totalOrderAmount OUT, 'totalOrderAmount'
EXEC sp_OAMethod @json, 'StringOf', @totalTaxAmount OUT, 'totalTaxAmount'
EXEC sp_OAMethod @json, 'StringOf', @totalShippingAmount OUT, 'totalShippingAmount'
EXEC sp_OAMethod @json, 'StringOf', @currency OUT, 'currency'
EXEC sp_OAMethod @json, 'StringOf', @orderLanguage OUT, 'orderLanguage'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingFirstName OUT, 'customer.shipping.firstName'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingLastName OUT, 'customer.shipping.lastName'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingFullName OUT, 'customer.shipping.fullName'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingPhoneNumber OUT, 'customer.shipping.phoneNumber'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingEmail OUT, 'customer.shipping.email'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingAddressAddress1 OUT, 'customer.shipping.address.address1'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingAddressAddress2 OUT, 'customer.shipping.address.address2'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingAddressCity OUT, 'customer.shipping.address.city'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingAddressCounty OUT, 'customer.shipping.address.county'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingAddressState OUT, 'customer.shipping.address.state'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingAddressPostalCode OUT, 'customer.shipping.address.postalCode'
EXEC sp_OAMethod @json, 'StringOf', @customerShippingAddressCountry OUT, 'customer.shipping.address.country'
EXEC sp_OAMethod @json, 'StringOf', @customerBillingFirstName OUT, 'customer.billing.firstName'
EXEC sp_OAMethod @json, 'StringOf', @customerBillingLastName OUT, 'customer.billing.lastName'
EXEC sp_OAMethod @json, 'StringOf', @customerBillingFullName OUT, 'customer.billing.fullName'
EXEC sp_OAMethod @json, 'StringOf', @customerBillingPhoneNumber OUT, 'customer.billing.phoneNumber'
EXEC sp_OAMethod @json, 'StringOf', @customerBillingEmail OUT, 'customer.billing.email'
EXEC sp_OAMethod @json, 'StringOf', @customerBillingAddressState OUT, 'customer.billing.address.state'
EXEC sp_OAMethod @json, 'StringOf', @customerBillingAddressPostalCode OUT, 'customer.billing.address.postalCode'
EXEC sp_OAMethod @json, 'StringOf', @customerBillingAddressCountry OUT, 'customer.billing.address.country'
EXEC sp_OAMethod @json, 'StringOf', @upsellUpsellOriginalReceipt OUT, 'upsell.upsellOriginalReceipt'
EXEC sp_OAMethod @json, 'IntOf', @upsellUpsellFlowId OUT, 'upsell.upsellFlowId'
EXEC sp_OAMethod @json, 'StringOf', @upsellUpsellSession OUT, 'upsell.upsellSession'
EXEC sp_OAMethod @json, 'StringOf', @upsellUpsellPath OUT, 'upsell.upsellPath'
EXEC sp_OAMethod @json, 'StringOf', @hopfeedHopfeedClickId OUT, 'hopfeed.hopfeedClickId'
EXEC sp_OAMethod @json, 'IntOf', @hopfeedHopfeedApplicationId OUT, 'hopfeed.hopfeedApplicationId'
EXEC sp_OAMethod @json, 'IntOf', @hopfeedHopfeedCreativeId OUT, 'hopfeed.hopfeedCreativeId'
EXEC sp_OAMethod @json, 'StringOf', @hopfeedHopfeedApplicationPayout OUT, 'hopfeed.hopfeedApplicationPayout'
EXEC sp_OAMethod @json, 'StringOf', @hopfeedHopfeedVendorPayout OUT, 'hopfeed.hopfeedVendorPayout'
EXEC sp_OAMethod @json, 'StringOf', @version OUT, 'version'
EXEC sp_OAMethod @json, 'IntOf', @attemptCount OUT, 'attemptCount'
EXEC sp_OAMethod @json, 'StringOf', @vendorVariablesV1 OUT, 'vendorVariables.v1'
EXEC sp_OAMethod @json, 'StringOf', @vendorVariablesV2 OUT, 'vendorVariables.v2'
SELECT @i = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'trackingCodes'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @strVal OUT, 'trackingCodes[i]'
SELECT @i = @i + 1
END
SELECT @i = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'lineItems'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @itemNo OUT, 'lineItems[i].itemNo'
EXEC sp_OAMethod @json, 'StringOf', @productTitle OUT, 'lineItems[i].productTitle'
EXEC sp_OAMethod @json, 'BoolOf', @shippable OUT, 'lineItems[i].shippable'
EXEC sp_OAMethod @json, 'BoolOf', @recurring OUT, 'lineItems[i].recurring'
EXEC sp_OAMethod @json, 'StringOf', @accountAmount OUT, 'lineItems[i].accountAmount'
EXEC sp_OAMethod @json, 'IntOf', @quantity OUT, 'lineItems[i].quantity'
EXEC sp_OAMethod @json, 'StringOf', @downloadUrl OUT, 'lineItems[i].downloadUrl'
EXEC sp_OAMethod @json, 'StringOf', @lineItemType OUT, 'lineItems[i].lineItemType'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @json
END
GO