Give it a REST Apex

REST or "Representational state transfer" to give it it's full name has been around for a while, i hear lots of people talking about it but to be frank i don't really have a clue what it's really about.

I tried to find a simple explanation by searching the net but couldn't really find one that just explained it in simple terms so i gave up.

Even the 'simple' wikipedia link didn't seem to explain it well.

Anyway for my purposes i don't really need to know all the in's and out's of how it functions i just need to know how to call it to return some data.

I want to do this via an Apex application as we are building a reporting dashboard that needs to fetch data from another system. This system has a database (mySQL) backend to which firewall access is quite restrictive, it also has a datamodel that is not that simple to work with so direct database link (using hsodbc for example) would be tricky.

However the vendor of the application helpfully provides a 'REST' interface into the application and we can use that to fetch data.

Before i even contemplate doing this in apex though i want to make sure the REST interface works - to do this i make use of the wget utility for linux which can make the appropriate calls. Note: simple browsing to the addresses does not work - the thing calling the rest interface has to be rest capable - a native browser is not (though plugins can be got to do this kind of thing).

I won't bore you with the details of how i arrived at the command line - but the end result is this

wget  --keep-session-cookies --save-cookies cookies.txt  --post-data 'user=xxxx&pass=xxxx'  http://servername/REST/1.0/search/ticket?query=queue=\'"DB Administrator"\'

This command when run returns the data i want (basically a list of tickets from a queue) - the actual details of what this particlar call does is not that relevant as every rest interface built is going to be unique for that application - the end result is many rows of colon separated data is returned from this call.

I now know the rest interface works all i have to do now is call this from Apex - so how do i do that? Well it's relatively easy provided you know a couple of tricks.

The first thing to do is just create an application which will contain the call, just do this through the wizard as normal , just a minimal selection will do - just choose database, click next, give it a name and link it with a preexisting schema (or create a new one) then click create application (just skip all the other bits for now).

Once that's done and you are on the main application screen do the following (and bear with it as there are a few screenshots here):

1) click on shared components

2) Choose web service references

3) Choose REST

4) Fill in the appropriate REST URL details and give it a name. Note here i have to substitute a space character with %20 and also note i have to choose the POST method for this particular REST call.

5) Now i specify the two arguments to be passed in - in this case username and password - these correspond to the 2 parameters i passed earlier in wget

6) Now i define what the returned data will look like - in my case it's text, with fields separated by : and with the default newline character delimiting a 'row'

7) Now i click the test button fill in the username/password - and as you can see at the bottom here some data is returned - so far so good.

8) Now we click create and finish up, which gives us this screen where we can straight away create a form and report based on the rest 'thing' we just created.

9) First we choose the reference which is the name we just created previously - everything else is default

10) All defaults on the next page

11) Then we define the form elements that are passed through to the REST call (i.e. username/password in this specific case)

12) Give the collection that holds the results a name and define which if the returned elements we are interested in - in this case everything.

13) ok - it seems happy - now we click run page

14) we get a form with fields to populate with the elements to be passed to the REST post call

15) and.... ta -daah it works!

We still need to make all this look nice etc - but the basic connectivity (which is the hard bit i think) is now in place. Actually it's surprisingly easy once you figure out the various elements involved

One issue i did have that is maybe worth mentioning is that when the database is started it picks up a couple of shell variables that define proxy servers - i found that our proxy did not support rest calls and i had to connect direct.

This meant i had to explicitly unset these two parameter below before starting the database (i'm running Apex directly from the DB) i.e. using the plsql gateway.

[oracle@server]::[~]# unset http_proxy
[oracle@server]::[~]# unset https_proxy