Recent

Author Topic: reading attributes from table  (Read 399 times)

mangakissa

  • Hero Member
  • *****
  • Posts: 1029
reading attributes from table
« on: April 06, 2020, 03:46:01 pm »
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:
Quote
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?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Middlecope

  • Jr. Member
  • **
  • Posts: 65
Re: reading attributes from table
« Reply #1 on: April 06, 2020, 05:09:38 pm »
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

  • New member
  • *
  • Posts: 7
Re: reading attributes from table
« Reply #2 on: April 06, 2020, 05:44:34 pm »

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

  • Hero Member
  • *****
  • Posts: 1029
Re: reading attributes from table
« Reply #3 on: April 07, 2020, 10:14:06 am »
@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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

zoltanleo

  • Full Member
  • ***
  • Posts: 245
Re: reading attributes from table
« Reply #4 on: April 07, 2020, 11:00:48 am »
@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
Win7 Ultim x64/Deb 10 amd64 GTK3:
Lazarus x32/x64 2.1(r.63122); FPC 3.3.1 (r.44724), FB 3.0.5

Sorry for my bad English, I'm using translator ;)

mangakissa

  • Hero Member
  • *****
  • Posts: 1029
Re: reading attributes from table
« Reply #5 on: April 08, 2020, 08:58:16 am »
It's an option to consider but difficult to manage in my model.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

 

TinyPortal © 2005-2018