RE: SV: Embedded SQL

[rob@xxxxxxxxx]

Joel,

I too use an external DS, even when I only select a portion of the fields. 
 However, that doesn't guarantee field definitions are correct.  One of 
our packages changed all the field sizes, including the key to the item 
master, (which, in an ERP package, is a big thing).  I could continue to 
use the same program without recompiling and accept truncation, etc. Using 
the external DS does help you keep it simple by just recompiling.

And this is one of those places where externalizing your I/O still doesn't 
change your work fields.  I suppose you could issue the corporate edict 
that you can't use any of the new positions.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Joel Cochran <jrc@xxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
05/12/2005 08:48 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: SV: Embedded SQL






On Thu, 2005-05-12 at 16:51, Wilt, Charles wrote:
> Good point Joel, I for one wasn't aware of it.
> 
> However, it could still be a problem since depending on such behavior 
assumes that columns will be added to the end of the file, unused columns 
will never be removed and in general the order will never change.

> If you really need every column, maybe using select * could be excused. 
But even then, it's simply the lazy way out.  It doesn't really take that 
much effort to get a list of the fields a file.  What to me is 
inexcusable, is the use of select * when you don't actually need all the 
fields.

Charles,

These are completely valid points, and I agree that when feasible you
should specify selected columns. I was just pointing out that simply
adding a field to a file does not *require* a program to be recompiled:
someone might get the wrong idea that this created some kind of level
check problem. 

As for using *, it can be seen as lazy but it fits better with what
people are used to using Native I/O: they read a record and all the
fields are there.  Using select * in conjunction with an externally
defined DS based on the same file is a simple way to get native I/O
programmers comfortable with using embedded select.

Also, if the file is very large and you need *most* of the fields, then
I think using * is perfectly valid.  I'll give you an example: the
master file for one of our old Sys-36 apps has 237 fields in it.  We
have a print program that uses about 175 of those fields: if I wanted to
use SQL and NOT use *, I would have to list all 175 of them.  That just
doesn't seem very programmer or maintenance friendly to me.  Of course,
many would argue that for such an application I should use native I/O
anyway, but that is neither here nor there...

And just for the archives, or if anyone is curious, even when I select
individual field names, I still use an externally described DS based on
the same file.  I do this for Select, Insert, and Update statements:
first of all, it guarantees that the field definitions are correct which
helps prevent errors; second, since the field definitions are the same,
there are no hidden conversions that have to take place, so performance
is better; finally, since no conversions have to take place, you can
(typically) rely on '00000' and '02000' SQLSTT values, which means less
error checking.  Just some random thoughts...

Joel Cochran
http://www.rpgnext.com

-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.