create null values in external tables

[Ismael L. Donis Garcia]

Is it possible to create null values in external tables with firebird 2.5.9?

 

It always adds 0 to the value even if I don't pass values

 

  strsql: string;

  strsql := 'CREATE TABLE tmpscia EXTERNAL ''' + txtcamino.Text + 'tmpscia.txt' + '''';
  strsql := strsql + ' (FECHA DATE NOT NULL, IDCLA INTEGER NOT NULL, IDELE INTEGER DEFAULT NULL, IDEMP INTEGER DEFAULT NULL, ';
  strsql := strsql + 'SALDO DECIMAL(15,2) DEFAULT 0 NOT NULL, IDAC INTEGER NOT NULL, ENTIDAD VARCHAR(9) NOT NULL)';

IDELE and IDEMP

 

insert into tmpscia (fecha, idcla, saldo, idac, entidad) values (:dFec, :iCla, :cSal, :iEnt, :sEnt); 

 

Best Regards

--
Ismael

[Dimitry Sibiryakov]

Ismael L. Donis Garcia wrote 31.10.2022 13:38:
> Is it possible to create null values in external tables with firebird 2.5.9?

No. Null flags are neither written nor read from it.

--
WBR, SD.

[Mark Rotteveel]

No, it is not possible to discern `NULL` in external tables. The data
format of external tables is a simplified fixed-width *binary* format,
similar to how row data is stored in memory. It does not include the
NULL bitmap that normal tables have to identify which columns have a
NULL value. In other words, it is impossible to discern NULL from 0 (in
the case of integer types), as their storage is the same (0x00 bytes for
the field width), and there is no NULL bitmap.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

Mark Rotteveel wrote 31.10.2022 13:53:
> The data format of external tables is a simplified fixed-width *binary* format,
> similar to how row data is stored in memory.

Not "similar", it is exactly this format.

> It does not include the NULL bitmap that normal tables have to identify which columns have a NULL value.

Nulls array is simply cut and because of this alignment (padding) of fields
in the record looks completely insane at first glance.

--
WBR, SD.

[Mark Rotteveel]

On 31-10-2022 13:57, 'Dimitry Sibiryakov' via firebird-support wrote:
> Mark Rotteveel wrote 31.10.2022 13:53:
>> The data format of external tables is a simplified fixed-width
>> *binary* format, similar to how row data is stored in memory.
>
>   Not "similar", it is exactly this format.

Except no NULL bitmap.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

Mark Rotteveel wrote 31.10.2022 13:58:
> Except no NULL bitmap.

BTW, because of this https://github.com/FirebirdSQL/firebird/issues/3799 exists.
Its implementation would not only make record in database smaller but also
allow record with NOT NULL fields only to be compatible with this current
external table format. After that another trivial change would allow NULL mask
to be written into external tables making it to support NULLs.

--
WBR, SD.

[Ismael L. Donis Garcia]

Thank you all.

I started using external tables to move information between servers and had
seen that event.

Best Regards
--
Ismael

> --
> You received this message because you are subscribed to the Google Groups
> "firebird-support" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to firebird-suppo...@googlegroups.com.
> To view this discussion on the web, visit
> https://groups.google.com/d/msgid/firebird-support/6f5ad208-b223-34ab-b826-f0555ec6049b%40ibphoenix.com.
>

[Mark Rotteveel]

On 31-10-2022 14:33, Ismael L. Donis Garcia wrote:
> I started using external tables to move information between servers and
> had seen that event.

If you need to preserve null information, then you could - for example -
define additional boolean columns to preserve this information.

--
Mark Rotteveel