Re: Implicitly hidden column & EXTNAME D/S

Hi Justin,

One of the things the IBM i and RPG protect you from when you're using files as opposed to SQL is file changes.  If the file changes, you get a level check error, and you know you have to review and recompile the program.

With an externally-defined data structure in your RPG, you do NOT get a level check if the file the data structure is defined by changes.  If you're using SQL in that RPG program to populate the data structure, and you use SELECT *, it works great when you first compile it because at that point the data structure matches the field list.  But if the file is changed, and you do not recompile, your SELECT * gets the current fields from the file, which may or may not match the data structure, which is stuck at the definition used when the program was compiled. And worse, you might not get an error, just weird stuff happening. Or you might get an error because it's putting alpha data in a numeric field.

Specifying fields, e.g. SELECT fld1, fld2, fld3, helps, because that SQL statement will match the data structure definition even if the file changes. EXCEPT if someone deleted one of the fields you're using.  Then you have the same problem - maybe no error, just strange results.  However, deleting a field from a file is much rarer than adding a field.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /

On 5/1/2020 10:19 AM, Justin Taylor wrote:

" BTW it is never a good ide to use SELECT *!"

Personally, I'd say "almost never". Can you explain why it's better for the SELECT (in static SQL) to explicitly list every single column rather than doing SELECT *?


Thanks



-----Original Message-----
From: Birgitta Hauser [mailto:Hauser@xxxxxxxxxxxxxxx]
Sent: Friday, May 01, 2020 10:38 AM
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Implicitly hidden column & EXTNAME D/S

That is a problem with RPG / SQL and embedded SQL!

Even though the implicitly hidden columns are not displayed when executing SELECT * FROM, the RPG external data structure based on the table includes all columns (also the implicitly hidden ones).
You may create an additional view, based on SELECT * (without the implicitly hidden columns) and then use this view as base for the external data structure.

BTW it is never a good ide to use SELECT *!
It is much better to retrieve only the information/columns you really need.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)