• Guru: Storing Images In An SQL Table, Part 1

  June 26, 2017 Mike Larsen

  In the first part of this series, we’ll look at how we can store images (or other media) in an SQL table on the IBM i. To accomplish this goal, we’ll write an RPG program that reads a table that has the name and location of the images from a folder in the IFS, and writes those images to a table.

  The images will be stored in a column that is defined as a BLOB data type. ‘BLOB’ stands for ‘Binary Large Object’, and is a collection of binary data that is stored as a single entity in a database. …

  Read more

• Guru: Common Table Expressions Can Replace Query Chains

  June 5, 2017 Ted Holt

  Modernization efforts often concentrate on the database and programs. That is well and good, but there is more to modernization. Replacing Query for IBM i with more modern query tools is also important. But what do you do with those queries that people depend on? More confusing, what do you do with query chains?

  A query chain is a series of queries that run one after another, consolidating and reformatting data in temporary physical files, in order to produce a resulting data set, often in report form. It’s not unusual to see small CL programs like the following one:

  PGM
  

  …

  Read more

• Top Ten New IBM i RFEs

  May 22, 2017 Alex Woodie

  Should IBM open source its database drivers, or add support for .NET? Should it add the latest SSL ciphers to IBM i 7.1? These are some of the changes that it has received through its new Web-based requests for enhancement (RFE) program on its developerWorks site.

  The new RFE functionality is only a few months old, but it’s already attracting the attention of IBM i professionals who want IBM to change things about the stack. It’s also getting attention from other IBM i pros who get to vote on them.

  Here are the top ten RFEs so far, ranked by …

  Read more

• Guru: How To Cancel A Bad SQL Update

  May 15, 2017 Ted Holt

  In Three Ways To Manage Unmatched Data I wrote about the use of the RAISE_ERROR function to force a SELECT statement to cancel when unmatched data is considered a fatal error. Another good use of RAISE_ERROR is to force an UPDATE statement to cancel when an invalid condition occurs.

  To illustrate, imagine that you and I work in a factory. All factories have inventory. The people we serve purchase some inventory items and manufacture others. Our job is to write a program that will allow certain people to zero out the inventory balance for certain types of purchased items.

  The …

  Read more

• Guru: The Three Ways to Insert

  May 8, 2017 Ted Holt

  The INSERT statement is THE (as in the only) SQL way to add new data to a relational database table. At the risk of sounding like a GEICO commercial, “Everybody knows that.” Well, did you know that the INSERT statement supports three distinct ways to add new rows to a table?

  To illustrate the three forms of INSERT, imagine that you and I work for a small company that stores goods in, and ships goods from, a warehouse. Since the company has only one warehouse, there has never been a need for a warehouse ID column in any of …

  Read more

• Guru: SQL Table Functions Can Do Non-function Things

  May 1, 2017 Ted Holt

  We IBM i developers owe a great debt to Scott Forstie. He’s responsible for the wonderful DB2 for i Services and IBM i Services, which give us SQL interfaces for many functions of the operating system. Like IBM, we can write SQL interfaces to help us with non-database tasks. I recently did exactly that.

  While I can’t say that I never use the Start SQL Interactive Session (STRSQL) command, I can say that I prefer to use GUI SQL clients, in particular the Run SQL Scripts utility that is part of IBM i Access Client Solutions (ACS). I do get …

  Read more

• Guru: Three Ways To Manage Unmatched Data

  April 24, 2017 Ted Holt

  Heaven forbid that I would ignore a failed RPG CHAIN (random read) operation. I always take appropriate action. Which action I take depends on the situation. The same applies to outer joins that don’t find matching data in a secondary table. Here are three ways to deal with unmatched data in an outer join using SQL.

  To illustrate, let’s use three tables from an overly simplified general ledger system. The first is a table of departments into which the business is divided. The second is a chart of accounts. The third is a transaction file that feeds the general ledger. …

  Read more

• SQL To The Rescue

  April 10, 2017 Bob Cozzi

  Since the early 1990s, developers have been using SQL embedded in RPG as a way to retrieve just the right data for the task at hand. Certainly it was at least a decade before it became commonplace to see SQL embedded in RPG, but now, 15 years later, it is in fact the go-to method for data access, or at least you can see it from here.

  I was an early adopter of SQL, but an off-again, on-again user of embedded SQL. Originally, I felt the first format RPG with quasi-free format SQL (and all those plus signs to continue …

  Read more

• Guru: SQL Facts Of UNION And ORDER BY

  April 10, 2017 Ted Holt

  UNION and ORDER BY are powerful SQL features, but put the two together and you may get some strange and frustrating error messages. Fortunately for us DB2 for i professionals, there are easy ways to make the two collaborate and cooperate. Today is a great day to be sure we understand them.

  We need some data for examples. For some of the queries, I use a customer master table (file) and a vendor master table. These are a company’s trading partners, so the two have many attributes in common, especially names and addresses. I also use two sales history tables, …

  Read more

• Conditional SQL I/O, Take 2

  March 20, 2017 Ted Holt

  Bob writes, “Hey, Ted! I hope you can teach an old dog a new trick. I am trying to replace the CHAIN operation with SQL. I chain once to a file to read a certain record. If that record is not found, I chain again to retrieve a default record. How can I make SQL do a second read to the same file?”

  This is not a hard thing to do, as SQL has no problem joining more than once to the same table. I’ll show you two methods to retrieve your data. The first method is the easier one, …

  Read more

Previous Articles Next Articles