Firebird, FlameRobin differing results
lazarus / 16-Databases

----------------------------------------------------------------------
TRNelson · 2016-02-18 16:19

Some field lengths (in particular CAHR and VARCAHR) returned from an
SQL query appear to be different from the table definition.

The first clip is from FlameRobin when I double click the table name. 
This is what I expected.

The second clip shows the result of running the following SQL command:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
t1.RDB$FIELD_NAME, t2.RDB$FIELD_LENGTH, t2.rdb$field_type,        CASE
t2.RDB$FIELD_TYPE          WHEN 14 THEN 'CHAR'          WHEN 8 THEN
'INTEGER'          WHEN 37 THEN 'VARCHAR'          ELSE 'UNKNOWN'    
  END as type   FROM RDB$RELATION_FIELDS t1   LEFT JOIN RDB$FIELDS t2
ON t1.RDB$FIELD_SOURCE = t2.RDB$FIELD_NAME  WHERE
t1.RDB$RELATION_NAME='TABLE1';
It appears that Char and VarChar are exactly 4 times the length I was
expecting.

What is going on here?  Any insight would be most welcome.

----------------------------------------------------------------------
taazz · 2016-02-18 16:26

Firbird assumes the size you gave it is in characters and it returns
the size of the field in bytes depending the default character set of
the database.

----------------------------------------------------------------------
rvk · 2016-02-18 17:04

Yep, like taazz explained, your character set of the database is
probably UTF8.

You can check on following page that a character (in UTF8) has at most
4 bytes in that character set. And because Firebird needs to be able
to store those 50 characters it needs to take into account that all
characters could be 4 byte-characters so it needs 200 bytes of space.

http://www.firebirdsql.org/refdocs/langrefupd25-charsets.html

----------------------------------------------------------------------
TRNelson · 2016-02-18 17:06

Quote from: taazz on February 18, 2016, 05:26:49 pmFirbird assumes the
size you gave it is in characters and it returns the size of the field
in bytes depending the default character set of the database.
I see.  The default character set is defined as UTF8.  That is a
variable length encoding that uses 1 to 4 bytes per character.  I
think what it is indicating is the maximum space that would be used by
a character string if it was full length and used all bytes for
encoding the characters.

----------------------------------------------------------------------
TRNelson · 2016-02-18 17:07

Quote from: rvk on February 18, 2016, 06:04:05 pmYep, like taazz
explained, your character set of the database is probably UTF8.

You can check on following page that a character (in UTF8) has at most
4 bytes in that character set. And because Firebird needs to be able
to store those 50 characters it needs to take into account that all
characters could be 4 byte-characters so it needs 200 bytes of space.

http://www.firebirdsql.org/refdocs/langrefupd25-charsets.html
Yup!  Makes sense now.  Thanks for the link.