SQL Server Requires Chilkat v11.0.0+
SQL Server
Clickatell Chat Flow: Resume a waiting conversation
See more Clickatell Examples
Use this outbound REST call from a billing job after it generates an invoice. Configure a Wait for Callback node with the same reference UUID supplied to the job and the result value used by the flow. Reuse that UUID for retries. Clickatell hosts the endpoint. The billing job needs no inbound HTTP listener.
Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @iTmp1 int
-- 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
-- These values come from the waiting session and its configured result branch.
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'reference', 'WAITING_SESSION_UUID'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'result', 'invoice_ready'
DECLARE @data int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @data OUT
DECLARE @success int
EXEC sp_OAMethod @data, 'UpdateString', @success OUT, 'invoiceNumber', 'INV-1042'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
DECLARE @sent int
EXEC sp_OAMethod @http, 'HttpJson', @sent OUT, 'POST', 'https://flow-callback.clickatell.com/chat/callback', @data, 'application/json', @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 204
BEGIN
PRINT 'Clickatell already processed this reference.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @data
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF (@sent = 1) and (@iTmp0 = 202)
BEGIN
PRINT 'Invoice data accepted for the waiting conversation.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @data
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'No response. Keep the same reference if the job retries.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @data
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Chat Flow returned HTTP ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp1 OUT
IF (@iTmp0 = 400) or (@iTmp1 = 410)
BEGIN
PRINT 'Check the reference, result and whether the session is still waiting.'
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @data
EXEC @hr = sp_OADestroy @resp
END
GO