Issue with parameter format codes

[nickbarnes]

I'm trying to modify PgDAC 4.6.21 so that it maps JSONB fields to ftMemo, even when "UnknownAsString" is enabled.

I seem to have this mapping working, but now I'm encountering errors when binding JSONB parameters; PgDAC is sending a binary format code to the server, but then sending the parameter value as text. This seems to stem from TPgSQLCommand.GetFormatCode(), which returns a text format code for all other text types (dtUnknown, dtString, dtPgName, dtPgChar), but returns a binary code for dtMemo.

I found it odd that there's a distinction between dtMemo and dtString, since Postgres represents TEXT and VARCHAR(N) values identically. Is this deliberate, or is this difference an oversight?

[azyk]

In the latest version, PgDAC 5.1.3 maps the JSONBtype type to ftMemo. Please upgrade PgDAC to the latest version.

In PostgreSQL data types VARCHAR(N) and TEXT differ. More details about this in PostgreSQL documentation: https://www.postgresql.org/docs/current ... acter.html . Therefore, PgDAC maps them to dtString and dtMemo correspondingly and this is correct behavior.

[nickbarnes]

Thanks azyk, we renewed our PgDAC licence, but unfortunately 5.1.3 behaves the same as 4.6.21 in this regard.

It looks like JSONB is considered an "unknown" type by PgDAC, and so depending on the UnknownAsString setting, it is mapped to either ftMemo or ftString. We need UnknownAsString enabled for historical reasons, but we also need for JSONB fields to map to ftMemo.

Here is a minimal example which I've been trying to get working:

Code: Select all

  Query := TPgQuery.Create(Connection);
  Query.Connection := Connection;
  Query.Options.UnknownAsString := True;
  Query.SQL.Text := 'SELECT CAST(:Foo AS JSONB) Bar';
  Query.ParamByName('Foo').AsString := '{}';
  Query.Open;
  Assert(Query.FieldByName('Bar').DataType = ftMemo);

Any other suggestions?

[azyk]

We are investigating a possibility to implement the specified behavior now and will let you know the results.

[azyk]

We implemented mapping of JSONB type as ftMemo when UnknownAsString=True. This behavior will be included in the next PgDAC build.

If you want to get it before the official release, use the contact form at our site: http://www.devart.com/company/contactform.html and provide us with PgDAC license number, as well as the email address where the night build can be sent to.

[nickbarnes]

I've submitted a request for the nightly build.

Thanks a lot for your help!

[azyk]

We answered to the email address specified in the request.

[nickbarnes]

I've rolled out the nightly build, all seems to be working as advertised.

Thanks again!