Hi Michael,
Michael Bouschen wrote:
>Hi Tom,
>
>
>>Hi Michael,
>>
>> Some comments:
>>
>>- can we map today() to the same things as currentDate() and add a
>>currentTimeStampMethod()?
>>
>>
>Do you mean the methods in class Expression or the constants in
>ExpressionOperator? In the current code the Expression method
>currentDate() uses ExpressionOperator.Today and represents
>CURRENT_TIMESTAMP.
>
Lets just change the ExpressionOperator code. Since
ExpressionOperator.today() already exists, I am hesitant to remove it,
and I think that in order to change Expression, we would have to do just
that.
>Method currentDateDate() uses
>ExpressionOperator.currentDate and represents CURRENT_DATE.
>
>I agree it would be cleaner to have a method currentTimeStamp()
>representing CURRENT_TIMESTAMP, so I can add this. Do you also propose
>to change the implementation of currentDate() to represent CURRENT_DATE
>and then remove method currentDateDate()?
>
>
I am a little concerned about changing the way those methods work for
backwards compatibility reasons. My vote is to keep them as they are.
>
>
>>- In DateFunctionNode, there are some if statements without braces
>>around the contents. Our current coding convention requires the
>>braces. Could you please add them.
>>
>>
>OK.
>
>
>> I am working on running the tests on DB2. I will let you know the
>>results, but I think you can go ahead and check-in.
>>
>>
>>
>OK.
>
>
>> Are you adding a test?
>>
>>
>Yes, I will add test cases that use the different JPQL date functions in
>a bulk update.
>
>Thanks!
>
>Regards Michael
>
>
>>-Tom
>>
>>Michael Bouschen wrote:
>>
>>
>>
>>>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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>>
>>>
>>>
--
Tom Ware
Principal Software Engineer
Oracle Canada Inc.
Direct: (613) 783-4598
Email: tom.ware_at_oracle.com