... was too fast before:
Downside of Stored Procedures: You cannot filter the result set directly,
you have to loop the result set within the same sequence as it is returned.
If you need a different sequence or not all rows that are returned
programming effort (in each Programming Language!!!) is required.
A UDTF on the other side is specified within the FROM clause of a SELECT
statement and can be handled like any table or view!
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Richard
Schoen
Sent: Freitag, 31. Januar 2020 14:57
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: RE: Returning Resultsets from RPG Subprocedures
Hi Birgitta,
Thanks for the feedback.
Sounds like you've summarized it. Data structures for internal usage and
JSON for external usage.
I was inspired by a discussion with Marina Schwenk the other day on her
IBMiUnit testing component.
https://github.com/MarinaSchwenk/IBMiUnit
I started thinking about ways to make existing RPG code more flexible for
both internal app use (In existing RPG apps that run in batch or green
screen) and web apps and services (which typically require JSON or XML
interaction and output).
When I write in .Net, Java, etc it's easy to return result sets, so I
started thinking about the best way to simulate that in RPG.
In RPG we're trying to promote subprocedures, but in todays world of
modernization I think of making all my code flexible enough to work in
screen applications or web applications and services as well.
On the side of stored procedure usage I may have an unpopular opinion, but
I've always kept my database code as simple as possible and relied on the
business layer to contain the business logic. Since RPG is so easy to call
as a sproc that's worked well for me. I also have experienced the nuances of
MySql and SQL Server and Oracle on other platforms and they each have
specific things to think about so I've always kept DB access as simple as
possible in the business tier.
Thanks again for the input.
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx
------------------------------
message: 2
date: Fri, 31 Jan 2020 05:51:15 +0100
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: RE: Returning Resultsets from RPG Subprocedures
If you are only working with RPG for externalizing Data Access, I'd suggest
returning the result as Data structure.
Returning the result as JSON makes it more flexible, especially when calling
from other programming languages, but the JSON data must be parsed on the
other side.
... just as an aside, try to move as much logic into the database, i.e. use
(complex) views, user defined (table) functions and other database
techniques to reduce your (RPG) source code to a minimum.
Mit freundlichen Gr??en / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is
worse than training your staff and losing them? Not training them and
keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Richard
Schoen
Sent: Donnerstag, 30. Januar 2020 19:03
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Returning Resultsets from RPG Subprocedures
Hey RPG folks,
In thinking about using RPG subprocedures and service programs for querying
individual records and record sets, what would be the best way to call a
subproc that returns a resultset and have it return the data to the calling
program ?
For my discussion the calling program could be another Green Screen app or a
web app.
What are my options for most universal DB access code re-use ?
-Return as data structure ?
-Return as JSON or XML packet ?
-Write to temp DB table ?
-What about actually returning the results object ?
I suppose just using copy books is an option to include at the code level
on-the-fly.
Insight appreciated since I don't do as much RPG as I used to.
Thanks
Regards,
Richard Schoen
Web:
http://www.richardschoen.net<
http://www.richardschoen.net/>
Email: richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx>
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
