Re: SQL question

Hi Rob,
Thanks, I have gone through one of your posting which as below

How different they are from Java Stored procedures?


**********************************
I wouldn't use RPG source to create stored procedures.
I wouldn't use the STRSQL part of the SQL development toolkit either. Why?
Because you don't retain the source (well, without jumping through
hoops).
I would use one of two things.
My first choice would be storing them into a source member.  Then I could
use RUNSQLSTM to generate them.  Think of using RUNSQLSTM to create stored
procedure, functions and even files; like you would use CRTPF to compile
DDS source members.
My much lower second choice would be to store the source in an IFS file
that has a share built on it's directory.  Then I could map a drive to
this from my PC and use iSeries Navigator's "Run SQL scripts".

Getting back to the RPG.  Now, I may create a program, or service program,
with rpgle and this program may, or may not even, have any sql in it. Then
I would still create a different source member that I would then use
RUNSQLSTM against to make the RPGLE program, or service program, into a
stored procedure or user defined function.  Clear?  But I sure wouldn't
have any RPG source that had any CREATE... statements in it.  (Well,
outside of QTEMP anyway.)  To me, that would be like burying all your DDS
into a compile time table in a RPG program and having to recompile the
rpg, and run it, every time you wanted to change a file.

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



****************



Warm Regards,
JoeP

From: rob@xxxxxxxxx
Reply-To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
Subject: Re: SQL question
Date: Wed, 30 Aug 2006 17:12:21 -0400

Yes you have to imbed it if you don't want to use commands.

Here's a sample:

     H ActGrp(*CALLER)
     H DftActGrp(*NO)

     D OpenCursor      PR              n
     D FetchCursor     PR              n
     D CloseCursor     PR              n

     D MyLib           s             10a
     D MyFile          s             10a

      /free
       *inlr=*on;
       if not OpenCursor();
         // perform error routine to alert the troops
         // ...
       Else;
         Dow FetchCursor();
           // putting the fetchcursor on the do loop allows the user of
           // iter, and thus iter will not perform an infinite loop
           // normal processing here...
         EndDo;
         CloseCursor();
       EndIf;
       return;
      /end-free

     P OpenCursor      B
     D OpenCursor      PI                  like(ReturnVar)
     D ReturnVar       s               n

     C/EXEC SQL
     C+ Set Option
     C+     Naming    = *Sys,
     C+     Commit    = *None,
     C+     UsrPrf    = *User,
     C+     DynUsrPrf = *User,
     C+     Datfmt    = *iso,
     C+     CloSqlCsr = *EndMod
     C/END-EXEC

     C/EXEC SQL
     C+ Declare C1 cursor for
     C+  Select System_Table_Schema as library,
     C+         System_Table_Name   as file
     C+  from qsys2/systables
     C/END-EXEC

     C/EXEC SQL
     C+ Open C1
     C/END-EXEC

      /free
       Select;
         When SqlStt='00000';
           return *on;
         Other;
           return *off;
       EndSl;
      /end-free
     P OpenCursor      E

      /eject
     P FetchCursor     B
     D FetchCursor     PI                  like(ReturnVar)
     D ReturnVar       s               n
     C/EXEC SQL
     C+ Fetch C1 into :MyLib, :MyFile
     C/END-EXEC
      /free
       Select;
         When sqlstt='00000';
           // row was received, normal
           ReturnVar=*on;
         When sqlstt='02000';
           // same as %eof, sooner or later this is normal
           ReturnVar=*off;
         Other;
           // alert the troops!
           ReturnVar=*off;
       EndSl;
       return ReturnVar;
      /end-free
     P FetchCursor     E

      /eject
     P CloseCursor     B
     D CloseCursor     PI                  like(ReturnVar)
     D ReturnVar       s               n
     C/EXEC SQL
     C+ Close C1
     C/END-EXEC
      /free
       Select;
         When sqlstt='00000';
           // cursor was closed, normal
           ReturnVar=*on;
         Other;
           // alert the troops!
           ReturnVar=*off;
       EndSl;
       return ReturnVar;
      /end-free
     P CloseCursor     E

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





"Joseph Pascoguin" <joe.pascoguin@xxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
08/30/2006 04:32 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
rpg400-l@xxxxxxxxxxxx
cc

Subject
SQL question






Hi All

I have a written a group of SQL statements(stored procedure) in text and
saved it in QSQLSESS in my library with the name QRY.
I know how to call QRY & create report  in CL by STRQMQRY or RUNSQLSTM ,
but if I want to call this QRY in RPGLE without using QCMDEXC/or CL
commands
STRQMQRY/RUNSQLSTM will it be possible?

(or) I have to embed those SQL statements in my RPGLE and create
SQLRPGLE...

I apologize if this is stupid question, more pointers welcome.




Warm Regards,
JoeP

_________________________________________________________________
Don't just search. Find. Check out the new MSN Search!
http://search.msn.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.


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

_________________________________________________________________