Hi all,

I got a pretty tricky problem that I want to share.

We got an application that written in C++ using IBPP that converts an old flat file database into a firbird database (using 2.1.1 embedded version of FB)

Everything have worked really well, until yeasterday when I tried it out on a new test machine that we recently got.

What we do is this:

Step 1. Write down data into a flat file
Step 2. Map a External table to the flat file in Firebird.
Step 3. With a select statement, transfer the content of the external table into an internal table.

The problem I see on the new machine says not much:

"
Fri Apr 10 09:20:48 2009 - *** IBPP::SQLException ***
Context: Statement::Prepare( INSERT INTO TYPE_4 (id, time_stamp, data)
SELECT id, rtime, rdata FROM ext4 )
Message: isc_dsql_prepare failed

SQL Message : -802
Arithmetic overflow or division by zero has occurred.

Engine Code : 335544321
Engine Message :
arithmetic exception, numeric overflow, or string truncation
"

I think I may have discovered what the problem is, long file names.

When I move the whole directory structure out of the following directory:

C:\Program Files (x86)\Our very Long Product Name Path\Folder with data\

to

C:\Short path\Folder to data\

It works.

So I figured I should use relative paths when defining the external table, modified the program and ran it again, but got the same error.

So I exited the program and opened up flamerobin and tried the import statement (see below), and it worked.

So the question is, what magic do Flamerobin do with external tables that apparently I'm missing ?

Here is the import statement:

INSERT INTO TYPE_2 (id, time_stamp, data)
SELECT id, rtime, rdata FROM ext2

Here is how I define the external table:

CREATE TABLE ext2 EXTERNAL file 'Folder with data\datafile.tmp'
(
id int,
rtime int,
rdata float
);

Its interesting to note that when I look at the DDL of the external table in firebird, the whole path of the external table is displayed even if I know that I only specified the relative path when creating it.

Any ideas are very much welcome!