Den 2011-03-15 21:57 skrev Woody såhär:

> From: "Kjell Rilbe"<kjell.rilbe@...>
>> Den 2011-03-15 21:44 skrev Woody såhär:
>>> I'm trying to come up with the best way to use procedures and UDF calls
>>> to import some data occasionally from an Access database. I have no
>>> problems getting the data from the Access server in the UDF and saving
>>> it to the external file. I also don't have any problems creating the
>>> external table definition and inserting the data into my own tables.
>>
>> This isn't what you asked about, but I'm curious about a couple of things.
>>
>> Where does the UDF come into the picture?
>>
>> If you have some kind of program to extract data from Access and write
>> it to a file that you can read via external table, why don't you simply
>> let that program insert the data into the Firebird database natively?
>> Why go the roundabout way via an external table?
>
> The UDF I wrote is what is pulling the data (using OLE) and writing it to a
> file. I know that you shouldn't connect back to the database within a UDF so
> I did it that way. The problem is that the database keeps a lock on the file
> as soon as I transfer a batch of records using the external table
> definition. Attempting to transfer a second time keeps the UDF routine from
> clearing and re-writing the file. That wouldn't change regardless of using a
> UDF or an external program.
>
> Firebird can't run an external program from SQL, to my knowledge, so even if
> I wrote a separate program to transfer the data directly into the database,
> I would have to use a UDF to run the external program. If all else fails, I
> may try to do it that way. Since the database is on the server, everything
> has to run from there.

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

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.

Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64