Re: Date problem more info
Gabriel Wong
Could you please provide the code snippet of the CREATE, INSERT, and
SELECT
of the DATE issue.
Regards
Nigel Maddocks wrote:
> Some further info:
>
> For the date field having the value 2001-01-25, the error breaks as follows:
> 2001-01-25 Invalid DateTime Format. Should be like yyyy-MM-dd HH:mm:ss
> Thu Mar 04 14:52:07 GMT 2010[ERROR]: LAST_CHANGED ERROR Row.get 2001-01-25 2001-01-25 Invalid DateTime Format. Should be like yyyy-MM-dd HH:mm:ss
> 2001-01-25 Invalid DateTime Format. Should be like yyyy-MM-dd HH:mm:ss
>
> If I add a time component, e.g. 2001-01-26 10:11:12, then I get the following:
> 2001-01-26 Invalid DateTime Format. Should be like yyyy-MM-dd HH:mm:ss
>
> The field is a DATE field and might not support the time component, which is why I didn't have it at first. It was only the error message saying "Should be like yyyy-MM-dd HH:mm:ss" that prompted me to add a time component.
>
> Cheers
>
> NM
>
Gabe Wong
At this point the DATE TIME format supported by jiql are:
EEE MMM dd HH:mm:ss z yyyy
yyyy/MM/dd
Please also elaborate what you mean by
(above needs to be on one line - else doesn't execute)
Is that in your java code or jiqladmin?
Also I would appreciate if you post any followup to
the jiql group at:
http://groups.google.com/group/jiql
So instead of 2001-01-25 use 2001/01/25
On Thu, Mar 4, 2010 at 7:55 AM, Nigel Maddocks <nig...@rocketmail.com> wrote:
> Hi Gabriel,
>
> Here is some code I've just executed:
> CREATE TABLE TESTABLE2 (
> ID INT NOT NULL,
> DESCRIPTION VARCHAR(256) NOT NULL,
> LAST_CHANGED DATE NOT NULL
> )
>
> INSERT INTO TESTABLE2 (ID, DESCRIPTION, LAST_CHANGED)
> VALUES(1, 'BICYCLE', '2001-01-25')
>
> SELECT ID, DESCRIPTION, LAST_CHANGED FROM TESTABLE2
> (above needs to be on one line - else doesn't execute)
>
> ID and DESCRIPTION are returned correctly, but resultset.getDate, or
> resultset.getString return null for LAST_CHANGED.
>
> SELECT * FROM TESTABLE2 WHERE LAST_CHANGED='2001-01-25'
> returns 1 record
>
> SELECT * FROM TESTABLE2 WHERE LAST_CHANGED='2001-01-26'
> returns 0 records
>
> This implies that LAST_CHANGED does contain a value of some kind that is
> something like 25 Jan 2001
>
> I have tried other ways to see my date data, but nothing has worked for me
> yet.
> It could be that the INSERT statement is incorrect, however I do not know
> any alternative format. So if I am inserting the date data incorrectly,
> could you direct me to the correct format.
>
> Thanks
>
> Nigel
>
> ________________________________
> From: Gabriel Wong <appc...@gmail.com>
> To: jiql <ji...@googlegroups.com>
> Cc: nig...@rocketmail.com
> Sent: Thu, March 4, 2010 3:27:56 PM
> Subject: Re: Date problem more info
NM
I have now tried the two formats.
2001-01-25
inserted via:
INSERT INTO TESTABLE2 (ID, DESCRIPTION, LAST_CHANGED)
VALUES (1, 'One', '2001-01-25')
This outputs null from rs.getString():
No exception thrown outside of jiql jar, but following written to
System.out:
2001-01-25 Invalid DateTime Format. Should be like yyyy-MM-dd HH:mm:ss
Fri Mar 05 18:59:56 GMT 2010[ERROR]: LAST_CHANGED ERROR Row.get
2001-01-25 2001-01-25 Invalid DateTime Format. Should be like yyyy-MM-
dd HH:mm:ss
2001-01-25 Invalid DateTime Format. Should be like yyyy-MM-dd HH:mm:ss
at org.jiql.util.jiqlException.get(jiqlException.java:47)
at org.jiql.util.jiqlException.get(jiqlException.java:55)
at org.jiql.db.objs.jiqlCellValue.getObj(jiqlCellValue.java:107)
at org.jiql.db.Row.get(Row.java:177)
at org.jiql.db.Row.getString(Row.java:188)
at org.jiql.jdbc.ResultSet.getString(ResultSet.java:1050)
at org.jiql.jdbc.ResultSet.getString(ResultSet.java:1005)
SELECT ID FROM TESTABLE2 WHERE LAST_CHANGED='2001-01-25'
returns 1 record
SELECT ID FROM TESTABLE2 WHERE LAST_CHANGED='2001/01/25'
reports:
java.lang.IllegalArgumentException: LAST_CHANGED: java.sql.Date is not
a supported property type.
--------------------------------------------------------------------------------------------------------------
2001/01/26 - getString throws an exception with a similar message -
see below:
inserted via:
INSERT INTO TESTABLE2 (ID, DESCRIPTION, LAST_CHANGED)
VALUES (2, 'Two', '2001/01/26')
retrieved via rs.getString():
jiqlException thrown
2001-01-26 Invalid DateTime Format. Should be like yyyy-MM-dd HH:mm:ss
at org.jiql.util.jiqlException.get(jiqlException.java:47)
at org.jiql.util.jiqlException.get(jiqlException.java:55)
at org.jiql.db.objs.jiqlCellValue.getObj(jiqlCellValue.java:107)
at org.jiql.jdbc.ResultSet.getString(ResultSet.java:1062)
at org.jiql.jdbc.ResultSet.getString(ResultSet.java:1005)
(Note that the exception message reports 2001-01-26, but it was
inserted as 2001/01/26)
SELECT ID FROM TESTABLE2 WHERE LAST_CHANGED='2001-01-26'
returns 0 recs
SELECT ID FROM TESTABLE2 WHERE LAST_CHANGED='2001/01/26'
reports:
java.lang.IllegalArgumentException: LAST_CHANGED: java.sql.Date is not
a supported property type.
My conclusion is that 2001-01-25 is the correct format, but that
getDate and getString are not working.
Using the format 2001/01/26 will not match record (with ID as 2) using
either date comparison format and it throws a more serious exception.
In both cases, there is some data in the LAST_CHANGED column. For
ID=1, I believe a correct date is being stored. For ID=2, something
is being stored but it doesn't appear to be a date. I find this quite
odd because in a traditional database, a proper DATE-typed column
would be used. For BigTable it seems to me that LAST_CHANGED is not
really a DATE column, but something else - maybe a character column
being interpreted as a date.
Have you any light to throw on this? I'm only doing a feasibility
study at the moment by trying out the different data-types that I
might need - but I do need DATE.
Thanks
Nigel
Gabriel Wong
Apologies for the somewhat late response.
The select was a bug now fixed with the latest download:
http://jiql.googlecode.com/files/jiql.jar
NOTE you can now use both yyyy/MM/dd or yyyy-MM-dd
Regards
NM
Confirmed that this fixes the problem I reported.
Many thanks
Nigel