reading attributes from table

[mangakissa]

I have FB3 database with this table created:

Code: SQL  [Select][+][-]

1. CREATE TABLE BOLNAMEN
2. (
3.   ID                    BIGINT         GENERATED BY DEFAULT AS IDENTITY NOT NULL,
4.   NAAMID               VARCHAR(     6) NOT NULL COLLATE UTF8,
5.   BOLKORT              VARCHAR(     4)  COLLATE UTF8,
6.   NAAM                 VARCHAR(    35)  COLLATE UTF8,
7.   TYP                  VARCHAR(     4)  COLLATE UTF8,
8.  CONSTRAINT PK_BOLNAMEN PRIMARY KEY (ID)
9. );
10.  

My idea is getting the attributes from the table using this piece of code:

Code: Pascal  [Select][+][-]

1. procedure TMyDatabase.LoadAttributes;
2. var index : integer;
3.     Fielddef : TobjFielddef;
4. begin
5.   fSQLQuery.First;
6.   for index := 0 to fSQLQuery.Fields.Count -1 do
7.   begin
8.     Fielddef := TobjFielddef.Create;
9.     Fielddef.fieldname := lowercase(fSQLQuery.Fields[index].FieldName);
10.     Fielddef.varlength := fSQLQuery.Fields[index].DataSize;
11.     Fielddef.datatype  := GetEnumName(TypeInfo(TFieldType), Ord(fSQLQuery.Fields[index].DataType));
12.     ObjFielddeflist.Add(Fielddef);
13.   end;
14. end;
15.  

This code can be used by every type of database.

If I look at the length of the field, this is the result:

naam : id
len  : 8
type : ftLargeint
naam : naamid
len  : 25
type : ftString
naam : bolkort
len  : 17
type : ftString
naam : naam
len  : 141
type : ftString
naam : typ
len  : 17
type : ftString

The field nameid should has the length of 6 instead of 25. It's logical, because the calculation is made by UTF-8 characters.
Can I use TSQLQuery to read the right length of a field or do I have to use somekind different?

[Middlecope]

It looks like your database management machine is storing each character in 4 bytes and adding 1 extra byte.
Unless you are making a backend DB program this will be OK?
4 x 6 +1 = 25
4 x 4 +1 = 17
4 x 35 +1 = 141 etc.
Is that a problem for you?

[gsa]

The field nameid should has the length of 6 instead of 25. It's logical, because the calculation is made by UTF-8 characters.
Can I use TSQLQuery to read the right length of a field or do I have to use somekind different?

You should use fSQLQuery.Fields[index].Size;

https://www.freepascal.org/docs-html/current/fcl/db/tfield.size.html

Gerd

[mangakissa]

@Middlecope

You're right on that I I know that also. But Should SQLdb not calculating the right length of the field?
If my charset is set to 'UTF8' in the connection and I open the dataset I set property charset is changed to 'UTF-8'. So SQLdb is reading the charset from te connection. But it should convert the length of a field in the right length if the charset is other then 'none'.

My workaround is at the moment this piece of code:

Code: Pascal  [Select][+][-]

1.     if SQLConnector1.CharSet = 'UTF-8' then
2.       Fielddef.varlength := (SQLQuery.Fields[index].DataSize - 1) div 4
3.     else
4.       Fielddef.varlength := SQLQuery.Fields[index].DataSize;
5.  

@gsa
property size is equivant to datasize.

[zoltanleo]

@mangakissa

Try to use system tables, e.g.

Code: Pascal  [Select][+][-]

1. select R.RDB$RELATION_NAME, R.RDB$FIELD_POSITION, R.RDB$FIELD_NAME,
2. F.RDB$FIELD_LENGTH, F.RDB$FIELD_TYPE, F.RDB$FIELD_SCALE, F.RDB$FIELD_SUB_TYPE
3. from RDB$FIELDS F, RDB$RELATION_FIELDS R
4. where F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE and R.RDB$SYSTEM_FLAG = 0
5. order by R.RDB$RELATION_NAME, R.RDB$FIELD_POSITION

There is a good article in Russian, it’s easy to understand using a translator
Or read LangRef here

[mangakissa]

It's an option to consider but difficult to manage in my model.