Page 1 of 1

BUG: DateTimeOffset command parameter value conversion.

Posted: Mon 14 Jan 2013 12:42
by bpipe
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?

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

Posted: Thu 17 Jan 2013 12:49
by Pinturiccio
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.

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

Posted: Fri 01 Feb 2013 10:47
by bpipe
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.

Re: BUG: DateTimeOffset command parameter value conversion.

Posted: Fri 01 Feb 2013 11:21
by bpipe
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.

Re: BUG: DateTimeOffset command parameter value conversion.

Posted: Fri 01 Feb 2013 13:01
by bpipe
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.

Re: BUG: DateTimeOffset command parameter value conversion.

Posted: Tue 05 Feb 2013 15:21
by Pinturiccio
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

Re: BUG: DateTimeOffset command parameter value conversion.

Posted: Wed 06 Feb 2013 14:33
by Pinturiccio
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.

Re: BUG: DateTimeOffset command parameter value conversion.

Posted: Fri 08 Feb 2013 09:23
by Pinturiccio
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/postgr ... nload.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