Java ResultSet and column database problem

[darietto]

Hi all of you!
I am new in this site and I hope any of you could help me.
This is my problem:
I have a mysql database NO INNODB.
There is a table which contains some columns and one of these is a Time type column.
To retrieve informations from that table in my java method, I have created a ResultSet rs in which I have the result of my query:

PreparedStatement stmtOrari = mysqlCon.prepareStatement("SELECT * FROM [tableName] where id="+id);
ResultSet rs = stmtOrari.executeQuery();

The problem is that in the database there are no restriction on column and data values, so in that Time column I found values like '51:50:24' and other greater than '23:59:59'.
If I try to retrieve values from the resultSet using rs.getTime("columnName"), the method crashes and launch an SqlException: "Illegal hour value '51' for java.sql.Time type in value '51:50:24."

My first reaction was to use the getString method on that field of the resultSet rs trying to convert the data type on a String object to solve the problem: String s = rs.getString("columnName");
but I had the same exception.

Any of you could help me to solve this problem?
Thanks!

[keang]

I'm not sure but I suspect most of the getXXX methods in the resultSet will use the getObject method to retrieve the data in the appropriate object type for the column type and then cast it to the method's return type or in the case of getString() call the object's toString() method. So I imagine you will also get the same exception if you try getObject().

However, there is a getBinaryStream() method which acording to the API docs "Retrieves the value of the designated column in the current row of this ResultSet object as a stream of uninterpreted bytes.". Because it is uninterpreted the illegal values won't cause you any problems other than it's now up to you to sort out which ones are valid times and do the conversions yourself.

This does beg the question of how did these illegal values get into the table in the first place.

[Londbrok]

In my humble opinion, you get a clean result storing the long value for the time. You can store this as a Unix time, (Seconds from epoch -> Java time / 1000), milliseconds, or even nanoseconds, depending on the granulation needed by your system.

Dealing with Time / DateTime etc. tends to be turbulent between database implementations, and storing a shared common value (such as unixtime) is a good allround solution. Also you do not need to do parsing to a Date, before the result needs to be shown.

Of course, when Long values are used, the table does not give any indication as to which columns are date values, and not just eg. some id values, which are often longs as well. All that said, I have personally liked working with long values preferable over using more database specific date values etc.