NetNews Usenet Archive 1992 #27/NN_1992_27.iso/spool/comp/database/informix/2495

Text File | 1992-11-21 | 4.0 KB | 95 lines

Newsgroups: comp.databases.informix
Path: sparky!uunet!dplanet.ssf-sys.DHL.COM!ssf-sys.DHL.COM!jgordon
From: [e-mail] (Jim Gordon)
Subject: Stored Procedures, Views,
Reply-To: [e-mail] (Jim Gordon)
Organization: DHL
Date: Fri, 20 Nov 1992 18:23:32 GMT
Message-ID: <[e-mail]>
Sender: [e-mail] (DHL Netnews)
Lines: 83

A week or two ago the following article appeared from Adam Tiller in
Australia:-

> In article <[e-mail]> [e-mail]
(Rob Freyder) writes:
> >Article 1614 (93 more) in comp.databases.informix:
> >Organization: Western Atlas Int'l - Houston TX
>
> >We are looking for report writers for Informix Online V5.0.
>
> We are using IQ (version 3) on Informix Online 5.0.
>
> We haven't found any problems.
> IQ can handle Informix views.
> We have put stored procedures under the views,
> and this way we can get IQ to take full advantage of Stored Procedures too.
>
>
> Adam
> --
>    ,-_|\     Adam Tiller                  Phone:  [phone] (was
280 2322)
>   /     \    Co-Cam Computer Group        Fax:    [phone]
>   \_,-._/    Abbotsford, Melbourne        Work:
[e-mail]
>        v     AUSTRALIA                    Play:   [e-mail]

This mail got me quite excited for a while and I wrote and asked for
how he managed to get Stored Procedures to be called by Informix
views.  Unfortunately I never received a reply.  Having investigated I
am sure that he is referring to some other capability but the
suggestion got me thinking.

One of the things that we are currently developing at DHL is a Data
Access Module that will sit between our applications and the database
engine.  This module will handle all updates and queries along with
all integrity checking (Eg. Domain, Referential, Complex Business,
etc).  It will also handle any tightly bound processing that may need
doing on the database for a business transaction.  We are
implementing this through a combination of C, Stored Procedures and
Views.  (The views are used for external report writers)

My idea was that if Stored Procedures could be used as part of the
Create view statement in place of the select statement and the Stored
Procedures had some way of knowing whether an Insert, Delete, Update
or Select function had been used.  Using this method much of our Data
Access Module could be replaced by these views which could be used as
if the underlying database looked like the view but in reality the
Stored Procedure was handling the work on as many tables as is
necessary for the business transaction.

This would have a number of benefits.

1) It avoids the restrictions on updating through views that
currently exist.

2) It would allow the underlying database structure to be radically
changed but leave applications the same.  This is the purpose of
writing the Data Access Module in the first place but this would
greatly reduce coding changes above the Stored Procedure level to
achieve the same thing.

3) It would allow the introduction of views that could much more
closely mirror particular reporting, application or business
requirements placing the processing burden on the server end and
reducing a lot of client programming and processing.

The above suggestion is thrown out for discussion and comment before
possibly being put forward as a feature request.  Comments from
anybody, including Informix, would be appreciated.  Note SPL would
probably need to be extended to cope with dynamically allocated arrays
and would definitely require access to the SQLCA structure (Something
that is planned for 6.0?).

Cheers - Jim
--------------------------------------------------------------------
Name:     Jim Gordon               Internet: [e-mail]
Company:  DHL Systems Inc          Phone:    [phone] (Work)
Address:  1700 S. Amphlett Blvd.             [phone] (Home)
          San Mateo, CA 94402      Fax:      [phone]
--------------------------------------------------------------------