Skip site navigation (1) Skip section navigation (2)

Re: [BUGS] BUG #1523: precision column value returned from getTypeInfo()

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Sergio_Lob(at)iwaysoftware(dot)com
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #1523: precision column value returned from getTypeInfo()
Date: 2005-03-06 22:42:26
Message-ID: 422B8752.3060300@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-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

In response to

Responses

pgsql-bugs by date

Next:From: Peter WrightDate: 2005-03-06 22:47:13
Subject: BUG #1528: Rows returned that should be excluded by WHERE clause
Previous:From: Hashem MasoudDate: 2005-03-06 20:25:47
Subject: Likely typo in FAQ_DEV.html

pgsql-jdbc by date

Next:From: Todd GeeDate: 2005-03-07 06:28:38
Subject: ident auth postgres 7.4 fedora core 3
Previous:From: Michael BarkerDate: 2005-03-05 04:39:17
Subject: Re: [Patch] JDBC3 Blob support

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group