Invalid field type

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Invalid field type

Post by radub » Wed 04 Dec 2013 09:14

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.
Last edited by radub on Sun 08 Dec 2013 11:18, edited 3 times in total.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Invalid field type

Post by AlexP » Thu 05 Dec 2013 09:10

Hello,

Please send the script for creating the table

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Invalid field type

Post by radub » Thu 05 Dec 2013 09:42

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.

nickbarnes
Posts: 18
Joined: Mon 18 Nov 2013 01:37

Re: Invalid field type

Post by nickbarnes » Mon 09 Dec 2013 08:49

"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.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Invalid field type

Post by AlexP » Mon 09 Dec 2013 09:10

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.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Invalid field type

Post by radub » Mon 09 Dec 2013 12:15

It works.
Case closed.

Thank you,
Radu B.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Invalid field type

Post by AlexP » Mon 09 Dec 2013 13:37

Hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.

nickbarnes
Posts: 18
Joined: Mon 18 Nov 2013 01:37

Re: Invalid field type

Post by nickbarnes » Tue 10 Dec 2013 00:34

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?

nickbarnes
Posts: 18
Joined: Mon 18 Nov 2013 01:37

Re: Invalid field type

Post by nickbarnes » Tue 10 Dec 2013 07:20

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?

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Invalid field type

Post by AlexP » Tue 10 Dec 2013 09:13

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

nickbarnes
Posts: 18
Joined: Mon 18 Nov 2013 01:37

Re: Invalid field type

Post by nickbarnes » Tue 10 Dec 2013 11:15

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?

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Invalid field type

Post by AlexP » Wed 11 Dec 2013 08:18

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.

nickbarnes
Posts: 18
Joined: Mon 18 Nov 2013 01:37

Re: Invalid field type

Post by nickbarnes » Wed 11 Dec 2013 11:42

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 :wink:.

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.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Invalid field type

Post by AlexP » Thu 12 Dec 2013 10:30

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.

nickbarnes
Posts: 18
Joined: Mon 18 Nov 2013 01:37

Re: Invalid field type

Post by nickbarnes » Fri 13 Dec 2013 02:31

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?

Post Reply