firebird embedded external table insert error update

[JustAsking]

I’m trying to load data into a Firebird embedded DB using an external table using the SQL queries explained here:

http://www.firebirdfaq.org/faq209/

CREATE TABLE ext1 EXTERNAL ‘c:\myfile.txt’
(
field1 char(20),
field2 smallint
);

After that I’m trying to populate a normal table with the external data using

INSERT INTO realtable1 (field1, field2)
SELECT field1, field2 FROM ext1;

The problem is the data from the external table is not visible in Base and the second statement returns:

1: firebird_sdbc error: *Use of external file at location C:\Program Files\LibreOffice\Cndtn_Code.txt is not allowed by server configuration

I guess this is a problem with the parameter ‘ExternalFileAccess’ that must be set as either ‘Restricted’ or ‘Full’ in the ‘firebird.conf’ file to allow the access.

The only problem is I can’t find firebird.conf in the embedded server distributed with LO. Does someone know how to solve this? The SQL Statements can be done in macros and I find that way more convenient than the usual copy&paste method to insert existing tables in Base.

[Ratslinger]

Hello,

To my knowledge there is no requirement for firebird.conf to be present when using Firebird embedded. This is based upon some older documentation:

The configuration file firebird.conf is only needed if you want to change the Firebird root directory;

from Using Firebird: (work in progress)

Did try to insert a file into the database section of the .odb but it had no effect. There are probably a number of reasons for this. The Firebird database as it resides in the .odb (Base file) is a backup file. When the database is opened it is restored as the database. It is probable this resides in memory. The Base code is most likely not set to utilize a firebird.conf file.

You you most likely need to submit an enhancement request for this functionality. Do this here → Bugzilla

[GNK]

I don’t think it will be possible to configure FB embedded the same as for FB external for LibreBase…

But for the record:

I also wanted to use .txt tables (FB External Tables) with LibreBase - the solution i came to was BASIC macros to export/import using Libre API ‘FreeFile’ and ‘Print’ functions combined with procedures & global-temp-tables to prepare/unpack data, i.e. rather than use actual FB External Tables, emulate them with Libre API.

This works very fast, just a good as ‘native FB external tables’ - better perhaps because BASIC macros have wider range of possible functions, e.g. table-write routine can be constructed without need for LineFeeds…

NB - use global-temp-tables for data export/import to avoid bloating the LO.odb file, as it seems it can not be ‘compacted’ like an external Firebird (ie backup+restore(with garbage collection))