Re: SQL Functions

Rob/David:

There are a few considerations here ...

First of all, you probably want each of these functions to return data not 
only from the same database record but also FROM THE SAME INSTANT in time. 
 That means that you _MUST_ do only one read from the database if there is 
any possibility that some other process may be updating it concurrently.

Secondly, that same possibility of database update means that you cannot 
simply get the record in one of these functions and then reuse the data in 
others because the second and following functions cannot know that the 
first was run recently enough unless they are coded so they are _ALWAYS_ 
used in the proper sequence.  This makes them not independent functions at 
all, but it may well be the correct way to go with this code.

One possibility would be to add a second parameter to each function call 
to be used as a switch to indicate whether or not to (re)fetch the data 
record.  Turn this switch *ON only in the first function to be called. 
Now, the question arises "how do you know in what sequence these are used" 
in a composite select statement such as that shown in David's posting? 
Perhaps the best answer to this is that the second parameter is updated by 
each function so any one of them will fetch the data and turn off the 
switch.  You then just have to turn the switch *ON before using the 
composite select statement.

Dave Schnee
Barsa Consulting Group, LLC

- - - - - - - - - - - - - - - - - - - -

Rob Berendt wrote about David Smith's posting: - - - - - - - - - - - - -

date: Fri, 16 Sep 2005 16:19:06 -0500
from: rob@xxxxxxxxx
subject: Re: SQL Functions

I think your question is, if each of those functions does a
Select ... 
 from IIM
 into :ReturnVar
 where iprod=:item#
and you call all three will it end up doing three i/o's or just one?

I would hope it would all be buffered.  But, honestly, that's just a wag.

Perhaps there's some game you could play with your service program that 
would only actually 'get' the row on the first function that is executed. 
Then the other functions/subprocedures would somehow know that the ITEM# 
is not changed and would thus use some global variable or other technique 
to return the new value requested without an I/O.  Sort of like the getter 

and setter techniques.

Rob Berendt