Re: Error in DatabaseMetaData.getColumns() with Views

On Fri, 2 Jul 2004, Dario Fassi wrote:

> Hi, I wish to report a erroneous information returned by
> DatabaseMetaData.getColumns() method.
>
> This happens with ResultSet's column (7) COLUMN_SIZE and (9)
> DECIMAL_DIGITS ,
> when DatabaseMetaData.getColumns() inform about a VIEW with columns
> formed with :
> coalesce , case or numeric operations over DECIMAL fields.
>
> Suppose
>
> CREATE TABLE A ( f1 DEC(6,3), f2 dec(6,3) );
> CREATE VIEW B as ( select ( f1 + f2 ) as f from a;
>
> Then DatabaseMetaData.getColumns() returns:
>
> VIEW B
> F NUMERIC( 65535 , -65531 )
>

The problem is that f1+f2 does not retain the numeric(6,3) size
restriction, but turns into an unbounded plain "numeric" datatype. So
when retrieving this data the precision/scale are unavailable and the
unreasonable values you see are returned. We could return NULL instead,
but I'm not sure that would be much more helpful if the client is
expecting real values. Any other ideas?

Kris Jurka