Sending a Text Message with SQL (IBMi)

barrettotte profile image Barrett Otte ・4 min read

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.

IBMi Example

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

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


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(
    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

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


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(
        <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"/>
    ) as clob),
      '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

... Wed, 22 Apr 2020 23:35:26 +0000 ... ... ... ... ...


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.

Posted on by:

barrettotte profile

Barrett Otte


IBMi and web developer aspiring to be good at being mediocre at everything.


markdown guide