SQL Server
SQL Server
MedTunnel: Send Message Text Only
See more MedTunnel Examples
Demonstrates the MedTunnel SendMessage method to send a message to one or more recipients.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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
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 https://server.medtunnel.com/MedTunnelMsg/api/Message/SendMessage -X POST -k
-- -F "ApplicationId=yourApplicationId" -F "LocationId=yourLocationId"
-- -F "MedTunnelId=yourMedTunnelId" -F "MedTunnelPassword=yourMedTunnelPassword"
-- -F "To=recipientsMedTunnelId"
-- -F "Body=Test of SendMessage"
-- Use the following online tool to generate HTTP code from a CURL command
-- Convert a cURL Command to HTTP Source Code
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @req, 'Path', '/MedTunnelMsg/api/Message/SendMessage'
EXEC sp_OASetProperty @req, 'ContentType', 'multipart/form-data'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'ApplicationId', 'yourApplicationId'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'LocationId', 'yourLocationId'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'MedTunnelId', 'yourMedTunnelId'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'MedTunnelPassword', 'yourMedTunnelPassword'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'To', 'recipientsMedTunnelId'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'Body', 'Test'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpSReq', @success OUT, 'server.medtunnel.com', 443, 1, @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
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 @req
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)
-- {
-- "ReturnCode": 1,
-- "ReturnCodeText": "Success",
-- "Data": "{\"Id\":989432,\"FromUserId\":36990,\"FromMailBoxId\":36965, ... \"SendGlobalNotifications\":false}"
-- }
-- 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 @ReturnCode int
EXEC sp_OAMethod @jResp, 'IntOf', @ReturnCode OUT, 'ReturnCode'
DECLARE @ReturnCodeText nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @ReturnCodeText OUT, 'ReturnCodeText'
DECLARE @Data nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @Data OUT, 'Data'
-- Load the Data into another JSON object and parse..
DECLARE @jsonData int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonData OUT
EXEC sp_OAMethod @jsonData, 'Load', @success OUT, @Data
EXEC sp_OASetProperty @jsonData, 'EmitCompact', 0
EXEC sp_OAMethod @jsonData, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- {
-- "Id": 989436,
-- "FromUserId": 36990,
-- "FromMailBoxId": 36965,
-- "FromUserType": 0,
-- "FromUserName": "...",
-- "FromUserFullName": "...",
-- "FromUserAccountName": "...",
-- "FromUserAccountTitle": "...",
-- "ToUserId": 36990,
-- "ToUserType": 1,
-- "ToUserMailboxId": "36965",
-- "ToUserName": "...",
-- "ToUserFullName": "...",
-- "EmailAddress": "",
-- "Password": "",
-- "Subject": "",
-- "PatientMedTunnelId": "",
-- "Body": "Test",
-- "DateReceived": "4/29/2021 2:29:18 PM",
-- "DisplayDateReceived": "04/29/2021 2:29 PM",
-- "ViewCount": 0,
-- "ViewedOn": "",
-- "AttachmentCount": 0,
-- "AttachmentNames": [
-- ],
-- "AllRecipients": [
-- {
-- "Id": 989436,
-- "UserName": "...",
-- "AccountId": 0,
-- "AccountName": "...",
-- "AccountTitle": "",
-- "FirstName": "...",
-- "LastName": "...",
-- "EmailAddress": "",
-- "LastSentOn": "",
-- "SendCount": 0,
-- "IsFavorite": false
-- }
-- ],
-- "Status": 1,
-- "ParentMessageId": 989436,
-- "DistributionListId": 0,
-- "DistributionListName": "",
-- "BodyHistory": "",
-- "ReadReceiptCallbackUrl": null,
-- "SendGlobalNotifications": false
-- }
--
DECLARE @UserName nvarchar(4000)
DECLARE @AccountId int
DECLARE @AccountName nvarchar(4000)
DECLARE @AccountTitle nvarchar(4000)
DECLARE @FirstName nvarchar(4000)
DECLARE @LastName nvarchar(4000)
DECLARE @LastSentOn nvarchar(4000)
DECLARE @SendCount int
DECLARE @IsFavorite int
DECLARE @Id int
EXEC sp_OAMethod @jsonData, 'IntOf', @Id OUT, 'Id'
DECLARE @FromUserId int
EXEC sp_OAMethod @jsonData, 'IntOf', @FromUserId OUT, 'FromUserId'
DECLARE @FromMailBoxId int
EXEC sp_OAMethod @jsonData, 'IntOf', @FromMailBoxId OUT, 'FromMailBoxId'
DECLARE @FromUserType int
EXEC sp_OAMethod @jsonData, 'IntOf', @FromUserType OUT, 'FromUserType'
DECLARE @FromUserName nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @FromUserName OUT, 'FromUserName'
DECLARE @FromUserFullName nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @FromUserFullName OUT, 'FromUserFullName'
DECLARE @FromUserAccountName nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @FromUserAccountName OUT, 'FromUserAccountName'
DECLARE @FromUserAccountTitle nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @FromUserAccountTitle OUT, 'FromUserAccountTitle'
DECLARE @ToUserId int
EXEC sp_OAMethod @jsonData, 'IntOf', @ToUserId OUT, 'ToUserId'
DECLARE @ToUserType int
EXEC sp_OAMethod @jsonData, 'IntOf', @ToUserType OUT, 'ToUserType'
DECLARE @ToUserMailboxId nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @ToUserMailboxId OUT, 'ToUserMailboxId'
DECLARE @ToUserName nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @ToUserName OUT, 'ToUserName'
DECLARE @ToUserFullName nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @ToUserFullName OUT, 'ToUserFullName'
DECLARE @EmailAddress nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @EmailAddress OUT, 'EmailAddress'
DECLARE @Password nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @Password OUT, 'Password'
DECLARE @Subject nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @Subject OUT, 'Subject'
DECLARE @PatientMedTunnelId nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @PatientMedTunnelId OUT, 'PatientMedTunnelId'
DECLARE @Body nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @Body OUT, 'Body'
DECLARE @DateReceived nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @DateReceived OUT, 'DateReceived'
DECLARE @DisplayDateReceived nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @DisplayDateReceived OUT, 'DisplayDateReceived'
DECLARE @ViewCount int
EXEC sp_OAMethod @jsonData, 'IntOf', @ViewCount OUT, 'ViewCount'
DECLARE @ViewedOn nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @ViewedOn OUT, 'ViewedOn'
DECLARE @AttachmentCount int
EXEC sp_OAMethod @jsonData, 'IntOf', @AttachmentCount OUT, 'AttachmentCount'
DECLARE @Status int
EXEC sp_OAMethod @jsonData, 'IntOf', @Status OUT, 'Status'
DECLARE @ParentMessageId int
EXEC sp_OAMethod @jsonData, 'IntOf', @ParentMessageId OUT, 'ParentMessageId'
DECLARE @DistributionListId int
EXEC sp_OAMethod @jsonData, 'IntOf', @DistributionListId OUT, 'DistributionListId'
DECLARE @DistributionListName nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @DistributionListName OUT, 'DistributionListName'
DECLARE @BodyHistory nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @BodyHistory OUT, 'BodyHistory'
DECLARE @ReadReceiptCallbackUrl nvarchar(4000)
EXEC sp_OAMethod @jsonData, 'StringOf', @ReadReceiptCallbackUrl OUT, 'ReadReceiptCallbackUrl'
DECLARE @SendGlobalNotifications int
EXEC sp_OAMethod @jsonData, 'BoolOf', @SendGlobalNotifications OUT, 'SendGlobalNotifications'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jsonData, 'SizeOfArray', @count_i OUT, 'AttachmentNames'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jsonData, 'I', @i
SELECT @i = @i + 1
END
SELECT @i = 0
EXEC sp_OAMethod @jsonData, 'SizeOfArray', @count_i OUT, 'AllRecipients'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jsonData, 'I', @i
EXEC sp_OAMethod @jsonData, 'IntOf', @Id OUT, 'AllRecipients[i].Id'
EXEC sp_OAMethod @jsonData, 'StringOf', @UserName OUT, 'AllRecipients[i].UserName'
EXEC sp_OAMethod @jsonData, 'IntOf', @AccountId OUT, 'AllRecipients[i].AccountId'
EXEC sp_OAMethod @jsonData, 'StringOf', @AccountName OUT, 'AllRecipients[i].AccountName'
EXEC sp_OAMethod @jsonData, 'StringOf', @AccountTitle OUT, 'AllRecipients[i].AccountTitle'
EXEC sp_OAMethod @jsonData, 'StringOf', @FirstName OUT, 'AllRecipients[i].FirstName'
EXEC sp_OAMethod @jsonData, 'StringOf', @LastName OUT, 'AllRecipients[i].LastName'
EXEC sp_OAMethod @jsonData, 'StringOf', @EmailAddress OUT, 'AllRecipients[i].EmailAddress'
EXEC sp_OAMethod @jsonData, 'StringOf', @LastSentOn OUT, 'AllRecipients[i].LastSentOn'
EXEC sp_OAMethod @jsonData, 'IntOf', @SendCount OUT, 'AllRecipients[i].SendCount'
EXEC sp_OAMethod @jsonData, 'BoolOf', @IsFavorite OUT, 'AllRecipients[i].IsFavorite'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
EXEC @hr = sp_OADestroy @jsonData
END
GO