How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

gunix · 2025-01-08 10:13:11

i Should know the size of Character field, and Whether or not field can be null?

ab · 2025-01-08 12:58:08

You have ISqlDBRows.ColumnNull() for this.
For the field size, use ColumnUtf8() and its returned length.

gunix · 2025-01-09 02:58:22

ab wrote:

You have ISqlDBRows.ColumnNull() for this.
For the field size, use ColumnUtf8() and its returned length.

function ColumnUtf8(Col: integer): RawUtf8;
ColumnUtf8 can't get field size, only return empty string

ab · 2025-01-09 07:58:49

What do you call "field size" then?

gunix · 2025-01-09 08:30:02

ab wrote:

What do you call "field size" then?

create table Test (F1 char(20), F2 varchar(10));
insert into Test(F1,F2) Values('F1 Test Value','Should ok')

How Can I get F1 Field Size=20 and F2 Field size=10?

Last edited by gunix (2025-01-09 08:31:03)

ab · 2025-01-09 08:50:28

Please take care about how you ask your question: it has nothing to do with TSqlDbStatement.

It is part of the table description.
All this information is available from TSqlDBConnectionProperties.GetFields().

gunix · 2025-01-09 09:45:49

SqlStatement：TSqlDbStatement；

SqlStatement.Execute('select A.F1,A.F2,...... from Test A join xxx B on A.xxx=B.xxx where .....');
ColumnType(0) Can Get F1 FieldType ftUtf8, But i don't know how to get field size

ab · 2025-01-09 10:45:03

You are making a lot of confusion, I am afraid.

Read again my answer, and try to find out what is wrong with your assumptions.

First: why do you need this "field size"?
Modern databases (like PostgreSQL) does not even favor any such field size in table field declarations.

gunix · 2025-01-09 22:02:01

in order to convert into Delphi TDataset, FieldDefs.Add( FieldName,FieldType,FieldSize,CanNull)

zen010101 · 2025-01-10 03:44:28

gunix wrote:

in order to convert into Delphi TDataset, FieldDefs.Add( FieldName,FieldType,FieldSize,CanNull)

You can try the TSqlDBConnectionProperties.GetFields() method ab mentioned. This method outputs the field definition to the Fields parameter, which is a TSqlDBColumnDefineDynArray type. Each element contains the following information:

  TSqlDBColumnDefine = packed record
    /// the Column name
    ColumnName: RawUtf8;
    /// the Column type, as retrieved from the database provider
    // - returned as plain text by GetFields method, to be used e.g. by
    // TSqlDBConnectionProperties.GetFieldDefinitions method
    // - SqlCreate will check for this value to override the default type
    ColumnTypeNative: RawUtf8;
    /// the Column default width (in chars or bytes) of ftUtf8 or ftBlob
    // - can be set to value <0 for CLOB or BLOB column type, i.e. for
    // a value without any maximal length
    ColumnLength: PtrInt;
   ...
  end;

gunix · 2025-01-10 06:28:59

zen010101 wrote:

You can try the TSqlDBConnectionProperties.GetFields() method ab mentioned. This method outputs the field definition to the Fields parameter, which is a TSqlDBColumnDefineDynArray type. Each element contains the following information:

I don't know Field from which Table,because I read Sql from Json File, Fetch Data,Display Data.

Last edited by gunix (2025-01-10 06:52:46)

ab · 2025-01-10 07:17:51

I don't know Field from which Table,because I read Sql from Json File, Fetch Data,Display Data.

Then the is no such thing as "field size" in JSON.
What you could get is the maximum size of all values in a JSON string field, but it is mostly pointless.

Therefore, my initial question stands: why do you need this "field size"?
And in which unit? UTF-8 bytes? Unicode codepoints?

gunix · 2025-01-10 08:40:00

fsize :: Result
-> Column
-> IO Int
fsize result (Col colNum) = numFromResult result $ \ptr -> c_PQfsize ptr colNum;

Can Use fsize function in libpq.dll get field size on Postgresql DBMS?

if Can get field size ,the unit can be UTF-8 bytes

ab · 2025-01-10 08:51:54

There is no such information in the transmitted JSON.

Therefore, my initial question stands: why do you need this "field size"?

gunix · 2025-01-10 09:16:49

then,why FieldDefs need set field size of string field?
DBgrid Can Adjust Column width by Field size?

also I can set field size to -1

ab · 2025-01-10 09:57:37

DBgrid needs some other mean to adjust its columns, because UTF-8 size is not at all the same as visual width.
FieldDefs use field size only for old DBs requiring it.

gunix · 2025-01-11 14:17:10

unidac can Get field size, string field.size =character_octet_length
pq.fsize always return -1
when client app commit data to server, raise error "value is too long(20)" when insert more than 20 character ( fiedtype=varchar(20)),so, Give a permit length is a good idea in client side.

I have test ,Can't work right

