(SQL Server) ClickBank Parse Instant Notification JSON

Demonstrates how to parse the JSON of a ClickBank instant notification.

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 @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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