SQL Server / Broker API / CIP Info
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
DECLARE @iTmp0 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
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @success int
EXEC sp_OASetProperty @http, 'BasicAuth', 1
EXEC sp_OASetProperty @http, 'Login', '{{api_key}}'
EXEC sp_OASetProperty @http, 'Password', '{{api_secret}}'
-- Use this online tool to generate code from sample JSON: Generate Code to Create JSON
-- The following JSON is sent in the request body.
-- {
-- "provider_name": [
-- "onfido"
-- ],
-- "kyc": {
-- "id": "CBDAD1C4-1047-450E-BAE5-B6C406F509B4",
-- "risk_level": "LOW",
-- "applicant_name": "John Doe",
-- "email_address": "johndoe@example.com",
-- "nationality": "American",
-- "id_number": "jd0000123456789",
-- "date_of_birth": "1970-12-01",
-- "address": "42 Faux St",
-- "postal_code": "94401",
-- "country_of_residency": "USA",
-- "kyc_completed_at": "2021-06-10T15:37:03Z",
-- "ip_address": "127.0.0.1",
-- "check_initiated_at": "2021-06-10T15:37:03Z",
-- "check_completed_at": "2021-06-10T15:37:03Z",
-- "approval_status": "approved",
-- "approved_by": "Jane Doe",
-- "approved_at": "2021-06-10T15:38:03Z"
-- },
-- "document": {
-- "id": "55B9931A-3BE6-4BC0-9BDD-0B954E4A4632",
-- "result": "clear",
-- "status": "complete",
-- "created_at": "2021-06-10T15:37:03Z",
-- "image_integrity": "clear"
-- },
-- "photo": {
-- "id": "0DD13020-F0FD-4B5A-B58F-BC1885E90A6D",
-- "result": "clear",
-- "status": "complete",
-- "created_at": "2021-06-10T15:37:03Z",
-- "face_comparison": "clear"
-- },
-- "identity": {
-- "id": "28E1CCE8-1B1A-4472-9AD4-C6C5B7C3A6AF",
-- "result": "clear",
-- "status": "complete",
-- "created_at": "2021-06-10T15:37:03Z",
-- "sources": "clear",
-- "address": "clear",
-- "date_of_birth": "clear"
-- },
-- "watchlist": {
-- "id": "7572B870-EB4C-46A2-8B88-509194CCEE7E",
-- "result": "clear",
-- "status": "complete",
-- "created_at": "2021-06-10T15:37:03Z",
-- "politically_exposed_person": "clear",
-- "sanction": "clear",
-- "adverse_media": "clear",
-- "monitored_lists": "clear"
-- }
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'provider_name[0]', 'onfido'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.id', 'CBDAD1C4-1047-450E-BAE5-B6C406F509B4'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.risk_level', 'LOW'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.applicant_name', 'John Doe'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.email_address', 'johndoe@example.com'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.nationality', 'American'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.id_number', 'jd0000123456789'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.date_of_birth', '1970-12-01'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.address', '42 Faux St'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.postal_code', '94401'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.country_of_residency', 'USA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.kyc_completed_at', '2021-06-10T15:37:03Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.ip_address', '127.0.0.1'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.check_initiated_at', '2021-06-10T15:37:03Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.check_completed_at', '2021-06-10T15:37:03Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.approval_status', 'approved'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.approved_by', 'Jane Doe'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'kyc.approved_at', '2021-06-10T15:38:03Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'document.id', '55B9931A-3BE6-4BC0-9BDD-0B954E4A4632'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'document.result', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'document.status', 'complete'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'document.created_at', '2021-06-10T15:37:03Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'document.image_integrity', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'photo.id', '0DD13020-F0FD-4B5A-B58F-BC1885E90A6D'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'photo.result', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'photo.status', 'complete'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'photo.created_at', '2021-06-10T15:37:03Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'photo.face_comparison', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.id', '28E1CCE8-1B1A-4472-9AD4-C6C5B7C3A6AF'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.result', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.status', 'complete'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.created_at', '2021-06-10T15:37:03Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.sources', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.address', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.date_of_birth', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'watchlist.id', '7572B870-EB4C-46A2-8B88-509194CCEE7E'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'watchlist.result', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'watchlist.status', 'complete'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'watchlist.created_at', '2021-06-10T15:37:03Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'watchlist.politically_exposed_person', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'watchlist.sanction', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'watchlist.adverse_media', 'clear'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'watchlist.monitored_lists', 'clear'
DECLARE @resp int
EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://domain.com/v1/accounts/:account_id/cip', 'application/json', @json
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
END
GO
Curl Command
curl -X POST
-u '{{api_key}}:{{api_secret}}'
-d '{
"provider_name": ["onfido"],
"kyc": {
"id": "CBDAD1C4-1047-450E-BAE5-B6C406F509B4",
"risk_level": "LOW",
"applicant_name": "John Doe",
"email_address": "johndoe@example.com",
"nationality": "American",
"id_number": "jd0000123456789",
"date_of_birth": "1970-12-01",
"address": "42 Faux St",
"postal_code": "94401",
"country_of_residency": "USA",
"kyc_completed_at": "2021-06-10T15:37:03Z",
"ip_address": "127.0.0.1",
"check_initiated_at": "2021-06-10T15:37:03Z",
"check_completed_at": "2021-06-10T15:37:03Z",
"approval_status": "approved",
"approved_by": "Jane Doe",
"approved_at": "2021-06-10T15:38:03Z"
},
"document": {
"id": "55B9931A-3BE6-4BC0-9BDD-0B954E4A4632",
"result": "clear",
"status": "complete",
"created_at": "2021-06-10T15:37:03Z",
"image_integrity": "clear"
},
"photo": {
"id": "0DD13020-F0FD-4B5A-B58F-BC1885E90A6D",
"result": "clear",
"status": "complete",
"created_at": "2021-06-10T15:37:03Z",
"face_comparison": "clear"
},
"identity": {
"id": "28E1CCE8-1B1A-4472-9AD4-C6C5B7C3A6AF",
"result": "clear",
"status": "complete",
"created_at": "2021-06-10T15:37:03Z",
"sources": "clear",
"address": "clear",
"date_of_birth": "clear"
},
"watchlist": {
"id": "7572B870-EB4C-46A2-8B88-509194CCEE7E",
"result": "clear",
"status": "complete",
"created_at": "2021-06-10T15:37:03Z",
"politically_exposed_person": "clear",
"sanction": "clear",
"adverse_media": "clear",
"monitored_lists": "clear"
}
}'
https://domain.com/v1/accounts/:account_id/cip
Postman Collection Item JSON
{
"name": "CIP Info",
"request": {
"method": "POST",
"header": [
],
"body": {
"mode": "raw",
"raw": "{\n \"provider_name\": [\"onfido\"],\n \"kyc\": {\n \"id\": \"CBDAD1C4-1047-450E-BAE5-B6C406F509B4\",\n \"risk_level\": \"LOW\",\n \"applicant_name\": \"John Doe\",\n \"email_address\": \"johndoe@example.com\",\n \"nationality\": \"American\",\n \"id_number\": \"jd0000123456789\",\n \"date_of_birth\": \"1970-12-01\",\n \"address\": \"42 Faux St\",\n \"postal_code\": \"94401\",\n \"country_of_residency\": \"USA\",\n \"kyc_completed_at\": \"2021-06-10T15:37:03Z\",\n \"ip_address\": \"127.0.0.1\",\n \"check_initiated_at\": \"2021-06-10T15:37:03Z\",\n \"check_completed_at\": \"2021-06-10T15:37:03Z\",\n \"approval_status\": \"approved\",\n \"approved_by\": \"Jane Doe\",\n \"approved_at\": \"2021-06-10T15:38:03Z\"\n },\n \"document\": {\n \"id\": \"55B9931A-3BE6-4BC0-9BDD-0B954E4A4632\",\n \"result\": \"clear\",\n \"status\": \"complete\",\n \"created_at\": \"2021-06-10T15:37:03Z\",\n \"image_integrity\": \"clear\"\n },\n \"photo\": {\n \"id\": \"0DD13020-F0FD-4B5A-B58F-BC1885E90A6D\",\n \"result\": \"clear\",\n \"status\": \"complete\",\n \"created_at\": \"2021-06-10T15:37:03Z\",\n \"face_comparison\": \"clear\"\n },\n \"identity\": {\n \"id\": \"28E1CCE8-1B1A-4472-9AD4-C6C5B7C3A6AF\",\n \"result\": \"clear\",\n \"status\": \"complete\",\n \"created_at\": \"2021-06-10T15:37:03Z\",\n \"sources\": \"clear\",\n \"address\": \"clear\",\n \"date_of_birth\": \"clear\"\n },\n \"watchlist\": {\n \"id\": \"7572B870-EB4C-46A2-8B88-509194CCEE7E\",\n \"result\": \"clear\",\n \"status\": \"complete\",\n \"created_at\": \"2021-06-10T15:37:03Z\",\n \"politically_exposed_person\": \"clear\",\n \"sanction\": \"clear\",\n \"adverse_media\": \"clear\",\n \"monitored_lists\": \"clear\"\n }\n}",
"options": {
"raw": {
"language": "json"
}
}
},
"url": {
"raw": "{{HOST}}/v1/accounts/:account_id/cip",
"host": [
"{{HOST}}"
],
"path": [
"v1",
"accounts",
":account_id",
"cip"
],
"variable": [
{
"key": "account_id",
"value": null
}
]
},
"description": "| Attribute | Requirement |\n| ---------------|:-------------: |\n| `provider` | Optional |\n| `kyc` | Optional |\n| `document` | Optional |\n| `photo` | Optional |\n| `identity` | Optional |\n| `watchlist` | Optional |"
},
"response": [
]
}
