BUG: DateTimeOffset command parameter value conversion.

BUG: DateTimeOffset command parameter value conversion.

Postby bpipe » Mon 14 Jan 2013 12:42

I have PostgreSql table with Date column type, I'm executing a prepared INSERT command and set value for this column to DateTime C# object, with command's DbType.DateTime, this throws exception
incorrect binary data format in bind parameter


Changing database column type to "TimeStamp without timezone" fixes the problem but I don't need TimeStamp column, I need Date column.

What is the C# type for Date column and DBType value for command parameter?
Last edited by bpipe on Fri 01 Feb 2013 10:56, edited 1 time in total.
bpipe
 
Posts: 19
Joined: Mon 08 Oct 2012 12:14

Re: Date column in BD, with DateTime parameter doesn't work?

Postby Pinturiccio » Thu 17 Jan 2013 12:49

When you set the DbType property value equal to DbType.DateTime for a parameter, then for another PgSqlType property the PgSqlType.TimeStamp value is assigned automatically, which corresponds to the timestamp data type of PostgreSQL server.

Try replacing DbType.DateTime with DbType.Date.
Pinturiccio
Devart Team
 
Posts: 2026
Joined: Wed 02 Nov 2011 09:44

Re: Date column in BD, with DateTime parameter doesn't work?

Postby bpipe » Fri 01 Feb 2013 10:47

It might be a bug In PgSqlParameter.
First the DbTipe.DateTimeOffset is converter to PgSqlType.TimeStampTZ which is correct.
Second I get the exception:
"Cannot convert parameter value of type 'System.DateTimeOffset' to PostgreSQL type 'PgSqlType.TimeStampTZ'."
at Devart.Data.PostgreSql.PgSqlParameter.b()
at Devart.Data.PostgreSql.PgSqlParameter.get_PgSqlValue()

I'm investigation the problem now. I think, I can workaround this problem by using parameter with String type, and doing the conversion of DateTimeOffset to string by myself.
I was unable to workaround this problem this way. I got server side error incorrect binary parameter type binding.
Last edited by bpipe on Fri 01 Feb 2013 12:08, edited 2 times in total.
bpipe
 
Posts: 19
Joined: Mon 08 Oct 2012 12:14

Re: BUG: DateTimeOffset command parameter value conversion.

Postby bpipe » Fri 01 Feb 2013 11:21

This is a bug in implementation of PgSqlParameter.
Your conversion method of DateTimeOffset to string is failing. I think it's due to Cultural Settings. I can reproduce the problem on a PC using Russian cultural settings.

Looks like it's not due to cultural settings I have tried setting
Code: Select all
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
            Thread.CurrentThread.CurrentUICulture = CultureInfo.InvariantCulture;


Still failed.
bpipe
 
Posts: 19
Joined: Mon 08 Oct 2012 12:14

Re: BUG: DateTimeOffset command parameter value conversion.

Postby bpipe » Fri 01 Feb 2013 13:01

I have understood what is happening.
First when client connect it send SET dateStyle = ISO (i'n my case it's ISO) and postgres expects datetime's in this format from client.
Second when command.Prepare() is executed Devart driver send command to postgres and correct command parameters type according to postgres response.
Third Devart driver PgSqlTimeStamp class uses incorrect string formatting.
I was able to make DateTimeOffset work using this format (ISO 8601)
DateTimeOffset.Now.ToString("yyyy-MM-dd HH:mm:sszz")

I hope you could fix the problem with this info.
bpipe
 
Posts: 19
Joined: Mon 08 Oct 2012 12:14

Re: BUG: DateTimeOffset command parameter value conversion.

Postby Pinturiccio » Tue 05 Feb 2013 15:21

bpipe wrote:It might be a bug In PgSqlParameter.
First the DbTipe.DateTimeOffset is converter to PgSqlType.TimeStampTZ which is correct.
Second I get the exception:
"Cannot convert parameter value of type 'System.DateTimeOffset' to PostgreSQL type 'PgSqlType.TimeStampTZ'."

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

bpipe wrote:Devart driver PgSqlTimeStamp class uses incorrect string formatting.
I was able to make DateTimeOffset work using this format (ISO 8601)
DateTimeOffset.Now.ToString("yyyy-MM-dd HH:mm:sszz")

If you want to pass parameters as strings, you have to control them to have the right server format. The ToString method of both PgSqlTimeStamp and DateTimeOffset depends on the local settings and doesn't always match the valid formats for the server. To get the string in the format expected by the server, you have to use the ToString(string format) method overload. For example:
Code: Select all
ToString("yyyy-MM-dd HH24:MI:SS.USTZH:TZM BC") for timestamp with timezone
ToString("yyyy-MM-dd HH24:MI:SS.US BC") for timestamp without timezone
ToString("yyyy-MM-dd AD") for date
Pinturiccio
Devart Team
 
Posts: 2026
Joined: Wed 02 Nov 2011 09:44

Re: BUG: DateTimeOffset command parameter value conversion.

Postby Pinturiccio » Wed 06 Feb 2013 14:33

We have fixed the bug with using parameter value of the System.DateTimeOffset type. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.
Pinturiccio
Devart Team
 
Posts: 2026
Joined: Wed 02 Nov 2011 09:44

Re: BUG: DateTimeOffset command parameter value conversion.

Postby Pinturiccio » Fri 08 Feb 2013 09:23

The new build of dotConnect for PostgreSQL 6.4.179 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=25831
Pinturiccio
Devart Team
 
Posts: 2026
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL