Posted on Apr 23, 2020

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.

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' ) ) );