>At 3/6/2001 06:21 AM (Tuesday), darko_prenosil@... wrote:
> >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 !

Unfortunately each invocation of a UDF returns only one set of
values and the UDF can not maintain state between calls.

> >
> >For example sybase support proxy table that is similar to this idea,
> >and MsSql does not have problems to access more databases at all.

Bitter as gall. InterBase doesn't have any trouble accessing several
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 extremely
>difficult and ultimately unreliable.

Which is probably not true. If you want to return something like
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 no
>context to the function.

This is true, and is a serious problem if you are trying to access
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 to
>provide data to it. How does it know how to do this?

No problem. The UDF has no connection with the current database.
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 (or
>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.

Yes, that's probably better - at least possible, which the UDF solution
is not.

Regards,

Ann
www.ibphoenix.com
We have answers.