Re: SQL_CURSOR_TYPE prepare execute issue

On 01/15/2015 02:22 PM, Faith, Jeremy wrote:
> As long as you are confident that the test suite is checking this code path that is fine, I just wanted to be sure that was happening.

Sure, thanks!

> I hadn't thought of
> select x-?
> You are right that is a problem.
> What does the server prepare code path do?
> I did a quick check, sever side prepare, with the log turned on and it shows the following
> LOG: execute <unnamed>: select 1-$1
> DETAIL: parameters: $1 = '-1'
> LOG: execute <unnamed>: select 1-$1
> DETAIL: parameters: $1 = '1'
> But if the bound string is '-1' then I get
> ERROR: invalid input syntax for integer: "'-1'" i.e. double quote,single quote,-,1,single quote,double quote
> STATEMENT: select 'wibble',1-$1

Huh. I cannot reproduce that. Can you modify the
test/src/param-conversions-test.c test case to demonstrate it?

> Also get invalid syntax error if it is just a minus sign.
>
> ok, server side prepare log when string is just 6 shows
> LOG: execute <unnamed>: select '555'>$1
> DETAIL: parameters: $1 = '6'
> and this return one row with a value of 0
> so this is doing select '555'>'6';
>
> So to be consistent with what the server side prepare does, I think it should quote the value.
> You should check this as I may have been getting confused.
> I realise this may have some implications for existing programs but given that sometimes it will be server side prepare and sometimes client side it is likely to cause more issues if the results are different.

Ugh, I didn't realize that happens with UseServerSidePrepare=1. But
looking at the code, it's quite obvious that it does; we never send the
datatypes with server-side parameters, so the server always treats them
as "unknown", that is, the same as a plain string literal. That usually
works fine, as the server deduces the right datatype from the context,
but for ambiguous cases like above, it goes wrong.

I think it's pretty clear that the UseServerSidePrepare=1 behaviour is
wrong in this case. In particular, if you bind a parameter as
SQL_INTEGER, and run query "SELECT '555' > ?", the argument should be
treated as an integer and the answer should be "true".

I propose the attached two patches. The first one fixes the "-" case and
negative integers, with UseServerSidePrepare=0. The second one changes
the UseServerSidePrepare=1 behaviour so that a datatype is sent along
with each parameter. String-like datatypes, SQL_CHAR, SQL_WCHAR,
SQL_VARCHAR etc. are still sent as "unknown". It makes sense for string
types IMHO.

Thoughts?
- Heikki
h