(SQL Server) Twilio: Send SMS using Basic Authentication

Demonstrates how to use Twilio to send an SMS message using Basic authentication.

For more information, see https://www.twilio.com/docs/sms/send-messages

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)
    -- Demonstrates how to use Basic Authentication in a REST API call for Twilio.
    -- Sends an SMS text message..

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use Basic Authentication.
    -- Your Twilio Account SID is the username.
    -- Your Twilio Auth Token is the password.
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'SetAuthBasic', @success OUT, 'TWILIO_ACCOUNT_SID', 'TWILIO_AUTH_TOKEN'

    -- Make the initial connection (without sending a request yet) to Twilio.
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'api.twilio.com', @port, @bTls, @bAutoReconnect
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Provide the information for the SMS text message:
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'To', '+16518675309'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'From', '+15005550006'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'Body', 'Hey Jenny! Good luck on the bar exam!'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'MediaUrl', 'http://farm2.static.flickr.com/1075/1404618563_3ed9a44a3a.jpg'

    -- Send the SMS text message.
    -- Your Twilio Account SID is part of the URI path:
    DECLARE @responseJson nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestFormUrlEncoded', @responseJson OUT, 'POST', '/2010-04-01/Accounts/TWILIO_ACCOUNT_SID/Messages.json'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- When successful, the response status code will equal 201.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 201
      BEGIN
        -- Examine the request/response to see what happened.

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'response status code = ' + @iTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'response status text = ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT 'response header: ' + @sTmp0

        PRINT 'response body (if any): ' + @responseJson

        PRINT '---'

        EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
        PRINT 'LastRequestStartLine: ' + @sTmp0

        EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
        PRINT 'LastRequestHeader: ' + @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- The response is JSON.  We'll show how to get a few bits of information from it.
    -- A full sample JSON response is shown below..

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Load', @success OUT, @responseJson

    -- First show the entire JSON.
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Now get some individual pieces of information:

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'sid'
    PRINT 'sid: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'body'
    PRINT 'body: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'subresource_uris.media'
    PRINT 'media: ' + @sTmp0


    PRINT 'Success.'

    -- Sample JSON response:

    -- {
    --   "sid": "MM97ecfd43e9f24e99b0c2c6ee016949e3",
    --   "date_created": null,
    --   "date_updated": null,
    --   "date_sent": null,
    --   "account_sid": "112e1111e0151133d11112101111d1111",
    --   "to": "+16518675309",
    --   "from": "+15005550006",
    --   "messaging_service_sid": null,
    --   "body": "Sent from your Twilio trial account - Hey Jenny! Good luck on the bar exam!",
    --   "status": "queued",
    --   "num_segments": "1",
    --   "num_media": "0",
    --   "direction": "outbound-api",
    --   "api_version": "2010-04-01",
    --   "price": null,
    --   "price_unit": "USD",
    --   "error_code": null,
    --   "error_message": null,
    --   "uri": "/2010-04-01/Accounts/AC2e9b6bc0f51133df24926f07341d3824/Messages/MM97ecfd43e9f24e99b0c2c6ee016949e3.json",
    --   "subresource_uris": {
    --     "media": "/2010-04-01/Accounts/AC2e9b6bc0f51133df24926f07341d3824/Messages/MM97ecfd43e9f24e99b0c2c6ee016949e3/Media.json"
    --   }
    -- }

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @json


END
GO