Getting Started With REST and SQL
Rest, can you ever really get enough of it? Not only is rest great in the overnight hours, but it’s key in today’s world of modern development. For many years, IBM i has included a web services engine, which was originally able to create SOAP-based web services over RPG and COBOL programs and services programs. It was enhanced several years ago, allowing REST APIs to be created over these ILE artifacts. In June, the REST API engine was added for SQL.
Why REST API Matters
Developers have many ways to call SQL on IBM i today, so why would we add another? With the addition to ILE programs and service programs, you can now wrapper SQL with a REST API call. This opens up some interesting opportunities. Now, you have the option to prohibit external ODBC or JDBC activity. You can also wrapper well-constructed and tested SQL within a HTTP-based connection that’s callable as a REST API.
With HTTP, you now can leverage all of the existing simplicity, security and reliability that the HTTP protocol provides. You can easily set up the connection for TLS/SSL. Keeping that data pipe encrypted continues to be an increasingly important requirement for today’s businesses. You can also add user authentication to the request.
Getting Started
With the latest HTTP PTF groups for IBM i 7.2, 7.3 and 7.4 installed, open the Web Administration GUI (http://hostname:2001/HTTPAdmin). After creating a web services server, you can then proceed to the “Deploy a new service” interface. On the first page of the deploy wizard, you have an option to create a SOAP or REST API. When creating a REST API, you can choose to create an API over an ILE program or the new SQL support.
The Integrated Web Service (IWS) engine, when called, will handle the calling of the SQL. Because the SQL call is being wrapped in a HTTP-based call, the IWS engine will handle the actual calling of the SQL. This is done using the JDBC connection over localhost. The other option is to specify a remote JDBC connection; now the IWS engine will call the SQL on that remote system. This gives you the ability to have a multitier solution with your web infrastructure on one system and the database on a separate node.
Once you've determined your JDBC connection, you can specify the SQL that you want to leverage. This is where things get interesting. You can specify SQL that can read or write information into a Db2* for i database. And this allows you to ditch the database access wrapped in an ILE program and leverage SQL directly. The other option to leverage the many IBM i SQL services that we’ve been creating and delivering over the past several years. This means that you have the option to wrapper some system management activities with a REST API as well.
In Figure 1, you can see several options, including retrieving data from the database or choosing to insert data into the database. As you might expect, you can include variables that can be passed in any of the many options available on the HTTP request. The last example is some SQL that’s leveraging the IBM i service, which provides user profile details. Using the power of SQL, you can easily sort and filter the data returned to get only the values you want. In this case, the that’s a list of user profiles that have *ALLOBJ special authority either explicitly or from a group profile.
Once you’ve specified a few JDBC options, you’re then presented with REST parameters that you must determine and specify. You can choose what HTTP request this REST API will use. Additionally, you must determine where in the HTTP request (form, query, parameter, etc.) any input data will be coming from. You can also specify what output format you want to leverage: XML, JSON or even have the data returned in an HTML document. Once you’ve finished determining the specifics of the REST part of the request, you’re ready to start calling and leveraging your new REST APIs.
New Updates and Expanded Opportunities
One recently delivered set of IBM i services updates and controls data queues. I find this interesting because you can now interact with your application data queues with a REST API connection. Think about the ability to easily integrate new UI options, being able to leverage and talk with your existing back end in a simple and tightly integrated manner.
The world of REST on IBM i has been greatly expanded and enhanced, allowing application developers and even system management folks to participate in the REST world.