Barrett Otte

Posted on Apr 23, 2020

Sending a Text Message with SQL (IBMi)

#ibmi #sql #twiliohackathon

This is going to be a pretty short post, but I thought it would be fun to share this in light of the Twilio + DEV hackathon this month. Honestly, I'm not sure if either project I planned out will be finished in time. This month went by way too fast, so the outlook is not good.

If you are unfamiliar with IBMi and have a few minutes to read, I encourage you to glance over a previous subpar post. If not, it doesn't really matter; its not critical to looking at a couple blocks of SQL.

This image is IBMi in a nutshell. It either disgusts or fascinates a younger developer; it is what it is.

Since I'm still a pretty new IBMi developer, I found that the best way to learn this new world was to take a fun idea and see if it was possible to do with RPG and/or DB2 SQL.
Now I have a handful of arguably useless code snippets for my day job lol...

Example HTTP Request in DB2 for i

One of the major things that made me fall in love with IBMi was that DB2 was really versatile. The fact that I could easily send HTTP requests with SQL was mind blowing.

For a really basic example, I'm going to do a basic HTTP GET to the GitHub REST API v3 for my user using only SQL. The equivalent CURL command will be

#!/bin/bash
curl -i https://api.github.com/users/barrettotte

This HTTP request is super simple because we don't need any HTTP headers.

values SysTools.HttpGetClob(
  cast('https://api.github.com/users/barrettotte' as varchar(128)), -- url
  cast(null as clob) -- http headers
);

The request returns

00001
'{"login":"barrettotte","id":15623775,"node_id"...}'

Ok. That's cool because we can parse the JSON string in another program or something. But, actually we can do some way cooler stuff. We can return the HTTP response as a result set.

Here is a really basic example, I mostly just used varchars for simplicity, but any field could be mapped to any appropriate data type.

select *
from json_table(
  SysTools.HttpGetClob(
    cast('https://api.github.com/users/barrettotte' as varchar(128)),
    cast(null as clob)
  ),
  '$' columns(
    login          varchar(64)   path 'lax $.login',
    id             varchar(16)   path 'lax $.id',
    node_id        varchar(32)   path 'lax $.node_id',
    avatar_url     varchar(64)   path 'lax $.avatar_url',
    url            varchar(64)   path 'lax $.url',
    html_url       varchar(64)   path 'lax $.html_url',
    followers_url  varchar(64)   path 'lax $.followers_url',
    following_url  varchar(64)   path 'lax $.following_url',
    gists_url      varchar(64)   path 'lax $.gists_url',
    starred_url    varchar(64)   path 'lax $.starred_url',
    subs_url       varchar(64)   path 'lax $.subscriptions_url',
    orgs_url       varchar(64)   path 'lax $.organizations_url',
    repos_url      varchar(64)   path 'lax $.repos_url',
    events_url     varchar(64)   path 'lax $.events_url',
    rcv_events_url varchar(64)   path 'lax $.received_events_url',
    type           varchar(16)   path 'lax $.type',
    site_admin     varchar(8)    path 'lax $.site_admin',
    name           varchar(64)   path 'lax $.name',
    company        varchar(64)   path 'lax $.company',
    blog           varchar(64)   path 'lax $.blog',
    location       varchar(64)   path 'lax $.location',
    email          varchar(64)   path 'lax $.email',
    hireable       varchar(8)    path 'lax $.hireable',
    bio            varchar(512)  path 'lax $.bio',
    public_repos   int           path 'lax $.public_repos',
    public_gists   int           path 'lax $.public_gists',
    followers      int           path 'lax $.followers',
    following      int           path 'lax $.following',
    created_at     varchar(32)   path 'lax $.created_at',
    updated_at     varchar(32)   path 'lax $.updated_at'
  )
);

The request returns

LOGIN	ID	NODE_ID	...
barrettotte	15623775	MDQ6VXN...	...

Personally I think this is really neat.

Calling Twilio from DB2 SQL

I figured I should say I know that this is not an ideal solution for probably a majority of cases. But, this is just a fun thing I'm showing.

