Not familiar with SQL CREATE TABLE syntax? No problem! There's a really easy way to create SQL DDL (Data Definition Language) statements.

Written by Sam Lennon

IBM i developers are becoming more comfortable exploiting the power of SQL to retrieve and manipulate data. We regularly use the workhorse SELECT, UPDATE, and DELETE statements and can bang them out quickly with minimal syntax errors. Defining tables, indexes, constraints, and the like occurs less frequently, and consequently we are much less accomplished in the syntax.

Create useful date-centric service programs with embedded SQL.

Written by Tom Snyder

Welcome to 2012! All of our year-end processes are complete, and it's the beginning of a new year with a clean slate. 2011 was a very clean year date-wise because it ended on a Saturday. Typically, there is always special handling with the last week of the year for one reason or the other, which makes working with data a prevalent topic. For this article, I wanted to share a few handy little SQL functions that I've put into service programs to extend the existing RPG date resources. And this is all done with very minimal code in a reliable way that is highly reusable.

Avoid headaches and wasted time by learning the major differences between DB2 for i and T-SQL dialects.

Written by Michael Sansoterra

It's apparent by emails I've received that some SQL Server developers are having an opportunity to delve into the DB2 for i realm. Even though SQL would ideally be a platform-neutral, standards-based language, the truth is there are many SQL syntax and feature differences among the major database providers.

With a little creativity, SQL allows you to add a form of overloading to RPG that can really extend your toolkit.

Written by Joe Pluta

In the previous article in this series, I created a simple user-defined function (UDF) that acted as an SQL interface to an RPG service program procedure. It was simple and easy to do, but the emphasis was on simple. The UDF simply passed parameters from the SQL environment straight through to the RPG procedure and returned the result. This is enough in many cases, but at other times, you might want a little more flexibility.

In this installment of our continuing series on using SQL in real business applications, we introduce the concept of invoking service programs from within SQL.

Written by Joe Pluta

We continue to provide you with examples of integrating SQL with traditional RPG. In this article, we're going to focus on how we can use a service program to extend SQL and provide an essential feature that SQL is sorely lacking: converting binary data in a character field to numeric values. Note that this is the opposite of something like the HEX function, which returns the hexadecimal representation of a field. This function will take some alphanumeric data, either in packed or binary form, and return the numeric value the data represents. This practical extension to SQL can be used by any shop.

JOINing tables is one of the fundamental tasks in SQL, and this article explains three basic syntactical approaches.

Written by Joe Pluta

SQL is the tool of choice for querying relational data, and the whole idea behind relational data is that the tables are related. What this means is that a common field in two tables can be used to tie rows from each table together into a coherent set of data. In SQL, this is performed via the JOIN, which has three distinct syntactical variations. This article will explain the differences, including a recent change that might catch you unaware.

This second article on combining the strengths of RPG and SQL shows how to turn a stored procedure into a user-defined function (UDF).

Written by Joe Pluta

In my previous article, I showed you how to create a stored procedure that will invoke an RPG program (or COBOL or CL or any other program, ILE or non-ILE). I also showed you how to test it and even explained the primary shortcoming of stored procedures: they can't be used in SELECT statements. I explained that in order to use external business logic inside of a SELECT statement, you need to use the other SQL extension technique, the user-defined function (UDF). This article shows how to create a UDF that uses your stored procedure.

This first article on combining the strengths of RPG and SQL focuses on defining and testing stored procedures.

Written by Joe Pluta

SQL and RPG are powerful and complementary tools. SQL does things well that are difficult or nearly impossible in RPG and vice versa. The trick is to develop programming strategies that take advantage of both. IBM recognized this and created the potent embedded RPG tooling, which allows you to easily take advantage of SQL from within RPG programs. This article is going to focus on the related technique of calling RPG from within SQL.