Scott,
I appreciated your well considered reply. It motivated me to track down various IBM i manuals, redbooks, and articles, and do more reading about stored procedures and UDTFs.
I also pulled out the source code for an RPG program I wrote 20 years ago, which I think is still in use today at about 250 credit unions in the US, and supports over-the-counter teller transactions. The program uses about 30 database tables and 1 5250 display file. And I considered what it might take to separate the code, so that the database I/O and business logic could be evoked by say a PHP client.
I concluded that it would be a lot easier to separate the screen I/O and application control logic, from the business rules and database I/O, than to rewrite the business logic in PHP.
This is more for Eric Lehti's benefit and others who may be considering marriages between PHP (or something comparable) and RPG. If I were modernizing a CRM package, I'd use my own ILE framework for handling browser I/O, and database I/O. And drop the notion of a marriage between PHP and RPG.
Stored Procedures and UDTFs do seem like a viable option, but may extract enough pain, that folks may flip-flop back and forth between the alternatives of building stored procedures or just rewriting the "business layer" in PHP, depending on the particulars of each situation.
In business applications, the same tables are used for both inquiry and maintenance. And may or may not include calculated field values where the logic would be easier to implement in stored procedures as opposed to SQL selects. With stored procedures, both input and output parameters are passed on the same call. And depending on whether the database is used in an inquiry or maintenance context, the call-level interface may change. So you end up writing multiple stored procedures, or extending parameters to cover multiple contexts.
So it seems to me, that in the long run, if one were really bent on a marriage between PHP and RPG, something of a client-server request-response protocol would be better. Where the structure of requests and responses were more flexible. And where the state of RPG servers might be persistent for individual users in some applications, but support multiple users in other applications.
Nathan.
----- Original Message ----
From: Scott Klement <web400@xxxxxxxxxxxxxxxx>
To: Web Enabling the AS400 / iSeries <web400@xxxxxxxxxxxx>
Sent: Thursday, July 10, 2008 2:08:55 PM
Subject: Re: [WEB400] PHP and SQL web developer re-creates all business logic found in RPG programs
Hi Nathan,
I wasn't familiar with the UDTF acronym, so I did a google search
which produced a link to an article you wrote for System i Network
UDTF is a UDF (SQL User Defined Function) that returns a "table" (that's
where the T comes from). So in a way they're very similar to stored
procdures. They're SQL functions that return a set of rows.
The UDTF syntax is a little harder to code on the RPG side than a stored
procedure would be, but it's usable from embedded SQL -- whereas stored
procdure result sets cannot be read in embedded SQL. UDTFs are invoked
from a SELECT statement which means you can also use the features of the
select statement to your advantage. Stuff like joining, WHERE clauses,
ORDER BY, GROUP BY, etc. A powerful tool.
Let's also say that the company is inclined to retain most of the
business logic in RPG, but provide a thin UI wrapper, using PHP. ...
is there one that you'd recommend in the context that I've outlined?
Depends on the business logic and how "thin" you want the wrapper to be.
Personally, I refactor my RPG code to put the business logic in it's
own service program (i.e. a "business layer" discussed in earlier
thread) I write nice procedures that make my code as elegant as possible
to call from ILE languages like RPG.
Then I write "wrapper" routines (simple procedures that do nothing but
rearrange the parameters a bit and call the "real" routine) that are
called as an external stored procedure, which I use when I want to call
it from PHP or other non-ILE languages.
I mostly use UDTFs when I want to emulate a file, such as that IFS
example, but not so often as a replacement for a procedure call (though,
it'd certainly work). But, since I don't typically go through an SQL
layer to access my business logic from RPG and other ILE languages,
there's very little value to me in using UDTFs for business logic.
But I don't know if this paradigm is "thin" enough for your needs.
For example, if your code is written to mix screen logic with business
logic, the work required to refactor your code might be overwhelming??
It's up to you to make that decision.
If you need to make only the most minor changes to your existing RPG
code, then your best bet is probably data queues, because it provides an
easy way to continue using a "stateful" backend with a stateless (web)
front end. Personally I abhor the notion of doing this, but that's
me... IMHO, that's not really modernization, it's just a hack to make
things work. In the long run, the business logic should be modernized,
not just the UI.
So, I've been using stored procedures for most everything.
Web services are another interesting method. I've done quite a bit of
consuming web services provided by others, but my experience with
providing them (and using ones I've provided in a production app) is
limited at this point. I can see how this might be a lot more elegant
when the client side communicates with the server side over the
internet, since you don't want all of your clients to have to install
database drivers. Furthermore, there are platforms that IBM doesn't
make drivers for, and web services would be useful there as well. So I
can see advantages, and I'm playing with these web services more and
more, but so far I haven't done enough to offer a good comparison vs. SQL.
midrange.com
