Clickatell One API: Send an SMS and get its message ID
See more Clickatell Examples
Send one text using an SMS-enabled One API integration. Supply an international phone number starting with the country code, using digits only. For a US two-way integration, set from to its provisioned two-way number. With a test integration, use a registered test phone.
HTTP 202 means the submission was processed. Inspect accepted for each message. Save the returned apiMessageId with the application record and use it to look up delivery status. When a multi-message submission returns HTTP 207, check each result before retrying any rejected message.
Already using the SMS-only REST integration? Its documented request is POST /messages with the integration key in Authorization and a body such as {"content":"Test message","to":["PHONE_NUMBER"]}. One API has a different request body and a separate integration key. SMS API reference
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 @iTmp1 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
-- Supply the key for this One API integration from application configuration.
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', 'ONE_API_KEY'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Accept', 'application/json'
DECLARE @body int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @body OUT
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].channel', 'sms'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].to', 'PHONE_NUMBER'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].content', 'Test message: your order is ready.'
-- For a two-way sender, supply the number provisioned on the integration.
DECLARE @sender nvarchar(4000)
SELECT @sender = ''
IF @sender <> ''
BEGIN
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'messages[0].from', @sender
END
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://platform.clickatell.com/v1/message', @body, 'application/json', @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 @body
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
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp1 OUT
IF (@success <> 1) or ((@iTmp0 <> 202) and (@iTmp1 <> 207))
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Clickatell returned HTTP ' + @iTmp0
IF @parsed = 1
BEGIN
EXEC sp_OAMethod @reply, 'HasMember', @iTmp0 OUT, 'error.description'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'error.description'
PRINT @sTmp0
END
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @body
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 @body
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, 'error.code'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'IsNullOf', @iTmp0 OUT, 'error.code'
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'error.code'
EXEC sp_OAMethod @reply, 'StringOf', @sTmp1 OUT, 'error.description'
PRINT 'Submission error ' + @sTmp0 + ' ' + @sTmp1
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @body
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 @body
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].error.code'
EXEC sp_OAMethod @reply, 'StringOf', @sTmp1 OUT, 'messages[i].error.description'
PRINT 'Result ' + @resultNumber + ' rejected: ' + @sTmp0 + ' ' + @sTmp1
END
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @body
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
END
GO