can't set a time field in DMI

[claudiobosticco]

SmartClient Version: SNAPSHOT_v13.1d_2024-11-05/Enterprise Deployment (built 2024-11-05)

Hello, I'v e got a SQLDataSource field declared as type="time", and I need to set its value for an add operation in a DMI. I've tried using java.time.LocalTime and java.sql.Time, but I get an error ORA-01861 because in the insert I see:

TO_DATE('2024-08-07 08:00:00','HH24:MI:SS')

database is Oracle 19

[Isomorphic]

If you just use a java.util.Date, the date portion will be ignored, and only the time used (in the server's timezone).

However, we'll check on those other time types - we'd like it to "just work".

[claudiobosticco]

In practice it's what I was already doing, I mean that I have in my dataSource:

<field name="START_TIME" type="datetime"/>
<field name="START_DATE" type="datetime" includeFrom="..."/>

and in my DMI I was simply doing

values.put("START_TIME", record.get("START_DATE"));
addRequest.setValues(values);

and then I was using a TImeItem on the client.

But then I tried to use time in the DataSourceField START_TIME, and in the INSERT I see:

TO_DATE('2024-08-07 08:00:00','HH24:MI:SS')

which raises the ORA-01861 error

Am I missing something obvious?

[Isomorphic]

What's the flow here? Does the DMI code you show form part of an update request before the database operation, so START_DATE comes up from the client and you are using it to populate START_TIME? Or is it part of a fetch request after the database operation, so START_DATE comes from the database and START_TIME is just manually populated from it, and the troublesome SQL is caused by a save of a DynamicForm containing a TimeItem?

[claudiobosticco]

It's entirely server side, I mean "record" (where START_DATE comes from) is from a dsRequest fetch constructed server side, and then it's used in the values of an add constructed again server side.

[Isomorphic]

Your DataSource snippet shows START_TIME declared as a "datetime", but earlier you state that it is a "time". Which of these is correct?

[claudiobosticco]

As I mentioned in post #3, I initially started with a datetime field and a TimeItem on the client side. Then, I decided to try setting the START_TIME field to type="time".

However, with type="time", I noticed that the TO_DATE function uses the 'HH24:MI' format. The issue is that I can't seem to find a way to set a valid value in my Java code, as it always ends up (in the TO_DATE function) being a datetime value in the yyyy-MM-dd HH:mm:ss format.

[Isomorphic]

We had difficulty reproducing this issue in either in our regular development environment or by directly using the same built package you are using. It turns out this is because the issue only arises when you use a different dbName to our default "Oracle". The underlying problem is a typo in our internal "framework.properties" file - a usage of "Oracle" where it should be "oracle" (note, all lower-case).

This issue is now fixed, so you can pick that up in tomorrow's builds. Alternatively, you can fix it in the build you already have by adding one of the following lines to your "server.properties" file:

# Either set the property at dbType level - ie, "oracle" rather than "Oracle"
sql.oracle.defaultTimeFormat: HH:mm:ss

# Or set it at dbName level, using whatever dbName you have chosen
sql.MyCustomDbName.defaultTimeFormat: HH:mm:ss

[claudiobosticco]

SmartClient Version: v13.1p_2024-11-14/Enterprise Deployment (built 2024-11-14)

Hello, now if I set the date like this:

values.put("START_TIME", record.get("START_DATE"));

the INSERT statement shows:

TO_DATE('13:00:00','HH24:MI:SS')

which appears correct. In the database column, however, I see 2024-11-01 13:00:00.000000. According to ChatGPT, this happens because Oracle adds a default date when only the time part is provided. I think I can live with that.

However, if I try to set a java.time.LocalTime, I encounter a validation error:

Validation error: [
    {
        START_TIME:{
            severity:"ERROR",
            errorMessage:"Must be a time."
        }
    }
]

Is this behavior expected?

[Isomorphic]

There are a number of things here. As our original response suggested, the value is expected to be an instance of java.util.Date. Our initial investigation centered around fixing the main issue - you weren't able to insert a time value at all. We do intend to add support for other Java time types, but that has not been done yet.

As far as Oracle adding its own date information in, unfortunately this is just the way Oracle behaves and nothing can be done about it if you want to use a native Oracle date column. SmartClient should ensure that only the time portion is returned to your application, so from that point of view it is just noise that you have to ignore. SmartClient also supports storing dates, times and datetimes in text or numeric columns = see https://smartclient.com/smartclient-...torageStrategy

[claudiobosticco]

ok, that's not a problem for me, as I said I can use it as is, let me know if you add support for types other than java.util.Date, so I can try it.
Thanks for now