I’m not going to criticise anyone for trying to move away from native I/O on the IBM i and into SQL, but I have seen a few eye-watering attempts. So in the spirit of sharing knowledge and making life a bit easier for all of us, here is an example of reading and updating a table using SQL.
Let’s start with the table we’re updating. It’s a very simple table and simply maps the SoldTo/ShipTo combination in the distibution system to a Store number in the retail system. You can build a simplified version of this using the following:
create table testlib.sqltest (index integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), Soldto char (5) not null, shipto char (4) not null, store numeric (5, 0) not null)
The requirement is that, based on various business rules, some SoldTo numbers in this table need to be changed. In order to keep things simple, the below program applies a simple rule of: If the store number is less than ten, change the SoldTo to 99999. (I know, I said I was keeping things simple).
The program, as it stands, is pretty much as simple as it gets, but there are couple of points that are worth noting explicitly.
Firstly, when we declare the cursor, we need to include a
For Update clause to identify which field (or fields) can be updated.
Secondly, in the Update statement, the
Where Current of C1 clause applies the update to all records returned by the cursor. That means that flexible mass updates with a minimum of logical mess are now at your fingertips.
H D SqlRecord DS D SoldTo 5A D ShipTo 4A D Store 5S 0 D D NewSoldTo S 5A /Free Exec Sql Declare C1 cursor for Select SoldTo, ShipTo, Store From SqlTest For Update of SoldTo; Exec Sql Open C1; Dou SQLSTATE>='02000'; Exec Sql Fetch C1 into :SqlRecord; If SQLSTATE<'02000'; If Store<=10; NewSoldTo='99999'; Exec Sql Update SqlTest Set SoldTo = :NewSoldTo Where current of C1; Endif; Endif; Enddo; Exec Sql Close C1; *INLR=*ON; /End-Free