Subject Re: [firebird-support] External tables
Author Woody
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.

Woody (TMW)