Commented by: @dyemanov

NULLs are not supported for external tables. However, I'd rather prefer to see an error raised in this case.

Commented by: Yakushev.Y (yyy)

It'a a pity. :(
External tables is a flexiable and comfortable way to import data from external systems.
As for example I can convert various dbf data and load it into database using stored procedures.

I also tried to use it in order to transfer big amount of data across databases, as the fastest way to load data is to export table in a copy of it in external table and then insert it like this.

{{{
begin
select list(' declare variable '||field_name||' '||meta_type,';') /*list_of_vars*/, list(':'||field_name) /*list_of_params*/, list (field_name) /*list_of_fields*/
from META_ET_TABLE_field_types (:source,'-1')
where meta_type not containing 'BLOB' or 1=:ext into :list_of_vars,:list_of_params,list_of_fields;
end
/* Если роль не REPL, то REPL$ID и REPL$GRPID не копируются */
if (:srole <> 'REPL') then
begin
select list(' declare variable '||field_name||' '||meta_type,';') /*list_of_vars*/, list(':'||field_name) /*list_of_params*/, list (field_name) /*list_of_fields*/
from META_ET_TABLE_field_types (:source,'-1')
where (meta_type not containing 'BLOB' or 1=:ext) and field_name not starting with 'REPL$' into :list_of_vars,:list_of_params,list_of_fields;
end

/* формируем начало для скрипта копирования таблицы */
copysql = 'execute block returns (counter integer) as '||:list_of_vars||'; begin counter=0; for select '||:list_of_fields||' from '||:source||' ' ||:customwhere||' into '||:list_of_params||' do begin'||
' begin update or insert into '||:target||' ('||:list_of_fields||') values ('||:list_of_params
||'); end counter=:counter+1; /*suspend;*/ end'||' end ';

/* получаем данные для SQL */
execute statement copysql as USER :SUSER PASSWORD :SPASSWORD ROLE :SROLE ;
}}

Error won't help me, I'll treat '17.11.1858' as null.

Commented by: @krilbe

You could import/export date data as varchar and use cast in select/insert. In that case you could use empty string or some marker, e.g. '<NULL>' for nulls.

Commented by: Yakushev.Y (yyy)

That what I'll do.

Issue is closed.

But may be FB team will think of adding support for NULLs in external tables.

Commented by: @dyemanov

See CORE2198 and CORE3438.