|
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 the SQL-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 are SQL 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
midrange.com
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.
_________________________________________________________________ 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
