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
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', '{{authorization-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://domain.com/crm/v2/org', @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)
-- {
-- "org": [
-- {
-- "country": null,
-- "photo_id": null,
-- "city": null,
-- "description": null,
-- "mc_status": true,
-- "gapps_enabled": false,
-- "domain_name": "org60036018",
-- "translation_enabled": true,
-- "street": null,
-- "alias": null,
-- "currency": "Indian Rupee",
-- "id": "738964000000300043",
-- "state": null,
-- "fax": null,
-- "employee_count": null,
-- "zip": null,
-- "website": null,
-- "currency_symbol": "₹",
-- "mobile": null,
-- "currency_locale": "en_IN",
-- "primary_zuid": "60034346",
-- "zia_portal_id": null,
-- "time_zone": "Asia/Kolkata",
-- "zgid": "60036018",
-- "country_code": "IN",
-- "license_details": {
-- "paid_expiry": "2021-10-20T00:00:00+00:00",
-- "users_license_purchased": 25,
-- "trial_type": null,
-- "trial_expiry": null,
-- "paid": true,
-- "paid_type": "enterprise"
-- },
-- "phone": "312",
-- "company_name": "Zylker",
-- "privacy_settings": true,
-- "primary_email": "patricia@zylker.com",
-- "iso_code": "INR"
-- }
-- ]
-- }
-- 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 @photo_id nvarchar(4000)
DECLARE @city nvarchar(4000)
DECLARE @description nvarchar(4000)
DECLARE @mc_status int
DECLARE @gapps_enabled int
DECLARE @domain_name nvarchar(4000)
DECLARE @translation_enabled int
DECLARE @street nvarchar(4000)
DECLARE @alias nvarchar(4000)
DECLARE @v_currency nvarchar(4000)
DECLARE @id nvarchar(4000)
DECLARE @state nvarchar(4000)
DECLARE @fax nvarchar(4000)
DECLARE @employee_count nvarchar(4000)
DECLARE @zip nvarchar(4000)
DECLARE @website nvarchar(4000)
DECLARE @currency_symbol nvarchar(4000)
DECLARE @mobile nvarchar(4000)
DECLARE @currency_locale nvarchar(4000)
DECLARE @primary_zuid nvarchar(4000)
DECLARE @zia_portal_id nvarchar(4000)
DECLARE @time_zone nvarchar(4000)
DECLARE @zgid nvarchar(4000)
DECLARE @country_code nvarchar(4000)
DECLARE @Paid_expiry nvarchar(4000)
DECLARE @Users_license_purchased int
DECLARE @Trial_type nvarchar(4000)
DECLARE @Trial_expiry nvarchar(4000)
DECLARE @Paid int
DECLARE @Paid_type nvarchar(4000)
DECLARE @phone nvarchar(4000)
DECLARE @company_name nvarchar(4000)
DECLARE @privacy_settings int
DECLARE @primary_email nvarchar(4000)
DECLARE @iso_code nvarchar(4000)
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'org'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'StringOf', @country OUT, 'org[i].country'
EXEC sp_OAMethod @jResp, 'StringOf', @photo_id OUT, 'org[i].photo_id'
EXEC sp_OAMethod @jResp, 'StringOf', @city OUT, 'org[i].city'
EXEC sp_OAMethod @jResp, 'StringOf', @description OUT, 'org[i].description'
EXEC sp_OAMethod @jResp, 'BoolOf', @mc_status OUT, 'org[i].mc_status'
EXEC sp_OAMethod @jResp, 'BoolOf', @gapps_enabled OUT, 'org[i].gapps_enabled'
EXEC sp_OAMethod @jResp, 'StringOf', @domain_name OUT, 'org[i].domain_name'
EXEC sp_OAMethod @jResp, 'BoolOf', @translation_enabled OUT, 'org[i].translation_enabled'
EXEC sp_OAMethod @jResp, 'StringOf', @street OUT, 'org[i].street'
EXEC sp_OAMethod @jResp, 'StringOf', @alias OUT, 'org[i].alias'
EXEC sp_OAMethod @jResp, 'StringOf', @v_currency OUT, 'org[i].currency'
EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'org[i].id'
EXEC sp_OAMethod @jResp, 'StringOf', @state OUT, 'org[i].state'
EXEC sp_OAMethod @jResp, 'StringOf', @fax OUT, 'org[i].fax'
EXEC sp_OAMethod @jResp, 'StringOf', @employee_count OUT, 'org[i].employee_count'
EXEC sp_OAMethod @jResp, 'StringOf', @zip OUT, 'org[i].zip'
EXEC sp_OAMethod @jResp, 'StringOf', @website OUT, 'org[i].website'
EXEC sp_OAMethod @jResp, 'StringOf', @currency_symbol OUT, 'org[i].currency_symbol'
EXEC sp_OAMethod @jResp, 'StringOf', @mobile OUT, 'org[i].mobile'
EXEC sp_OAMethod @jResp, 'StringOf', @currency_locale OUT, 'org[i].currency_locale'
EXEC sp_OAMethod @jResp, 'StringOf', @primary_zuid OUT, 'org[i].primary_zuid'
EXEC sp_OAMethod @jResp, 'StringOf', @zia_portal_id OUT, 'org[i].zia_portal_id'
EXEC sp_OAMethod @jResp, 'StringOf', @time_zone OUT, 'org[i].time_zone'
EXEC sp_OAMethod @jResp, 'StringOf', @zgid OUT, 'org[i].zgid'
EXEC sp_OAMethod @jResp, 'StringOf', @country_code OUT, 'org[i].country_code'
EXEC sp_OAMethod @jResp, 'StringOf', @Paid_expiry OUT, 'org[i].license_details.paid_expiry'
EXEC sp_OAMethod @jResp, 'IntOf', @Users_license_purchased OUT, 'org[i].license_details.users_license_purchased'
EXEC sp_OAMethod @jResp, 'StringOf', @Trial_type OUT, 'org[i].license_details.trial_type'
EXEC sp_OAMethod @jResp, 'StringOf', @Trial_expiry OUT, 'org[i].license_details.trial_expiry'
EXEC sp_OAMethod @jResp, 'BoolOf', @Paid OUT, 'org[i].license_details.paid'
EXEC sp_OAMethod @jResp, 'StringOf', @Paid_type OUT, 'org[i].license_details.paid_type'
EXEC sp_OAMethod @jResp, 'StringOf', @phone OUT, 'org[i].phone'
EXEC sp_OAMethod @jResp, 'StringOf', @company_name OUT, 'org[i].company_name'
EXEC sp_OAMethod @jResp, 'BoolOf', @privacy_settings OUT, 'org[i].privacy_settings'
EXEC sp_OAMethod @jResp, 'StringOf', @primary_email OUT, 'org[i].primary_email'
EXEC sp_OAMethod @jResp, 'StringOf', @iso_code OUT, 'org[i].iso_code'
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>"
-H "Authorization: {{authorization-token}}"
https://domain.com/crm/v2/org
Postman Collection Item JSON
{
"name": "Organization Details",
"request": {
"auth": {
"type": "bearer",
"bearer": [
{
"key": "token",
"value": "{{access-token}}",
"type": "string"
}
]
},
"method": "GET",
"header": [
{
"key": "Authorization",
"value": "{{authorization-token}}"
}
],
"url": {
"raw": "{{api-domain}}/crm/v2/org",
"host": [
"{{api-domain}}"
],
"path": [
"crm",
"v2",
"org"
]
},
"description": "To get the organization data."
},
"response": [
{
"name": "SUCCESS RESPONSE",
"originalRequest": {
"method": "GET",
"header": [
{
"key": "Authorization",
"value": "{{authorization-token}}"
}
],
"url": {
"raw": "{{api-domain}}/crm/v2/org",
"host": [
"{{api-domain}}"
],
"path": [
"crm",
"v2",
"org"
]
}
},
"status": "OK",
"code": 200,
"_postman_previewlanguage": "json",
"header": [
{
"key": "Server",
"value": "ZGS"
},
{
"key": "Date",
"value": "Tue, 11 May 2021 10:49:49 GMT"
},
{
"key": "Content-Type",
"value": "application/json;charset=utf-8"
},
{
"key": "Content-Length",
"value": "796"
},
{
"key": "Connection",
"value": "keep-alive"
},
{
"key": "Referrer-Policy",
"value": "strict-origin"
},
{
"key": "X-Content-Type-Options",
"value": "nosniff"
},
{
"key": "X-XSS-Protection",
"value": "1; mode=block"
},
{
"key": "Pragma",
"value": "no-cache"
},
{
"key": "Cache-Control",
"value": "no-store, no-cache, must-revalidate, private"
},
{
"key": "Expires",
"value": "Thu, 01 Jan 1970 00:00:00 GMT"
},
{
"key": "X-Frame-Options",
"value": "SAMEORIGIN"
},
{
"key": "X-ACCESSTOKEN-RESET",
"value": "2021-05-11T11:08:26+00:00"
},
{
"key": "clientVersion",
"value": "4023320"
},
{
"key": "clientsubVersion",
"value": "4d8b6ee4f7d1d284c930a4e807480c5c"
},
{
"key": "Content-Disposition",
"value": "attachment; filename=response.json"
},
{
"key": "Content-Language",
"value": "en-US"
},
{
"key": "Strict-Transport-Security",
"value": "max-age=15768000"
}
],
"cookie": [
],
"body": "{\n \"org\": [\n {\n \"country\": null,\n \"photo_id\": null,\n \"city\": null,\n \"description\": null,\n \"mc_status\": true,\n \"gapps_enabled\": false,\n \"domain_name\": \"org60036018\",\n \"translation_enabled\": true,\n \"street\": null,\n \"alias\": null,\n \"currency\": \"Indian Rupee\",\n \"id\": \"738964000000300043\",\n \"state\": null,\n \"fax\": null,\n \"employee_count\": null,\n \"zip\": null,\n \"website\": null,\n \"currency_symbol\": \"₹\",\n \"mobile\": null,\n \"currency_locale\": \"en_IN\",\n \"primary_zuid\": \"60034346\",\n \"zia_portal_id\": null,\n \"time_zone\": \"Asia/Kolkata\",\n \"zgid\": \"60036018\",\n \"country_code\": \"IN\",\n \"license_details\": {\n \"paid_expiry\": \"2021-10-20T00:00:00+00:00\",\n \"users_license_purchased\": 25,\n \"trial_type\": null,\n \"trial_expiry\": null,\n \"paid\": true,\n \"paid_type\": \"enterprise\"\n },\n \"phone\": \"312\",\n \"company_name\": \"Zylker\",\n \"privacy_settings\": true,\n \"primary_email\": \"patricia@zylker.com\",\n \"iso_code\": \"INR\"\n }\n ]\n}"
},
{
"name": "INVALID_REQUEST_METHOD",
"originalRequest": {
"method": "COPY",
"header": [
{
"key": "Authorization",
"value": "{{authorization-token}}"
}
],
"url": {
"raw": "{{api-domain}}/crm/v2/org",
"host": [
"{{api-domain}}"
],
"path": [
"crm",
"v2",
"org"
]
}
},
"status": "Bad Request",
"code": 400,
"_postman_previewlanguage": "json",
"header": [
{
"key": "Server",
"value": "ZGS"
},
{
"key": "Date",
"value": "Tue, 11 May 2021 10:50:28 GMT"
},
{
"key": "Content-Type",
"value": "application/json;charset=utf-8"
},
{
"key": "Content-Length",
"value": "124"
},
{
"key": "Connection",
"value": "keep-alive"
},
{
"key": "X-Frame-Options",
"value": "deny"
},
{
"key": "X-Download-Options",
"value": "noopen"
},
{
"key": "X-Content-Type-Options",
"value": "nosniff"
}
],
"cookie": [
],
"body": "{\n \"code\": \"INVALID_REQUEST_METHOD\",\n \"details\": {},\n \"message\": \"The http request method type is not a valid one\",\n \"status\": \"error\"\n}"
},
{
"name": "AUTHENTICATION_FAILURE",
"originalRequest": {
"method": "GET",
"header": [
{
"key": "Authorization",
"value": "{{authorization-token}}"
}
],
"url": {
"raw": "{{api-domain}}/crm/v2/org",
"host": [
"{{api-domain}}"
],
"path": [
"crm",
"v2",
"org"
]
}
},
"status": "Unauthorized",
"code": 401,
"_postman_previewlanguage": "json",
"header": [
{
"key": "Server",
"value": "ZGS"
},
{
"key": "Date",
"value": "Tue, 11 May 2021 10:52:27 GMT"
},
{
"key": "Content-Type",
"value": "application/json;charset=utf-8"
},
{
"key": "Content-Length",
"value": "98"
},
{
"key": "Connection",
"value": "keep-alive"
},
{
"key": "Referrer-Policy",
"value": "strict-origin"
},
{
"key": "X-Content-Type-Options",
"value": "nosniff"
},
{
"key": "X-XSS-Protection",
"value": "1; mode=block"
},
{
"key": "Pragma",
"value": "no-cache"
},
{
"key": "Cache-Control",
"value": "no-cache"
},
{
"key": "Expires",
"value": "Thu, 01 Jan 1970 00:00:00 GMT"
},
{
"key": "X-Frame-Options",
"value": "SAMEORIGIN"
},
{
"key": "X-Download-Options",
"value": "noopen"
}
],
"cookie": [
],
"body": "{\n \"code\": \"AUTHENTICATION_FAILURE\",\n \"details\": {},\n \"message\": \"Authentication failed\",\n \"status\": \"error\"\n}"
},
{
"name": "OAUTH_SCOPE_MISMATCH",
"originalRequest": {
"method": "GET",
"header": [
{
"key": "Authorization",
"value": "{{authorization-token}}"
}
],
"url": {
"raw": "{{api-domain}}/crm/v2/org",
"host": [
"{{api-domain}}"
],
"path": [
"crm",
"v2",
"org"
]
}
},
"status": "Unauthorized",
"code": 401,
"_postman_previewlanguage": "json",
"header": [
{
"key": "Server",
"value": "ZGS"
},
{
"key": "Date",
"value": "Thu, 13 May 2021 06:31:49 GMT"
},
{
"key": "Content-Type",
"value": "application/json;charset=utf-8"
},
{
"key": "Content-Length",
"value": "113"
},
{
"key": "Connection",
"value": "keep-alive"
},
{
"key": "Referrer-Policy",
"value": "strict-origin"
},
{
"key": "X-Content-Type-Options",
"value": "nosniff"
},
{
"key": "X-XSS-Protection",
"value": "1; mode=block"
},
{
"key": "Pragma",
"value": "no-cache"
},
{
"key": "Cache-Control",
"value": "no-cache"
},
{
"key": "Expires",
"value": "Thu, 01 Jan 1970 00:00:00 GMT"
},
{
"key": "X-Frame-Options",
"value": "SAMEORIGIN"
},
{
"key": "X-Download-Options",
"value": "noopen"
}
],
"cookie": [
],
"body": "{\n \"code\": \"OAUTH_SCOPE_MISMATCH\",\n \"details\": {},\n \"message\": \"invalid oauth scope to access this URL\",\n \"status\": \"error\"\n}"
}
]
}
