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

Thanks. Digging in to the code I found the connection
parameter BoolsAsChar. Setting that to "0" helps.

/Johan

On Tue, Oct 4, 2016 at 4:44 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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};UseSe
>> rverSidePrepare={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(dot)klaver(at)aklaver(dot)com
>