SQL Server Requires Chilkat v11.0.0+
SQL Server
Clickatell HTTP API: Send an SMS with query parameters
See more Clickatell Examples
Use an SMS integration configured for HTTP in the Clickatell Portal. The endpoint takes apiKey, to, and content as query parameters. Pass raw values to Chilkat so it can encode spaces, ampersands, and other reserved characters. Supply a country code and phone number as digits. Set from when your two-way integration requires a sender.
The URL contains the API key, recipient, and message text. Keep full request URLs out of logs and browser history. Check the per-message result after HTTP 202. If the connection is interrupted, check the Portal message report before resending. For the header-authenticated JSON workflow, see Send an SMS with One API.
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 @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1
-- Use the key issued for this HTTP integration. Chilkat encodes each query value.
DECLARE @query int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @query OUT
EXEC sp_OASetProperty @query, 'HttpVerb', 'GET'
EXEC sp_OAMethod @query, 'AddHeader', NULL, 'Accept', 'application/json'
EXEC sp_OAMethod @query, 'AddParam', NULL, 'apiKey', 'HTTP_API_KEY'
EXEC sp_OAMethod @query, 'AddParam', NULL, 'to', 'PHONE_NUMBER'
EXEC sp_OAMethod @query, 'AddParam', NULL, 'content', 'Test message: pickup between 2 & 4.'
DECLARE @sender nvarchar(4000)
SELECT @sender = ''
IF @sender <> ''
BEGIN
EXEC sp_OAMethod @query, 'AddParam', NULL, 'from', @sender
END
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, 'https://platform.clickatell.com/messages/http/send', @query, @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'The send outcome is unknown. Check the message report before resending.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @reply int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @reply OUT
DECLARE @parsed int
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @parsed OUT, @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF (@success <> 1) or ((@iTmp0 <> 202))
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Clickatell returned HTTP ' + @iTmp0
IF @parsed = 1
BEGIN
EXEC sp_OAMethod @reply, 'HasMember', @iTmp0 OUT, 'errorDescription'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'errorDescription'
PRINT @sTmp0
END
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
IF @parsed <> 1
BEGIN
PRINT 'Expected a JSON response.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
-- A request-level error applies to the submission as a whole.
EXEC sp_OAMethod @reply, 'HasMember', @iTmp0 OUT, 'errorCode'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'IsNullOf', @iTmp0 OUT, 'errorCode'
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'errorCode'
EXEC sp_OAMethod @reply, 'StringOf', @sTmp1 OUT, 'errorDescription'
PRINT 'Submission error ' + @sTmp0 + ' ' + @sTmp1
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
END
DECLARE @count int
EXEC sp_OAMethod @reply, 'SizeOfArray', @count OUT, 'messages'
IF @count <= 0
BEGIN
PRINT 'No recipient results returned. Check the message report before resending.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
DECLARE @i int
SELECT @i = 0
WHILE (@i < @count)
BEGIN
EXEC sp_OASetProperty @reply, 'I', @i
DECLARE @resultNumber int
SELECT @resultNumber = @i + 1
-- JsonTypeOf returns 5 for a JSON boolean.
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'messages[i].accepted'
IF @iTmp0 <> 5
BEGIN
PRINT 'Result ' + @resultNumber + ' has no acceptance flag.'
END
ELSE
BEGIN
EXEC sp_OAMethod @reply, 'BoolOf', @iTmp0 OUT, 'messages[i].accepted'
IF @iTmp0 = 1
BEGIN
DECLARE @messageId nvarchar(4000)
EXEC sp_OAMethod @reply, 'StringOf', @messageId OUT, 'messages[i].apiMessageId'
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'messages[i].apiMessageId'
IF (@iTmp0 <> 1) or (@messageId = '')
BEGIN
PRINT 'Result ' + @resultNumber + ' was accepted without a tracking ID.'
END
ELSE
BEGIN
PRINT 'Result ' + @resultNumber + ' accepted. Message ID: ' + @messageId
END
END
ELSE
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'messages[i].errorCode'
EXEC sp_OAMethod @reply, 'StringOf', @sTmp1 OUT, 'messages[i].errorDescription'
PRINT 'Result ' + @resultNumber + ' rejected: ' + @sTmp0 + ' ' + @sTmp1
END
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
END
GO