RE: IIS to as/400 odbc

Joe,

I'll agree with you _only_ in the case of the AS/400 community. Stored
procedures are the standard for data access in the SQL Server, Oracle,
Sybase, DB2 etc. world (at least in large environments, I'm not counting
anyone that considers 1000 rows a large table.) You are correct that you
need to perform separate queries to gather header and detail but in most
cases you can return that in a single call as two record sets (I think. I
know I can from SQL Server. I think I can from the AS/400) As far as sending
multiple rows to the AS/400 in one shot, what about XML? Send the routing
and bill of materials up as a single XML document and strip is out up there.

The biggest advantage to using stored procedures and ODBC (or actually
OLE/DB) in an ADO environment is that I don't have to teach my front end
programmers anything about the AS/400 or proprietary interfaces. Every
(good) ASP programmer knows how to use ADO to access a stored procedure. I
don't care if that procedure is on SQLServer or the AS/400 -- or both!

One of the biggest problems the AS/400 has is that it is considered
proprietary (and in many cases it is.) The unfortunate reality is that if I
can say to a client that they'll be using "industry standard, modern"
interfaces to access the as/400 they might leave it in as the core of their
environment. If, on the other hand, they think they'll be creating more
"AS/400 specific, closed, proprietary, old fashioned, mainframe-like, tired,
slow, boring, difficult to maintain, and expensive" code then they're more
likely to move to some "new, open, cheap, better, faster, cool" system like
SQL Server. While we may not agree with the quotes, I doubt anyone will
argue that they represent the feeling of many AS/400 customers.

-Walden

-----Original Message-----
From: Joe Pluta [mailto:joepluta@PlutaBrothers.com]
Sent: Wednesday, August 15, 2001 9:57 AM
To: midrange-l@midrange.com
Subject: RE: IIS to as/400 odbc


Walden, for me I'm still undecided about stored procedures.  They have great
potential, although I think you'll agree that, like exit programs, a
majority of companies that implement ODBC do not implement it purely as
stored procedures.

That aside, stored procedures is an approach that is almost equivalent to a
message-based architecture, with the benefit of industry standards and only
a couple of weaknesses.

PRO
A. Industry standard interface

CONS
A. Return only homogenous data
B. Don't allow sets of data as INPUT to the stored procedure

It's a tradeoff at this point.  If what you're doing is querying homogenous
blocks of data, then stored procedures do indeed have a lot of potential.

On the other hand, with a solid message-based architecture, you get the
ability to retrieve heterogenous blocks of data, which is especially
important when you have high-volume header/detail types of queries.  With
SQL, you must repeat the header information on every row of the detail
unless you perform two separate queries.

Also, you can only add or update a single record at a time.  I'm sure you
can send a REALLY BIG record and break it up, but that's not what I'm
talking about.  I'm talking about sending arbitrary amounts of arbitrarily
formatted data, such as would be required for, say, production sheet
maintenance, where you send up the entire routing and bill of materials for
a product, or order entry, where you send up header, detail and notes
information all at one shot - or a whole raft of orders in a single
document.

So I guess my question is, if you're taknig the time to write stored
procedures, why not invest a little more time and have a true message-based
system?

Joe


> -----Original Message-----
> From: midrange-l-admin@midrange.com
> [mailto:midrange-l-admin@midrange.com]On Behalf Of Walden H. Leverich
> Sent: Wednesday, August 15, 2001 7:00 AM
> To: 'midrange-l@midrange.com'
> Subject: RE: IIS to as/400 odbc
>
>
> Joe,
>
> While I agree with your assessment of A, B and C I think you are making a
> flaw in what a command is. It looks as if you are assuming that a
> command is
> "select * from file..." or "update file set..." or "delete from file..."
>
> What if the commands allowed from the web server are limited to "call ..."
> These stored procedures can return result sets satisfying the need for
> select and can obviously update and delete rows. Whenever I add a
> new table
> I need a new set of stored procedures (in RPG, Cobol, C, whatever) that
> allows me access to them and I don't need to change the list of
> things in my
> exit point.
>
> The other advantage to this is that the user (web server) is isolated from
> data layout changes on the AS/400. If I want to split a table, or change
> something so when a record in inserted into one table an audit record is
> inserted into another all I have to do it change the stored
> procedure. There
> is ZERO impact on the web server.
>
> -Walden

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com