Need Help with Oracle Data Time Format

[dineshbabu179]

Thank you for the wonderful project! I recently came across it and decided to give it a try for my personal project.

However, I encountered an issue while testing the db2rest application with an Oracle database. When inserting records into a table, the date field seems to be causing a problem.

Here’s the request I used:

POST /db2rest/v1/rdbms/INT/EMPLOYEE  
Content-Type: application/json  
{
    "first_name": "foo",
    "last_name": "bar",
    "email": "Foo.bar@example.com",
    "hire_date": "31-DEC-2024" // This is the only working format 
}

I have tried various date along with time formats, but none seem to work. Could you please let me know the correct way to send date-time values to Oracle for INSERT operations?

Thanks and regards,
Dinesh

[kdhrubo]

@dineshbabu179 is this the only date format you are using or other like date time and time?

[dineshbabu179]

@kdhrubo

Yes, I am trying to save the date and time in ISO format YYYY-MM-DDTHH:mm:ss.SSS

example
2024-12-31T23:59:59Z for UTC
2024-12-31T23:59:59-06:00 with specifc time zone

Input

{
    "first_name": "Zita",
    "last_name": "Davis",
    "email": "Clint.Smitham@example.com",
    "created_at": "2024-12-31T23:59:59.000Z"  // or with 2024-12-31T23:59:59-06:00
}

"status": 400,
"detail": "Error insert - PreparedStatementCallback; bad SQL grammar [INSERT INTO XXINT.XXKG_POC_EMPLOYEE\n(first_name,last_name,email,created_at)\nVALUES\n(?,?,?,?)\n]",

[kdhrubo]

Checking.

[souravroy]

@dineshbabu179 I just used the same date time format and was able to insert a record. Please find attached screenshot.

[kdhrubo]

@dineshbabu179 - please review the update from @souravroy

Also which version of oracle are you using.
We will add few more tests in oracle to help on this.

[dineshbabu179]

@kdhrubo

Oracle DB Version 12.1.0.2.0
DB2REST Versuin kdhrubo/db2rest:v1.4.1

My Table Spec

POST /v1/rdbms/INT/XXKG_POC_EMPLOYEE

{
    "FIRST_NAME": "Herbert",
    "LAST_NAME": "Smith",
    "HIRE_DATE": "2024-12-31T23:59:59.000Z"
}

RESPONSE

{
    "type": "https://db2rest.com/error/generic-error",
    "title": "Generic Data Access Error",
    "status": 400,
    "detail": "Error insert - PreparedStatementCallback; SQL [\nINSERT INTO XXINT.XXKG_POC_EMPLOYEE\n    (FIRST_NAME,LAST_NAME,HIRE_DATE)\nVALUES\n    (?,?,?)\n]; ORA-01861: literal does not match format string\n",
    "instance": "/db2rest/v1/rdbms/INT/XXKG_POC_EMPLOYEE",
    "errorCategory": "Data-access-error",
    "timestamp": "2024-12-03T21:56:24.341105399Z"
}

[thadguidry]

@dineshbabu179 You would need to change your database column type to a TIMESTAMP(6) instead of DATE or you can change your frontend code to reduce your timestamp to only a date, before calling the DB2Rest API. You are asking Oracle to try to squeeze a timestamp format into only a date(without time). We don't do automatic date conversions.

@kdhrubo In our Oracle Dialect code, we do handle datatype with TIMESTAMP(6) which means up to 6 decimal precision for fractional seconds. But the real issue here is that of Oracle being supplied with a literal string in format of 2024-12-31T23:59:59.000Z and it tries to insert that into a DATE column, and of course fails.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlqr/Data-Types.html#GUID-219C338B-FE60-422A-B196-2F0A01CAD9A4

Although this is not our direct problem, it's a String format not matching the DB's column datatype format expected.
we could also improve our code directly covering the Oracle datatype DATE , although it is handled well in Spring Data... we don't seem to have the com.homihq.db2rest.jdbc.model.DbColumn model account for DATE types, but only TIMESTAMP through isDateTimeFamily(). We probably need to add another one for isDateFamily() (i.e. no timestamps, only dates) ? Still it would not help @dineshbabu179 who has a timestamp format and trying to insert it into a DATE datatype column, unless we did auto conversion when a Java timestamp is being asked to insert into a isDateFamily() we would do the Oracle TO_DATE(literal, format) down conversion?

But I detest automatic date conversions. I think that makes some folks very happy, and then others very unhappy when unexpected.

[kdhrubo]

Got hold of an Oracle 12c-xe image. Will triage over the weekend.

[souravroy]

@dineshbabu179 It is working for me with Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit and Oracle JDBC driver 21.9.0.0.0, attaching screenshots:

[kdhrubo]

@dineshbabu179 - its working per the triage by @souravroy

[dineshbabu179]

@kdhrubo thank you. I will check this out in my test env.