Subject Re: [firebird-support] External Table Size
Author Helen Borrie
At 02:03 AM 7/07/2007, you wrote:
>The following error occurred when attempted to insert an external table into
>an internal table. The external table is about 2.1 GB. This occurred on a
>windows XP pro system with an NTFS file system.

1. Could you show the DDL you used for creating the external table?

2. How is the external data formatted?

>Unhandled Exception: FirebirdSql.Data.FirebirdClient.FbException: I/O error
>for file fseek "C:\DATABASE\INST360DATA20060630CFLN.XTN"

Here's where the problem starts (if the Create External Table
definition is correct): the server either cannot open the table or
it cannot read the data.


3. What is your ExternalFileAccess setting in firebird.conf?

4. Is your application trying to read the table whilst some other
application has it open?

5. Or has someone perhaps renamed, deleted or corrupted the external file?

>Error while trying to open file ---> FirebirdSql.Data.Common.IscException:
>Exception of type 'FirebirdSql.Data.Common.IscException' was thrown.
> at FirebirdSql.Data.Client.Gds.GdsConnection.ReadStatusVector()
> at FirebirdSql.Data.Client.Gds.GdsConnection.ReadResponse()
> at FirebirdSql.Data.Client.Gds.GdsDatabase.ReadResponse()
> at FirebirdSql.Data.Client.Gds.GdsStatement.Execute()
> at
>behavior, Boolean returnsSet)
> at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteNonQuery()
> --- End of inner exception stack trace ---
> Is the error due to a limit on external table size?


>Is it possible to
>insert an external table larger than 2GB into an internal table?

You're not "inserting an external table", you are making the data of
a filesystem file visible to the database by defining a table
structure that the server can read and interpret as data. If the
file is not there when the CREATE EXTERNAL TABLE statement is
committed, the engine will try to create it. That's fine: you can
organise things so that the original, empty file is overwritten at
some point where nothing is trying to read or write.

As to size, the filesystem limits of the host determine how large the
external file can be. The engine has no idea how big the file is (or
will become).

So, if all the other stuff is kosher, reality check 6 is:

6. Are you attempting to create external tables "on the fly", e.g.,
passing a create, alter or recreate statement from your app in
response to reading the file's name? you do need to be aware of
various underlying things, such as.-

a. Any table that is in use cannot be dropped; therefore, you will
get an exception if you try to create, alter or recreate it whilst
the external file is open.

b. You need to commit one DDL statement concerning that object
before you attempt to pass another.

c. You won't be able to access the external file until the
transaction that requested the DDL statement is committed.

d. You should never mix DDL and DML in a single
transaction. Complete and commit ALL DDL before attempting to open
the external table.