Escape ? for Postgres

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
daveL
Posts: 2
Joined: Wed 14 Jun 2017 02:11

Escape ? for Postgres

Post by daveL » Wed 14 Jun 2017 02:22

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,
Dave

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Escape ? for Postgres

Post by azyk » Thu 15 Jun 2017 08:19

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:
https://docs.microsoft.com/en-us/sql/od ... 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.

daveL
Posts: 2
Joined: Wed 14 Jun 2017 02:11

Re: Escape ? for Postgres

Post by daveL » Thu 15 Jun 2017 16:41

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?

-Dave

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Escape ? for Postgres

Post by azyk » Fri 16 Jun 2017 10:15

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.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Escape ? for Postgres

Post by azyk » Tue 30 Jan 2018 13:27

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.

Post Reply