getPrecision() on TIME and TIMESTAMP is zero

• Key: DERBY-194
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: A B
• Reporter: George Baklarz
• Component/s: JDBC
• Affects Version/s: 10.0.2.0
• Fix Version/s: 10.1.1.0

Description

Sun JDBC defines getPrecision() to return either the maximum length or maximum number of digits of the column, or zero for failure (such as the precision is unknown).

http://docs.sun.com/source/816-6105-10/apicola.htm#211083

The DATE field returns 10 characters on a getPrecision() call so why doesn't TIME and TIMESTAMP give a precision length equal to the display length? Just seems inconsistent that DATE would return a precision (as well as all other data types) and not TIME nor TIMESTAMP.

Comments

[A B]

I (Army) have been looking at a couple of database metadata issues in Derby (as a follow-up to DERBY-107), and this is one that I too have noticed.

Fix is to change the "setTypeIdSpecificInstanceVariables()" method in org/apache/derby/iapi/types/TypeId.java to set the "maxMaxWidth" variable to be the correct value (namely, 8 for TIME (hh:mm:ss) and 26 for TIMESTAMP (yyyy-mm-dd hh:mm:ss.ffffff)).

Unless someone wants to create a specific patch for this, I'll submit this fix as part of another patch that I'm writing--one that will make Derby metadata return the correct value for the BUFFER_LENGTH field for builtin (including datetime) types. See the thread here for more on the BUFFER_LENGTH issue: http://mail-archives.eu.apache.org/mod_mbox/db-derby-dev/200503.mbox/[e-mail]%3e

[Myrna van Lunteren]

When this get fixed, the PRECISION field of Metadata.getTypeInfo should also be changed to match.

[Daniel John Debrunner]

While the JDBC spec does say 'length', it does not explictly say what length is being referred to. Length of the object as a String, length of the stored form of the value, maximum length of the Java serialized form of getObject or something else?
Is there any clarification in the JDBC tutorial book, or is returning NULL a better option here?

[A B]

Attaching a patch for this issue.

Since the definitions of "precision" and "scale" aren't clearly defined for datetime values in JDBC, I've set them based on the ODBC specification. It was agreed in discussion of this issue (and also of DERBY-319) that the "intent" of JDBC for these values is to mimic ODBC behavior. See the thread here for that discussion:

http://article.gmane.org/gmane.comp.apache.db.derby.devel/2786
http://article.gmane.org/gmane.comp.apache.db.derby.devel/2787

[A B]

Patch was committed with svn revision 179839. George Baklarz, if you can do an "svn update" and then confirm that the problem has been fixed, please post saying so and I will close this issue. ..

[A B]

Patch was committed with svn revision 179839. I tried to "Resolve" this issue with a comment, but something went wrong with JIRA so I don't think it went through. That said, the "resolve" option is no longer available, so I guess "close" is the next option. SO, George Baklarz, if you can do an "svn update" and then confirm that the problem has been fixed, please post saying so and I will close this issue...Thanks.

[Andrew McIntyre]

Reopening to fix resolved field in JIRA.

[Andrew McIntyre]

Patch was committed with svn revision 179839. Could Army or George please verify this is fixed and close?

[A B]

Hearing nothing more from George Baklarz, who reported this issue, I'm going to go ahead and close it...