Page 1 of 1
Issue with parameter format codes
Posted: Fri 13 Oct 2017 04:01
by 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?
Re: Issue with parameter format codes
Posted: Wed 18 Oct 2017 12:53
by 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.
Re: Issue with parameter format codes
Posted: Mon 23 Oct 2017 02:49
by 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?
Re: Issue with parameter format codes
Posted: Wed 25 Oct 2017 10:06
by azyk
We are investigating a possibility to implement the specified behavior now and will let you know the results.
Re: Issue with parameter format codes
Posted: Fri 27 Oct 2017 14:22
by 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.
Re: Issue with parameter format codes
Posted: Tue 31 Oct 2017 01:13
by nickbarnes
I've submitted a request for the nightly build.
Thanks a lot for your help!
Re: Issue with parameter format codes
Posted: Tue 31 Oct 2017 10:41
by azyk
We answered to the email address specified in the request.
Re: Issue with parameter format codes
Posted: Fri 03 Nov 2017 04:32
by nickbarnes
I've rolled out the nightly build, all seems to be working as advertised.
Thanks again!