Re: Server side prepared statements 'bit' parameters generate errors.

On 10/04/2016 07:35 AM, Johan Levin wrote:
> Hello
>
> I have run into a problem after upgrading from 9.03 to the 9.05.04 ODBC
> driver. Parameters of type 'bit' seem to be interpreted as 'char' in
> some cases when using server side prepared statements.
>
> If I execute the query
>     select * from some_table where ?;
> with a bit parameter then I get an error that reads:
>     ERROR [42804] ERROR: argument of WHERE must be type boolean, not
> type "char";
>
> The error is not reproducible is I set the connection string
> parameter UseServerSidePrepare=0. It also isn't reproducible in version
> 9.03 regardless of the value of UseServerSidePrepare. The problem seems
> to only affect bit parameters. A program that duplicates the problem
> follows at the end of this email. I am using the driver distributed in
> "psqlodbc_09_05_0400-x64.zip" on Windows 10. I have tested PostgreSQL
> version 9.4 and 9.5.
>
> Have I run into a bug in the driver, or is this a server issue? Any help
> would be much appreciated.

Got to believe it is related to:

https://odbc.postgresql.org/docs/release.html
"
psqlODBC 09.05.0100 Release

....

12. Send datatype information for query parameters, when known
If a query parameter is bound with a specific SQL type, pass on that
information to the server. This makes the behaviour of queries like
"SELECT '555' > ?" more sensible, where the result depends on whether
the query parameter is interpreted as an integer or a string.

....

"

>
> Regards,
> Johan Levin
>
>
>
>
> Program listing: (C#)
>
> using System.Data.Odbc;
> using System.Diagnostics;
> internal class Program
> {
>     private static void Main(string[] args)
>     {
>         // Command line: Server, User, Password, Database,
> UseServerSidePrepare
>         // Database schema and data:
>         //   create table some_table (id int, name varchar(20));
>         //   insert into some_table values (42, 'Arthur Dent');
>
>         Trace.Assert(args.Length == 5, "Wrong number of command line
> arguments.");
>         var connStr = string.Format("Driver={{PostgreSQL
> Unicode(x64)}};Server={0};Uid={1};Pwd={2};Database={3};UseServerSidePrepare={4}",
> args);
>         using (var connection = new OdbcConnection(connStr))
>         using (var command = connection.CreateCommand())
>         {
>             connection.Open();
>             command.CommandText = "select * from some_table where ?;";
>             command.Parameters.Add(new OdbcParameter
>             {
>                 OdbcType = OdbcType.Bit,
>                 Value = 1
>             });
>             command.ExecuteNonQuery();    // <-- Exception here if
> UseServerSidePrepare=1
>             // OdbcException.Message =
>             //   ERROR [42804] ERROR: argument of WHERE must be type
> boolean, not type "char";
>             //   Error while executing the query
>         }
>     }
> }
>


--
Adrian Klaver
adrian.klaver@aklaver.com