Issue with parameter format codes
-
- Posts: 18
- Joined: Mon 18 Nov 2013 01:37
Issue with parameter format codes
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?
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
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.
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.
-
- Posts: 18
- Joined: Mon 18 Nov 2013 01:37
Re: Issue with parameter format codes
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:
Any other suggestions?
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);
Re: Issue with parameter format codes
We are investigating a possibility to implement the specified behavior now and will let you know the results.
Re: Issue with parameter format codes
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.
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.
-
- Posts: 18
- Joined: Mon 18 Nov 2013 01:37
Re: Issue with parameter format codes
I've submitted a request for the nightly build.
Thanks a lot for your help!
Thanks a lot for your help!
Re: Issue with parameter format codes
We answered to the email address specified in the request.
-
- Posts: 18
- Joined: Mon 18 Nov 2013 01:37
Re: Issue with parameter format codes
I've rolled out the nightly build, all seems to be working as advertised.
Thanks again!
Thanks again!