At 02:56 23/12/2002 +0100, you wrote:

>Hi all,
>
>I'm trying to copy data from one database to the other via external
>tables. I have problem with null values.
>
>I first insert rows from source database to external table. Then I try
>to insert from external table to destination database, but all NULL
>values are converted:
>
>integers become zero
>dates become 17.11.1858

This is "date zero" on the time line so you are getting zero as a
placeholder for null.

If you inspect the external table, do you see 18 zeroes or 18 blanks? or a
mixture?

>Is this behaviour expected?
>
>If so, how to overcome it?

Well, text is text. How do you represent a null date or a null number as
an ascii string? I generally make all columns of ext. tables char (not
varchar) and cast all the output to make it behave as I want; and likewise
when porting it back again. For example, export an empty string and use a
UDF to trim off blanks and zeros when reading it in; or store the word
NULL and bring it in via a stored proc that knows what to do with the
string 'NULL' for a particular column.

If you are porting from one Fb/IB database to another, don't go this
route. If it's a regular thing you have to do, use a multi-database
transaction and write a stored proc that takes the values from a select
query on one as inputs to an insert on the other. Reserve the external
table stuff for transferring from and to non-compatible data storage
systems. It is really quite limited; and of course you can't move blobs
by the external table method, either.

heLen