The HTTP request I'm going to make looks like this

POST {{base}}/2010-04-01/Accounts/{{account}}/Messages.json HTTP/1.1
Authorization: Basic {{account}}:{{auth}}
Accept: application/json
Content-Type: application/x-www-form-urlencoded

To={{to}}&From={{from}}&Body=Hello+World

Calling Twilio takes a few more steps than the previous example. In DB2 for i, you have to pass HTTP headers as XML (I assume its a legacy thing and I wish it was JSON, but its still easy to work with).

The authorization header has to be passed as a Base64 encoded string. There is a scalar function called Base64Encode in SysTools that seems to work fine.

Additionally, Twilio wants the request body as a url-encoded string. To my surprise, I learned that SysTools also has a scalar function called UrlEncode. All of the hard work is done with scalar functions, you just have to piece them together.

I replaced all secrets in this query with 'variables' surrounded by double curly braces.

select *
from json_table(
  SysTools.HttpPostClob(
    'https://api.twilio.com/2010-04-01/Accounts/{{account}}/Messages.json',
    cast((
      '<httpHeader>
        <header name="Authorization" value="Basic ' || trim(SysTools.Base64Encode(
          cast('{{account}}:{{auth}}' as varchar(256) ccsid 1208))) ||
        '"/>
        <header name="Accept" value="application/json"/>
        <header name="Content-Type" value="application/x-www-form-urlencoded"/>
      </httpHeader>'
    ) as clob),
    cast((
      'To='    || SysTools.UrlEncode('{{to}}', 'UTF-8') ||
      '&From=' || SysTools.UrlEncode('{{from}}', 'UTF-8') ||
      '&Body=' || SysTools.UrlEncode('Hello World', 'UTF-8')
    ) as clob)
  ),
  '$' columns(
    sid           varchar(64)   path 'lax $.sid',
    date_created  varchar(64)   path 'lax $.date_created',
    date_updated  varchar(64)   path 'lax $.date_updated',
    date_sent     varchar(64)   path 'lax $.date_sent',
    account_sid   varchar(64)   path 'lax $.account_sid',
    phone_to      varchar(32)   path 'lax $.to',
    phone_from    varchar(32)   path 'lax $.from',
    msg_srv_sid   varchar(64)   path 'lax $.messaging_service_sid',
    body          varchar(1600) path 'lax $.body',
    status        varchar(16)   path 'lax $.status',
    num_segments  varchar(8)    path 'lax $.num_segments',
    num_media     varchar(8)    path 'lax $.num_media',
    direction     varchar(32)   path 'lax $.direction',
    api_version   varchar(16)   path 'lax $.api_version',
    price         varchar(8)    path 'lax $.price',
    price_unit    varchar(4)    path 'lax $.price_unit',
    error_code    varchar(8)    path 'lax $.error_code',
    error_message varchar(512)  path 'lax $.error_message',
    uri           varchar(256)  path 'lax $.uri',
    nested '$.subresource_uris[*]' columns(
      media       varchar(256)  path 'lax $.media'
    )
  )
);

The request returns

SID	DATE_CREATED	DATE_UPDATED	DATE_SENT	ACCOUNT_SID	PHONE_TO	...
...	Wed, 22 Apr 2020 23:35:26 +0000	...	...	...	...	...

Conclusion

That's kind of it, I thought it was pretty cool and figured I'd share it for anyone browsing around this morning.
The gist can be found here

Realistically, I don't know what you would do with this. But, knowing that it exists "might" do some good in some situation.

DEV Community

Sending a Text Message with SQL (IBMi)

Example HTTP Request in DB2 for i

Calling Twilio from DB2 SQL

Conclusion

Top comments (0)

Read next

Common Table Expression (CTE) in PLSQL | Part 1

List of the best Google Dorks in 2024 for finding SQL injection vulnerabilities and other security issues:

Understanding and Configuring Oracle Access Control Lists (ACLs) for Email Sending Using UTL_MAIL

Publish Dacpac to Azure SQL with Entra-only authentication using GitHub Actions