I am attempting to use external tables for the first time. My goal is to import data using an external table and an SP. I have tested the SP and the code that creates the external table, and it works for the first record written to the external table. However, the second record fails inside the SP with Error -413, Overflow occurred during datatype conversion, Code 335544334 Conversion error from string '0'.

I suspect that my record length, written from C++, differs from what Firebird (1.5 final) is expecting. As one attempt to fix this, I placed a #pragma pack(1) command around the C structure definition. That changed the file size, but didn't fix the problem.

Are there any gotchas I should be aware of regarding creation and storage of records into external tables?

My external table definition is:

RECREATE TABLE SA_IMPORTS
EXTERNAL FILE 'f:\DataFiles\SA_Imports.dat' (
SA_ID BIGINT,
S_ID BIGINT,
ACTION_TYPE SMALLINT,
STATUS SMALLINT,
LIST_POSITION SMALLINT,
NET_CREDIT DOUBLE PRECISION,
STRIKE_SPREAD DOUBLE PRECISION,
EXPOSURE DOUBLE PRECISION,
DECAY_DIFF DOUBLE PRECISION,
RESULTS_1 DOUBLE PRECISION,
RESULTS_2 DOUBLE PRECISION,
RESULTS_3 DOUBLE PRECISION,
RESULTS_4 DOUBLE PRECISION,
STOCK1_PRICE_ID BIGINT,
OPTION1_PRICE_ID BIGINT,
OPTION2_PRICE_ID BIGINT,
OPTION3_PRICE_ID BIGINT,
OPTION4_PRICE_ID BIGINT,
EXPIR_DAYS INTEGER,
SPREAD_TIME INTEGER,
ACTION_TIME INTEGER,
CREATION_TIMESTAMP CHAR(20),
TRANS_ID BIGINT);

My C structure is defined as:

#pragma pack(push,1)
typedef struct SA_ExtRec
{
__int64 ID;
__int64 iSpreadID;
short iAction;
short iStatus;
short iListPos;
double dNetCredit;
double dStrikeSpread;
double dExposure;
double dDecayDiff;
double dResult1;
double dResult2;
double dResult3;
double dResult4;
__int64 iStock1PriceID;
__int64 iOption1_PriceID;
__int64 iOption2_PriceID;
__int64 iOption3_PriceID;
__int64 iOption4_PriceID;
int iExpirDays;
int iSpreadTime;
int iActionTime;
char szCreationTime[20];
__int64 iTransID;
} tSA_ExtRec;
#pragma pack(pop)

I use binary file i/o, and do NOT append CR/LF or anything else to each data row.

TIA,

Dan.