Morning Cristoph,

>> what is the value stored in a varchar() column by:
>> update tab1 set col1 = '' ?

You asked "what is stored", read on ....... ;-)


I created a test table and inserted two rows, one empty string the other
a NULL:

SQL> create table test(a varchar(10));
SQL> insert into test values ('');
SQL> insert into test values (null);
SQL> commit;


Now I need the relation id for this table:

SQL> select rdb$relation_id
CON> from rdb$relations
CON> where rdb$relation_name = 'TEST';

RDB$RELATION_ID
===============
147


Now I need the type 4 page (pointer page) to get a list of the pages
where data are stored for this table:

SQL> select rdb$page_number,rdb$page_type
CON> from rdb$pages
CON> where rdb$relation_id = 147
CON> and rdb$page_type = 4;

RDB$PAGE_NUMBER RDB$PAGE_TYPE
=============== =============
172 4


Now all I have to do is look at the pointer page to get my actual data
pages:

SQL> shell;


tux> ./fbdump -p 172 ../blank.fdb

DATABASE PAGE DETAILS - Page 172
Page Type: 4 (Pointer Page)
PAGE DATA
...
Page[0000]: 174


I can see that this table has a single data page, page 174. Now I can
dump that out:


tux> ./fbdump -p 174 ../blank.fdb

DATABASE PAGE DETAILS - Page 174
Page Type: 5 (Table Data Page)
PAGE DATA
Sequence: 0
Relation: 147
Count: 2
...
Data[0000].offset: 4072
Data[0000].length: 22
...
Data[0000].hex: 01 fe f1 00 00 00 00 00 00
Data[0000].ASCII: . . . . . . . . .

Data[0001].offset: 4048
Data[0001].length: 22
...
Data[0001].hex: 01 ff f1 00 00 00 00 00 00
Data[0001].ASCII: . . . . . . . . .


From the above, I see two records. They are in INSERT order at the
moment, but that is not always guaranteed. So the one at offset 4072 is
the empty string and the one at offset 4048 is the NULL. The data are
stored in a compressed format, so uncompressing gives me this for the
empty string:

fe 00 00 00 00 00 00 ....... 00

And this for the NULL:

ff 00 00 00 00 00 00 ....... 00

The first 4 bytes of the uncompressed data are the NULL flags for
all/the first 32 fields in this record. We have only one field so we
only have one bit to check. Bit 0 of the first byte is field 0, bit 1 is
field 1 and so on. We look at bit 0 of the first byte.

The empty string NULL flag shows that bit zero is unset, so the field
data is not a NULL value. The other record has bit zero set indicating a
NULL.

The two bytes following the NULL flags are, in this case, the low and
high bytes of the VARCHAR data length, so the NULL is length zero as
indeed the empty string's length is.

When you query the table, Firebird will return data for the empty string
as zero length while the NULL flag indicates that the data doesn't have
a value at all - even though both appear to be zero length VARCHARS.

HTH


Cheers,
Norman.

Norman Dunbar
Contract Senior Oracle DBA
Capgemini Database Team (EA)
Internal : 7 28 2051
External : 0113 231 2051



Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk