Обсуждение: BUG #1523: precision column value returned from getTypeInfo() always has value 9

The following bug has been logged online:

Bug reference:      1523
Logged by:          Sergio Lob
Email address:      sergio_lob@iwaysoftware.com
PostgreSQL version: 8.0.1
Operating system:   linux
Description:        precision column value returned from getTypeInfo()
always has value 9
Details:

After calling Connection.DatabaseMetaData.getTypeInfo() method, the
PRECISION column of the ResultSet seems hardcoded to value 9, which of
course in incorrect

To repro:
1. connect to a PostgresSQL JDBC data source
2. Invoke Connection.getMetaData() on that connection to get
DatabaseMetaData object
3. Invoke DataBaseMetaData.getTypeInfo() to get ResultSet  of all supported
data types
4. Invoke ResultSet.next() to get to first row
5. Invoke ResultSet.getString(3) to get PRECISION column value for current
row. Notice that no matter what row is checked in the answer set, precision
of data type is ALWAYS 9.

(cc'ing -jdbc)

Sergio Lob wrote:

> After calling Connection.DatabaseMetaData.getTypeInfo() method, the
> PRECISION column of the ResultSet seems hardcoded to value 9, which of
> course in incorrect

Well, it's working as intended in the sense that there is no code to
support retrieving a real precision value -- it is indeed a hardcoded
placeholder value. I'm not sure why '9' off the top of my head, perhaps
making that column NULL would be better? What are the units of this
value anyway?

If you're willing to do the implementation work to improve this, then
post a patch to pgsql-jdbc and I can look at applying it.

-O

On Fri, 4 Mar 2005, Oliver Jowett wrote:

> > After calling Connection.DatabaseMetaData.getTypeInfo() method, the
> > PRECISION column of the ResultSet seems hardcoded to value 9, which of
> > course in incorrect
>
> Well, it's working as intended in the sense that there is no code to
> support retrieving a real precision value -- it is indeed a hardcoded
> placeholder value. I'm not sure why '9' off the top of my head, perhaps
> making that column NULL would be better? What are the units of this
> value anyway?

Compare the existing implementation of ResultSetMetaData.getPrecision.

Kris Jurka

Hi Oliver,
    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. I have never seen a definition of precision in any JDBC doc I have read, however.
According to ODBC 2.0 spec definition of precision, "the precision of a numeric column or parameter refers to the maximum number of digits used by the data type of the column or parameter. The precision of a nonnumeric column or parameter generally refers to either the maximum length or defined length of the column or parameter. The following table defines the precision for each ODBC SQL data type:

SQL Type                                             Precision
-----------                                             ----------
SQL_CHAR                                          The defined length of the column or parameter. For
SQL_VARCHAR                                  example, the precision of a column defined as CHAR(10)
                                                               is 10

SQL_LONGVARCHAR                       The maximum length of the column or parameter
                                               
SQL_DECIMAL                                    The defined number of digits. For example, the
SQL_NUMERIC                                    precision of a column defined as NUMERIC(10,3) is 10

SQL_BIT                                                1

SQL_TINYINT                                       3

SQL_SMALLINT                                   5

SQL_INTEGER                                      10

SQL_BIGINT                                          19(if signed) or 20 (if unsigned)

SQL_REAL                                             7

SQL_FLOAT                                          15
SQL_DOUBLE

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

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
                                                                   timestamp data type. For example, if the timestamp does
                                                                   not use seconds of fractional digits, the precision is 16
                                                                  (the number of characters in the "yyyy-mm-dd hh:mm"
                                                                   format). If a timestamp uses thousandths of a second,
                                                                   the precision is 23 ("yyyy-mm-dd  hh:mm:ss.fff" format)



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.
Anyway, I hope I answered your question about precision.

Sergio Lob

oliver@opencloud.com wrote:

(cc'ing -jdbc)

Sergio Lob wrote:
 

After calling Connection.DatabaseMetaData.getTypeInfo() method, the
PRECISION column of the ResultSet seems hardcoded to value 9, which of
course in incorrect   

Well, it's working as intended in the sense that there is no code to 
support retrieving a real precision value -- it is indeed a hardcoded 
placeholder value. I'm not sure why '9' off the top of my head, perhaps 
making that column NULL would be better? What are the units of this 
value anyway?

If you're willing to do the implementation work to improve this, then 
post a patch to pgsql-jdbc and I can look at applying it.

-O