|Partner TechTip: Working with Embedded SQL Is Error-Prone and Messy. Is There an Easier Way?|
|Tips & Techniques - SQL|
|Written by Scott Salisbury|
|Tuesday, 11 November 2008 19:00|
Coding SQL within RPG or Java is time-consuming and tedious, but what if you could automatically generate SQL for RPG or Java in seconds?
As System i programmers, we are being nudged, prodded, and goaded to use embedded SQL in our programs. Some of us have fully embraced embedded SQL, and some of us have used it reluctantly, but in either case, we can all agree on one thing: working with embedded SQL can be a hassle, especially when compared to good old SETLL, READ, and CHAIN. However, despite the tedious details, SQL provides power and flexibility that can never be matched with the old RPG op codes.
SQL(snap) eliminates the tediousness of embedded SQL and turns hours of tiresome coding into a few clicks.
With that in mind, let's review the steps for building an embedded SQL program, regardless of language.
Here's the old-fashioned way:
Build It Manually
(Let's use a prepared statement since it's an SQL best practice.)
Oh, and let's not forget this part:
Here's an easier faster way:
Build It Automatically Using SQL(snap)
If you're like most programmers, you've worked through the steps of the old-fashioned scenario many times. So now, let's take a look at an easier way using SQL(snap). Figure 1 below shows the SQL(snap) client.
Figure 1: This is the SQL(snap) client. (Click images to enlarge.)
Note that SQL(snap) allows you to execute SQL against any of your databases, including Microsoft SQL Server, Oracle, MySQL, DB2, Postgre, and of course, DB2/400.
In Figure 2 below, I have selected the options to generate SQLRPGLE and Java based on the query also shown in Figure 2.
Figure 2: SQL(snap) allows you to generate code for RPGLE and Java.
In Figure 3 below, SQL(snap) allows you to copy the generated source code to a System i or to a spot on your network (in the case of Java).
Figure 3: Once the embedded RPG and/or Java code is generated, you can copy the source to the library and source file of your choosing.
So in just a few seconds, SQL(snap) has created a fully functional program--in both RPG and Java--that you can compile and run based on the above query. Here is a list of the code components that SQL(snap) did automatically:
Your job now is to either place the generated code into one of your own programs or build your own business logic around the fully functional program that SQL(snap) just built for you.
Both RPG and Java provide powerful SQL implementations. Each offers tremendous flexibility and granularity for building embedded SQL code. Unfortunately, with greater flexibility comes greater complexity, which translates into time spent...lots of time. SQL(snap) gives you back that time and makes your programs easier to manage at the same time.
For a free 30-day trial of SQL(snap), a video demonstration, or a free whitepaper, visit http://www.pinchhittersolutions.com/. Pinch Hitter Solutions is located near Nashville, Tennessee, and can also be reached at 800.653.7404.
|Last Updated on Tuesday, 14 April 2009 04:54|