I have been using them here and there (SQL External Stored Procedures more
than UDF's). They are decently fast in execution _and_ they retain state of
the called RPG *SRVPGM. What I mean by that is you can make a first call to
RPG *SRVPGM XYZ and subsequent calls on the same connection with get the
same RPG object loaded into memory (i.e global variables will retain their
values, etc).
iSeries Network has a three part article series on pattern recognition where
the same RPG logic is re-used from multiple mediums (green screen, Web app,
Java desktop). I wrote the Java desktop article portion and used SQL
External Stored Procedures to call the existing RPG service program sub
procedures.
The thing that would make them mint is if you could generate the CREATE
PROCEDURE code from an RPG prototype, and then if you could generate Java
code from the new SQL procedure. Actually, generating the code wouldn't be
that hard being there are only so many scenarios/patterns you would have to
deal with. That would make a cool WDSC plugin! If only the Good Lord put
36 hours in the day instead of 24 :-)
One thing to note is that you have to change-manage all of the CREATE
PROCEDURE statements which can be a decent sized task unless you have a CMS
product that does it well. There isn't an object stored on the system for
the created SQL procedure, but rather it's definition is stored in system
tables (at least that's how I understand it - somebody correct me if I am
wrong). So for each environment (i.e. dev, test, qa, prod) you need to
recreated the stored proc, and you need to dynamically specify which library
it should be created in (i.e. modify the source on the fly at compile time
based on the environment you are compiling into). This is more an issue for
widespread adoption (i.e. creating hundreds or thousands of them).
Richard, it's good to see you post on the RPG list. Let's us know the dark
side doesn't completely have a hold on you ;-)
Aaron Bartell
http://mowyourlawn.com
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Richard Schoen
Sent: Wednesday, October 10, 2007 5:36 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL User Defined Functions
Is anybody else wrapping RPG logic into SQL user defined functions or stored
procedures on the iSeries/Systemi ?
This is a powerful way to bring RPG logic into a reporting tool, web app or
desktop application.
I recently did a project for a new document management customer where we
were able to extend our WebDocs security by simply creating a user defined
function that tied into their business application to determine additional
document permissions. One little service program, an additional WHERE
clause and viola !!
I would be interested in hearing your usage of these capabilities.
Regards,
Richard Schoen
RJS Software Systems Inc.
"Get the information you need. Now!"
Email: richard@xxxxxxxxxxxxxxx
Web Site:
http://www.rjssoftware.com
Tel: (952) 898-3038
Fax: (952) 898-1781
Toll Free: (888) RJSSOFT
--
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.
midrange.com
