Обсуждение: Server side prepared statements 'bit' parameters generate errors.
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.
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
}
}
}
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
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.klaver@aklaver.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.klaver@aklaver.com