procedure TSqlDBPostgresStatement.BindColumns;
var
nCols, c,k: integer;
cName: RawUtf8;
p: PUtf8Char;
begin
ClearColumns;
nCols := PQ.nfields(fRes);
fColumn.Capacity := nCols;
for c := 0 to nCols - 1 do
begin
p := PQ.fname(fRes, c);
FastSetString(cName, p, StrLen(p));
with AddColumn(cName)^ do
begin
ColumnAttr := PQ.ftype(fRes, c);
ColumnType := TSqlDBPostgresConnectionProperties(Connection.Properties).
Oid2FieldType(ColumnAttr);
//add
ColumnDataSize:=PQ.fsize(fRes, c);
end;
end;
end;

Last edited by gunix (2025-01-11 14:20:34)

ab · 2025-01-11 14:37:29

Too much confusion is killing the confusion.

lfyey121 · 2025-01-13 03:51:58

I solved this problem by defining the fields of the dataset directly at design time

gunix · 2025-02-14 09:27:07

lfyey121 wrote:

I solved this problem by defining the fields of the dataset directly at design time

It's mistake，I should use TsqlDataset
but all null value widestring field the size will be 0 in TsqlDataset
TVirtualDataSet.GetFieldData to get widestring value will rise exception when value is chinese words like '测试中文' (fieldsize=12)

change
Utf8ToWideString(data, len, WideString(dest^));

to
if len = 0 then
PWideChar(dest)^ := #0
else
Utf8ToWideChar(dest, data, Field.DataSize, len);
can display right value

largeInt field set filter is Ineffective

Last edited by gunix (2025-02-14 09:52:39)

ab · 2025-02-14 18:17:35

@gunix
Which version of Delphi are you using?

Edit: my guess is that it is FPC/Lazarus.
I have committed a huge refactoring for better Lazarus compatibility of the mormot.db.rad.*.pas units.

gunix · 2025-02-15 01:23:12

ab wrote:

@gunix
Which version of Delphi are you using?
Edit: my guess is that it is FPC/Lazarus.
I have committed a huge refactoring for better Lazarus compatibility of the mormot.db.rad.*.pas units.

Fpc 3.3.1, lazarus 3.6

largeintfield set filter error reported freepascal.org forum

create table testtable(prjid int8 primary key,prjname varchar(20),address varchar(64),telephone varchar(12));
INSERT INTO testtable ("prjid", "prjname", "address", "telephone") VALUES (3556856671915480078, '华泰大夏', NULL, NULL);
INSERT INTO testtable ("prjid", "prjname", "address", "telephone") VALUES (3566802869275333664, '盛世钱门', '', '17706555935');

var
  i:integer;
  FDBCOnnect:TSqlDBConnectionProperties;
  FPData:TsqlDataset;
begin
  FDBCOnnect:=TSqlDBPostgresConnectionProperties.Create('0575.tech:5432','test','test','test123');
  FPData:=TsqlDataset.Create(self);
  FPData.Connection:=FDBCOnnect;
  FPData.CommandText:='select * from testtable';
  FPData.Open;
  showmessage('address size='+inttostr(FPData.fieldbyname('address').Datasize));
  while not FPData.EOF do begin
    showmessage(inttostr(FPData.RecNo)+'row prjname='+ FPData.FieldByName('prjname').AsWideString);
    FPData.Next;
  end;

Last edited by gunix (2025-02-15 02:03:38)

lfyey121 · 2025-02-15 06:38:23

TSqlDataset is readonly，
I used the TBufDataset to obtain the json of the dataset through restful api, then passed it into the TBufDataset by the client, edited it in dbgrid, then obtained the delta data and submitted it to the server. All these were processed by the interface-based service.
Because there is no field size information, I defined the column information directly in the tbufdataset at design time

mORMot Open Source

#1 2025-01-08 10:13:11

How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#2 2025-01-08 12:58:08

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#3 2025-01-09 02:58:22

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#4 2025-01-09 07:58:49

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#5 2025-01-09 08:30:02

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#6 2025-01-09 08:50:28

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#7 2025-01-09 09:45:49

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#8 2025-01-09 10:45:03

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#9 2025-01-09 22:02:01

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#10 2025-01-10 03:44:28

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#11 2025-01-10 06:28:59

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#12 2025-01-10 07:17:51

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#13 2025-01-10 08:40:00

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#14 2025-01-10 08:51:54

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#15 2025-01-10 09:16:49

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#16 2025-01-10 09:57:37

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#17 2025-01-11 14:17:10

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#18 2025-01-11 14:37:29

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#19 2025-01-13 03:51:58

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#20 2025-02-14 09:27:07

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#21 2025-02-14 18:17:35

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#22 2025-02-15 01:23:12

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

#23 2025-02-15 06:38:23

Re: How Can I Get Column FieldSize when I Use TSQLDBStatement Get ReCord?

Board footer