Re: Astonished by embedded sql

Ok, first off to address Dennis' concern about the qualified DS,
assuming you are at v5r4 or later a qualified DS should work fine.

Second, it doesn't appear to me that MyFile is read in it's entirety.
As Dennis pointed out, there's a WHERE clause. So at most only the
records with a matching date would be read.

However, looking at the fetch loop:
DoU SqlCod <> 0;
Exec SQL
Fetch Next from C2 Into :MyFile_DS;
Leave;

EndDo;

It seems to me that with the LEAVE in there, only the first record
with matching date would be read.

Honestly, the loop with the leave makes no sense. You'd get the same
same with just a single FETCH NEXT.
Exec SQL
Fetch Next from C2 Into :MyFile_DS;

Note that the above isn't checking SqlCod, but in your original code
SqlCod isn't being checked either!


Now, unless there's more to this statement than you've shown us,
there's no need to use dynamic SQL. A static statement would work:

someDateFieldCHAR = %char(someDateField);

Exec SQL
Declare C2 cursor for
Select * From myfile
where myfield = : someDateFieldCHAR;

Exec SQL
Open C1;
if SqlCod <> 0;
/handle error
endif;


Exec SQL
Fetch Next From C2 into :MyFile_DS;
if SqlCod <> 0;
/handle error
endif;


However, remember my motto, "If you're using a cursor you're probably
doing something wrong." You might see the term RBAR thrown about when
it comes to cursors, especially in MS SQL Server forums.

RBAR stands for Row-By-Agonizing-Row.

There are a few places where you have to use cursors, but this isn't
one of them!

someDateFieldCHAR = %char(someDateField);
exec sql
select * into :MyFile_DS
from myfile
where myfield = : someDateFieldCHAR
fetch first 1 row only;
if SqlCod <> 0;
/handle error
endif;


Lastly, while I'm a big proponent of SQL, I'm a proponent of using it
for what it's designed for; namely set based operations. Returning a
single row isn't set based. The only way the above statement will
preform well is if there's an index (logical file) over MyFile keyed
by myField. That being the case, when it comes to returning a single
row, an RPG CHAIN is going to perform much better.

HTH,
Charles Wilt







On Tue, Oct 12, 2010 at 4:11 AM, David FOXWELL <David.FOXWELL@xxxxxxxxx> wrote:

 I don't understand the use of this embedded sql instead of using a CHAIN operation. Hopefully someone will explain.

 A file is read by the RPG cycle (primary file) and the subroutine is called for every record of the file.
 This file contains a couple of hundred thousand records.

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.
Although I'm not sure about the reading. Is the whole file read at the open cursor statement?

Myfile has a couple of hundred records. For the whole primary file only about 10 different values for someDateField will be used.


        D  MyFile_DS    E DS                  EXTNAME (myfile) QUALIFIED

       Begsr mysr

       wSql =  'Select * From myfile '+
               'Where myfield = ''' + %Char(someDateField) + ''' ';

       Exec SQL
         Prepare S2 From :wSql;

       If SqlCod < 0;
         //Treat error
       EndIf;

       Exec SQL
         Declare C2 Cursor For S2;

       Exec SQL
         Open C2;

       If SqlCod < 0;
         //Treat error
       EndIf;

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

       EndDo;

       Exec SQL
         Close C2;

       EndSr;
--
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.