Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Support PostgreSQL data type CITEXT


This question is not answered. Helpful answers available: 1. Correct answers available: 1.


Permlink Replies: 7 - Last Post: Dec 2, 2017 3:12 PM Last Post By: Clayton Arends
Clayton Arends


Posts: 25
Registered: 7/19/01
Support PostgreSQL data type CITEXT  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 4:18 PM
I am attempting to connect to a PostgreSQL database that uses the CITEXT extension and CITEXT data type for case-insensitive text fields. FireDAC complains about an unknown type:

[FireDAC][Phys][PG]-1505. Cannot describe type [26118]. Complex type must be an array, composite, enum or range type

I wonder if there is a way to tell FireDAC about the data type. From the code sniffing that I've done it would be similar to the code for the SQL_TEXT data type in TPgTypesManager.Create(). E.g.

AddBase(26118, 'citext', 0, 0, 0, [paBlob]);


I know that's not available to my application but it explains what I'm hoping to accomplish. As a temporary measure I have set the "UnknownFormat" connection property to "BYTEA" but that's not a permanent solution.

Is there anything I can do?

Thank you,
Clayton

RAD Studio 10.1
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Support PostgreSQL data type CITEXT  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 11:12 PM   in response to: Clayton Arends in response to: Clayton Arends
Clayton Arends wrote:

[snip]

I wonder if there is a way to tell FireDAC about the data type. From
the code sniffing that I've done it would be similar to the code for
the SQL_TEXT data type in TPgTypesManager.Create(). E.g.

AddBase(26118, 'citext', 0, 0, 0, [paBlob]);

Is there anything I can do?

[snip]

Yes there is, you should fill a ticket for this at
https://quality.embarcadero.com/.
Clayton Arends


Posts: 25
Registered: 7/19/01
Re: Support PostgreSQL data type CITEXT  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 3, 2017 7:39 AM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Lajos Juhasz wrote:

Yes there is, you should fill a ticket for this at
https://quality.embarcadero.com/.

I'm looking for a solution a little more immediate than hoping this gets in a release 6 months to a year from now.

Surely, with all of the hooks in FireDAC there must be some way to get this working without a change to the code base.

Clayton
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Support PostgreSQL data type CITEXT  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 3, 2017 8:17 AM   in response to: Clayton Arends in response to: Clayton Arends
Clayton Arends wrote:

Lajos Juhasz wrote:

Yes there is, you should fill a ticket for this at
https://quality.embarcadero.com/.

I'm looking for a solution a little more immediate than hoping this
gets in a release 6 months to a year from now.

Surely, with all of the hooks in FireDAC there must be some way to
get this working without a change to the code base.

Clayton

I didn't checked but most probably there isn't. FireDAC is a bit closed
to extensions. Maybe you could ask this question on the CodeRage?
Clayton Arends


Posts: 25
Registered: 7/19/01
Re: Support PostgreSQL data type CITEXT  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 3, 2017 8:57 AM   in response to: Clayton Arends in response to: Clayton Arends
The data stored in CITEXT is UTF8 (in this database). I attempted to use a mapping rule (TFDMapRule) to automatically convert the blob to string. It worked as long as the characters were in the lower ASCII range (< hex 7F). When the text contained higher UTF8 characters then there was no field type I could use to automatically convert the text.

My current workaround isn't ideal but it's getting me past the first hurdle. The code must assume the data is a UTF8 string and convert from a blob to string. There may be a way for me to query the database to determine the text format in case there is a database that stores the text as UTF16/32, MBCS, etc. Here's the current code:

  // Tell FireDAC to handle unknown formats as blobs rather than error
  FDConnection1.Params.Values['UnknownFormat'] := 'BYTEA';
 
  // Read results from DBMS (the sql is already set)
  query.Connection := FDConnection1;
  query.Open();
 
  // Read the field's data (the first field is the CITEXT data type)
  stream := query.CreateBlobStream(query.Fields[0], bmRead);
  dstream := TStringStream.Create('', TEncoding.UTF8, False);
  try
    dstream.CopyFrom(stream, -1);
    str := dstream.DataString;
  finally
    stream.Free;
    dstream.Free;
  end;


When performing an insert or update query the parameter's data type must be set to ftWideString.

I'll submit a feature request to Quality Portal and include this workaround. I'm still hoping there's something I'm missing that will make this more automatic or by using hooks of some kind (like TFDMapRule).

Clayton
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Support PostgreSQL data type CITEXT
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 5, 2017 2:34 AM   in response to: Clayton Arends in response to: Clayton Arends
Try to cast CITEXT column to TEXT, eg Col::TEXT

--
With best regards,
Dmitry
Clayton Arends


Posts: 25
Registered: 7/19/01
Re: Support PostgreSQL data type CITEXT  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 6, 2017 10:30 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Try to cast CITEXT column to TEXT, eg Col::TEXT

Thank you for the reply. Can you explain how a person would go about doing this? Code please.

Clayton
Clayton Arends


Posts: 25
Registered: 7/19/01
Re: Support PostgreSQL data type CITEXT  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 2, 2017 3:12 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Try to cast CITEXT column to TEXT, eg Col::TEXT

Recently, while looking at other problems related to PostgreSQL, I came across someone using the "::" casting operator in the SQL statement to cast a column's data type. Since I was unfamiliar with this nomenclature I had no idea that's what you were trying to tell me to do a month ago. Now that I've seen it in use I am able to try your suggestion.

Example:
select id, name::text from some_table where name = 'some_string'

This statement returns results regardless of the case of "some_string". This should work very well for statements that my application has complete control of. It will not work for "select *" statements or SQL that my customers are allowed to write. It is a step in the right direction though.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02