Re: [BUGS] BUG #1523: precision column value returned from - Mailing list pgsql-jdbc

Um, I don't see how this is going to work. The native text format of 
timestamps etc. (what you get if you call getString() on a timestamp 
result column) is not as described in the ODBC spec, so I don't see how 
returning a precision value based on the length of the text 
representation is useful or portable, and returning a precision value 
based on a notional text representation that we don't actually use seems 
a bit weird..

What do other JDBC drivers do here? Is the returned "precision" 
consistent with their text representations of date/time types?

I'd be much happier about doing something like this if the JDBC spec at 
least said that the returned columns were meant to follow the ODBC spec. 
But it doesn't say anything at all :(

===========================

The Java 2 api spec says in description of Timestamp class method toString():

"Formats a timestamp in JDBC timestamp escape format. yyyy-mm-dd 
hh:mm:ss.fffffffff, where ffffffffff indicates nanoseconds."

The Java 2 api spec says in description of Time class method toString() that it returns a string in hh:mm:ss format. 

This is consistent with ODBC behavior and inconsistent with what you say in your statement. What format does PostgreSQL return in getString() method for a timestamp column, for example? Seems like it should return same as toString() method of a timestamp object as defined in java 2 api spec.

Regards, Sergio

Sergio Lob wrote:
 

My take then, is that for something like a CHAR data types, if the 
maximum supported precision is 32767, then that is the value that should 
be returned. Same for data types like VARCHAR and DATE/DATETIME...   

The maximum supported precision for varchar/char/etc is 10485760. We can 
return that.
 

Here are the answers to your questions:

Q1: Is this from the ODBC spec or something specific to Microsoft? (I'm 
not familiar with ODBC at all).

Answer: The ODBC standard itself was developed Microsoft Corp, so there 
is no nothing specific to Microsoft, as they wrote the standard.   

Ah, joy, the sort of standard that revolves around one implementation..
 

Q2: We can't do this in getTypeInfo() as it's describing all VARCHARs, 
not a specific one. What should we return in this case?

Answer: I can interpret this question in two ways. [...]   

No, I meant that the type-info row describes all varchar(n) types, not 
one specific varchar(42) type. If it's meant to return the maximum 
precision, that makes some sense.
 

Q5: Gah, those look pretty hairy, especially since JDBC has accessors 
specifically for date/time/timestamp -- you're not really meant to deal 
with them as text..

Answer:  This precision information of TIME/DATETIME data types is 
precisely what I need for my application to work properly. The key to 
calculating the max precision is how many fractional digits are 
supported. For instance, if timestamp data type supports down to 
microseconds, max precision is 26. If timestamp data type does not 
support fractional seconds at all, max precision is 19. The precision 
value includes separators in the character count. (eg. "yyyy-mm-dd 
hh:mm:ss" has precision 19) . Not hard to figure out....   

Um, I don't see how this is going to work. The native text format of 
timestamps etc. (what you get if you call getString() on a timestamp 
result column) is not as described in the ODBC spec, so I don't see how 
returning a precision value based on the length of the text 
representation is useful or portable, and returning a precision value 
based on a notional text representation that we don't actually use seems 
a bit weird..

What do other JDBC drivers do here? Is the returned "precision" 
consistent with their text representations of date/time types?

I'd be much happier about doing something like this if the JDBC spec at 
least said that the returned columns were meant to follow the ODBC spec. 
But it doesn't say anything at all :(
 

Q6: I'll take a look at the length issue too. What was the actual type 
of the column that was returning bad values?

DatabaseMetadata.getColumns() returns an answer set with most (if not 
all) of the VARCHAR columns described as having length of -4.  For 
instance, the first four columns of the answer set (columns "table_cat", 
"table_schem", "table_name", and "column_name") return column length -4.    

Oh, you're looking at the resultset metadata of a metadata-generated 
result set? That's a bit of a corner case, I'm not too surprised it is 
broken.

Given that there's no underlying table for these metadata result sets, 
whatever meta-meta-data we could generate is likely to be quite 
arbitary. Expect NULLs..
 

As an example, the output says that the actual name of the table (in 
column 3) has a length of -4 bytes. So my application will attempt to 
copy -4 bytes from the buffer containing the actual table name instead 
of copying the correct length.   

I do wonder why you don't just use the returned String length, though.

-O