Escape ? for Postgres

Escape ? for Postgres

Postby 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
daveL
 
Posts: 2
Joined: Wed 14 Jun 2017 02:11

Re: Escape ? for Postgres

Postby 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/odbc/reference/develop-app/statement-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.
azyk
Devart Team
 
Posts: 962
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Escape ? for Postgres

Postby 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
daveL
 
Posts: 2
Joined: Wed 14 Jun 2017 02:11

Re: Escape ? for Postgres

Postby 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: 962
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A


Return to ODBC Drivers