Hi Tom,
unfortunately derby does not accept assigning a timestamp to a time
column. When using the currentDate Expression for CURRENT_TIME my test
runs into similar exception as Mitesh mentioned below:
SqlException: Columns of type 'TIME' cannot hold values of type
'TIMESTAMP'. Error Code: -1
Call:UPDATE EMPLOYEE_TBL SET TESTTIME = CURRENT TIMESTAMP
I propose to introduce a new method currentTime to the Expression class
and use this for the JPQL date function CURRENT_TIME. Class
ExpressionOperator implements the default SQL for the date/time
ExpressionOperator methods today(), currentDate() and currentTime(). I
propose to change this default to the SQL 92 date functions
CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP. I adapted some of the
DatabasePlatform subclasses accordingly, but I was not able to do this
for all the platforms. It would be great if you could take a look.
I was able to successfully run a modified version of Mitesh's test case
running all the date functions (CURRENT_DATE, CURRENT_TIME and
CURRENT_TIMESTAMP) for the databases: Derby, Oracle 9, SQL Server 2000
and MySQL 5.
You find my changes attached, thanks!
Regards Michael
> Hi Michael,
>
> I think currentDate() should work for CURRENT_TIME.
>
> -Tom
>
> Michael Bouschen wrote:
>
>> Hi Mitesh, hi Tom,
>>
>> good catch!
>>
>> I'll take a look at changing the query compiler. What is the correct
>> mapping for the JPQL function CURRENT_TIME? Can I use currentDate()
>> since currentDateDate() does not include the time portion?
>>
>> Mitesh, could you do a me favor and file an issue for this? Please
>> assign it to me.
>>
>> Regards Michael
>>
>>
>>
>>> Hi Mitesh,
>>>
>>> You are correct. To get the current timestamp, currentDate() should
>>> be used.
>>>
>>> -Tom
>>>
>>> Mitesh Meswani wrote:
>>>
>>>
>>>> Hi Michael,
>>>> The current code for query compiler maps all of these functions to
>>>> Expression#currentDateDate(). Some databases(for example derby)
>>>> distinguish between a TIMESTAMP type and DATE type.
>>>> For example the attached program which tries to update a TIMESTAMP
>>>> field on derby using "Update Employee e set e.testTimeStamp =
>>>> CURRENT_TIMESTAMP" generates sql "UPDATE EMPLOYEE_TBL SET
>>>> TESTTIMESTAMP = CURRENT DATE" which is not accepted by Derby. It is
>>>> obvious that we will need to map this functions to different
>>>> expressions
>>>>
>>>> Tom,
>>>> From javadocs in Expression.java, it seems that CURRENT_DATE should
>>>> be mapped to Expression#currentDateDate()
>>>> (ExpressionOperator.currentDate) and CURRENT_TIMESTAMP should be
>>>> mapped to Expression#currentDate() (ExpressionOperator.Today) . Is
>>>> that correct?
>>>>
>>>> Thanks,
>>>> Mitesh
>>>>
>>>>
>>>>
>>>>
>>
>>
>>
>