Re: File Encapsulation for use with SQL Stored Procedures

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

Methinks considerations beforehand:

in what language do you want to write those SQL stored procedures? SQL,
embedded SQL in RPG, plain RPG?

On what keys are you going to do the read/update/insert (add)/delete a
file? Are the files accessed with one key set or can they manage records
from the file using different keys. (I mean a distinction between primary
and secondary keys on the file)

For each key set (logical access) you have to create, prepare and use
different SQL statements. Using SQL you can have a CASE-statement on the
ORDER BY for ordering the result set, but to make use of one of those
options you still have to close, prepare and open the statement.

When using this with a SFL you have to consider a cursor and if a user
changes the selection for the SFL you have to close the existing cursor,
prepare the cursor and open it, before retrieving the new data (when using
the same SQL statement). 

If you use SQL, you have to sequence the SQL actions (prepare, open, close,
fetch, etc) in the correct order (for the SQL pre-compiler).

You can also look at DYNSQL by Dave Morris, IIRC. It is part of his iSeries
Toolkit at Sourceforge. I do not know if it is updated recently or
regularly. This is a SRVPGM with embedded SQL with a possibility to have 8
cursors.

With regards,
Carel Teijgeler.


*********** REPLY SEPARATOR  ***********

On 10-9-2008 at 10:36 GKern@xxxxxxxxxxxxxxxx wrote:

> I'm looking for code examples/templates, web articles, or a utility that
will allow me to define database files so that I can access them via SQL 
> stored procedures. My goal is to bring proof of concept to management that
we can serve our database data with tools other than the 5250 green 
> screen. 
>
> The basic need would be to enable simple I/O stuff such as read, add,
update and delete, and also be able return x number of records to fill a 
> subfile or a data grid for a browser or windows .net app. Obviously this
would preferable as a service program. 
> I did follow the thread last week regarding Kurt Anderson's solicitation
for testers for his utility and requested to be included.