BUG: DateTimeOffset command parameter value conversion.

[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?

[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.

[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.

[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.

[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.

[Pinturiccio]

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.

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]

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]

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