|Subject||Re: [IB-Architect] Proxy Query ?|
|Author||Ann W. Harrison|
>At 3/6/2001 06:21 AM (Tuesday), darko_prenosil@... wrote:Unfortunately each invocation of a UDF returns only one set of
> >Is it possible to write UDF (exetended stored proc) that opens
> >another database, runs query across that database and return the
> >result back to IB ? Is there problem by accessing IB back-end engine
> >again from UDF ? It will be ideal to return cursor or complete result
> >set !
values and the UDF can not maintain state between calls.
> >Bitter as gall. InterBase doesn't have any trouble accessing several
> >For example sybase support proxy table that is similar to this idea,
> >and MsSql does not have problems to access more databases at all.
databases, but its SQL interface does. That's getting higher on the
Firebird to do list.
At 08:06 AM 3/6/2001 -0500, Doug Chamberlin wrote:
>No, you do not want to follow this plan.Which is certainly true.
>While it may be possible to do something like this it would be extremelyWhich is probably not true. If you want to return something like
>difficult and ultimately unreliable.
a cursor or result set, a UDF just can't do it. If you want a
single value or a single set of values, you can do it in a UDF,
and it would be reliable.
>UDF's were not designed for this type of work.Which is true. They can not aggregate or return indeterminate
quantities of data.
>Some problems which come to mind: 1) The UDF call provides noThis is true, and is a serious problem if you are trying to access
>context to the function.
back into the current database. Since you have no context in the
other database, it's no so important in this case.
>2) The UDF processing has no associated transaction context.As above - a serious problem when trying to access back into the
current database, but irrelevant for foreign database access.
>3) The UDF processing stalls the server while it is happening.I know this is common wisdom, and it's probably true, but it's
not architectural. One frequent use of blob UDF's was to put
up a screen for editing blobs. That certainly did not tie up
the server. Even I would have noticed,
>4) The UDF function would have to re-connect to the database toNo problem. The UDF has no connection with the current database.
>provide data to it. How does it know how to do this?
It's a subroutine, you called it, it will return to you.
>A better design might be to have another process running on the server (orYes, that's probably better - at least possible, which the UDF solution
>another server) which maintains an open connection to the database and
>responds to event alerts. When this processing is needed the event is
>triggered. The waiting process responds by doing the processing and adding
>the results to the Interbase database.
We have answers.