Page 1 of 1

Escape ? for Postgres

Posted: Wed 14 Jun 2017 02:22
by daveL
I'm using the Devart ODBC driver to link a Postgres 9.6.2 server to a MS SQL 2012 server.

I've got my linked servers set up and I can query the Postgres dB from MS SQL.

When I try to execute a query on the Postgres server from SQL by issuing the following command:

Code: Select all

EXECUTE ('select * from user_json where data ? ''{}'';') AT [PGTEST-DA]
I get the following error:
OLE DB provider "MSDASQL" for linked server "PGTEST-DA" returned message "[Devart][ODBC]COUNT field incorrect".

In this case, it's the presence of the ? (question mark) that appears to be causing the driver to think that I am passing a parameter. I am not. This is an operator in PGSQL for working with JSONB.
I've tried escaping the parameter (Using \\?, \? and ??), but nothing works.
I'm having the same problem with the PostgreSQL ODBC driver as well and I've seen some articles of other people having this problem. This is why I investigated using the Devart driver.

Thank you,

Re: Escape ? for Postgres

Posted: Thu 15 Jun 2017 08:19
by azyk
In ODBC specification it is stated that question mark (?) is used to include a parameter in SQL query. Therefore, our ODBC driver interprets the question mark (?) specified in your SQL query not as a PostgreSQL JSONB operator, but as a parameter - this behavior is correct.

For more information about this, see the MSDN article: ... parameters

We will investigate the possibility of implementing PostgreSQL JSONB operators passing using the escape sequence in Devart ODBC driver and will inform about the results.

Re: Escape ? for Postgres

Posted: Thu 15 Jun 2017 16:41
by daveL
Thank you for your reply, azyk.
I understand the '?' functionality as a parameter is correct, but a '?' is also used in PGSQL as a JSON operator. Is there no way to issue JSON operators through an ODBC driver? What about LIKE statements using a '?' or even embedded '?' in strings?


Re: Escape ? for Postgres

Posted: Fri 16 Jun 2017 10:15
by azyk
ODBC specification strictly regulates the rules for using question marks (?) in statements, therefore, our ODBC driver must interpret them in this way, despite the specifics of using question marks in PostgreSQL. As we have already mentioned, currently we are investigating the possibility of using question marks as an operator when working with JSON.

As for the use of question marks within strings (as well as an argument of the LIKE statement), this usage is quite acceptable, since in that case ? can not be interpreted as a parameter marker.

Re: Escape ? for Postgres

Posted: Tue 30 Jan 2018 13:27
by azyk
We supported the possibility of passing JSON operators using the escape sequence. This functionality will be included in the next build of Devart ODBC Driver for PostgreSQL.

When using operators ?, ?| and ?& the question mark needs to be escaped \?, \?| and \?& correspondingly. It is not necessary for other operators.