Skip to main content

Leverage SQL Through an HTTP Connection

IBMer Tim Rowe Discusses new IBM i update that allows users to leverage SQL through an HTTP connection.

beam of light on blue background.

Clients who want to use SQL to access Db2* directly no longer have to open an additional ODBC or JDBC data connection to do so. New support, released with the July 4 Integrated Web Services (IWS) update, allows users to create REST APIs that leverage SQL directly through an HTTP connection.

“If you think about web applications, mobile applications, many types of modern applications today, much of the communication between the front end and back end is done using some form of a REST API,” says Tim Rowe, business architect for IBM i application development.

REST APIs—also known as SOAP APIs or web services back in the day—are important for many of the front-end languages people use for applications, whether it’s Python, Node, or PHP. All of these languages have built-in tooling that allows them to easily connect with REST APIs. This enables users to create a screen, and then connect that screen into the data points from a REST API to either retrieve or update data going back and forth between the end user and the server.

IWS support allows the user to easily create REST or SOAP APIs for pretty much any Integrated Language Environment (ILE) program. It was created with the idea of enabling the RPG or COBOL programmer to easily create a web interaction endpoint (i.e., a SOAP or a REST API) over the top of their business logic built in RPG or COBOL. This allows traditional programmers who aren’t fluent in web development to be successful quickly in this space. “IWS has been widely used, tried and true, and we’ve continued to upgrade it using the latest technologies in the web and in the REST world the past few years,” Rowe says.

Anybody who wants to leverage SQL or has been leveraging SQL now has a simpler and more consistent way to do that from their web interfaces.
Tim Rowe

Leveraging Data Directly

Web applications offer multiple sources for a user to get data, including traditional ILE programs, but what if the user wants to get data directly from the database? Developers are increasingly using SQL to connect with the database, whether it’s to read or update data, or any of the myriad possibilities with SQL on IBM i, now that so many SQL services exist.

SQL has always been part of the equation with web programming, but in the past, if a user was running some kind of web or mobile interaction, they would then have to get a different data connection in order to use SQL, whether that’s an ODBC or JDBC interface. This has proven itself in practice for many years, but if the user has a web application, they have potentially two data channels that must be available.

“IWS has served our community very well. Lots of people are using it. But it’s only ever been able to do program calls, such as to an RPG or COBOL program,” says Rowe. “We got to thinking that SQL is such a key way to interact with data, and more and more clients are wanting and needing to do that in a more native and secure fashion, so why have multiple ways within your program to access your system? Why don’t we simplify and use the HTTP connection?”

To avert the need to use ODBC or JDBC, IBM developers enabled users to connect from their web or mobile endpoint to their back-end server using HTTP. Users can encrypt data, have authorizations and all of the normal HTTP framework applied to their payload. As opposed to their payload going and saying, “Call this ILE program,” they now say, “No, run this SQL statement,” and IBM i will execute the SQL statement. This gives direct SQL access through a REST or HTTP connection.

Simplifying Connection Points

The single HTTP connection simplifies applications. It’s all driven through the IBM i web administration GUI, so users who have been using the IWS support can still leverage that capability, but it’s enhanced now so they can easily go about creating these REST APIs that use SQL directly using a convenient, easy-to-use interface.

“It’s simplifying how developers work, making it easier for them to write web applications by enabling them to take advantage of the power of the database on IBM i. They don’t have to go and get multiple connections, so it’s simpler. It’s more secure because they don’t have additional connections to deal with,” says Rowe. “It’s a part of our focus on helping the end user and development community unlock the power and business value that’s already built into their IBM i to be able to more easily create new and interesting web applications.”

Direct to Database Inquiries

Consider a rental company that has an application written to interact with their database—whether that’s orders, sales or whatever data is relevant. The people in the field need to make certain that they have a sufficient level of inventory, can understand what the price quotes consist of and know the tax implications.

A lot of that information is already present in existing ILE programs, so if an end user wants to understand what the price of something is, they need to do some calculations based on current costs, maintenance, taxes and so on—all that business logic is built into existing programs. On the other hand, if they want to query and get, say, the list of elements in stock, then they don’t necessarily need to go into a back-end program to get that information because there wouldn’t necessarily be any additional processing.

IBM i has simplified the process of running SQL against a database through a single point of connection: the REST API.

Using the update is simple. “Users need to know how to get to the IWS product page—go to Google and type ‘IBM IWS’—that one’s easy,” Rowe says. “Beyond that, we have a GUI interface we put together that allows users to quickly and easily configure this support.

“We have hundreds and hundreds of clients that use IWS today as an engine for their ILE programs. The IWS support is widely used, but clients today use it to call their existing RPG and COBOL programs, because that’s the only support they have available,” Rowe says. “Anybody who wants to leverage SQL or has been leveraging SQL now has a simpler and more consistent way to do that from their web interfaces. Basically, we’re making REST APIs on IBM i more interesting and more useful.”

Sponsored Content

5 Benefits of Using a Modern IBM i Forms Solution

Document output systems are often overlooked when organizations consider IBM i modernization. But modernizing printed forms, checks and labels with an electronic forms solution is straightforward and generates a quick ROI. The benefits are many.

Improve customer service: Modernizing your forms solution lets you electronically deliver documents that would normally be printed. Customers will receive invoices delivered as a PDF email attachment.

Reduce the burden on IT:

With an electronic forms solution, you can make changes

to forms without modifying reports or applications, automate distribution to reduce the burden on IT, and set the layout and add graphics yourself.

Reduce printing and distribution costs: By replacing printed

forms with digital documents, you save money on printing, postage and labor.

Leverage existing technologies: Older form solutions often lack support, and IBM’s AFP Utilities is nearing end of life. A modern, well-supported electronic forms solution is the most cost-effective way to leverage your existing technology.

Improve branding equity: When customers regularly see your logo on professional, easy-to-read documents, they’ll develop a lasting perception of your company.

Stephen Flaherty 

Technical specialist and IBM i enthusiast, Fresche Solutions

As a strategist and IBM i application development expert, Stephen helps clients transform and modernize their IBM i systems.

Learn More

Get the details on Integrated Web Services for IBM i at ibm.co/30RhcfS

IBM Systems Webinar Icon

View upcoming and on-demand (IBM Z, IBM i, AIX, Power Systems) webinars.
Register now →