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

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