Thread: Error in DatabaseMetaData TableColumn lenght.dec information

Error in DatabaseMetaData TableColumn lenght.dec information

From
"Dario V. Fassi"
Date:
Hi, I whish 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 )

This error its very molest because if you are trying to do CodeGeneration tools from DatabaseMetaData , you get lost.

Any suggestion will be appreciated.
Best regards,

    Dario V. Fassi


SISTEMATICA ingenieria de software  srl
Ituzaingo 1628  (2000)  Rosario, Santa Fe, Argentina.
Tel / Fax:  +54 (341) 485.1432 / 485.1353



Re: Error in DatabaseMetaData TableColumn lenght.dec

From
Kris Jurka
Date:

On Fri, 2 Jul 2004, Dario V. Fassi wrote:

> Hi, I whish to report a erroneous information returned by
> DatabaseMetaData.getColumns()  method.
>
> 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 )
>

I've modified the cvs version of the driver to return numeric(1000,1000)
which is the maximum precision and scale you are allowed to define.  A
more complete solution is outside the realm of the JDBC driver.

Kris Jurka


Re: Error in DatabaseMetaData TableColumn lenght.dec

From
Tom Lane
Date:
Kris Jurka <books@ejurka.com> writes:
> I've modified the cvs version of the driver to return numeric(1000,1000)
> which is the maximum precision and scale you are allowed to define.  A
> more complete solution is outside the realm of the JDBC driver.

That doesn't sound like a usable answer at all.  A client that believes
this result will think that the value cannot contain any digits to the
left of the decimal point --- which is certainly not so.

Does the JDBC spec allow you to return NULL for these entries?  That's
what we recently fixed the information_schema views to do for
unconstrained numeric columns.

            regards, tom lane

Re: Error in DatabaseMetaData TableColumn lenght.dec information

From
"Dario V. Fassi"
Date:


Kris Jurka wrote:
On Fri, 2 Jul 2004, Dario V. Fassi wrote:
 
Hi, I whish to report a erroneous information returned by   
DatabaseMetaData.getColumns()  method.

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 )
   
I've modified the cvs version of the driver to return numeric(1000,1000) 
which is the maximum precision and scale you are allowed to define.  A 
more complete solution is outside the realm of the JDBC driver.

Kris Jurka 
Kris,

A return value of numeric(1000,1000) , is far better than  numeric(65535,-65531) , but this mean a number with all the presicion in the fractional part and is not very usefull.

Why not a more login value like    "Numeric(100,50)"  , yet oversized but more appropiate.
Can you imagine a number with 1000 digit's presicion, it's impractical and undisplayable.

Dario Fassi.

Re: Error in DatabaseMetaData TableColumn lenght.dec

From
Kris Jurka
Date:

On Sat, 10 Jul 2004, Tom Lane wrote:

> Kris Jurka <books@ejurka.com> writes:
> > I've modified the cvs version of the driver to return numeric(1000,1000)
> > which is the maximum precision and scale you are allowed to define.  A
> > more complete solution is outside the realm of the JDBC driver.
>
> That doesn't sound like a usable answer at all.  A client that believes
> this result will think that the value cannot contain any digits to the
> left of the decimal point --- which is certainly not so.

I blew this one.  For some reason I was thinking 1000, 1000 was 1000 to
the left and right of the decimal.

> Does the JDBC spec allow you to return NULL for these entries?  That's
> what we recently fixed the information_schema views to do for
> unconstrained numeric columns.

No, the method returns "int" which is a primitive type and therefore can't
be null.  Maybe -1?  I'm not sure what the best answer is.

Kris Jurka