RJS Adopts OAR to Simplify Remote Database Access

November 2, 2010 Alex Woodie

RJS Software Systems last week unveiled a free extension to its RPG2SQL Integrator software that uses the new Rational Open Access: RPG Edition (OAR) technology to connect RPG applications running on IBM i to remote databases. According to RJS, the OAR handler conceals some of the complexity of using RPG2SQL Integrator, and makes it easier for programmers to bring outside databases into the IBM i frame.

RPG2SQL Integrator is an established product that allows IBM i programs to read and write to other databases such as Microsoft‘s SQL Server and Access, Oracle‘s 11g database and MySQL, and even Excel spreadsheets. By creating a real-time connection between IBM i apps (COBOL and CL programs are also supported) and outside data sources, the Windows-based software eliminates the need to adopt other processes to move data, such as file transfers, database replication, and manual re-keying.

According to RJS software architect Vernon Hamberg, there was a bit of a learning curve for programmers to adopt RPG2SQL Integrator and correctly integrate its APIs and procedures into business applications. “The code is more complex than standard I/O. It’s on a par with embedded SQL, perhaps, or ODBC processing in Visual Basic,” he writes in an e-mail. “Although this is no problem for experienced ILE developers, it adds a level of complexity.”

When IBM unveiled OAR as a component of IBM i 7.1 earlier this year, Hamberg immediately thought it could be a good fit for an RJS product. While most ISVs who have adopted OAR use it for driving Web-based GUIs from RPG, Hamberg foresaw its use for simplifying remote database access with RPG2SQL Integrator. “We pride ourselves on staying ahead of the curve,” he says.

More Like IBM i

Before OAR, setting up the RPG2SQL software involved a number of steps that might be unfamiliar to the average IBM i programmer who’s proficient with the standard OPEN, READ, and CLOSE I/O opcodes, but is out of his comfort zone when dealing with lower-level programming on other platforms.

Pre-OAR, the setup process for RPG2SQL Integrator included using GetFld* functions to extract column values from the remote database. Then the programmer needed to use RJS’ binding directory and implement error handling on the PC server before getting to the work of connecting IBM i applications to remote databases.

With the new OAR handler, the setup process will be more familiar to IBM i programmers of all skill levels. As Hamberg explains, the process includes setting up a typical F-spec that incorporates the new OAR I/O handler, which features a template for the optional parameter that’s passed to the handler. Then the programmer sets just two values: the IP address of the PC running the RPG2SQL Integrator middleware, and the connection string for the remote database (as entered in the optional parameter). “It’s very similar to reading a local physical file,” Hamberg says.

After the setup process is complete, the RPG2SQL Integrator user can get down to business specifying which tables to access and what to do with the data. Once again, with the OAR handler doing the dirty work, this process is more familiar to the typical IBM i programmer.

Remote database OPENS, READS, and CLOSES work as IBM i programmers would expect when working with RPG opcodes, and aren’t as lengthy and convoluted as they were before. “In the words of one who has seen a demonstration, the OAR handler conceals the complexity of retrieving data from remote databases,” Hamberg says.

At this point, the OAR I/O handler supports the OPEN, READ, and CLOSE opcodes. Hamberg says it should be fairly easy to add CHAIN, keyed DELETE, and others. Opcodes such as SETLL and READE (with varying numbers of keys) will take more time. The decision to add more advanced features will depend on customer interest, he says.

The new OAR extension is a free download for existing RPG2SQL Integrator users. For more information and trial downloads, see the company’s website at www.rjssoftware.com.