(Comments on distributed transactions etc below)

On 2001.03.06 13:52:40 -0500 Ann W. Harrison wrote:
>
> >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.

Is there any documentation on how this multidatabase access works? which
language? Is this the yvalve?

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

I'm not sure how you are thinking, but to me this looks like a distributed
transaction is happening. Assuming snapshot transaction isolation, I would
think you would need a global announcement to all servers/ databases
whenever a transaction starts, to keep from losing record versions present
when the transaction starts, and 2pc when the transaction ends... I assume
the UDF would be able to do updates as well as queries.

By the way, how does firebird do 2pc now? Is it documented reasonably well?
(I know, RTFM, but which one?)
>
> >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.
>
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

Thanks
David Jencks
>
>
>
>