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

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
'@)