Receiving a "Can't write CLR type System.String with handler type TimestampHandler" when entering a date into the design time parameters of stand alone report designer for a PostgreSQL data source

[Ryan]

My environment

1. Telerik Report Designer, v16.2.22.914, Target Framework: NET 6.0
2. database: PostgreSQL
   
3. data provider: Npgsql version: 6.0.7
4. 1 database table named 'facilities' with columns:
   
   • billing_month: timestamp
   • facility_id: varchar(16)
5. 1 report parameter defined as
   1. name: BillingMonth
   2. type: DateTime
   3. Value: 6/1/2019
6. 1 data source defined as
   
   1. name: facilities
   2. sql: "select billing_month schema.facilities where billing_month = @BillingMonth"

7. 1 data source parameter defined as:

   1. name: @BillingMonth

   2. DbType: DateTime2

   3. value: = Parameters.BillingMonth.Value

8. 1 design time parameter defaulted to

   1. name: @BillingMonth

   2. Constant Value: 6/1/2019

The Issue

When I click "Execute Query" on the preview data source results screen in the configure SQL Data Source wizard am presented with an error such as: Can't write CLR type System.String with handler type TimestampTzHandler or Can't write CLR type System.String with handler type TimestampHandler.

If I dismiss the error and hit "Finish" Data explorer will not show the data fields but instead displays the same error with a rather long stack trace.

Interestingly enough, the report may still work, but design time assistance is unavailable. For example I cannot drag data source fields to the report. To validate I created another data source for the list of unique billing months, assigned that to "AvailableValues" of the BillingMonth report parameter, and set Visible to True. I can then select from an appropriate month in the report Preview and run the report.

My Question

What is the appropriate format to specify a DateTime constant value in the design time parameters window of the Configure SQL Data Source wizard?

Previously, when we were targeting oracle specifying M/D/YYYY worked fine but as we're retargeting PostgreSQL we find ourselves with this mystery.

 

 

 

[Dimitar]

Hello Ryan,

Thank you for the provided information on the scenario, it helped me reproduce the problem!

It seems that the Npgsql data provider of version 6.0.7 is unable to parse the date and thus perform the filter. The Design-Time value is only a string, expressions cannot be used there so there isn't really a way to send a DateTime object as a Design-Time value for the parameter.

With that being said, I tested an older version of the Npgsql provider -  version 3.2.7, and it was able to successfully parse the date string set at design time so the issue is with the new data provider.

I am not sure exactly in which version is the behavior changed but I managed to find this GitHub thread from 2019 - Can't write CLR type System.DateTimeOffset with handler type TimestampHandler · Issue #22 · npgsql/doc (github.com). It indicates that the behavior was changed in that year so it makes sense that it works with an older version such as 3.2.7.

My recommendation is to install the 3.2.7 version since it comes with an MSI and to use that for designing the report in the report designer. If the report is displayed in a report viewer, that project can still use the new version of Npgsql since the issue is only at design time.

To install the 3.2.7 version, please have a look at the How to Configure Postgres with Npgsql? - Telerik Reporting article.

Please let me know if you have any further questions.

Regards,
Dimitar
Progress Telerik

[Ryan]

Hello Dimitar,

Thank you for your response, however, I think this is not an ideal solution for a couple reasons.

1. Our production application will be an ASP.NET 6 application and will be using the .NET 6 PostgreSQL provider. We would therefore like to use the same provider at design time as we do at runtime as there are documented differences in the provider between Framework and Core versions.
2. The Telerik Reporting Standalone Designer for .NET has a much simpler provider install process than Telerik Reporting (for Framework). We would like to continue using that for the simplicity of adding the PostgreSQL provider. I believe this would require the 6+ PostgreSQL provider to function, is this correct?

As for the string to datetime conversion issue, that seems like it could be on the PostgreSQL provider.

As for the Designer's Design-Time value only supporting strings, that seems like a UX design flaw in Telerik Reporting. If the report parameters themselves can accept different types (numbers, strings, dates) then the design time view should align with that for the best user experience.

If I make the report parameter, a DateTime visible, and bind it to a data source returning valid DateTimes, I can choose a DateTime when I preview the report. The functionality is almost there, we just need it at Design Time.

I would like to make this a Feature Request. Design Time should support the multitude of types allowed by the Report Parameters themselves.

V/r

Ryan

[Dimitar]

Hello Ryan,

Thank you for the feedback!

I have consulted with the development team and we have confirmed that the problem is caused because the design-time value does not respect the selected DbType on the previous step and is instead always of type string.

This is a fault on our part and as a reward for discovering and reporting this issue to us, I have updated your account's Telerik points.

I have also logged the issue on your behalf so that other users that experience it may also vote for it - The Design-Time parameter value does not respect the previously selected parameter type (telerik.com).

Thank you for using Telerik Reporting!

Regards,
Dimitar
Progress Telerik

[Ryan]

Hi Dimitar,

Thank you for following up on this with the development team. I see that the bug is currently unplanned. Is there an established policy for escalating a fix for certain bugs? Unfortunately, this bug is requiring us to to rewrite our reports. We now have to pass in a date as a string, rather than an actual date. This adds additional conversions within the report, in the sql, anytime we do date arithmetic, and in for date formatting.

V/r

Ryan

[Dimitar]

Hi Ryan,

Indeed, the current status of the item has been set to Unplanned which means that the item has been approved but has no scheduled release date for the fix/implementation yet.

With that being said, I have again contacted the development team about this case and you can expect a follow-up from one of the developers later today or tomorrow at the latest.

Thank you for using Telerik Reporting!