RE: AW: SQL question -- RPG400-L

Thanks very much Birgitta and Rob.



Warm Regards,
JoeP

From: "HauserSSS" <Hauser@xxxxxxxxxxxxxxx>
Reply-To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Subject: AW: SQL question
Date: Thu, 31 Aug 2006 12:23:21 +0200

Hi,

may be we talk about different things:
1. Embedded SQL in RPG (as in Rob's example) allows you to execute each
SQL-Statement in RPG, not only stored procedures or UDFs.
2. A stored procedure is a program that can be called from any interface
that allows embedded SQL.
   i.e. interactive/embedded SQL, JDBC, ODBC

A stored procedure can be written in SQL or an High Level Language(HLL)

such as RPG or COBOL or in JAVA.

This program must be registered as stored procedure with theSQL-Command

CREATE PROCEDURE.
   A stored procedure must be called with the SQL-Command CALL.

For more information about embedded SQL and stored procedures look at the
following redbooks:
Modernizing IBM eServer iSeries Application Data Access - A Roadmap
Cornerstone
http://www.redbooks.ibm.com/abstracts/sg246393.html?Open
Stored Procedures, Triggers and User Defined Functions on DB2 Universal
Database for iSeries
http://www.redbooks.ibm.com/abstracts/sg246503.html?Open

Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Joseph Pascoguin
Gesendet: Thursday, August 31, 2006 02:59
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: SQL question


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

I believe that what Iam going to create through embedding SQL in RPG areSQL

stored procedures (also UDF right)in RPG right? Your example shows stored
procedures (SQLRPGLE).
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) mailinglist

>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) mailinglist

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

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

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


_________________________________________________________________

Express yourself instantly with MSN Messenger! Download today it's FREE!http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/