Re: Problems with question marks in operators (JDBC, ECPG, ...)

On Tue, May 19, 2015 at 9:51 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

>
>
> Actually the issue is what to do about a number of connectors which use a
> fairly standard '?' as a placeholder.
> Notably absent from the discussion is ODBC upon which JDBC was modelled
> and probably predates any use of ? as an operator
>
>
Ah, good point. I must admit I don't normally use ODBC, but I've given it a
try, and it doesn't look good regarding the question mark. Maybe I simply
don't know how it should be escaped, but my attempts (shown below) didn't
work.

This is run using PowerShell. Of all those tests, only the first one works
(it's not using the question mark, just to make sure something worked).
Interestingly, the question mark in the pseudo-column name ("Does it
work?") doesn't cause problems. (The errors are slightly different
depending on the attempt.)

Best wishes,

Bruno.

______ Output

******* Test query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ->>
?::text)::text AS "Does it work?"

Does it
work?

-------------

123

******* Test query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ?
?::text)::text AS "Does it work?"
Exception calling "Fill" with "1" argument(s): "ERROR [42601] ERROR: syntax
error at or near "$1";
Error while preparing parameters"

******* Test query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb \?
?::text)::text AS "Does it work?"
Exception calling "Fill" with "1" argument(s): "ERROR [42601] ERROR: syntax
error at or near "\";
Error while preparing parameters"

******* Test query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ??
?::text)::text AS "Does it work?"
Exception calling "Fill" with "1" argument(s): "ERROR [42601] ERROR: syntax
error at or near "$1";
Error while preparing parameters"

******* Test query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb {?}
?::text)::text AS "Does it work?"
Exception calling "Fill" with "1" argument(s): "ERROR [07002] The # of
binded parameters < the # of parameter markers"

******* Test query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb {'?'}
?::text)::text
Exception calling "Fill" with "1" argument(s): "ERROR [HY000] ODBC escape
convert error"

______ PowerShell script

function test_query($query) {
$conn = New-Object System.Data.Odbc.OdbcConnection
try {
$conn.ConnectionString = "DSN=PostgreSQL35W"
$conn.Open()
Write-Output "******* Test query: $query";
$cmd = New-Object System.Data.Odbc.OdbcCommand($query, $conn)
$cmd.Parameters.Add("key", "key1") | out-null
$ds = New-Object System.Data.DataSet
(New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) |
out-null
$ds.Tables[0] | Format-Table
} catch {
Write-Output $_.Exception
} finally {
if ($conn.State -eq 'Open' ) {
$conn.Close()
}
}
Write-Output ""
}

test_query(@'
SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ->> ?::text)::text AS
"Does it work?"
'@)

test_query(@'
SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ? ?::text)::text AS "Does
it work?"
'@)

test_query(@'
SELECT ('{"key1":123,"key2":"Hello"}'::jsonb \? ?::text)::text AS "Does
it work?"
'@)

test_query(@'
SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ?? ?::text)::text AS "Does
it work?"
'@)

test_query(@'
SELECT ('{"key1":123,"key2":"Hello"}'::jsonb {?} ?::text)::text AS
"Does it work?"
'@)

test_query(@'
SELECT ('{"key1":123,"key2":"Hello"}'::jsonb {'?'} ?::text)::text
'@)