column type unknown after CREATE VIEW

Classic Servoy
1

I am creating a VIEW using MySQL. There is one column that appears to be created fine. If I use the MySQL Query Browser to look at the data in the backend in this view, it looks correct. This is a DATETIME column and I see all datetimes except for a few rows were the column is NULL.

The View statement unions data from two tables. For this particular column the source fields in both tables are DATETIME. This has worked fine with Servoy, but I needed to add a case statement so now it is possible that this view’s column could have data from a third field. This field in servoy is also a DATETIME. However, when I create this view servoy shows that the column type is . How could this be? Is there anyway to force this column to be a DATETIME after the fact? I even tried using a cast statement to cast all 3 possible sources as DATETIME in the CREATE VIEW statement and even that does not work. The type of column remains . In case it helps here is the snippet I used for each table:

cast(useage.date_used as DATETIME) AS effective_date

and after the UNION in the second table:

(case when (line_item.closed_flag = 1)
then cast(receipt.received as DATETIME)
else cast(job_order.plant_date as DATETIME)
end)
AS effective_date

The joins with tables receipt and job_order work as expected and useage is the source table for the first part

2

Some further insight. When I brake out the troublesome field, receipt.received into an additional column, that column is recognized by servoy as a DATETIME and the original column is again recognized as a DATETIME as it was before. It’s just when I try to include all 3 fields into the same column Servoy see’s it as and the data in the query browser looks like correct datetime data.

Very bizzare.

Also, in case it’s relevant this is Servoy 3.5.3

Thanks,
-Jeremy

3

Jeremy,

It is the mysql driver that does not return the expected type for the column with the case statement.
Servoy uses standard jdbc calls to the driver to determine the column type.

Rob

4

Thanks Rob,

That makes sense, but why would the MySQL driver not recognize it as DATETIME when I’m casting it as such? The MySQL back end data looks like valid date times, although I cannot view the data in Servoy.

Is there any way to rectify this, or is my only recourse to hack up my reporting methods using the receipt_date as a separate column where applicable?