Playing with SQL: translator function

Oh yes, playing with SQL on an IBM i system can reserve more and more fun. SQL on these systems can also help sysadmin (as me) that not know perfectly how to program in RPG & co. to optimize day by day activities; that because since V7R3M0 IBM invests a lot in native SQL services that now a day cover a lot of different fields in system management.

Let’s start with a small example in order to break the ice, a translator function.

Sometimes, in multilanguage environments, could be really comfortable getting information in your current language. IBM i with secondary language and their message file will do it for you only for operative system menu/commands, so for application you need to get a middleware unless you want to translate every data. In this short example, you will see how it’s so easy to create your own SQL service that helps to do that.

Firstly, in this example, I will use Azure’s public API. When I’ve built this solution at work I choose this service because it’s so easy to use and because it has a very free tier, you can translate 2.000.000 of characters a month. Here you can get the FAQ page. It’s obvious that in order to use this API, you need that your partition is able to go on the internet. Another thing that you need to consider is to trust via DCM some public CAs, that because without doing that you will get some SQL errors.

At this point, we only need to play with SQL, have fun!

---

So, let’s test in your SQL IDE (ACS, VSCode, DataGrip, ecc) QSYS2.HTTP_POST function in order to test your credential and how this API works:

values QSYS2.HTTP_post('https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from=it&to=en', '[{"text":"proviamo a tradurre qualcosa con questa API"}]','{"headers":{"Content-Type":"application/json","Ocp-Apim-Subscription-Key":"SUBSCRIPTION-KEY","Ocp-Apim-Subscription-Region":"REGION"}}')

As you can see, this API give me back a JSON string with the translation.

So as not to overcomplicate the example, we assume that we will give to our API only one string to translate, that because as you can see in this example below, it’s able to translate as much string as you want:

So, if we know that we have only one string, it’s quite easy manipulating this JSON, to do that we will use JSON_TABLE. This table function convert a JSON object (or array) into a relation database table, so you only need to give as parameter the JSON object and the relative path:

select *
from JSON_TABLE(QSYS2.HTTP_post('https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from=it&to=en','[{"text":"proviamo a tradurre qualcosa con questa API"}]','{"headers":{"Content-Type":"application/json","Ocp-Apim-Subscription-Key":"SUBSCRIPTION-KEY","Ocp-Apim-Subscription-Region":"REGION"}}'),'lax $' COLUMNS(dataout varchar(3000) PATH 'lax $[0].translations[0].text'))

I know that my example is not difficult, my JSON object hasn’t so many attributes so we only get back the string in the target language (in my example is DATAOUT varchar(3000)).

Now we can easily proceed creating our function. In my example I will put three parameters like language source, language target and text to translate:

CREATE OR REPLACE FUNCTION sqltools.translate (lanin char(2), lanout char(2), textin varchar(10000))
    RETURNS varchar(10000)
    LANGUAGE SQL
    BEGIN
        DECLARE txtout varchar(10000);
        SELECT dataout
        into txtout
        from JSON_TABLE(QSYS2.HTTP_post('https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from=it&to=en','[{"text":"proviamo a tradurre qualcosa con questa API"}]','{"headers":{"Content-Type":"application/json","Ocp-Apim-Subscription-Key":"SUBSCRIPTION-KEY","Ocp-Apim-Subscription-Region":"REGION"}}'),'lax $' COLUMNS(dataout varchar(3000) PATH 'lax $[0].translations[0].text'));
        RETURN txtout;
    END;

To check if its everything OK, you could check if there is an object in your library like the screen:

---

Now let’s try our function with a sample database (you can create with this statement: CALL
 QSYS.CREATE_SQL_SAMPLE('DEMODB')):

SELECT DEPTNAME,sqltools.translate('en','it',DEPTNAME) as DEPTIT FROM demodb.DEPT

As you can see, the DEPTIT column contains the translation of the column DEPTNAME.

---

So, this would be only an example of what you can do with SQL on IBM i, we will soon see some example more complex of SQL usage, as monitoring tool and so on.

And… what about you, do you have or use any SQL function that you have built? Please tell us your experience on the comments.

Andrea