reading attributes from table
lazarus / 16-Databases

----------------------------------------------------------------------
mangakissa · 2020-04-06 13:46

I have FB3 database with this table created:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE BOLNAMEN (  ID                    BIGINT         GENERATED BY
DEFAULT AS IDENTITY NOT NULL,  NAAMID               VARCHAR(     6)
NOT NULL COLLATE UTF8,  BOLKORT              VARCHAR(     4)  COLLATE
UTF8,  NAAM                 VARCHAR(    35)  COLLATE UTF8,  TYP      
          VARCHAR(     4)  COLLATE UTF8, CONSTRAINT PK_BOLNAMEN
PRIMARY KEY (ID)); My idea is getting the attributes from the table
using this piece of code:
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 procedure
TMyDatabase.LoadAttributes;var index : integer;    Fielddef :
TobjFielddef;begin  fSQLQuery.First;  for index := 0 to
fSQLQuery.Fields.Count -1 do  begin    Fielddef :=
TobjFielddef.Create;    Fielddef.fieldname :=
lowercase(fSQLQuery.Fields[index].FieldName);    Fielddef.varlength :=
fSQLQuery.Fields[index].DataSize;    Fielddef.datatype  :=
GetEnumName(TypeInfo(TFieldType),
Ord(fSQLQuery.Fields[index].DataType));   
ObjFielddeflist.Add(Fielddef);  end;end; This code can be used by
every type of database.

If I look at the length of the field, this is the result:
Quotenaam : 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 · 2020-04-06 15:09

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 · 2020-04-06 15:44

Quote from: mangakissa on April 06, 2020, 03:46:01 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 · 2020-04-07 08:14

@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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     if
SQLConnector1.CharSet = 'UTF-8' then      Fielddef.varlength :=
(SQLQuery.Fields[index].DataSize - 1) div 4    else     
Fielddef.varlength := SQLQuery.Fields[index].DataSize; 
@gsa
property size is equivant to datasize.

----------------------------------------------------------------------
zoltanleo · 2020-04-07 09:00

@mangakissa

Try to use system tables, e.g.
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
R.RDB$RELATION_NAME, R.RDB$FIELD_POSITION,
R.RDB$FIELD_NAME,F.RDB$FIELD_LENGTH, F.RDB$FIELD_TYPE,
F.RDB$FIELD_SCALE, F.RDB$FIELD_SUB_TYPEfrom RDB$FIELDS F,
RDB$RELATION_FIELDS Rwhere F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE and
R.RDB$SYSTEM_FLAG = 0order 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 · 2020-04-08 06:58

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