Article Sponsor

Pinch Hitter Solutions

Address

PO BOX 223, Thompsons Station, TN, USA, 37179

Phone

800.653.7404 x702

E-mail

Website

http://www.pinchhittersolutions.com/index.html

Products

• Database Anywhere
• SQL(snap)

Pinch Hitter Solutions was formed in 2005. We build software products geared to take the tedium out of applications database work. Our goal is to provide software developers with easy access to all common enterprise database technologies regardless of their development platform.

 

 

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.)

1. Determine the query that you want to use in your program.
2. Create a prepared statement object to process the query.
3. Declare a cursor for the prepared statement object to iterate the result set.
4. Open the cursor, passing runtime parameters to it.
5. Fetch rows and iterate through the result set, executing your business steps.
6. Close the cursor.

Oh, and let's not forget this part:

7. Test the program.
8. Fix the SQL issues.
9. Test, fix, test, fix, test, fix, etc.

Here's an easier faster way:

Build It Automatically Using SQL(snap)

1. Create and test your query using the SQL(snap) browser interface.
2. Click to generate code that will work the first time in either RPG or Java.

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:

1. Generated a data structure to receive each row of the result set
2. Turned the above SQL statement into an embedded SQL prepared statement
3. Created a cursor object to iterate through the result set
4. Opened the cursor with the appropriate variable parameters
5. Created the looping logic to process the result set
6. Closed the cursor

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.