You are currently on IBM Systems Media’s archival website. Click here to view our new website.

IBM i > DEVELOPER > RPG

A SQL Sequel

Dynamic SQL and RPG for application flexibility

Dynamic SQL and RPG for application flexibility

As we teach at conferences and private corporate classes around the world, we’ve noticed SQL is a very popular topic among developers—especially embedding SQL into RPG programs. We covered the basics of embedding SQL into RPG in 2005. Support for embedding SQL into RPG has evolved quite a bit since that two-part series, including our personal favorite enhancement—the fact that we can now embed our SQL statements into /Free logic. Learn more about the embedded SQL updates for RPGers from Gina Whitney’s work here and here.

In our earlier articles, we addressed the most widely used form of embedded SQL, which is known as static SQL. In this article, we’ll take a look at another option—the use of dynamic SQL. As its name implies, dynamic SQL can make our RPG programs far more flexible and simplify our logic by reacting to the application’s runtime requirements dynamically. You can create the SQL statement your application requires “on the fly” in a character variable—reacting to the specific user’s requirement for selection or sequencing of the data, for example. After creating the statement you can “compile” and run it dynamically. Now that’s flexible!

When To Use Embedded Dynamic SQL

Before embarking on how to embed dynamic SQL into RPG, perhaps we should first discuss when we should use it and when we shouldn’t. Dynamic SQL is a powerful tool for those occasions when you require the flexibility it provides. If your program needs to be able to provide a variety of options for ordering or selection of data, in many cases, you may be able to supply a series of static SQL statements and use RPG logic to determine at runtime which statement to use at a particular time. But if you’re looking at more than two or three options, your RPG logic gets more complicated and you could simplify by using a single dynamic SQL instead. And, of course, some applications may require so much flexibility that there’s really no feasible way to provide a sequence of static SQL statements that meet all the requirements. In that case, using dynamic SQL is far preferable to writing a lot of RPG logic to do the filtering, sequencing, etc., based on the users’ needs at runtime.

But a word of caution: We occasionally find that some shops have become so enamored with dynamic SQL that they use it all the time, even when there’s nothing in the application that requires or even uses the flexibility. We’re big believers in using static SQL whenever possible and only use the dynamic form when we really need the flexibility. There are three primary reasons:

  • First, dynamic SQL requires a bit more coding and who needs more code complicating their RPG logic?
  • Second, as a general rule, static SQL provides better performance. This makes a lot of sense, if you think about it—if the system doesn’t know ahead of time what data you require so it can’t prepare the access plan at compile time and must do that at runtime. The optimizer has become much better at dealing with dynamic SQL in recent releases, but it still tends to slow things down on our platform (not necessarily true on other platforms).
  • The third reason to use static SQL when possible is to avoid the possibility—however small it may be—of something called SQL injection attacks. Look for an upcoming blog on this topic.

Jon Paris is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Susan Gantner is a technical editor with IBM Systems Magazine and co-owner of Partner400.



Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

New and Improved XML-INTO

Namespace support makes the opcode a viable option

Authenticating on the Web

The finer points of OpenRPGUI, Part 1

The Microphone is Open

Add your voice: Should IBM i include open-source RPG tools?

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters