The program does not require recompilation.
![cursors sql cursors sql](http://stevestedman.com/wp-content/uploads/SimpleCursor1.jpg)
Since the program object selects three columns only, the addition of another column to the table does not affect the program. The reason for this is that the SQL precompiler expands the column list, so. In this case, I added the new column at the end of the row (i.e., the record format). What happens if I add a new column? alter table employeesĭoes the program continue to run properly without modification and without compilation? This is where it depends. Statement = 'select * from employees order by clock' Here’s the same program with dynamic SQL: H dftactgrp(*no) actgrp(*new) option(*srcstmt: *nodebugio) I would not use assertions in this manner in a production program. Last, the purpose of the assertions is to let me know when an SQL statement fails. A program that only fetches one row doesn’t need a cursor, but a SELECT with the INTO clause. Third, to simplify the program I omitted the loop that would process the entire table.
![cursors sql cursors sql](https://codeandwork.github.io/courses/java/media/sp16.png)
#Cursors sql code
I don’t think most IBM i shops use nulls in the database, so I will not unnecessarily complicate the example by adding code to handle null values. Second, if the Employees table can contain null data, then I would have to define a null indicator array. I know that the fields in the data structure will be adequate to receive the fetched data. Let me point out a few things about this program.įirst, notice that the employees table - the table that the program reads - provides the external definition of the EmployeeData data structure. '*ESCAPE': '*PGMBDY': 1: MsgKey: ErrorDS) * Abruptly end the program if an unexpected condition arises. First, static SQL: H dftactgrp(*no) actgrp(*new) option(*srcstmt: *nodebugio)Īssert (SQLState < cSQLEOF: 'Open failed, state=' + SQLState) Īssert (SQLState < cSQLEOF: 'Fetch failed, state=' + SQLState) Īssert (SQLState < cSQLEOF: 'Close failed, state=' + SQLState) Let us consider two versions of a program that uses SELECT * in a cursor.
![cursors sql cursors sql](https://data-flair.training/blogs/wp-content/uploads/sites/2/2018/09/open-a-cursor.png)
Here’s the DDL for a table of employee data. Even though most programs use data from more than one table, programs that read only one table are not uncommon, and a program that reads only one table is a perfect candidate for the use of SELECT * in a cursor. To set the stage, let’s begin with a simple example - an RPG program that reads one table (physical file) and prints each row (record). Specifically, is that a good idea or a bad idea? I have learned that the answer to that question is “It depends.” Using SELECT * in a cursor declaration may or may not get you into trouble. From time to time someone brings to my attention the use of SELECT * with SQL cursors in RPG programs.