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

Sergio Lob wrote:

>     The Microsoft ODBC 2.0 SDK guide and reference (Appendix D) contains
> a pretty thorough definition of what precision means in relation to
> various data types.

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

> SQL_CHAR                                          The defined length of
> the column or parameter. For
> SQL_VARCHAR                                  example, the precision of a
> column defined as CHAR(10)

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

> SQL_LONGVARCHAR                       The maximum length of the column
> or parameter

This is going to be about 1GB if I read it correctly (but that varies
depending on what data you put in there). Is that really a useful value
to return?

> SQL_DECIMAL                                    The defined number of
> digits. For example, the
> SQL_NUMERIC                                    precision of a column
> defined as NUMERIC(10,3) is 10

Again, we can't do this in getTypeInfo() as we're describing all
NUMERICs, not a particular one.

> SQL_BINARY                                         The defined length of
> the column or parameter. For example,
> SQL_VARBINARY                                  the precision of a column
> defined as BINARY(10) is 10
>
> SQL_LONGVARBINBARY                   The maximum length of the column or
> parameter

Same as for varchar/longvarchar/etc above.

> SQL_DATE                                              10 (the number of
> characters in yyyy-mm-dd format)
>
> SQL_TIME                                                8 (the number of
> characters in hh:mm:ss format)
>
> SQL_TIMESTAMP                                    The number of
> characters in the
>
> "yyyy-mm-dd hh:mm:ss[.f...]" format used by the [...]

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..

For the other types (int/long/etc) I will take a look at returning a
better precision value. We already do this in ResultSetMetadata, as Kris
pointed out, so it shouldn't be too painful.

> I have found problems with the answer set of other JDBC methods which
> return metadata information as well. For instance, the description of
> length values of  certain columns in the DatabaseMetadata.getColumns()
> method answer set return negative numbers (-4 ?).
> This data being crucial to applications that I have written, I have
> unforunately had to take PostgresSQL off the list of JDBC data sources
> that I can support through my application.

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

What else have you had problems with? In general the metadata code isn't
heavily used, and is poorly specified in JDBC anyway, so it hasn't had
as much work done on it as the rest of the driver. The only way it's
going to get better is if those people actually using the metadata point
out the problems in detail :)

-O