SQL Server / Broker API / Account
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.
-- {
-- "contact": {
-- "email_address": "cool_alpaca@example.com",
-- "phone_number": "555-666-7788",
-- "street_address": [
-- "20 N San Mateo Dr"
-- ],
-- "city": "San Mateo",
-- "state": "CA",
-- "postal_code": "94401",
-- "country": "USA"
-- },
-- "identity": {
-- "given_name": "John",
-- "family_name": "Doe",
-- "date_of_birth": "1990-01-01",
-- "tax_id": "666-55-4321",
-- "tax_id_type": "USA_SSN",
-- "country_of_citizenship": "USA",
-- "country_of_birth": "USA",
-- "country_of_tax_residence": "USA",
-- "funding_source": [
-- "employment_income"
-- ]
-- },
-- "disclosures": {
-- "is_control_person": false,
-- "is_affiliated_exchange_or_finra": false,
-- "is_politically_exposed": false,
-- "immediate_family_exposed": false
-- },
-- "agreements": [
-- {
-- "agreement": "margin_agreement",
-- "signed_at": "2020-09-11T18:09:33Z",
-- "ip_address": "185.13.21.99"
-- },
-- {
-- "agreement": "account_agreement",
-- "signed_at": "2020-09-11T18:13:44Z",
-- "ip_address": "185.13.21.99"
-- },
-- {
-- "agreement": "customer_agreement",
-- "signed_at": "2020-09-11T18:13:44Z",
-- "ip_address": "185.13.21.99"
-- }
-- ],
-- "documents": [
-- {
-- "document_type": "identity_verification",
-- "document_sub_type": "passport",
-- "content": "QWxwYWNhcyBjYW5ub3QgbGl2ZSBhbG9uZS4=",
-- "mime_type": "image/jpeg"
-- }
-- ],
-- "trusted_contact": {
-- "given_name": "Jane",
-- "family_name": "Doe",
-- "email_address": "jane.doe@example.com"
-- }
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'contact.email_address', 'cool_alpaca@example.com'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'contact.phone_number', '555-666-7788'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'contact.street_address[0]', '20 N San Mateo Dr'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'contact.city', 'San Mateo'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'contact.state', 'CA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'contact.postal_code', '94401'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'contact.country', 'USA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.given_name', 'John'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.family_name', 'Doe'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.date_of_birth', '1990-01-01'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.tax_id', '666-55-4321'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.tax_id_type', 'USA_SSN'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.country_of_citizenship', 'USA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.country_of_birth', 'USA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.country_of_tax_residence', 'USA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'identity.funding_source[0]', 'employment_income'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'disclosures.is_control_person', 0
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'disclosures.is_affiliated_exchange_or_finra', 0
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'disclosures.is_politically_exposed', 0
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'disclosures.immediate_family_exposed', 0
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'agreements[0].agreement', 'margin_agreement'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'agreements[0].signed_at', '2020-09-11T18:09:33Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'agreements[0].ip_address', '185.13.21.99'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'agreements[1].agreement', 'account_agreement'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'agreements[1].signed_at', '2020-09-11T18:13:44Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'agreements[1].ip_address', '185.13.21.99'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'agreements[2].agreement', 'customer_agreement'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'agreements[2].signed_at', '2020-09-11T18:13:44Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'agreements[2].ip_address', '185.13.21.99'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'documents[0].document_type', 'identity_verification'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'documents[0].document_sub_type', 'passport'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'documents[0].content', 'QWxwYWNhcyBjYW5ub3QgbGl2ZSBhbG9uZS4='
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'documents[0].mime_type', 'image/jpeg'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'trusted_contact.given_name', 'Jane'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'trusted_contact.family_name', 'Doe'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'trusted_contact.email_address', 'jane.doe@example.com'
DECLARE @resp int
EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://domain.com/v1/accounts', '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 '{
"contact": {
"email_address": "cool_alpaca@example.com",
"phone_number": "555-666-7788",
"street_address": ["20 N San Mateo Dr"],
"city": "San Mateo",
"state": "CA",
"postal_code": "94401",
"country": "USA"
},
"identity": {
"given_name": "John",
"family_name": "Doe",
"date_of_birth": "1990-01-01",
"tax_id": "666-55-4321",
"tax_id_type": "USA_SSN",
"country_of_citizenship": "USA",
"country_of_birth": "USA",
"country_of_tax_residence": "USA",
"funding_source": ["employment_income"]
},
"disclosures": {
"is_control_person": false,
"is_affiliated_exchange_or_finra": false,
"is_politically_exposed": false,
"immediate_family_exposed": false
},
"agreements": [
{
"agreement": "margin_agreement",
"signed_at": "2020-09-11T18:09:33Z",
"ip_address": "185.13.21.99"
},
{
"agreement": "account_agreement",
"signed_at": "2020-09-11T18:13:44Z",
"ip_address": "185.13.21.99"
},
{
"agreement": "customer_agreement",
"signed_at": "2020-09-11T18:13:44Z",
"ip_address": "185.13.21.99"
}
],
"documents": [
{
"document_type": "identity_verification",
"document_sub_type": "passport",
"content": "QWxwYWNhcyBjYW5ub3QgbGl2ZSBhbG9uZS4=",
"mime_type": "image/jpeg"
}
],
"trusted_contact": {
"given_name": "Jane",
"family_name": "Doe",
"email_address": "jane.doe@example.com"
}
}'
https://domain.com/v1/accounts
Postman Collection Item JSON
{
"name": "Account",
"request": {
"method": "POST",
"header": [
],
"body": {
"mode": "raw",
"raw": "{\n \"contact\": {\n \"email_address\": \"cool_alpaca@example.com\",\n \"phone_number\": \"555-666-7788\",\n \"street_address\": [\"20 N San Mateo Dr\"],\n \"city\": \"San Mateo\",\n \"state\": \"CA\",\n \"postal_code\": \"94401\",\n \"country\": \"USA\"\n },\n \"identity\": {\n \"given_name\": \"John\",\n \"family_name\": \"Doe\",\n \"date_of_birth\": \"1990-01-01\",\n \"tax_id\": \"666-55-4321\",\n \"tax_id_type\": \"USA_SSN\",\n \"country_of_citizenship\": \"USA\",\n \"country_of_birth\": \"USA\",\n \"country_of_tax_residence\": \"USA\",\n \"funding_source\": [\"employment_income\"]\n },\n \"disclosures\": {\n \"is_control_person\": false,\n \"is_affiliated_exchange_or_finra\": false,\n \"is_politically_exposed\": false,\n \"immediate_family_exposed\": false\n },\n \"agreements\": [\n {\n \"agreement\": \"margin_agreement\",\n \"signed_at\": \"2020-09-11T18:09:33Z\",\n \"ip_address\": \"185.13.21.99\"\n },\n {\n \"agreement\": \"account_agreement\",\n \"signed_at\": \"2020-09-11T18:13:44Z\",\n \"ip_address\": \"185.13.21.99\"\n },\n {\n \"agreement\": \"customer_agreement\",\n \"signed_at\": \"2020-09-11T18:13:44Z\",\n \"ip_address\": \"185.13.21.99\"\n }\n ],\n \"documents\": [\n {\n \"document_type\": \"identity_verification\",\n \"document_sub_type\": \"passport\",\n \"content\": \"QWxwYWNhcyBjYW5ub3QgbGl2ZSBhbG9uZS4=\",\n \"mime_type\": \"image/jpeg\"\n }\n ],\n \"trusted_contact\": {\n \"given_name\": \"Jane\",\n \"family_name\": \"Doe\",\n \"email_address\": \"jane.doe@example.com\"\n }\n}",
"options": {
"raw": {
"language": "json"
}
}
},
"url": {
"raw": "{{HOST}}/v1/accounts",
"host": [
"{{HOST}}"
],
"path": [
"v1",
"accounts"
]
},
"description": "| Attribute | Requirement |\n| ------------- |:-------------: |\n| `contact` | Required |\n| `identity` | Required |\n| `disclosures` | Required |\n| `documents` | Optional |\n| `trusted_contact` | Optional |"
},
"response": [
]
}
