From: "Kjell Rilbe" <kjell.rilbe@...>

>
> What/how is the UDF "triggered"? Is it triggered by some kind of select
> query?
>

Basically, it's a load as needed situation. IOW, the user requests a certain
report on data that actually is being generated by another piece of
equipment on the network. They would rather see the data the way I can show
it to them (graphs, charts, etc.) instead of the other guys stuff. That
equipment generates one record per minute over the course of a certain run.
When they want to see a report on that data, I first check to see whether
I've already imported it since this only needs to be done once. If I don't
have any data for that particular run, I need to go out and transfer it from
their Access database (owned by the other company and on a different server)
to our Firebird database. Then I run the report from my own tables.

I am working closely with this other company since they also pull data from
our database for the run configuration information. But they barely know
Access let alone even heard of FB.

> May I suggest that you refactor as follows:
>
> 1. Drop the UDF and go for a separate program (a Windows service or a
> Linux daemon?).
>
> 2. Create a queue table with columns that lets you specify all that's
> required for a single Access-to-Firebird job.
>
> 3. The entity that triggers the UDF in your current solution instead
> inserts a record into the queue table.
>
> 4. The separate program monitors the queue table or even listens to a
> trigger or event on that table, to find when a new record has been
> inserted. When it finds a new record, it does its job, without using
> external tables. When it's done it either sets a flag in the queue
> record or deletes it, depending on your requirements.
>
> Would this approach work for you? If it would, I think it will result in
> a lot less problems. UDFs aren't supposed to do such extensive work such
> as complete data pump jobs.

This is overkill for this situation, I believe. Having a service running
just to check for an occasional report request which may happen a few times
a month or so just isn't worth it. Too bad I can't define a table that uses
an ODBC connection to get it's data. :-(

I'm leaning towards creating the separate program which is spawned by the
UDF call. The program can then insert the records directly using completely
separate connections and transactions.

Woody (TMW)