sqlslackexact-onlineinvantive-sql

Post message from SQL to a Slack channel using incoming webhook


I want to send messages based upon query on Exact Online to a Slack channel using the Slack incoming webhooks and Invantive SQL.

How do I this without massive SQL functions to properly escape the JSON?


Solution

  • After some tries, I found this to be working fine:

    select to_char
           ( httppost
             ( 'https://hooks.slack.com/services/XXX/YYY/zzzzzzzzz'
             , 'application/json'
             , to_binary
               ( '{'
                 || jsonencode('channel')
                 || ': '
                 || jsonencode('#test')
                 || ', '
                 || jsonencode('username')
                 || ': '
                 || jsonencode('testuser')
                 || ', '
                 || jsonencode('text')
                 || ': '
                 || jsonencode('Companies in city of ' || act.city || ': ' || act.companynames)
                 || '}'
               )
             )
           )
    from   ( select act.city
             ,      listagg(act.name) companynames
             from   exactonlinerest..accounts act
             where  act.city in ( 'Haelen', 'Horn', 'Heythuysen')
             group 
             by     act.city
           )
    

    The HTTP POST is not very elegant in the sense that it has a side effect, but it does the job.