(SQL Server) MedTunnel: Send Message with File Attachment

See more MedTunnel Examples

Demonstrates the MedTunnel SendMessage method to send a message with a file attachment to one or more recipients.

For more information, see https://server.medtunnel.com/apidocs/html/M_MedTunnelMsg_Controllers_MessageController_SendMessage.htm

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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
    -- 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

    -- 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"
    --         -F "file1=@qa_data/jpg/starfish.jpg"

    -- Use the following online tool to generate HTTP code from a CURL command
    -- Convert a cURL Command to HTTP Source Code

    DECLARE @req int
    -- Use "Chilkat_9_5_0.HttpRequest" for versions of Chilkat < 10.0.0
    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'

    EXEC sp_OAMethod @req, 'AddFileForUpload2', @success OUT, 'file1', 'qa_data/jpg/starfish.jpg', 'application/octet-stream'

    EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Expect', '100-continue'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'SynchronousRequest', @resp OUT, 'server.medtunnel.com', 443, 1, @req
    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 @req
        RETURN
      END

    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 @resp, 'GetBodySb', @success OUT, @sbResponseBody
    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 @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 @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END
    EXEC @hr = sp_OADestroy @resp

    -- 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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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": 989448,
    --   "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:48:22 PM",
    --   "DisplayDateReceived": "04/29/2021  2:48 PM",
    --   "ViewCount": 0,
    --   "ViewedOn": "",
    --   "AttachmentCount": 1,
    --   "AttachmentNames": [
    --     {
    --       "MessageId": 989448,
    --       "Id": 424857,
    --       "Name": "starfish.jpg.35910fe9-4118-414c-a845-4d092ca6e784",
    --       "DisplayName": "starfish.jpg",
    --       "Size": 6229,
    --       "WasViewed": false,
    --       "ViewedOn": "",
    --       "ViewCount": 0,
    --       "Location": "Default"
    --     }
    --   ],
    --   "AllRecipients": [
    --     {
    --       "Id": 989448,
    --       "UserName": "...",
    --       "AccountId": 0,
    --       "AccountName": "...",
    --       "AccountTitle": "",
    --       "FirstName": "...",
    --       "LastName": "...",
    --       "EmailAddress": "",
    --       "LastSentOn": "",
    --       "SendCount": 0,
    --       "IsFavorite": false
    --     }
    --   ],
    --   "Status": 0,
    --   "ParentMessageId": 989448,
    --   "DistributionListId": 0,
    --   "DistributionListName": "",
    --   "BodyHistory": "",
    --   "ReadReceiptCallbackUrl": null,
    --   "SendGlobalNotifications": false
    -- }

    DECLARE @MessageId int

    DECLARE @Name nvarchar(4000)

    DECLARE @DisplayName nvarchar(4000)

    DECLARE @Size int

    DECLARE @WasViewed int

    DECLARE @Location nvarchar(4000)

    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
        EXEC sp_OAMethod @jsonData, 'IntOf', @MessageId OUT, 'AttachmentNames[i].MessageId'
        EXEC sp_OAMethod @jsonData, 'IntOf', @Id OUT, 'AttachmentNames[i].Id'
        EXEC sp_OAMethod @jsonData, 'StringOf', @Name OUT, 'AttachmentNames[i].Name'
        EXEC sp_OAMethod @jsonData, 'StringOf', @DisplayName OUT, 'AttachmentNames[i].DisplayName'
        EXEC sp_OAMethod @jsonData, 'IntOf', @Size OUT, 'AttachmentNames[i].Size'
        EXEC sp_OAMethod @jsonData, 'BoolOf', @WasViewed OUT, 'AttachmentNames[i].WasViewed'
        EXEC sp_OAMethod @jsonData, 'StringOf', @ViewedOn OUT, 'AttachmentNames[i].ViewedOn'
        EXEC sp_OAMethod @jsonData, 'IntOf', @ViewCount OUT, 'AttachmentNames[i].ViewCount'
        EXEC sp_OAMethod @jsonData, 'StringOf', @Location OUT, 'AttachmentNames[i].Location'
        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 @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp
    EXEC @hr = sp_OADestroy @jsonData


END
GO