Page 1 of 2
Invalid field type
Posted: Wed 04 Dec 2013 09:14
by radub
Hello again,
Same project with PgConnection, PgQuery, DataseProvider but with a master-detail relation between two ClientDataset.
Code: Select all
with ClientDataSet1 do begin
if Active then Close;
CommandText:=
'select STITEMID, TITEMID from SUBTITEM';
Open;
end;
with ClientDataSet2 do begin
if Active then Close;
CommandText:=
'select s.STITEMID, s.TITGRCODE, s.TITGRNAME '+
'from SUBTITEM s '+
'union '+
'select s.STITEMID, '''' TITGRCODE, s.TITGRNAME '+
'from SUBTITEM s ';
Open;
end;
On the second cds I set the MasterSource to the DataSource of the first, MasterFields='STITEMID' and IndexFieldNames='STITEMID;TITGRCODE'.
At the opening time raise the above error.
Even I replace '''' with null, gives the same error.
The table is
CREATE TABLE subtitem
(
stitemid integer NOT NULL,
titemid integer NOT NULL,
titgrcode character varying(25) NOT NULL,
titgrname character varying(100) NOT NULL,
titgrdesc character varying(200),
stitemtype smallint DEFAULT 0,
CONSTRAINT pk_subtitem PRIMARY KEY (stitemid)
)
Thank you
Radu B.
Re: Invalid field type
Posted: Thu 05 Dec 2013 09:10
by AlexP
Hello,
Please send the script for creating the table
Re: Invalid field type
Posted: Thu 05 Dec 2013 09:42
by radub
In the meanwhile, I simplified the example, using only one table.
I updated the first post with the modification, including the description of the table.
Re: Invalid field type
Posted: Mon 09 Dec 2013 08:49
by nickbarnes
"TITGRCODE" is being created as an ftMemo field in your dataset, and TClientDataset doesn't allow indexing or filtering on memos.
The cause is the empty string in your query. Because you haven't given it a type, Postgres has to pick one, and Postgres almost always picks "text", which PgDAC maps to ftMemo.
If you just replace your '''' with '''':varchar(25), it should come back with an ftString instead, and the index should work.
Re: Invalid field type
Posted: Mon 09 Dec 2013 09:10
by AlexP
Hello,
Such behaviour is due to that server doesn't send such field size, and if a string field has no size, we map it to a Memo field. To solve the problem, you should enable the UnknownAsString option.
Re: Invalid field type
Posted: Mon 09 Dec 2013 12:15
by radub
It works.
Case closed.
Thank you,
Radu B.
Re: Invalid field type
Posted: Mon 09 Dec 2013 13:37
by AlexP
Hello,
Glad to see that the problem was solved. If you have any other questions, feel free to contact us.
Re: Invalid field type
Posted: Tue 10 Dec 2013 00:34
by nickbarnes
AlexP wrote:To solve the problem, you should enable the UnknownAsString option.
Oh, I thought that was only for fields with type "unknown" - I didn't realise it covered varchar/bpchar as well.
This seems like a dangerous option, though... Because it creates 8kB ftString fields in place of ftMemos, it can blow out your memory usage very easily. I can only load ~70,000 rows into a TClientDataset before it crashes with an "insufficient memory" error (although it's "only" using 570MB at that point).
Other than changing the query itself, are there any alternatives?
Re: Invalid field type
Posted: Tue 10 Dec 2013 07:20
by nickbarnes
It looks like I can avoid the high memory usage by setting Query.Options.FlatBuffers:=False, but it only applies to varchar fields, not bpchar/unknown. Is this by design?
Re: Invalid field type
Posted: Tue 10 Dec 2013 09:13
by AlexP
Hello,
When using the UnknownAsString option, all fields, to which this option applies, are mapped to String with size 8192. We plan to change the behaviour when using this option for cutting of values to correct size on fetching. The FlatBuffers option works with ftString and ftVarBytes fields only.
Therefore you can either modify the query, for the server to return the correct field size, or use the DataTypeMapping technology as well:
http://www.devart.com/pgdac/docs/data_type_mapping.htm
Re: Invalid field type
Posted: Tue 10 Dec 2013 11:15
by nickbarnes
AlexP wrote:The FlatBuffers option works with ftString and ftVarBytes fields only.
Here's the relevant code, from PgClasses.pas:
Code: Select all
if SubDataType in [dtString, dtWideString] then
if not FlatBuffers and (Length >= FlatBufferLimit) then
if DataType = dtString then
DataType := dtExtString
else if DataType = dtWideString then
DataType := dtExtWideString;
For a bpchar field, the DataType is dtString, but SubDataType is dtFixedChar, so it never enters this piece of code. It looks like SubDataType is only ever dtString for a varchar field.
Should it be checking "if DataType in [dtString, dtWideString]" instead?
Re: Invalid field type
Posted: Wed 11 Dec 2013 08:18
by AlexP
Hello,
In the query you provided, both DataType and SubDataType are defined as dtString, however, since the server doesn't return length for this field, we change DataType to dtMemo.
Re: Invalid field type
Posted: Wed 11 Dec 2013 11:42
by nickbarnes
AlexP wrote:In the query you provided, both DataType and SubDataType are defined as dtString
I didn't provide a query, that was the other guy
.
For the query
select ''::bpchar, with UnknownAsString=True, SubDataType is dtFixedChar.
For
select '', SubDataType is dtUnknown.
So for both of these queries, FlatBuffers=False doesn't seem to work - the dataset still allocates the full 8kB per row.
Re: Invalid field type
Posted: Thu 12 Dec 2013 10:30
by AlexP
Hello,
bpchar is an internal type similar to Char, therefore you should specify its size when using it - in this case, the field size will be correct.
Re: Invalid field type
Posted: Fri 13 Dec 2013 02:31
by nickbarnes
I know the bpchar cast doesn't make much sense, it was just the simplest way to reproduce the issue.
Here are some more examples:
Code: Select all
select max('a'::char(1));
select ''::char(1) union select ''::char(2);
In both of these cases, Postgres returns bpchar. But with UnknownAsString=True and FlatBuffers=False, PgDAC still uses a flat buffer. Is this a bug?