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

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

From: Sergio Lob <Sergio_Lob(at)iwaysoftware(dot)com>
To: oliver(at)opencloud(dot)com
Cc: pgsql-jdbc(at)postgresql(dot)org, Ephraim_Spravtsev(at)iwaysoftware(dot)com
Subject: Re: [BUGS] BUG #1523: precision column value returned from
Date: 2005-03-09 18:21:18
Message-ID: 422F3E9E.6080700@iwaysoftware.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-jdbc
Hi Oliver,
    Regarding your following statement:
=======================

Um, I don't see how this is going to work. The native text format of 
timestamps etc. (what you get if you call getString() on a timestamp 
result column) is not as described in the ODBC spec, so I don't see how 
returning a precision value based on the length of the text 
representation is useful or portable, and returning a precision value 
based on a notional text representation that we don't actually use seems 
a bit weird..

What do other JDBC drivers do here? Is the returned "precision" 
consistent with their text representations of date/time types?

I'd be much happier about doing something like this if the JDBC spec at 
least said that the returned columns were meant to follow the ODBC spec. 
But it doesn't say anything at all :(

===========================

The Java 2 api spec says in description of Timestamp class method toString():

"Formats a timestamp in JDBC timestamp escape format. yyyy-mm-dd 
hh:mm:ss.fffffffff, where ffffffffff indicates nanoseconds."

The Java 2 api spec says in description of Time class method toString() that it returns a string in hh:mm:ss format. 

This is consistent with ODBC behavior and inconsistent with what you say in your statement. What format does PostgreSQL return in getString() method for a timestamp column, for example? Seems like it should return same as toString() method of a timestamp object as defined in java 2 api spec.

Regards, Sergio





 



oliver(at)opencloud(dot)com wrote:

>Sergio Lob wrote:
>
>  
>
>>My take then, is that for something like a CHAR data types, if the 
>>maximum supported precision is 32767, then that is the value that should 
>>be returned. Same for data types like VARCHAR and DATE/DATETIME...
>>    
>>
>
>The maximum supported precision for varchar/char/etc is 10485760. We can 
>return that.
>
>  
>
>>Here are the answers to your questions:
>>
>>Q1: Is this from the ODBC spec or something specific to Microsoft? (I'm 
>>not familiar with ODBC at all).
>>
>>Answer: The ODBC standard itself was developed Microsoft Corp, so there 
>>is no nothing specific to Microsoft, as they wrote the standard.
>>    
>>
>
>Ah, joy, the sort of standard that revolves around one implementation..
>
>  
>
>>Q2: We can't do this in getTypeInfo() as it's describing all VARCHARs, 
>>not a specific one. What should we return in this case?
>>
>>Answer: I can interpret this question in two ways. [...]
>>    
>>
>
>No, I meant that the type-info row describes all varchar(n) types, not 
>one specific varchar(42) type. If it's meant to return the maximum 
>precision, that makes some sense.
>
>  
>
>>Q5: 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..
>>
>>Answer:  This precision information of TIME/DATETIME data types is 
>>precisely what I need for my application to work properly. The key to 
>>calculating the max precision is how many fractional digits are 
>>supported. For instance, if timestamp data type supports down to 
>>microseconds, max precision is 26. If timestamp data type does not 
>>support fractional seconds at all, max precision is 19. The precision 
>>value includes separators in the character count. (eg. "yyyy-mm-dd 
>>hh:mm:ss" has precision 19) . Not hard to figure out....
>>    
>>
>
>Um, I don't see how this is going to work. The native text format of 
>timestamps etc. (what you get if you call getString() on a timestamp 
>result column) is not as described in the ODBC spec, so I don't see how 
>returning a precision value based on the length of the text 
>representation is useful or portable, and returning a precision value 
>based on a notional text representation that we don't actually use seems 
>a bit weird..
>
>What do other JDBC drivers do here? Is the returned "precision" 
>consistent with their text representations of date/time types?
>
>I'd be much happier about doing something like this if the JDBC spec at 
>least said that the returned columns were meant to follow the ODBC spec. 
>But it doesn't say anything at all :(
>
>  
>
>>Q6: I'll take a look at the length issue too. What was the actual type 
>>of the column that was returning bad values?
>>
>>DatabaseMetadata.getColumns() returns an answer set with most (if not 
>>all) of the VARCHAR columns described as having length of -4.  For 
>>instance, the first four columns of the answer set (columns "table_cat", 
>>"table_schem", "table_name", and "column_name") return column length -4. 
>>    
>>
>
>Oh, you're looking at the resultset metadata of a metadata-generated 
>result set? That's a bit of a corner case, I'm not too surprised it is 
>broken.
>
>Given that there's no underlying table for these metadata result sets, 
>whatever meta-meta-data we could generate is likely to be quite 
>arbitary. Expect NULLs..
>
>  
>
>>As an example, the output says that the actual name of the table (in 
>>column 3) has a length of -4 bytes. So my application will attempt to 
>>copy -4 bytes from the buffer containing the actual table name instead 
>>of copying the correct length.
>>    
>>
>
>I do wonder why you don't just use the returned String length, though.
>
>-O
>  
>

In response to

Responses

pgsql-bugs by date

Next:From: Oliver JowettDate: 2005-03-09 21:10:25
Subject: Re: [BUGS] BUG #1523: precision column value returned from
Previous:From: Josh BerkusDate: 2005-03-09 17:49:30
Subject: Re: [pgsql-bugs] Daily digest v1.1387 (8 messages)

pgsql-jdbc by date

Next:From: Mican BicanDate: 2005-03-09 19:08:01
Subject: Re: java is locked when select for update
Previous:From: Csaba NagyDate: 2005-03-09 17:29:11
Subject: Re: [JDBC] java is locked when select for update

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