Power RPG with SQL
by Daniel Jacobs

With Power RPG with SQL you’ll quickly modernize your RPG skills. Instead of relying solely on limited RPG file operations, you’ll add the power and convenience of SQL to your RPG programming.

SQL is a powerful, convenient and universal tool and, if you program with RPG, there's no better way to learn SQL than to actually see how it is used in RPG programs.

Using the SQL precompiler, you’ll embed SQL statements in your RPG code. With SQL you’ll put more "ease of use" and functionality into your RPG programs. You'll work with data returned by SELECT statements and use INSERT, UPDATE and DELETE statements to make changes. You’ll learn how to create new files quickly and easily in an RPG program, without needing to call system commands.

Developed by programmers for programmers, this hands-on SQL training shows you plenty of short-cuts, gives you tips and real-world advice on how to develop and test statements. Most importantly, you’ll learn by running actual code on your system.

Here's just some of what you'll learn with Power RPG with SQL:

• How to compile RPG programs with embedded SQL statements
• SQL terminology and how it relates to traditional database terms
• Using the IBM Interactive SQL environment to create and work with tables
• Rules for defining and using the EXEC SQL statement block
• Work with host variables to pass data from RPG to SQL statements
• Retrieve list entries from a user space, use SQL INSERT to write to a table
• Unsure of the syntax of an SQL statement? Develop and test SQL statements in the Interactive SQL environment, copy/paste the working statements into RPG
• Easy options for saving your SQL tests - start your own library of examples
• Get started with new tools in Operations Navigator to create, view and work with your database
• Work with data returned from SQL SELECT statements in your RPG programs using cursors
• Cursors: read-only and updateable, serial, scroll and dynamic: know when and how to use each
• Special navigation capabilities of scroll cursors to work with data
• Boost SQL performance with DECLARE and PREPARE
• SELECT statement options to get just the data you want: LIKE, IN, BETWEEN, NOT and multiple conditions
• Work with column functions for statistics, summing and counting
• See how using GROUP BY gives you "level break" type functionality in SQL
• Develop and test RPG code with joins: inner join, outer joins, exception join and cross join
• Discover an easy way to assign default values to null values
• The power of one: how to combine SQL statements into one statement, using subselects, correlated subquries, nested tables and joins
• Rapidly extract a subset of data to a new file with this INSERT statement technique
• Mass update and delete vs. update and delete with a cursor - understand when to use each option
• How to run dynamic SQL statements using PREPARE and EXECUTE
• An important option you must set for your source members
• Work with the SQL pre-compile listing to root out compile-time bugs
• Simplify SQL error handling in your code with the WHENEVER statement
• Use the SQL Communication Area to get all information about an SQL error or warning
• Click here to reserve your 15-day free examination copy

You’ll Learn Fast!
Power RPG with SQL includes over a dozen RPG coding labs where you'll develop, test and debug RPG code using embedded SQL. You'll learn how to quickly develop SQL statements using the Interactive SQL environment or Operations Navigator, and how to incorporate those statements into RPG code.

You'll create an interactive SQL utility of your own, and see first-hand how SQL communicates not only data but also status and error information to you. Armed with this know-how, you'll be able to create bullet-proof programs that help you protect your users from potentially damaging database operations. This is gold!

Power RPG with SQL concludes with a subfile application that you'll use over and over as a template for your new applications. You'll see how you can prompt for values, pass those values to SQL SELECT statements to retrieve precisely the data you want, and display the returned data.

Power RPG with SQL includes a CD-ROM that contains all of the source code used in the course. Complete instructions for installing the source code from the CD-ROM to your iSeries and creating the test environment are included in the manual.

Don’t Get Left Behind!
SQL is an absolute must in today’s business environment. SQL is the language for virtually every widely used database including SQL Server, DB2, Oracle, MS Access, JDBC, ODBC, Net.Data and many more. Remember, with SQL you can work with data on other platforms and use query and report tools to extract data needed by your users.

Note: All RPG code is written in ILE RPG. You do not need to be proficient with ILE to use the course. Complete instructions for compiling and testing all programs are included. Completion time is approximately 25 hours.

Skill Level: RPG business programming skills, equivalent to at least 1-2 years of RPG programming experience. Proficiency working with iSeries database and file commands is assumed, but no prior knowledge of SQL is needed to use this course.

Important Note: To take this course you need the "DB2 Query Manager and SQL Developers Kit" from IBM on your AS/400 (program number 5722-ST1 for V5R1 or above, 5769-ST1 for V4R5 or lower).