SQL Server / New FreshBooks / Gateway Details
Back to Collection Items
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
-- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @success int
-- Adds the "Authorization: Bearer <access_token>" header.
EXEC sp_OASetProperty @http, 'AuthToken', '<access_token>'
DECLARE @sbResponseBody int
-- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://api.freshbooks.com/payments/account/{{accountId}}/gateway', @sbResponseBody
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbResponseBody
RETURN
END
DECLARE @jResp int
-- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jResp OUT
EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
EXEC sp_OASetProperty @jResp, 'EmitCompact', 0
PRINT 'Response Body:'
EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
PRINT @sTmp0
DECLARE @respStatusCode int
EXEC sp_OAGetProperty @http, 'LastStatus', @respStatusCode OUT
PRINT 'Response Status Code = ' + @respStatusCode
IF @respStatusCode >= 400
BEGIN
PRINT 'Response Header:'
EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
RETURN
END
-- Sample JSON response:
-- (Sample code for parsing the JSON response is shown below)
-- {
-- "gateway_connections": [
-- {
-- "stripe": {
-- "currencies": [
-- "AED",
-- "AFN",
-- "ALL",
-- "AMD",
-- "ANG",
-- "AOA",
-- "ARS",
-- "AUD",
-- "AWG",
-- "AZN",
-- "BAM",
-- "BBD",
-- "BDT",
-- "BGN",
-- "BIF",
-- "BMD",
-- "BND",
-- "BOB",
-- "BRL",
-- "BSD",
-- "BWP",
-- "BZD",
-- "CAD",
-- "CDF",
-- "CHF",
-- "CLP",
-- "CNY",
-- "COP",
-- "CRC",
-- "CVE",
-- "CZK",
-- "DJF",
-- "DKK",
-- "DOP",
-- "DZD",
-- "EGP",
-- "ETB",
-- "EUR",
-- "FJD",
-- "FKP",
-- "GBP",
-- "GEL",
-- "GIP",
-- "GMD",
-- "GNF",
-- "GTQ",
-- "GYD",
-- "HKD",
-- "HNL",
-- "HRK",
-- "HTG",
-- "HUF",
-- "IDR",
-- "ILS",
-- "INR",
-- "ISK",
-- "JMD",
-- "JPY",
-- "KES",
-- "KGS",
-- "KHR",
-- "KMF",
-- "KRW",
-- "KYD",
-- "KZT",
-- "LAK",
-- "LBP",
-- "LKR",
-- "LRD",
-- "LSL",
-- "MAD",
-- "MDL",
-- "MGA",
-- "MKD",
-- "MMK",
-- "MNT",
-- "MOP",
-- "MRO",
-- "MUR",
-- "MVR",
-- "MWK",
-- "MXN",
-- "MYR",
-- "MZN",
-- "NAD",
-- "NGN",
-- "NIO",
-- "NOK",
-- "NPR",
-- "NZD",
-- "PAB",
-- "PEN",
-- "PGK",
-- "PHP",
-- "PKR",
-- "PLN",
-- "PYG",
-- "QAR",
-- "RON",
-- "RSD",
-- "RUB",
-- "RWF",
-- "SAR",
-- "SBD",
-- "SCR",
-- "SEK",
-- "SGD",
-- "SHP",
-- "SLL",
-- "SOS",
-- "SRD",
-- "STD",
-- "SZL",
-- "THB",
-- "TJS",
-- "TOP",
-- "TRY",
-- "TTD",
-- "TWD",
-- "TZS",
-- "UAH",
-- "UGX",
-- "USD",
-- "UYU",
-- "UZS",
-- "VND",
-- "VUV",
-- "WST",
-- "XAF",
-- "XCD",
-- "XOF",
-- "XPF",
-- "YER",
-- "ZAR",
-- "ZMW"
-- ],
-- "country": "CA",
-- "gateway_user_id": "acct_198989W3477IY7l9murJy",
-- "id": "b5d4f07757ff444db095d4877878f3bc6",
-- "user_state": "registered",
-- "email": "api.freshbooks@gmail.com"
-- },
-- "fbpay": {
-- "currencies": [
-- "CAD"
-- ],
-- "account_id": "27873233720",
-- "action_reasons": [
-- "kyc",
-- "bank_account"
-- ],
-- "country": "CA",
-- "tos_accepted": true,
-- "email": "api.freshbooks@gmail.com",
-- "manage_account_url": "https://wepay.com/account/2430223721",
-- "state": "complete_kyc",
-- "bank_info": {
-- "bank_name": null,
-- "withdrawal_period": null,
-- "last_payment_date": null,
-- "withdrawal_schedule": [
-- ],
-- "next_payout_date": null,
-- "incoming_pending_amount": "0",
-- "last_payment_amount": null,
-- "withdrawal_type": null,
-- "outgoing_withdrawal_schedule": [
-- ],
-- "total_payout": "0"
-- },
-- "pricing": {
-- "ach_tier_3": null,
-- "percent_amex": "3.50",
-- "percent_non_amex_with_card": "2.70",
-- "percent_non_amex": "2.90",
-- "percent_virtual_terminal": "3.50",
-- "percent_amex_with_card": "3.40",
-- "ach_tier_1": null,
-- "ach_tier_2": null,
-- "tier_id": 105,
-- "per_transaction_fee": "0.30"
-- },
-- "visa_debit_accepted": false,
-- "id": "1074c55d1195cd470e0989b60a2f4ffad68c",
-- "bank_transfer_enabled": true
-- }
-- }
-- ]
-- }
-- Sample code for parsing the JSON response...
-- Use this online tool to generate parsing code from sample JSON: Generate JSON Parsing Code
DECLARE @Country nvarchar(4000)
DECLARE @Gateway_user_id nvarchar(4000)
DECLARE @Id nvarchar(4000)
DECLARE @User_state nvarchar(4000)
DECLARE @v_Email nvarchar(4000)
DECLARE @Account_id nvarchar(4000)
DECLARE @fbpayCountry nvarchar(4000)
DECLARE @Tos_accepted int
DECLARE @fbpayEmail nvarchar(4000)
DECLARE @Manage_account_url nvarchar(4000)
DECLARE @State nvarchar(4000)
DECLARE @Bank_name nvarchar(4000)
DECLARE @Withdrawal_period nvarchar(4000)
DECLARE @Last_payment_date nvarchar(4000)
DECLARE @Next_payout_date nvarchar(4000)
DECLARE @Incoming_pending_amount nvarchar(4000)
DECLARE @Last_payment_amount nvarchar(4000)
DECLARE @Withdrawal_type nvarchar(4000)
DECLARE @Total_payout nvarchar(4000)
DECLARE @Ach_tier_3 nvarchar(4000)
DECLARE @Percent_amex nvarchar(4000)
DECLARE @Percent_non_amex_with_card nvarchar(4000)
DECLARE @Percent_non_amex nvarchar(4000)
DECLARE @Percent_virtual_terminal nvarchar(4000)
DECLARE @Percent_amex_with_card nvarchar(4000)
DECLARE @Ach_tier_1 nvarchar(4000)
DECLARE @Ach_tier_2 nvarchar(4000)
DECLARE @Tier_id int
DECLARE @Per_transaction_fee nvarchar(4000)
DECLARE @Visa_debit_accepted int
DECLARE @fbpayId nvarchar(4000)
DECLARE @Bank_transfer_enabled int
DECLARE @j int
DECLARE @count_j int
DECLARE @strVal nvarchar(4000)
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'gateway_connections'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'StringOf', @Country OUT, 'gateway_connections[i].stripe.country'
EXEC sp_OAMethod @jResp, 'StringOf', @Gateway_user_id OUT, 'gateway_connections[i].stripe.gateway_user_id'
EXEC sp_OAMethod @jResp, 'StringOf', @Id OUT, 'gateway_connections[i].stripe.id'
EXEC sp_OAMethod @jResp, 'StringOf', @User_state OUT, 'gateway_connections[i].stripe.user_state'
EXEC sp_OAMethod @jResp, 'StringOf', @v_Email OUT, 'gateway_connections[i].stripe.email'
EXEC sp_OAMethod @jResp, 'StringOf', @Account_id OUT, 'gateway_connections[i].fbpay.account_id'
EXEC sp_OAMethod @jResp, 'StringOf', @fbpayCountry OUT, 'gateway_connections[i].fbpay.country'
EXEC sp_OAMethod @jResp, 'BoolOf', @Tos_accepted OUT, 'gateway_connections[i].fbpay.tos_accepted'
EXEC sp_OAMethod @jResp, 'StringOf', @fbpayEmail OUT, 'gateway_connections[i].fbpay.email'
EXEC sp_OAMethod @jResp, 'StringOf', @Manage_account_url OUT, 'gateway_connections[i].fbpay.manage_account_url'
EXEC sp_OAMethod @jResp, 'StringOf', @State OUT, 'gateway_connections[i].fbpay.state'
EXEC sp_OAMethod @jResp, 'StringOf', @Bank_name OUT, 'gateway_connections[i].fbpay.bank_info.bank_name'
EXEC sp_OAMethod @jResp, 'StringOf', @Withdrawal_period OUT, 'gateway_connections[i].fbpay.bank_info.withdrawal_period'
EXEC sp_OAMethod @jResp, 'StringOf', @Last_payment_date OUT, 'gateway_connections[i].fbpay.bank_info.last_payment_date'
EXEC sp_OAMethod @jResp, 'StringOf', @Next_payout_date OUT, 'gateway_connections[i].fbpay.bank_info.next_payout_date'
EXEC sp_OAMethod @jResp, 'StringOf', @Incoming_pending_amount OUT, 'gateway_connections[i].fbpay.bank_info.incoming_pending_amount'
EXEC sp_OAMethod @jResp, 'StringOf', @Last_payment_amount OUT, 'gateway_connections[i].fbpay.bank_info.last_payment_amount'
EXEC sp_OAMethod @jResp, 'StringOf', @Withdrawal_type OUT, 'gateway_connections[i].fbpay.bank_info.withdrawal_type'
EXEC sp_OAMethod @jResp, 'StringOf', @Total_payout OUT, 'gateway_connections[i].fbpay.bank_info.total_payout'
EXEC sp_OAMethod @jResp, 'StringOf', @Ach_tier_3 OUT, 'gateway_connections[i].fbpay.pricing.ach_tier_3'
EXEC sp_OAMethod @jResp, 'StringOf', @Percent_amex OUT, 'gateway_connections[i].fbpay.pricing.percent_amex'
EXEC sp_OAMethod @jResp, 'StringOf', @Percent_non_amex_with_card OUT, 'gateway_connections[i].fbpay.pricing.percent_non_amex_with_card'
EXEC sp_OAMethod @jResp, 'StringOf', @Percent_non_amex OUT, 'gateway_connections[i].fbpay.pricing.percent_non_amex'
EXEC sp_OAMethod @jResp, 'StringOf', @Percent_virtual_terminal OUT, 'gateway_connections[i].fbpay.pricing.percent_virtual_terminal'
EXEC sp_OAMethod @jResp, 'StringOf', @Percent_amex_with_card OUT, 'gateway_connections[i].fbpay.pricing.percent_amex_with_card'
EXEC sp_OAMethod @jResp, 'StringOf', @Ach_tier_1 OUT, 'gateway_connections[i].fbpay.pricing.ach_tier_1'
EXEC sp_OAMethod @jResp, 'StringOf', @Ach_tier_2 OUT, 'gateway_connections[i].fbpay.pricing.ach_tier_2'
EXEC sp_OAMethod @jResp, 'IntOf', @Tier_id OUT, 'gateway_connections[i].fbpay.pricing.tier_id'
EXEC sp_OAMethod @jResp, 'StringOf', @Per_transaction_fee OUT, 'gateway_connections[i].fbpay.pricing.per_transaction_fee'
EXEC sp_OAMethod @jResp, 'BoolOf', @Visa_debit_accepted OUT, 'gateway_connections[i].fbpay.visa_debit_accepted'
EXEC sp_OAMethod @jResp, 'StringOf', @fbpayId OUT, 'gateway_connections[i].fbpay.id'
EXEC sp_OAMethod @jResp, 'BoolOf', @Bank_transfer_enabled OUT, 'gateway_connections[i].fbpay.bank_transfer_enabled'
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'gateway_connections[i].stripe.currencies'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'gateway_connections[i].stripe.currencies[j]'
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'gateway_connections[i].fbpay.currencies'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'gateway_connections[i].fbpay.currencies[j]'
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'gateway_connections[i].fbpay.action_reasons'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'gateway_connections[i].fbpay.action_reasons[j]'
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'gateway_connections[i].fbpay.bank_info.withdrawal_schedule'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'gateway_connections[i].fbpay.bank_info.outgoing_withdrawal_schedule'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
END
GO
Curl Command
curl -X GET
-H "Authorization: Bearer <access_token>"
https://api.freshbooks.com/payments/account/{{accountId}}/gateway
Postman Collection Item JSON
{
"name": "Gateway Details",
"request": {
"method": "GET",
"header": [
],
"url": {
"raw": "https://api.freshbooks.com/payments/account/{{accountId}}/gateway",
"protocol": "https",
"host": [
"api",
"freshbooks",
"com"
],
"path": [
"payments",
"account",
"{{accountId}}",
"gateway"
]
}
},
"response": [
{
"name": "Gateway Details",
"originalRequest": {
"method": "GET",
"header": [
],
"url": {
"raw": "https://api.freshbooks.com/payments/account/{{accountId}}/gateway",
"protocol": "https",
"host": [
"api",
"freshbooks",
"com"
],
"path": [
"payments",
"account",
"{{accountId}}",
"gateway"
]
}
},
"status": "OK",
"code": 200,
"_postman_previewlanguage": "json",
"header": [
{
"key": "Server",
"value": "nginx"
},
{
"key": "Content-Type",
"value": "application/vnd.api+json"
},
{
"key": "X-CardApp-Version",
"value": "1905.6.0"
},
{
"key": "X-NewRelic-App-Data",
"value": "PxQBWV5TCBABV1FUDwYGV1QTGhE1AwE2QgNWEVlbQFtcCxYnRA9QFg1ZWU4FAkpXURQRTEVURAkRQ1VWEUgEA0QHTwNBFkZXQApME1IECQUFQlIWBxpKVkMLFBBUVBVKVB0GHVpVWloEUQFaAAwFCgZSAFAdTAJORgQHVQRTDQUAVVFWVVRVVl0TGlUDChAHbQ=="
},
{
"key": "X-RateLimit-Limit",
"value": "20"
},
{
"key": "X-RateLimit-Remaining",
"value": "0"
},
{
"key": "X-RateLimit-Reset",
"value": "1557346766"
},
{
"key": "Retry-After",
"value": "57"
},
{
"key": "Access-Control-Allow-Origin",
"value": "*"
},
{
"key": "Expires",
"value": "Tue, 08 May 2018 20:18:28 GMT"
},
{
"key": "Cache-Control",
"value": "no-cache"
},
{
"key": "Via",
"value": "1.1 google"
},
{
"key": "Via",
"value": "1.1 varnish"
},
{
"key": "Accept-Ranges",
"value": "bytes"
},
{
"key": "Accept-Ranges",
"value": "bytes"
},
{
"key": "Age",
"value": "0"
},
{
"key": "Age",
"value": "0"
},
{
"key": "Content-Length",
"value": "2097"
},
{
"key": "Date",
"value": "Wed, 08 May 2019 20:18:28 GMT"
},
{
"key": "Connection",
"value": "keep-alive"
},
{
"key": "X-Served-By",
"value": "cache-mdw17361-MDW"
},
{
"key": "X-Cache",
"value": "MISS"
},
{
"key": "X-Cache-Hits",
"value": "0"
},
{
"key": "Country",
"value": "CA"
},
{
"key": "Strict-Transport-Security",
"value": "max-age=31536000; includeSubDomains; preload"
}
],
"cookie": [
],
"body": "{\n \"gateway_connections\": [\n {\n \"stripe\": {\n \"currencies\": [\n \"AED\",\n \"AFN\",\n \"ALL\",\n \"AMD\",\n \"ANG\",\n \"AOA\",\n \"ARS\",\n \"AUD\",\n \"AWG\",\n \"AZN\",\n \"BAM\",\n \"BBD\",\n \"BDT\",\n \"BGN\",\n \"BIF\",\n \"BMD\",\n \"BND\",\n \"BOB\",\n \"BRL\",\n \"BSD\",\n \"BWP\",\n \"BZD\",\n \"CAD\",\n \"CDF\",\n \"CHF\",\n \"CLP\",\n \"CNY\",\n \"COP\",\n \"CRC\",\n \"CVE\",\n \"CZK\",\n \"DJF\",\n \"DKK\",\n \"DOP\",\n \"DZD\",\n \"EGP\",\n \"ETB\",\n \"EUR\",\n \"FJD\",\n \"FKP\",\n \"GBP\",\n \"GEL\",\n \"GIP\",\n \"GMD\",\n \"GNF\",\n \"GTQ\",\n \"GYD\",\n \"HKD\",\n \"HNL\",\n \"HRK\",\n \"HTG\",\n \"HUF\",\n \"IDR\",\n \"ILS\",\n \"INR\",\n \"ISK\",\n \"JMD\",\n \"JPY\",\n \"KES\",\n \"KGS\",\n \"KHR\",\n \"KMF\",\n \"KRW\",\n \"KYD\",\n \"KZT\",\n \"LAK\",\n \"LBP\",\n \"LKR\",\n \"LRD\",\n \"LSL\",\n \"MAD\",\n \"MDL\",\n \"MGA\",\n \"MKD\",\n \"MMK\",\n \"MNT\",\n \"MOP\",\n \"MRO\",\n \"MUR\",\n \"MVR\",\n \"MWK\",\n \"MXN\",\n \"MYR\",\n \"MZN\",\n \"NAD\",\n \"NGN\",\n \"NIO\",\n \"NOK\",\n \"NPR\",\n \"NZD\",\n \"PAB\",\n \"PEN\",\n \"PGK\",\n \"PHP\",\n \"PKR\",\n \"PLN\",\n \"PYG\",\n \"QAR\",\n \"RON\",\n \"RSD\",\n \"RUB\",\n \"RWF\",\n \"SAR\",\n \"SBD\",\n \"SCR\",\n \"SEK\",\n \"SGD\",\n \"SHP\",\n \"SLL\",\n \"SOS\",\n \"SRD\",\n \"STD\",\n \"SZL\",\n \"THB\",\n \"TJS\",\n \"TOP\",\n \"TRY\",\n \"TTD\",\n \"TWD\",\n \"TZS\",\n \"UAH\",\n \"UGX\",\n \"USD\",\n \"UYU\",\n \"UZS\",\n \"VND\",\n \"VUV\",\n \"WST\",\n \"XAF\",\n \"XCD\",\n \"XOF\",\n \"XPF\",\n \"YER\",\n \"ZAR\",\n \"ZMW\"\n ],\n \"country\": \"CA\",\n \"gateway_user_id\": \"acct_198989W3477IY7l9murJy\",\n \"id\": \"b5d4f07757ff444db095d4877878f3bc6\",\n \"user_state\": \"registered\",\n \"email\": \"api.freshbooks@gmail.com\"\n },\n \"fbpay\": {\n \"currencies\": [\n \"CAD\"\n ],\n \"account_id\": \"27873233720\",\n \"action_reasons\": [\n \"kyc\",\n \"bank_account\"\n ],\n \"country\": \"CA\",\n \"tos_accepted\": true,\n \"email\": \"api.freshbooks@gmail.com\",\n \"manage_account_url\": \"https://wepay.com/account/2430223721\",\n \"state\": \"complete_kyc\",\n \"bank_info\": {\n \"bank_name\": null,\n \"withdrawal_period\": null,\n \"last_payment_date\": null,\n \"withdrawal_schedule\": [],\n \"next_payout_date\": null,\n \"incoming_pending_amount\": \"0\",\n \"last_payment_amount\": null,\n \"withdrawal_type\": null,\n \"outgoing_withdrawal_schedule\": [],\n \"total_payout\": \"0\"\n },\n \"pricing\": {\n \"ach_tier_3\": null,\n \"percent_amex\": \"3.50\",\n \"percent_non_amex_with_card\": \"2.70\",\n \"percent_non_amex\": \"2.90\",\n \"percent_virtual_terminal\": \"3.50\",\n \"percent_amex_with_card\": \"3.40\",\n \"ach_tier_1\": null,\n \"ach_tier_2\": null,\n \"tier_id\": 105,\n \"per_transaction_fee\": \"0.30\"\n },\n \"visa_debit_accepted\": false,\n \"id\": \"1074c55d1195cd470e0989b60a2f4ffad68c\",\n \"bank_transfer_enabled\": true\n }\n }\n ]\n}"
}
]
}
