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

Internet Message Format | 1992-11-17 | 3.5 KB

Path: sparky!uunet!destroyer!sol.ctr.columbia.edu!emory!emory!not-for-mail
From: [e-mail] (Jim Gordon)
Newsgroups: comp.databases.informix
Subject: Re: Report Writers, Stored Procedures, etc
Date: 17 Nov 1992 20:08:04 -0500
Organization: Mailing List Gateway
Lines: 59
Sender: [e-mail]
Distribution: world
Message-ID: <[e-mail]>
Reply-To: [e-mail] (Jim Gordon)
NNTP-Posting-Host: emory.mathcs.emory.edu
X-Informix-List-ID: <list.1616>

> 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.
>
> etc
>
> Cheers - Jim

After considering this idea further I have realised that I am probably
trying to ask for more than is reasonable.  I still think the basic
idea is sound but I had forgotten the necessity for dealing with
select/update/delete "where" clauses that an application might use
against the view.  Also I was, to an extent, confusing the use of a
Stored Procedure in a View and also within a cursor.

I still think that being able to but Stored Procedures under views
could be helpful but this is bound to have a performance hit as the
Stored Procedure would have to provide all possible rows to the
external Select/Update/Delete and have the external process select
the correct rows based on the appropriate "where" clause.  This would
mean that no filter or index could be used to speed the process
unless very complex code was written to parse the "where" clause in
the Stored Procedure (Yuck).

So having considered this further and before anybody replies I would
like to change my suggestion to extend the use of Stored Procedures
in Cursors.  Currently it is possible to declare a SP as a cursor,
Open it and fetch results from the SP.  I would like to see this
extended so that you can declare the SP as a cursor for update.  Then
you could open it and fetch from it but also be able to pass data
back into it.  Now this could be done using the PUT command familiar
from Insert cursors or UPDATE WHERE CURRENT OF or something new.  It
does not matter as long as it is possible to change the values
originally passed to the SP in the OPEN statement.  One of these
values can be an action identifier used by the SP to know what action
to take with the new data.

This facility would be very useful where you have an array of data to
process and do not want the cost of re-opening or re-executing the SP
each time.  This can be a major factor especially if the SP does some
setup work each time.  Also it would allow the SP to handle arrays
and totalling for the transaction instead of needing to write that
into the cursor processing code.

I hope that this clarifies my ideas and steps neatly around any
rude comments darting in my direction.

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]
--------------------------------------------------------------------