RE: Astonished by embedded sql

What issues do you think there are in using qualified data structures?
All of my new coding uses them as targets of my sql fetches, and I've
been converting old ones as I get into programs for maintenance reasons.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Tuesday, October 12, 2010 7:06 AM
To: 'RPG programming on the IBM i / System i'
Subject: RE: Astonished by embedded sql

My last unbidden response:

DoU SqlCod <> 0;
Exec SQL
Fetch Next from C2 Into :MyFile_DS;
Leave;

I just noticed that LEAVE - the indentation got me.

Correction: Only one row (at most) will be read.

In that case, would be MUCH better off with:
dateChar = %char(someDateField)
Exec SQL
SELECT * FROM myFile
INTO :DS
WHERE myField = :dateChar
Fetch first row only
;

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"A good storyteller is a person who has a good memory and hopes other
people
haven't."
-- Irvin S. Cobb

Now that I've had my coffee:

Each time that the SR is entered, the SQL statement will be prepared,
cursor
will be opened, all matching rows will be fetched (but not to the
intended
structure, methinks), and the cursor will be closed.

Before I had my coffee, some of this wasn't true, so you can thank Mr.
Folger.

How do you break this to the developer? Ummm... "won't work properly"
comes
to mind. Or you could ask the developer what s/he thinks happens when
a
FETCH is done into a qualified DS. Or you could ask why there's a
global
unqualified DS of the same name in the program (I think there must be
one or
it wouldn't compile, right?)

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"A good scare is worth more to a man than good advice."
-- Ed Howe

It seems to me that this is the equivalent of opening

myfile for each

subroutine call, reading every record in the file then closing

the

file again.

Yes, that's right.

Although I'm not sure about the reading. Is the whole file

read at the

open cursor statement?

No, but it is read in its entirety at the DoU loop. (FETCH does

the

lifting.)

Thanks, Dennis. But if the FETCH was executed more than once, it
wouldn't be read in its entirety a second time, would it?

By the way, I didn't think FETCH into *qualified* DS did
quite what we would hope. Perhaps I am mistaken on that.

I didn't even notice. That's going too far! How do you suggest

breaking

all this to the person who wrote it?
--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.