DEV Community

Barrett Otte
Barrett Otte

Posted on

3 2

Sending a Text Message with SQL (IBMi)

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

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

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
);
Enter fullscreen mode Exit fullscreen mode

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'
  )
);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'
    )
  )
);
Enter fullscreen mode Exit fullscreen mode

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.

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay