SQL Server
SQL Server
MessageMedia - Send Messages
See more MessageMedia Examples
Submit one or more (up to 100 per request) SMS, MMS, or text to voice messages for delivery.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Implements the following CURL command:
-- curl --request POST \
-- --header "Content-Type: application/json" \
-- --header "Accept: application/json" \
-- --data-binary "{
-- \"messages\": [
-- {
-- \"callback_url\": \"https://my.callback.url.com\",
-- \"content\": \"My first message\",
-- \"destination_number\": \"+61491570156\",
-- \"delivery_report\": true,
-- \"format\": \"SMS\",
-- \"message_expiry_timestamp\": \"2016-11-03T11:49:02.807Z\",
-- \"metadata\": {
-- \"myKey\": \"myValue\",
-- \"anotherKey\": \"anotherValue\"
-- },
-- \"scheduled\": \"2016-11-03T11:49:02.807Z\",
-- \"source_number\": \"+61491570157\",
-- \"source_number_type\": \"INTERNATIONAL\"
-- },
-- {
-- \"callback_url\": \"https://my.callback.url.com\",
-- \"content\": \"My second message\",
-- \"destination_number\": \"+61491570158\",
-- \"delivery_report\": true,
-- \"format\": \"MMS\",
-- \"subject\": \"This is an MMS message\",
-- \"media\": [ \"https://images.pexels.com/photos/1018350/pexels-photo-1018350.jpeg?cs=srgb&dl=architecture-buildings-city-1018350.jpg\" ],
-- \"message_expiry_timestamp\": \"2016-11-03T11:49:02.807Z\",
-- \"metadata\": {
-- \"myKey\": \"myValue\",
-- \"anotherKey\": \"anotherValue\"
-- },
-- \"scheduled\": \"2016-11-03T11:49:02.807Z\",
-- \"source_number\": \"+61491570159\",
-- \"source_number_type\": \"INTERNATIONAL\"
-- }
-- ]
-- }" \
-- https://private-anon-ecbaffbc28-messages32.apiary-mock.com/v1/messages
-- Use the following online tool to generate HTTP code from a CURL command
-- Convert a cURL Command to HTTP Source Code
-- Use this online tool to generate code from sample JSON:
-- Generate Code to Create JSON
-- The following JSON is sent in the request body.
-- {
-- "messages": [
-- {
-- "callback_url": "https://my.callback.url.com",
-- "content": "My first message",
-- "destination_number": "+61491570156",
-- "delivery_report": true,
-- "format": "SMS",
-- "message_expiry_timestamp": "2016-11-03T11:49:02.807Z",
-- "metadata": {
-- "myKey": "myValue",
-- "anotherKey": "anotherValue"
-- },
-- "scheduled": "2016-11-03T11:49:02.807Z",
-- "source_number": "+61491570157",
-- "source_number_type": "INTERNATIONAL"
-- },
-- {
-- "callback_url": "https://my.callback.url.com",
-- "content": "My second message",
-- "destination_number": "+61491570158",
-- "delivery_report": true,
-- "format": "MMS",
-- "subject": "This is an MMS message",
-- "media": [
-- "https://images.pexels.com/photos/1018350/pexels-photo-1018350.jpeg?cs=srgb&dl=architecture-buildings-city-1018350.jpg"
-- ],
-- "message_expiry_timestamp": "2016-11-03T11:49:02.807Z",
-- "metadata": {
-- "myKey": "myValue",
-- "anotherKey": "anotherValue"
-- },
-- "scheduled": "2016-11-03T11:49:02.807Z",
-- "source_number": "+61491570159",
-- "source_number_type": "INTERNATIONAL"
-- }
-- ]
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[0].callback_url', 'https://my.callback.url.com'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[0].content', 'My first message'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[0].destination_number', '+61491570156'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messages[0].delivery_report', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[0].format', 'SMS'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[0].message_expiry_timestamp', '2016-11-03T11:49:02.807Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[0].metadata.myKey', 'myValue'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[0].metadata.anotherKey', 'anotherValue'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[0].scheduled', '2016-11-03T11:49:02.807Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[0].source_number', '+61491570157'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[0].source_number_type', 'INTERNATIONAL'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].callback_url', 'https://my.callback.url.com'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].content', 'My second message'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].destination_number', '+61491570158'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messages[1].delivery_report', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].format', 'MMS'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].subject', 'This is an MMS message'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].media[0]', 'https://images.pexels.com/photos/1018350/pexels-photo-1018350.jpeg?cs=srgb&dl=architecture-buildings-city-1018350.jpg'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].message_expiry_timestamp', '2016-11-03T11:49:02.807Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].metadata.myKey', 'myValue'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].metadata.anotherKey', 'anotherValue'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].scheduled', '2016-11-03T11:49:02.807Z'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].source_number', '+61491570159'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'messages[1].source_number_type', 'INTERNATIONAL'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Accept', 'application/json'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://private-anon-ecbaffbc28-messages32.apiary-mock.com/v1/messages', @json, 'application/json', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
DECLARE @jResp int
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 @resp, 'StatusCode', @respStatusCode OUT
PRINT 'Response Status Code = ' + @respStatusCode
IF @respStatusCode >= 400
BEGIN
PRINT 'Response Header:'
EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
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)
-- {
-- "messages": [
-- {
-- "message_id": "04fe9a97-a579-43c5-bb1a-58ed29bf0a6a",
-- "callback_url": "https://my.url.com",
-- "status": "delivered",
-- "content": "My first message",
-- "destination_number": "+61491570156",
-- "delivery_report": true,
-- "format": "SMS",
-- "message_expiry_timestamp": "2016-11-03T11:49:02.807Z",
-- "metadata": {
-- "myKey": "myValue",
-- "anotherKey": "anotherValue"
-- },
-- "scheduled": "2016-11-03T11:49:02.807Z",
-- "source_number": "+61491570157",
-- "source_number_type": "INTERNATIONAL"
-- }
-- ]
-- }
-- Sample code for parsing the JSON response...
-- Use the following online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
DECLARE @message_id nvarchar(4000)
DECLARE @callback_url nvarchar(4000)
DECLARE @status nvarchar(4000)
DECLARE @content nvarchar(4000)
DECLARE @destination_number nvarchar(4000)
DECLARE @delivery_report int
DECLARE @format nvarchar(4000)
DECLARE @message_expiry_timestamp nvarchar(4000)
DECLARE @MyKey nvarchar(4000)
DECLARE @AnotherKey nvarchar(4000)
DECLARE @scheduled nvarchar(4000)
DECLARE @source_number nvarchar(4000)
DECLARE @source_number_type nvarchar(4000)
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'messages'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'StringOf', @message_id OUT, 'messages[i].message_id'
EXEC sp_OAMethod @jResp, 'StringOf', @callback_url OUT, 'messages[i].callback_url'
EXEC sp_OAMethod @jResp, 'StringOf', @status OUT, 'messages[i].status'
EXEC sp_OAMethod @jResp, 'StringOf', @content OUT, 'messages[i].content'
EXEC sp_OAMethod @jResp, 'StringOf', @destination_number OUT, 'messages[i].destination_number'
EXEC sp_OAMethod @jResp, 'BoolOf', @delivery_report OUT, 'messages[i].delivery_report'
EXEC sp_OAMethod @jResp, 'StringOf', @format OUT, 'messages[i].format'
EXEC sp_OAMethod @jResp, 'StringOf', @message_expiry_timestamp OUT, 'messages[i].message_expiry_timestamp'
EXEC sp_OAMethod @jResp, 'StringOf', @MyKey OUT, 'messages[i].metadata.myKey'
EXEC sp_OAMethod @jResp, 'StringOf', @AnotherKey OUT, 'messages[i].metadata.anotherKey'
EXEC sp_OAMethod @jResp, 'StringOf', @scheduled OUT, 'messages[i].scheduled'
EXEC sp_OAMethod @jResp, 'StringOf', @source_number OUT, 'messages[i].source_number'
EXEC sp_OAMethod @jResp, 'StringOf', @source_number_type OUT, 'messages[i].source_number_type'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
END
GO